Nested queries that return sets of rows

To illustrate how a nested query returns a set of rows, imagine that you work for a systems integrator of computer equipment. Your company, Zetec Corporation, assembles systems from components that you buy, and then it sells them to companies and government agencies. You keep track of your business with a relational database. The database consists of many tables, but right now you're concerned with only three of them: the PRODUCT table, the COMP_USED table, and the COMPONENT table. The PRODUCT table (shown in Table 12-1) contains a list of all your standard products. The COMPONENT table (shown in Table 12-2) lists components that go into your products, and the COMP_USED table (shown in Table 12-3) tracks which components go into each product.

A component may be used in multiple products, and a product can contain multiple components (a many-to-many relationship). This situation can cause integrity problems. To circumvent the problems, create the linking table COMP_ USED to relate COMPONENT to PRODUCT. A component may appear in many rows in the COMP_USED table, but each of those rows will reference only one component (a one-to-many relationship). Similarly, a product may appear in many rows in COMP_USED, but each row references only one product (another one-to-many relationship). Adding the linking table transforms a troublesome many-to-many relationship into two relatively simple one-to-many relationships. This process of reducing the complexity of relationships is one example of normalization.

Nested queries that return a single value

Introducing a subquery with one of the six comparison operators (=, <>, <,<=, >, >=) is often useful. In such a case, the expression preceding the operator evaluates to a single value, and the subquery following the operator must also evaluate to a single value. An exception is the case of the quantified comparison operator, which is a comparison operator followed by a quantifier (ANY, SOME, or ALL).

To illustrate a case in which a subquery returns a single value, look at another piece of Zetec Corporation's database. It contains a CUSTOMER table that holds information about the companies that buy Zetec products. It also contains a CONTACT table that holds personal data about individuals at each of Zetec’s customer organizations. The tables are structured as shown in Tables 12-4 and 12-5.

Say that you want to look at the contact information for Olympic Sales, but you don't remember that company’s CustID. Use a nested query like this one to recover the information you want:

SELECT *

FROM CONTACT

WHERE CustID =

(SELECT CustID

FROM CUSTOMER

WHERE Company = 'Olympic Sales') ;

The result looks something like this:

CustID ContFName ContLName ContPhone ContInfo

------ --------- --------- --------- --------

118 Jerry Attwater 505-876-3456 Will play

major role in

additive

manufacturing.

You can now call Jerry at Olympic and tell him about this month’s special sale on 3D printers.

When you use a subquery in an “=” comparison, the subquery's SELECT list must specify a single column (CustID in the example). When the subquery is executed, it must return a single row in order to have a single value for the comparison.

In this example, I assume that the CUSTOMER table has only one row with a Company value of 'Olympic Sales'. The CREATE TABLE statement for CUSTOMER specifies a UNIQUE constraint for Company, and this statement guarantees that the subquery in the preceding example returns a single value (or no value). Subqueries like the one in this example, however, are commonly used on columns that are not specified to be UNIQUE. In such cases, you must rely on prior knowledge of the database contents for believing that the column has no duplicates.

If more than one customer has a value of 'Olympic Sales' in the Company column (perhaps in different states), the subquery raises an error.

If no customer with such a company name exists, the subquery is treated as if it was null, and the comparison becomes unknown. In this case, the WHERE clause returns no row (because it returns only rows with the condition True and filters rows with the condition False or unknown). This would probably happen, for example, if someone misspelled the Company as 'Olumpic Sales'.

Although the equal operator (=) is the most common, you can use any of the other five comparison operators in a similar structure. For every row in the table specified in the enclosing statement's FROM clause, the single value returned by the subquery is compared with the expression in the enclosing statement’s WHERE clause. If the comparison gives a True value, a row is added to the result table.

You can guarantee that a subquery will return a single value if you include an aggregate function in it. Aggregate functions always return a single value. (Aggregate functions are described in Chapter 3.) Of course, this way of returning a single value is helpful only if you want the result of an aggregate function.

