SQL allows one query to contain another query, called a subquery.
For example, in a bank, a customer might close accounts over time until they have no more accounts. The bank might want to identify all customers that do not have any accounts so that those customer 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.
The following statement lists all customers who have at least one account:
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 one time for each record that is selected by the outer query. Subqueries function a lot like nested loops would function in another programming language, except that with SQL you 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 returns TRUE if the customer has any accounts. If the customer has no accounts, then the EXISTS returns FALSE.
Thus, the statement lists those customers who have at least one account. No matter how many accounts the customer has (as long as it is at least 1), the customer is listed only once.
The following statement lists all customers who have no accounts:
SELECT id, name FROM customers c WHERE NOT EXISTS (SELECT * FROM accounts a WHERE a.customer_id = c.id);
Adding the keyword NOT reverses the sense of the query.