You are viewing an old version of this article. View the current version here.

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);

Comments

Comments loading...