Suppose you are a Zetec salesperson and you need to earn a big commission check to pay for some unexpected bills. You decide to concentrate on selling Zetec's most expensive product. You can find out what that product is with a nested query:

SELECT Model, ProdName, ListPrice

FROM PRODUCT

WHERE ListPrice =

(SELECT MAX(ListPrice)

FROM PRODUCT) ;

In the preceding nested query, both the subquery and the enclosing statement operate on the same table. The subquery returns a single value: the maximum list price in the PRODUCT table. The outer query retrieves all rows from the PRODUCT table that have that list price.

The next example shows a comparison subquery that uses a comparison operator other than =:

SELECT Model, ProdName, ListPrice

FROM PRODUCT

WHERE ListPrice <

(SELECT AVG(ListPrice)

FROM PRODUCT) ;

The subquery returns a single value: the average list price in the PRODUCT table. The outer query retrieves all rows from the PRODUCT table that have a lower list price than the average list price.

In the original SQL standard, a comparison could have only one subquery, and it had to be on the right side of the comparison. SQL:1999 allowed either or both operands of the comparison to be subqueries, and later versions of SQL retain that expansion of capability.

The ALL, SOME, and ANY quantifiers

Another way to make sure that a subquery returns a single value is to introduce it with a quantified comparison operator. The universal quantifier ALL, and the existential quantifiers SOME and ANY, when combined with a comparison operator, process the list returned by a subquery, reducing it to a single value.

You'll see how these quantifiers affect a comparison by looking at the baseball pitchers’ complete game database from Chapter 11, which is listed next.

The contents of the two tables are given by the following two queries:

SELECT * FROM NATIONAL

FirstName LastName CompleteGames

--------- -------- -------------

Sal Maglie 11

Don Newcombe 9

Sandy Koufax 13

Don Drysdale 12

Bob Turley 8

SELECT * FROM AMERICAN

FirstName LastName CompleteGames

--------- -------- -------------

Whitey Ford 12

Don Larson 10

Bob Turley 8

Allie Reynolds 14

The presumption is that the pitchers with the most complete games should be in the American League because of the presence of designated hitters in that league. One way to verify this presumption is to build a query that returns all American League pitchers who have thrown more complete games than all the National League pitchers. The query can be formulated as follows:

SELECT *

FROM AMERICAN

WHERE CompleteGames > ALL

(SELECT CompleteGames FROM NATIONAL) ;

This is the result:

FirstName LastName CompleteGames

---------- --------- --------------

Allie Reynolds 14

The subquery (SELECT CompleteGames FROM NATIONAL) returns the values in the CompleteGames column for all National League pitchers. The > ALL quantifier says to return only those values of CompleteGames in the AMERICAN table that are greater than each of the values returned by the subquery. This condition translates into “greater than the highest value returned by the subquery.” In this case, the highest value returned by the subquery is 13 (Sandy Koufax). The only row in the AMERICAN table higher than that is Allie Reynolds's record, with 14 complete games.

What if your initial presumption was wrong? What if the major-league leader in complete games was a National League pitcher, in spite of the fact that the National League has no designated hitter? If that was the case, the query

SELECT *

FROM AMERICAN

WHERE CompleteGames > ALL

(SELECT CompleteGames FROM NATIONAL) ;

would return a warning that no rows satisfy the query’s conditions — meaning that no American League pitcher has thrown more complete games than the pitcher who has thrown the most complete games in the National League.

Nested queries that are an existence test

A query returns data from all table rows that satisfy the query’s conditions. Sometimes many rows are returned; sometimes only one comes back. Sometimes none of the rows in the table satisfy the conditions, and no rows are returned. You can use the EXISTS and NOT EXISTS predicates to introduce a subquery. That structure tells you whether any rows in the table located in the subquery's FROM clause meet the conditions in its WHERE clause.

Subqueries introduced with EXISTS and NOT EXISTS are fundamentally different from the other subqueries in this chapter so far. In all the previous cases, SQL first executes the subquery and then applies that operation's result to the enclosing statement. EXISTS and NOT EXISTS subqueries, on the other hand, are examples of correlated subqueries.

A correlated subquery first finds the table and row specified by the enclosing statement and then executes the subquery on the row in the subquery's table that correlates with the current row of the enclosing statement’s table.

The subquery either returns one or more rows or it returns none. If it returns at least one row, the EXISTS predicate succeeds (see the following section), and the enclosing statement performs its action. In the same circumstances, the NOT EXISTS predicate fails (see the section after that), and the enclosing statement does not perform its action. After one row of the enclosing statement's table is processed, the same operation is performed on the next row. This action is repeated until every row in the enclosing statement’s table has been processed.

Other correlated subqueries

As noted in a previous section of this chapter, subqueries introduced by IN or by a comparison operator need not be correlated queries, but they can be.

UPDATE, DELETE, and INSERT

In addition to SELECT statements, UPDATE, DELETE, and INSERT statements can also include WHERE clauses. Those WHERE clauses can contain subqueries in the same way that SELECT statements' WHERE clauses do.

For example, Zetec has just made a volume purchase deal with Olympic Sales and wants to provide Olympic with a retroactive 10 percent credit for all its purchases in the last month. You can give this credit with an UPDATE statement:

UPDATE TRANSMASTER

SET NetAmount = NetAmount * 0.9

WHERE SaleDate > (CurrentDate – 30) DAY AND CustID =

(SELECT CustID

FROM CUSTOMER

WHERE Company = 'Olympic Sales') ;

You can also have a correlated subquery in an UPDATE statement. Suppose the CUSTOMER table has a column LastMonthsMax, and Zetec wants to give such a credit for purchases that exceed LastMonthsMax for the customer:

UPDATE TRANSMASTER TM

SET NetAmount = NetAmount * 0.9

WHERE NetAmount>

(SELECT LastMonthsMax

FROM CUSTOMER C

WHERE C.CustID = TM.CustID) ;

Note that this subquery is correlated: The WHERE clause in the last line references both the CustID of the CUSTOMER row from the subquery and the CustID of the current TRANSMASTER row that is a candidate for updating.

A subquery in an UPDATE statement can also reference the table that is being updated. Suppose that Zetec wants to give a 10 percent credit to customers whose purchases have exceeded $10,000:

UPDATE TRANSMASTER TM1

SET NetAmount = NetAmount * 0.9

WHERE 10000 < (SELECT SUM(NetAmount)

FROM TRANSMASTER TM2

WHERE TM1.CustID = TM2.CustID);

The inner subquery calculates the SUM of the NetAmount column for all TRANSMASTER rows for the same customer. What does this mean? Suppose the customer with CustID = 37 has four rows in TRANSMASTER with values for NetAmount: 3000, 5000, 2000, and 1000. The SUM of NetAmount for this CustID is 11000.

The order in which the UPDATE statement processes the rows is defined by your implementation and is generally not predictable. The order may differ depending on how the rows are arranged on the disk. Assume that the implementation processes the rows for this CustID in this order: first the TRANSMASTER with a NetAmount of 3000, then the one with NetAmount= 5000, and so on. After the first three rows for CustID 37 have been updated, their NetAmount values are 2700 (90 percent of $3,000), 4500 (90 percent of $5,000), and 1800 (90 percent of $2,000). Then, when you process the last TRANSMASTER row for CustID 37 (whose NetAmount is 1000), the SUM returned by the subquery would seem to be 10000 — that is, the SUM of the new NetAmount values of the first three rows for CustID 37, and the old NetAmount value of the last row for CustID 37. Thus it would seem that the last row for CustID 37 isn't updated, because the comparison with that SUM is not True — after all, 10000 is not less than 10000. But that is not how the UPDATE statement is defined when a subquery references the table that is being updated.

