Feature

Oracle querying for developers

Michael McLaughlin, Oracle ACE

This chapter shows you how to work with column returns from Oracle queries and how to join tables into multiple table result sets. Result sets are like tables because they’re two-dimensional data sets. Read on to learn highlights as to why that’s important, how to alleviate your querying pain points, and other important tricks of the trade.

 

Query Results

A SELECT statement (query) reads differently from how it acts. In English, a query selects something from a table, or a set of tables, where certain conditions are true or untrue. Translating that English sentence into programming instructions is the beauty and complexity of SQL. Although English seems straightforward, queries work in a different event order. The event order also changes with different types of queries.


Queries can be divided into three basic types:

  • Queries that return columns or results from columns
  • Queries that aggregate, or queries that return columns or results from columns by adding, averaging, or counting between rows
  • order
  • Queries that return columns or results selectively (filtered by conditional expressions such as if statements), and these types of queries may or may not aggregate result sets

You can return column values or expressions in the SELECT list. Column values are straightforward, because they’re the values in a column. Expressions aren’t quite that simple. Expressions are the results from calculations. Some calculations involve columns and string literal values, such as concatenated results (strings joined together to make a big string), parsed results (substrings), or the mathematical result of the columns, literal values, and function returns. Mathematical results can be calculated on numeric or date data types and returned as function results from several built-in functions in both databases.

 

You can also be selective in your SELECT list, which means you can perform if-then-else logic in any column. The selectivity determines the resulting value in the final result set. Result sets are also formally called aggregate results because they’ve been assembled by SELECT statements.
Here’s the basic prototype for a SELECT list:

SELECT {column_name | literal_value | expression } AS alias [, {...}]]
WHERE [NOT] column_name {{= | <> | > | >= | < | <=} |
[NOT] {{IN | EXISTS} | IS NULL}} 'expression'
[{AND | OR } [NOT] comparison_operation] [...];

You can return three things as an element in the SELECT list: a column value from a table or view, a literal value, and an expression. The column value is easy to understand, because it’s the value from the column—but what is its data type? A column returns the value in its native data type when you call the query from a procedural programming language, such as C, C#, C++, Java, PL/SQL, or SQL/PSM, or as a subquery. Subqueries are queries within queries and are covered in the “Subqueries” section later in this chapter. A column returns a string when you call the query from SQL*Plus or MySQL Monitor, and it is written to a console or a file. Literal values must have a column alias when you want to reuse the value in a procedural program or as a subquery result, and in those cases the values must be a string or number. Expressions are more difficult because they’re the result of processing operations, such as concatenation or calculation, or they return results from built-in or user-defined functions.

The next three examples show you how the types of queries work. All examples use queries from a single table to let you focus on the differences between types.

Queries that Return Columns or Results from Columns

Figure 11-1 shows how a query returns a result set of column values in the SELECT list. You can see how the elements are labeled and processed and the figure helps you visualize table aliases, column aliases, basic comparison operations, and the basic order of clauses within the SELECT statement.

The following list qualifies the ANSI SQL pattern for processing a single table query:

  • It finds a table in the FROM clause.
  • It optionally assigns a table alias as a runtime placeholder for the table name.
  • It gets the table definition from the data catalog to determine the valid column names (not shown in the figure because it’s a hidden behavior).
  • If a table alias is present (and it is), it optionally maps the alias to the table’s data catalog definition.
  • It filters rows into the result set based on the value of columns in the WHERE clause.
  • The list of columns in the SELECT clause filters the desired columns from the complete set of columns in a row.
  • If an ORDER BY clause occurs in the query, rows are sorted by the designated columns.

Figure 11-1 also demonstrates table and column aliases. The table alias is generally unnecessary when writing a query against a single table. It is useful and necessary when you want to avoid typing complete table names to disambiguate column names that are the same in two or more tables. Because the FROM clause is read first, all references to the item table are mapped to i in the rest of the query. This means that a reference to item.item_title would not be found.

TIP
The AS keyword is optional when setting column aliases but ensures clarity that an alias follows it. Consistent use increases typing but decreases support costs.

Column aliases shorten the item_title and item_rating column names to title and rating, respectively. Aliases let you use shorter or more descriptive words for columns in a specific use case. Sometimes the shorter words aren’t appropriate as column names because they’re too general, such as title. The AS keyword is optional in both Oracle and MySQL databases, but I recommend that you use it, because the clarity can simplify maintenance of queries. Just note that AS works only with column aliases and would create a statement parsing error if you tried to use it before a table alias.

