How to Use Nested SQL Queries that are an Existence Test
A query returns data from all SQL 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.
EXISTS and NOT EXISTS subqueries 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 performed on the next row. This action is repeated until every row in the enclosing statement’s table has been processed.
Suppose 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 evaluate the inner query, using the CustID value from the current CONTACT row of the outer query in the WHERE clause of the inner query.
Here’s what happens:
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’, 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.
The Zetec salesperson wants 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.