Subqueries and Query Expressions - The SQL2 Query Specification
(Page 11 of 12 )
Figure 9-16. SQL2 query specification: formal definition
The SQL2 standard formalizes the definition of what we have loosely been calling a SELECT statement or a query in the last three chapters into a basic building block called a query specification. For a complete understanding of the SQL2 table expression capabilities in the next section, it’s useful to understand this formal definition. The form of a SQL2 query specification is shown in Figure 9-16. Its components should be familiar from the earlier chapters:
- A select list specifies the columns of query results. Each column is specified by an expression that tells the DBMS how to calculate its value. The column can be assigned an optional alias with the AS clause.
- The keywords ALL or UNIQUE control duplicate row elimination in the query results.
- The FROM clause specifies the tables that contribute to the query results.
- The WHERE clause describes how the DBMS should determine which rows are included in the query results and which should be discarded.
- The GROUP BY and HAVING clauses together control the grouping of individual query results rows in a grouped query, and the selection of row groups for inclusion or exclusion in the final results.
The query specification is the basic query building block in the SQL2 standard. Conceptually, it describes the process of combining data from the tables in the FROM clause into a row/column table of query results. The value of the query specification is a table of data. In the simplest case, a SQL2 query consists of a simple query specification. In a slightly more complex case, a query specification is used to describe a subquery, which appears within another (higher-level) query specification. Finally, query specifications can be combined using table-valued operations to form general-purpose query expressions, as described in the next section.
Query Expressions (SQL2) The SQL2 standard defines a query expression as the full, general-purpose way that you can specify a table of query results in the SQL2 language. The basic building blocks you can use to create a query expression are the following:
- A query specification, as described in the preceding section (SELECT…FROM…). Its value is a table of query results.
- A table-value constructor, as previously described (VALUES …). Its value is a table of constructed values.
- An explicit table reference (TABLE tblname). Its value is the contents of the named table.
Using these building blocks, SQL2 lets you combine their table values using the following operations:
- JOIN. SQL2 provides explicit support for full cross-product joins (cross joins), natural joins, inner joins, and all types of outer joins (left, right, and full), as described in Chapter 7. A JOIN operation takes two tables as its input and produces a table of combined query results according to the join specification.
- UNION. The SQL2 UNION operation provides explicit support for merging the rows of two compatible tables (that is, two tables having the same number of columns and with corresponding columns having the same data types). The UNION operation takes two tables as its input and produces a single merged table of query results.
- DIFFERENCE. The SQL2 EXCEPT operation takes two tables as its input and produces as its output a table containing the rows that appear in the first table but that do not appear in another table—that is, the rows that are missing from the second table. Conceptually, the EXCEPT operation is like table subtraction. The rows of the second table are taken away from the rows of the first table, and the answer is the remaining rows of the first table.
- INTERSECT. The SQL2 INTERSECT operation takes two tables as its input and produces as its output a table containing the rows that appear in both input tables.
SQL2 UNION, INTERSECT, and DIFFERENCE Operations
The SQL2 UNION, INTERSECT, and DIFFERENCE operations provide set operations for combining two input tables to form an output table. All three of the operations require that the two input tables be union-compatible—they must have the same number of columns, and the corresponding columns of each table must have identical data types. Here are some simple examples of SQL2 query expressions involving UNION, INTERSECT, and DIFFERENCE operations based on the sample database:
Show all products for which there is an order over $30,000 or more than $30,000 worth of inventory on hand.
(SELECT MFR, PRODUCT
FROM ORDERS
WHERE AMOUNT > 30000.00)
UNION
(SELECT MFR_ID, PRODUCT_ID
FROM PRODUCTS
WHERE (PRICE * QTY_ON_HAND) > 30000)
Show all products for which there is an order over $30,000 and more than $30,000 worth of inventory on hand.
(SELECT MFR, PRODUCT
FROM ORDERS
WHERE AMOUNT > 30000.00)
INTERSECT
SELECT MFR_ID, PRODUCT_ID
FROM PRODUCTS
WHERE (PRICE * QTY_ON_HAND > 30000)
Show all products for which there is an order over $30,000 except for those products that sell for under $1000.
(SELECT MFR, PRODUCT
FROM ORDERS
WHERE AMOUNT > 30000.00)
EXCEPT
(SELECT MFR_ID, PRODUCT_ID
FROM PRODUCTS
WHERE PRICE < 100.00)
By default, the UNION, INTERSECT, and EXCEPT operations eliminate duplicate rows during their processing. This is usually the desired result, as it is in these examples, but occasionally, you may need to suppress the elimination of duplicate rows. You can do this by specifying the UNION ALL, INTERSECT ALL,or EXCEPT ALL forms of the operations.
Note each of these examples produces a two-column table of query results. The results come from two different source tables within the database—the ORDERS table and the PRODUCTS table. However, the columns selected from these tables have the same corresponding data types, so they can be combined using these operations. In the sample database, the corresponding columns have different names in the two tables. (The manufacturer-ID column is named MFR in the ORDERS table but named MFR_ID in the PRODUCTS table.)
However, corresponding columns such as these will often have the same name in each of the tables being combined. As a convenience, SQL2 lets you specify the corresponding columns in a CORRESPONDING clause attached to the UNION, INTERSECT, or EXCEPT operation. Here is the preceding UNION example, changed for the situation where the ORDERS and PRODUCTS tables have parallel column names for manufacturer ID and product ID:
Show all products for which there is an order over $30,000 or more than $30,000 worth of nventory on hand.
(SELECT *
FROM ORDERS
WHERE AMOUNT > 30000.00)
UNION CORRESPONDING BY (MFR, PRODUCT)
(SELECT *
FROM PRODUCTS
WHERE (PRICE * QTY_ON_HAND) > 30000)
In a case like this one where all of the corresponding (that is, identically named) columns from the two tables participate in the UNION operation, SQL2 even allows you to leave off the explicit list of column names:
Show all products for which there is an order over $30,000 or more than $30,000 worth of inventory on hand.
(SELECT *
FROM ORDERS
WHERE AMOUNT > 30000.00)
UNION CORRESPONDING
(SELECT *
FROM PRODUCTS
WHERE (PRICE * QTY_ON_HAND) > 30000)
Finally, it’s worth noting that the column alias capability of the query specification can be used to rename or assign names to the columns from the individual query results that are being combined with the
UNION operation. If we eliminate the assumption that the PRODUCTS and ORDERS tables use the same column names, it’s still possible to use the CORRESPONDING form of the UNION operation in this query simply by renaming the columns in one of the tables:Show all products for which there is an order over $30,000 or more than $30,000 worth of inventory on hand.
SELECT *
FROM ORDERS
WHERE AMOUNT > 30000.00)
UNION CORRESPONDING
SELECT MFR_ID AS MFR, PRODUCT_ID AS PRODUCT
FROM PRODUCTS
WHERE (PRICE * QTY_ON_HAND > 30000)
In this simple example, there is not much advantage in this construct, but in the more general case where the individual queries involve calculated columns or are grouped queries, the CORRESPONDING clause and column aliases can help to clarify the meaning of the query.
 This article is excerpted from MySQL The Complete Reference by Vikram Vaswani (McGraw-Hill, 2003; ISBN 0072224770). Check it out at your favorite bookstore today. Buy this book now. |
Next: Query Expressions in the FROM Clause >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
/ 19 