NOTE
The AS keyword cannot precede a table alias in Oracle; it can precede only a column alias MySQL supports an AS keyword for a table alias.

In our example, we can modify the SELECT list to return an expression by concatenating a string literal of “MPAA:” (Motion Picture Association of America) to the item_rating column.

 

table_11_1

Concatenating strings is like gluing them together to form a big string. It would look like this in Oracle using a piped concatenation:

SELECT    i.item_title AS title
, 'MPAA: ' || i.item_rating AS rating

The two vertical bars (||) are pipes, and when you use them to glue strings together, it’s known as piped concatenation. MySQL doesn’t support piped concatenation, so you use a built-in function to glue strings together, like so:

SELECT    i.item_title AS title
, CONCAT('MPAA: ',i.item_rating) AS rating

The CONCAT built-in function returns a string by concatenating the call parameters sequentially. Notice that literals and column values are equal call parameters to the function. Both of these would return results like these:

TITLE                       RATING
------------------------- ----------
Star Wars I MPAA:          PG
Star Wars II MPAA:         PG
Star Wars III MPAA:        PG

In another context, you can perform mathematical operations and string formatting. The following SELECT list retrieves a transaction_date and a transaction_amount column from the transaction table:

SQL> SELECT t.transaction_date
2 , TO_CHAR(t.transaction_amount,'90.00') AS price
3 , TO_CHAR(t.transaction_amount * .0875,'90.00') AS tax
4 , TO_CHAR(t.transaction_amount * 1.0875,'90.00') AS total
5 FROM transaction t
6 WHERE t.transaction_date = '10-JAN-2009';

The TO_CHAR function formats the final number as a string. The 90.00 format mask instructs the display as follows: a 9 means display a number when present and ignore the number when it is not present; 0 means display a number when it is present and display a 0 when no number is present. Inside the TO_CHAR function on lines 3 and 4, the column value is multiplied by numeric literals that represent sales tax and price plus sales tax. The query would produces output like so:

 Date       Price   Tax   Total
--------- ------ ------  ------
10-JAN-09  9.00    0.79    9.79
10-JAN-09  3.00    0.26    3.26
10-JAN-09  6.00    0.53    6.53

The output is left-aligned, which means it’s formatted as a number, because strings are displayed as right-aligned.

The equivalent formatting function in MySQL is the FORMAT function, which performs like this:

, FORMAT(t.transaction_amount * .0875,2) AS tax

The FROM clause takes a single table or a comma-separated list of tables when writing queries in ANSI SQL-89 format. The FROM clause takes tables separated by join keywords and their join criterion or criteria in ANSI SQL-92 syntax.

The WHERE clause performs two types of comparisons. One is an equality comparison of two values, which can come from columns, literals, or expressions. The other is an inequality comparison, which can check when one value is found in another (such as a substring of a larger string); when one value is greater than, greater than or equal to, less than, or less than or equal to another; when one value isn’t equal to another value; when one value is in a set of values; or when one value is between two other values. You can also state a negative comparison, such as WHERE NOT. The WHERE NOT comparison acts like a not equal to operation.

Two specialized operators let you limit the number of rows returned by a query. Oracle supports a ROWNUM pseudo column and MySQL supports the LIMIT function. You use ROWNUM in Oracle to retrieve only the top five rows, like this:

WHERE rownum <= 6;

Here’s the LIMIT function in MySQL:

WHERE LIMIT 5;

These are handy tools when you’ve presorted the data and know where to cut off the return set. When you forget to sort, the results generally don’t fit what you’re looking for.

The data set in the table determines whether the query returns unique or non-unique data—that is, there could be multiple rows with an item_title of “Star Wars: A New Hope,” and they would be returned because they match the criteria in the WHERE clause. You can use the DISTINCT operator to suppress duplicates without altering the logic of the WHERE clause (see Figure 11-2).

table 11_2

There is no difference between Oracle and MySQL when using the DISTINCT operator, which sorts the set to return a unique set—one copy of every row. Other than an incremental sort and disposal of duplicate rows, the query in Figure 11-2 performs more or less the same steps as the query shown in Figure 11-1. 


This was first published in December 2011

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: