Subqueries

Returning to our bank example, over time, some customers add accounts and other customers terminate accounts. In some cases, a customer might gradually terminate accounts until he has no more accounts. Our bank may want to identify all customers that do not have any accounts so that those customers' records can be deleted, for example. One way to identify the customers who do not have any accounts is to use a subquery and the EXISTS clause.

To try this out, we need to create a customer who doesn't have any accounts:

Before we list all customers who do not have accounts, let's list all customers who do have accounts.

SELECT id, name
FROM customers c
WHERE EXISTS
(SELECT * FROM accounts a WHERE a.customer_id = c.id);

The subquery (also called the "inner query") is the query inside the parentheses. The inner query is executed once for each record selected by the outer query. (This functions a lot like nested loops would function in another programming language, except that with SQL we can do nested loops in a single statement.) If there are any accounts for the particular customer that the outer loop is processing, then those account records are returned to the outer query.

The "EXISTS" clause in the outer query says, effectively, "We do not care what values are in those records; all we care about is whether there are any records or not." Thus EXISTS returns TRUE if the customer has any accounts. If the customer has no accounts, then the EXISTS returns false. The EXISTS clause doesn't care whether there are multiple accounts or single accounts. It doesn't care what values are in the accounts. All the EXISTS wants to know is "Is there at least one record?"

Thus, the entire statement lists those customers who have at least one account. No matter how many accounts the customer has (as long as it's at least 1), the customer is listed only once.

Now let's list all those customers who do not have any accounts:

SELECT id, name
FROM customers c
WHERE NOT EXISTS
(SELECT * FROM accounts a WHERE a.customer_id = c.id);

Merely adding the keyword NOT reverses the sense of the query.

Subqueries may themselves have subqueries. In fact, subqueries may be nested almost arbitrarily deep.