Chapter 4: SQL Language Elements
4.4 Query Expressions
Description
A query expression selects the specified column values from one or more rows contained in one or more tables specified in the FROM clause. The selection of rows is restricted by a search condition in the WHERE clause. The temporary table derived through the clauses of a select statement is called a result table.
Query expressions form the basis of other SQL statements and syntax elements:
- SELECT statements are query expressions with optional ORDER BY and FOR UPDATE clauses.
- CREATE VIEW statements specify their result table as a query expression.
- INSERT statements can specify a query expression to add the rows of the result table to a table.
- UPDATE statements can specify a query expression that returns a single row to modify columns of a row.
- Some search conditions can specify query expressions. Basic predicates can specify query expressions, but the result table can contain only a single value. Quantified and IN predicates can specify query expressions, but the result table can contain only a single column.
- The FROM clause of a query expression can itself specify a query expression, called a derived table.
Syntax
query_expression ::
query_specification
| query_expression set_operator query_expression
| ( query_expression )
set_operator ::
{ UNION [ ALL ] | INTERSECT | MINUS }
query_specification ::
SELECT [ALL | DISTINCT]
{
*
| { table_name | alias } . * [, { table_name | alias } . * ] …
| expr [ [ AS ] [ ' ] column_title [ ' ] ] [, expr [ [ AS ] [ ' ] column_title [ ' ] ] ] ...
}
FROM table_ref [ { dharma ORDERED } ] [ , table_ref [ { dharma ORDERED } ] …
[ WHERE search_condition ]
[ GROUP BY [table.]column_name [ COLLATE collation-name ]
[, [table.]column_name [ COLLATE collation-name ] ] ...
[ HAVING search_condition ]
table_ref ::
table_name [ AS ] [ alias [ ( column_alias [ , … ] ) ] ]
| ( query_expression ) [ AS ] alias [ ( column_alias [ , … ] ) ]
| [ ( ] joined_table [ ) ]
joined_table ::
table_ref CROSS JOIN table_ref
| table_ref [ INNER | LEFT [ OUTER ] ] JOIN table_ref ON search_condition
Arguments
SELECT [ ALL | DISTINCT ]DISTINCT specifies that the result table omits duplicate rows. ALL is the default, and specifies that the result table includes all rows.
SELECT * | { table_name | alias } . *Specifies that the result table includes all columns from all tables named in the FROM clause. For instance, the following examples both specify all the columns in the customers table:
SELECT * FROM customers; SELECT customers.* FROM customers;
The tablename.* syntax is useful when the select list refers to columns in multiple tables, and you want to specify all the columns in one of those tables:
SELECT CUSTOMERS.CUSTOMER_ID, CUSTOMERS.CUSTOMER_NAME, ORDERS.*
FROM CUSTOMERS, ORDERS …
SELECT expr [ [ AS ] [ ' ] column_title [ ' ] ]Specifies a list of expressions, called a select list, whose results will form columns of the result table. Typically, the expression is a column name from a table named in the FROM clause. The expression can also be any supported mathematical expression, scalar function, or aggregate function that returns a value.
The optional ' column_title ' argument specifies a new heading for the associated column in the result table. Enclose the new title in single or double quotation marks if it contains spaces or other special characters:
SELECT order_value, order_value * .2 AS 'order "markup"' FROM orders;
ORDER_VALUE ORDER "MARKUP"
----------- --------------
5000000.00 1000000.00
110000.00 22000.00
3300000.00 660000.00
You can qualify column names with the name of the table they belong to:
SELECT CUSTOMER.CUSTOMER_ID FROM CUSTOMERS
You must qualify a column name if it occurs in more than one table specified in the FROM clause:
SELECT CUSTOMERS.CUSTOMER_ID
FROM CUSTOMERS, ORDERS
Qualified column names are always allowed even when they are not required.
FROM table_ref …The FROM clause specifies one or more table references. Each table reference resolves to one table (either a table stored in the database or a virtual table resulting from processing the table reference) whose rows the query expression uses to create the result table. There are three forms of table references:
- A direct reference to a table, view or synonym
- A derived table specified by a query expression in the FROM clause
- A joined table that combines rows and columns from multiple tables
The usage notes specific to each form of table reference follow.
If there are multiple table references, SQL joins the tables to form an intermediate result table that is used as the basis for evaluating all other clauses in the query expression. That intermediate result table is the Cartesian product of rows in the tables in the FROM clause, formed by concatenating every row of every table with all other rows in all tables.
FROM table_name [ AS ] [ alias [ ( column_alias [ , … ] ) ] ]Explicitly names a table. The name listed in the FROM clause can be a table name, a view name, or a synonym.
The alias is a name you use to qualify column names in other parts of the query expression. Aliases are also called correlation names.
If you specify an alias, you must use it, and not the table name, to qualify column names that refer to the table. Query expressions that join a table with itself must use aliases to distinguish between references to column names.
For example, the following query expression joins the table customer with itself. It uses the aliases x and y and returns information on customers in the same city as customer SMITH:
SELECT y.cust_no, y.name
FROM customer x, customer y
WHERE x.name = 'SMITH'
AND y.city = x.city ;
Similar to table aliases, the column_alias provides an alternative name to use in column references elsewhere in the query expression. If you specify column aliases, you must specify them for all the columns in table_name. Also, if you specify column aliases in the FROM clause, you must use them—not the column names—in references to the columns.
FROM ( query_expression ) [ AS ] alias [ ( column_alias [ , … ] ) ]Specifies a derived table through a query expression. With derived tables, you must specify an alias to identify the derived table.
Derived tables can also specify column aliases. Column aliases provides an alternative name to use in column references elsewhere in the query expression. If you specify column aliases, you must specify them for all the columns in the result table of the query expression. Also, if you specify column aliases in the FROM clause, you must use them, and not the column names, in references to the columns.
FROM [ ( ] joined_table [ ) ]Combines data from two table references by specifying a join condition. The syntax currently allowed in the FROM clause supports only a subset of possible join conditions:
- CROSS JOIN specifies a Cartesian product of rows in the two tables
- INNER JOIN specifies an inner join using the supplied search condition
- LEFT OUTER JOIN specifies a left outer join using the supplied search condition
You can also specify these and other join conditions in the WHERE clause of a query expression. See "Inner Joins" and "Outer Joins" for more detail on both ways of specifying joins.
{ dharma ORDERED }Directs the SQL engine optimizer to join the tables in the order specified. Use this clause when you want to override the SQL engine's join-order optimization. This is useful for special cases when you know that a particular join order will result in the best performance from the underlying storage system. Since this clause bypasses join-order optimization, carefully test queries that use it to make sure the specified join order is faster than relying on the optimizer.
Note that the braces ( { and } ) are part of the required syntax and not syntax conventions.
SELECT sc.tbl 'Table', sc.col 'Column',
sc.coltype 'Data Type', sc.width 'Size'
FROM systpe.syscolumns sc, systpe.systables st
{ dharma ORDERED }
WHERE sc.tbl = st.tbl AND st.tbltype = 'S'
ORDER BY sc.tbl, sc.col;
WHERE search_conditionThe WHERE clause specifies a search_condition that applies conditions to restrict the number of rows in the result table. If the query expression does not specify a WHERE clause, the result table includes all the rows of the specified table reference in the FROM clause.
The search_condition is applied to each row of the result table set of the FROM clause. Only rows that satisfy the conditions become part of the result table. If the result of the search_condition is NULL for a row, the row is not selected.
Search conditions can specify different conditions for joining two or more tables. See "Inner Joins" on page 4-20 and "Outer Joins" on page 4-23 for more details.
See "Search Conditions" on page 4-24 for details on the different kinds of search conditions.
SELECT *
FROM customer
WHERE city = 'BURLINGTON' AND state = 'MA' ;
SELECT *
FROM customer
WHERE city IN (
SELECT city
FROM customer
WHERE name = 'SMITH') ;
GROUP BY column_name ...Specifies grouping of rows in the result table:
- For the first column specified in the GROUP BY clause, SQL arranges rows of the result table into groups whose rows all have the same values for the specified column.
- If a second GROUP BY column is specified, SQL then groups rows in each main group by values of the second column.
- SQL groups rows for values in additional GROUP BY columns in a similar fashion.
All columns named in the GROUP BY clause must also be in the select list of the query expression. Conversely, columns in the select list must also be in the GROUP BY clause or be part of an aggregate function.
If column_name refers to a character column, the column reference can include an optional COLLATE clause. The COLLATE clause specifies a collation sequence supported by the underlying storage system. (See "Specifying the Character Set for Character Data Types" on page 4-6 for notes on character sets and collations. See the documentation for your underlying storage system for details on any supported collations.)
HAVING search_conditionThe HAVING clause allows conditions to be set on the groups returned by the GROUP BY clause. If the HAVING clause is used without the GROUP BY clause, the implicit group against which the search condition is evaluated is all the rows returned by the WHERE clause.
A condition of the HAVING clause can compare one aggregate function value with another aggregate function value or a constant.
-- select customer number and number of orders for all
-- customers who had more than 10 orders prior to
-- March 31st, 1991.
SELECT cust_no, count(*)
FROM orders
WHERE order_date < to_date ('3/31/1991')
GROUP BY cust_no
HAVING count (*) > 10 ;
UNION [ALL]Appends the result table from one query expression to the result table from another.
The two query expressions must have the same number of columns in their result table, and those columns must have the same or compatible data types.
The final result table contains the rows from the second query expression appended to the rows from the first. By default, the result table does not contain any duplicate rows from the second query expression. Specify UNION ALL to include duplicate rows in the result table.
-- Get a merged list of customers and suppliers.
SELECT name, street, state, zip
FROM customer
UNION
SELECT name, street, state, zip
FROM supplier ;
-- Get a list of customers and suppliers
-- with duplicate entries for those customers who are
-- also suppliers.
SELECT name, street, state, zip
FROM customer
UNION ALL
SELECT name, street, state, zip
FROM supplier ;
INTERSECTLimits rows in the final result table to those that exist in the result tables from both query expressions.
The two query expressions must have the same number of columns in their result table, and those columns must have the same or compatible data types.
-- Get a list of customers who are also suppliers.
SELECT name, street, state, zip
FROM customer
INTERSECT
SELECT name, street, state, zip
FROM supplier ;
MINUSLimits rows in the final result table to those that exist in the result table from the first query expression minus those that exist in the second. In other words, the MINUS operator returns rows that exist in the result table from the first query expression but that do not exist in the second.
The two query expressions must have the same number of columns in their result table, and those columns must have the same or compatible data types.
-- Get a list of suppliers who are not customers.
SELECT name, street, state, zip
FROM supplier ;
MINUS
SELECT name, street, state, zip
FROM customer;
Authorization
The user executing a query expression must have any of the following privileges:
- DBA privilege
- SELECT permission on all the tables/views referred to in the query_expression.
- SQL Compliance
SQL-92. Extensions: { dharma ORDERED }clause, MINUS set operator
- Environment
Embedded SQL, interactive SQL, ODBC applications
CREATE TABLE, CREATE VIEW, INSERT, Search Conditions, SELECT, UPDATE
4.4.1 Inner Joins
Description
Inner joins specify how the rows from one table reference are to be joined with the rows of another table reference. Inner joins usually specify a search condition that limits the number of rows from each table reference that become part of the result table generated by the inner join operation.
If an inner join does not specify a search condition, the result table from the join operation is the Cartesian product of rows in the tables, formed by concatenating every row of one table with every row of the other table. Cartesian products (also called cross products or cross joins) are not practically useful, but SQL logically processes all join operations by first forming the Cartesian products of rows from tables participating in the join.
If specified, the search condition is applied to the Cartesian product of rows from the two tables. Only rows that satisfy the search condition become part of the result table generated by the join.
A query expression can specify inner joins in either its FROM clause or in its WHERE clause. For each formulation in the FROM clause, there is an equivalent syntax formulation in the WHERE clause. Currently, not all syntax specified by the SQL-92 standard is allowed in the FROM clause.
Syntax
from_clause_inner_join ::
| FROM table_ref CROSS JOIN table_ref
| FROM table_ref [ INNER ] JOIN table_ref ON search_condition
where_clause_inner_join ::
FROM table_ref, table_ref WHERE search_condition
Arguments
FROM table_ref CROSS JOIN table_refExplicitly specifies that the join generates the Cartesian product of rows in the two table references. This syntax is equivalent to omitting the WHERE clause and a search condition. The following queries illustrate the results of a simple CROSS JOIN operation and an equivalent formulation that does not use the CROSS JOIN syntax:
SELECT * FROM T1; -- Contents of T1
C1 C2
-- --
10 15
20 25
2 records selected
SELECT * FROM T2; -- Contents of T2
C3 C4
-- --
10 BB
15 DD
2 records selected
SELECT * FROM T1 CROSS JOIN T2; -- Cartesian product
C1 C2 C3 C4
-- -- -- --
10 15 10 BB
10 15 15 DD
20 25 10 BB
20 25 15 DD
4 records selected
SELECT * FROM T1, T2; -- Different formulation, same results
C1 C2 C3 C4
-- -- -- --
10 15 10 BB
10 15 15 DD
20 25 10 BB
20 25 15 DD
4 records selected
FROM table_ref [ INNER ] JOIN table_ref ON search_condition FROM table_ref, table_ref WHERE search_conditionThese two equivalent syntax constructions both specify search_condition for restricting rows that will be in the result table generated by the join. In the first format, INNER is optional and has no effect. There is no difference between the WHERE form of inner joins and the JOIN ON form.
Equi-joins
An equi-join specifies that values in one table equal some corresponding column's values in the other:
-- For customers with orders, get their name and order info, :
SELECT customer.cust_no, customer.name,
orders.order_no, orders.order_date
FROM customers INNER JOIN orders
ON customer.cust_no = orders.cust_no ;
-- Different formulation, same results:
SELECT customer.cust_no, customer.name,
orders.order_no, orders.order_date
FROM customers, orders
WHERE customer.cust_no = orders.cust_no ;
Self joins
A self join, or auto join, joins a table with itself. If a WHERE clause specifies a self join, the FROM clause must use aliases to have two different references to the same table:
-- Get all the customers who are from the same city as customer SMITH:
SELECT y.cust_no, y.name
FROM customer AS x INNER JOIN customer AS y
ON x.name = 'SMITH' AND y.city = x.city ;
-- Different formulation, same results:
SELECT y.cust_no, y.name
FROM customer x, customer y
WHERE x.name = 'SMITH' AND y.city = x.city ;
4.4.2 Outer Joins
Description
An outer join between two tables returns more information than a corresponding inner join. An outer join returns a result table that contains all the rows from one of the tables even if there is no row in the other table that satisfies the join condition.
In a left outer join, the information from the table on the left is preserved: the result table contains all rows from the left table even if some rows do not have matching rows in the right table. Where there are no matching rows in the left table, SQL generates null values.
In a right outer join, the information from the table on the right is preserved: the result table contains all rows from the right table even if some rows do not have matching rows in the left table. Where there are no matching rows in the right table, SQL generates null values.
SQL supports two forms of syntax to support outer joins:
- In the WHERE clause of a query expression, specify the outer join operator (+) after the column name of the table for which rows will not be preserved in the result table. Both sides of an outer-join search condition in a WHERE clause must be simple column references. This syntax is similar to Oracle's SQL syntax, and allows both left and right outer joins.
- For left outer joins only, in the FROM clause, specify the LEFT OUTER JOIN clause between two table names, followed by a search condition. The search condition can contain only the join condition between the specified tables.
Dharma's SQL implementation does not support full (two-sided) outer joins.
Syntax
from_clause_inner_join ::
FROM table_ref LEFT OUTER JOIN table_ref ON search_condition
where_clause_inner_join ::
WHERE [table_name.]column (+) = [table_name.]column
| WHERE [table_name.]column = [table_name.]column (+)
Examples
The following example shows a left outer join. It displays all the customers with their orders. Even if there is not a corresponding row in the orders table for each row in the customer table, NULL values are displayed for the orders.order_no and orders.order_date columns.
SELECT customer.cust_no, customer.name, orders.order_no,
orders.order_date
FROM customers, orders
WHERE customer.cust_no = orders.cust_no (+) ;
The following series of examples illustrates the outer join syntax:
SELECT * FROM T1; -- Contents of T1
C1 C2
-- --
10 15
20 25
2 records selected
SELECT * FROM T2; -- Contents of T2
C3 C4
-- --
10 BB
15 DD
2 records selected
-- Left outer join
SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.C1 = T2.C3;
C1 C2 C3 C4
-- -- -- --
10 15 10 BB
20 25
2 records selected
-- Left outer join: different formulation, same results
SELECT * FROM T1, T2 WHERE T1.C1 = T2.C3 (+);
C1 C2 C3 C4
-- -- -- --
10 15 10 BB
20 25
2 records selected
-- Right outer join
SELECT * FROM T1, T2 WHERE T1.C1 (+) = T2.C3;
C1 C2 C3 C4
-- -- -- --
10 15 10 BB
15 DD
2 records selected