SQL Series...36

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. 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 sub-query’s FROM clause meet the conditions in its WHERE clause.

Subqueries introduced with EXISTS and NOT EXISTS are fundamentally dif-ferent from the 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, and the enclosing statement performs its action. In the same circumstances, the NOT EXISTS predicate fails, and the enclosing statement does not perform its action. After one row of the enclosing statement’s table is processed, the same operation is per-formed on the next row. This action is repeated until every row in the enclos-ing statement’s table has been processed.

EXISTS

Say that you are a salesperson for Zetec Corporation and you want to call your primary contact people at all of Zetec’s customer organizations in California. Try the following query:

SELECT *

FROM CONTACT

WHERE EXISTS

(SELECT *

FROM CUSTOMER

WHERE CustState = ‘CA’

AND CONTACT.CustID = CUSTOMER.CustID) ;

Notice the reference to CONTACT.CustID, which is referencing a column from the outer query and comparing it with another column, CUSTOMER.CustID from the inner query. For each candidate row of the outer query, you evalu-ate the inner query, using the CustID value from the current CONTACT row of the outer query in the WHERE clause of the inner query.

The CustID column links the CONTACT table to the CUSTOMER table. SQL looks at the first record in the CONTACT table, finds the row in the CUSTOMER table that has the same CustID, and checks that row’s CustState field. If CUSTOMER.CustState = ‘CA’, then the current CONTACT row is added to the result table. The next CONTACT record is then processed in the same way, and so on, until the entire CONTACT table has been processed. Because the query specifies SELECT * FROM CONTACT, all the contact table’s fields are returned, including the contact’s name and phone number.

NOT EXISTS

In the previous example, the Zetec salesperson wanted to know the names and numbers of the contact people of all the customers in California. Imagine that a second salesperson is responsible for all of the United States except California. She can retrieve her contact people by using NOT EXISTS in a query similar to the preceding one:

SELECT *

FROM CONTACT

WHERE NOT EXISTS

(SELECT *

FROM CUSTOMER

WHERE CustState = ‘CA’

AND CONTACT.CustID = CUSTOMER.CustID) ;

Every row in CONTACT for which the subquery does not return a row is added to the result table.