The following examples are derived, with much editing, from an SQL application suite for a public library. They illustrate the most common situations where subqueries have proven to be useful tools.
When a query is on one Table, but requires a quick look at a Column in another Table — for example, "show the number of books checked out for a particular patron". Here's a subquery that answers this:
SELECT COUNT(*) FROM Circulations WHERE patron_id = ANY (SELECT patron_id FROM Patrons WHERE Surname = 'Jones');
When detail and summary data are retrieved or compared in the same SQL statement — for example, "set a report item to show the average cost of books". Here's a subquery that does this:
UPDATE Reports SET average_book_cost = (SELECT AVG(book_cost) FROM Books);
When a selection involves some calculation or complexity that has nothing to do with the contents of what is ultimately selected — for example, "who has taken out books from either branch 9 or branch 10". Here's a subquery that answers this:
SELECT DISTINCT patron_id FROM Circulations WHERE book_copy_id = ANY (SELECT book_copy_id FROM Book_Copies WHERE branch = 9 OR branch = 10);