Subqueries and Query Expressions - Row-Valued Comparisons (SQL2)
(Page 10 of 12 )
The most common use of row-valued expressions in the WHERE or HAVING clause is within a test for equality, as illustrated by the last few examples. A constructed row (often consisting of column values from a candidate row of query results) is compared to another constructed row (perhaps a row of subquery results or a row of literal values), and if the rows are equal, the candidate row is included in the query results. The SQL2 standard also provides for row-valued forms of the inequality comparison tests and the range test. When comparing two rows for inequality, SQL2 uses the same rules that it would use if the columns were being used to sort the rows. It compares the contents of the first column in the two rows, and if they are unequal, uses them to order the rows. If they are equal, the comparison moves to the second column, and then the third, and so on. Here are the resulting comparisons for some three-column constructed rows derived from the ORDERS table:
(‘ACI’,’41002’,54) < (‘REI’,’2A44R’,5)—based on first column(‘ACI’,’41002’,54) < (‘ACI’,’41003’,35)—based on second column(‘ACI’,’41002’,10) < (‘ACI’,’41002’,54)—based on third column
Table-Valued Expressions (SQL2) In addition to its extended capabilities for expressions involving simple scalar data values and row values, the SQL2 standard dramatically extends the SQL capabilities for table processing. It provides a mechanism for constructing a table of data values in place within a SQL statement. It allows table-valued subqueries and extends the SQL1 subquery tests to handle them. It also allows subqueries to appear in many more places within a SQL statement—for example, a subquery can appear in the FROM clause of a SELECT statement as of its source tables. Finally, it provides expanded capabilities for combining tables, including the UNION, INTERSECTION, and DIFFERENCE operations.
The Table-Value Constructor (SQL2) SQL2 allows you to specify a table of data values within a SQL statement by using a table-value constructor expression, whose syntax is shown in Figure 9-15. In its simplest form, the table-value constructor is a comma-separated list of row-value constructors, each of which contains a comma-separated set of literals that form individual column values. For example, the SQL2 INSERT statement uses a table-value constructor as the source of the data to be inserted into a database. While the SQL1 INSERT statement (described in Chapter 10) allows you to insert only a single row of data, the next SQL2 INSERT statement inserts three rows into the OFFICES table.
Figure 9-15. SQL2 table-value constructor syntax diagram
Add three offices to the OFFICES table.
INSERT INTO OFFICES (OFFICE,CITY,REGION,MGR,SALES)
VALUES (23, 'San Diego', 'Western', 108, 0.00),
(24, 'Seattle', 'Western', 104, 0.00),
(14, 'Boston', 'Eastern, NULL, 0.00)
Note that the individual rows in the table-value constructor are not restricted to contain only literal values. The source of a data value can be a scalar-valued subquery, or an entire row can be the result of a row-valued subquery. Although it doesn’t make much sense in the sample database, this is a legal SQL2 INSERT statement that illustrates these capabilities:
Add three offices to the OFFICES table.
INSERT INTO OFFICES (OFFICE,CITY,REGION,MGR,SALES)
VALUES (23, 'San Diego', 'Western', 108, 0.00),
(24, 'Seattle', 'Western', (SELECT MANAGER
FROM SALESREPS
WHERE EMPL_NUM 105),
0.00), (SELECT 'BOSTON', 'EASTERN', REGION, MGR, 0.00
FROM OFFICES
WHERE OFFICE = 12)
Like the preceding example, the VALUES clause in this INSERT statement generates a three-row table to be inserted. The first row is specified with literal values. In the second row, the fourth column is specified as a scalar-valued subquery that retrieves the manager of employee number 105. In the third row, the entire row is generated by a row-valued subquery. In this case, three of the column values in the subquery’s SELECT clause are actually literal values, but the third and fourth columns are produced by the subquery, which retrieves the manager and region for the New York office (number 12).
Table-Valued Subqueries (SQL2) Just as SQL2 expands the use of scalar subqueries into row-valued subqueries, it also extends the SQL subquery facility to support table-valued subqueries—that is, subqueries that return a full table of results. One useful role for table-valued subqueries is within the WHERE or HAVING clause, where it is combined with extended forms of the subquery tests. For example, suppose you wanted to list the descriptions and prices of all products with orders exceeding $20,000 in the sample database. Perhaps the most straightforward way to express this request is in this SQL2 statement that uses a table-valued subquery:
List the description and price of all products with individual orders over $20,000.
SELECT DESCRIPTION, PRICE
FROM PRODUCTS
WHERE (MFR_ID,PRODUCT_ID) IN (SELECT MFR, PRODUCT
FROM ORDERS
WHERE AMOUNT > 20000.00)
The top-level query is a straightforward statement of the English-language request—it asks for the description and price of those products whose identification (as in previous examples, a manufacturer-ID/product-ID pair) matches some set of products. This is expressed as a subquery set membership test in the WHERE clause. The subquery generates a two-column table of subquery results, which are the identifications of the products that meet the stated order size criterion.
It’s certainly possible to express this query in other ways. From the discussion in Chapter 7, you probably recognize that it can be stated as a join of the PRODUCTS and ORDERS tables with a compound search condition:
List the description and price of all products with individual orders over $20,000.
SELECT DESCRIPTION, PRICE
FROM PRODUCTS, ORDERS
WHERE (MFR_ID = MFR)
AND (PRODUCT_ID = PRODUCT)
AND (AMOUNT > 20000.00)
This is an equally valid statement of the query, but it’s a lot further removed from the English-language request, and therefore more difficult to understand for most people. As queries become more complex, the ability to use table-valued subqueries becomes even more useful to simplify and clarify SQL requests.
 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: The SQL2 Query Specification >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
/ 19 