All evaluations of subqueries in an UPDATE statement reference the old values of the table — the ones that are being updated. In the preceding UPDATE for CustID 37, the subquery returns 11000 — the original SUM.

The subquery in a WHERE clause operates the same as a SELECT statement or an UPDATE statement. The same is true for DELETE and INSERT. To delete all of Olympic's transactions, use this statement:

DELETE FROM TRANSMASTER

WHERE CustID =

(SELECT CustID

FROM CUSTOMER

WHERE Company = 'Olympic Sales') ;

As with UPDATE, DELETE subqueries can also be correlated and can also reference the table being deleted. The rules are similar to the rules for UPDATE subqueries. Suppose you want to delete all rows from TRANSMASTER for customers whose total NetAmount is larger than $10,000:

DELETE FROM TRANSMASTER TM1

WHERE 10000 < (SELECT SUM(NetAmount)

FROM TRANSMASTER TM2

WHERE TM1.CustID = TM2.CustID) ;

This query deletes all rows from TRANSMASTER that have CustID 37, as well as any other customers with purchases exceeding $10,000. All references to TRANSMASTER in the subquery denote the contents of TRANSMASTER before any deletions by the current statement. So even when you're deleting the last TRANSMASTER row for CustID 37, the subquery is evaluated on the original TRANSMASTER table and returns 11000.

When you update, delete, or insert database records, you risk making a table's data inconsistent with other tables in the database. Such an inconsistency is called a modification anomaly, discussed in Chapter 5. If you delete TRANSMASTER records and a TRANSDETAIL table depends on TRANSMASTER, you must delete the corresponding records from TRANSDETAIL, too. This operation is called a cascading delete, because the deletion of a parent record must cascade to its associated child records. Otherwise the undeleted child records become orphans. In this case, they would be invoice detail lines that are in limbo because they are no longer connected to an invoice record.

If your implementation of SQL doesn’t support cascading deletes, you must do the deletions yourself. In this case, delete the appropriate records from the child table before deleting the corresponding record from the parent. That way, you don’t have orphan records in the child table, even for a second.

Retrieving changes with pipelined DML

In the preceding section, I show how an UPDATE, DELETE, or INSERT statement can include a nested SELECT statement within a WHERE clause. SQL:2011 introduced a related capability, in which a data manipulation command (such as UPDATE, INSERT, DELETE, or MERGE statements) can be nested within a SELECT statement. This capability is called pipelined DML.

One way to look at a data change operation is to envision a table before it is changed with a DELETE, INSERT, or UPDATE operation. You could call the table before the change the old table and the table after the change, the new table. During the data change operation, auxiliary tables, called delta tables, are created. A DELETE operation creates an old delta table, which contains the rows to be deleted. An INSERT operation creates a new delta table, which contains the rows to be inserted. An UPDATE operation would create both an old and a new delta table, the old for the rows being replaced and the new for the rows replacing them.

With pipelined DML, you can retrieve the information in the delta tables. Suppose you want to delete from your product line all products with ProductIDs between 1000 and 1399, and you want a record of exactly which products in that range are deleted. You could use the following code:

SELECT Oldtable.ProductID

FROM OLD TABLE (DELETE FROM Product

WHERE ProductID BETWEEN 1000 AND 1399)

AS Oldtable ;

In this example, the keywords OLD TABLE specify that the result of the SELECT is coming from the old delta table. The result is the list of ProductID numbers for the products that are being deleted.

Similarly, you could retrieve a list from the new delta table by using the NEW TABLE keywords, which displays the Product ID numbers of rows inserted by an INSERT operation or updated by an UPDATE operation. Because an UPDATE operation created both an old delta table and a new delta table, you can retrieve the contents of either or both by using pipelined DML.