SQL Subquery

The SQL subquery is a separate SELECT statement that is embedded in the main statement and may be executed once for each row of the result set.




As always, an example explains it best:

SELECT * FROM Customer WHERE BirthTown IN (SELECT CapitalCity FROM Country)
matches all customers who were born in a capital city.

If you look at the execution plan for this, you can see that (SELECT CapitalCity FROM Country) is executed first. This is the subquery. The output from this subquery is then used to filter results from the outer query.

Another way to use a subquery is when you know it will return exactly one row; then you could write something like this:

SELECT * FROM Customer WHERE BirthTown = (SELECT CapitalCity FROM Country WHERE Country = 'France')
The IN operator has been replaced by =. This is because we know there is just one row in the Country table for 'France'. Other valid operators are <, <=, >, >= and <>.

Be aware that if this form is used, and the subquery returns more than one row you will get the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Which neatly brings me onto the next use of a subquery; as an expression. Let's suppose we want a list of customers, together with the value of the most expensive item each has purchased.

SELECT FirstName, LastName, (SELECT MAX(OrderValue) FROM Purchase WHERE Purchase.CustomerId = Customer.Id) AS MaxPurchase FROM Customer
For each row in the main result set, it performs a select from Purchase to get the corresponding maximum order value.

OK. I know this would have been more elegant using a GROUP BY clause, but I haven't covered that yet in this series of tutorials, and this is an easy example to explain.

This is actually known "in the trade" as a correlated subquery. Another example of a correlated subquery is:

SELECT * FROM Item i WHERE 1000 <= ( SELECT SUM(OrderValue) FROM Purchase p WHERE p.ItemId = i.Id)
This is another example that could have been done more efficiently with a GROUP BY and HAVING construct. Correlated subqueries, with some exceptions, tend not to be the best performing approach. It is important that you understand them though, as you they are used extensively.

Summary

This short tutorial has demonstrated how to use the SQL subquery through a few simple examples. I explained that a couple of these examples could have been written with an SQL GROUP BY clause, so this is the subject of the next article.

SQL FAQ |  Recent Articles |  SQL Server Administration |  SQL Server Performance
SQL Server Scripts |  SQL Server Error Reference |  Learn SQL |  Reviews |  Miscellany
My Services |  Contact Me |  Privacy Policy |  Site Map

© Richard Fryar. All Rights Reserved.