SQL technique: subqueries
Sometimes you don’t have enough information available when you design a query to determine which rows you want. In this case, you’ll have to find the required information with a subquery.
Example: Find the name of customers who live in the same zip code area as Wayne Dick. We might start writing this query as we would any of the ones that we have already done:
SELECT cFirstName, cLastName FROM customers WHERE cZipCode = ???
SELECT cZipCode FROM Customers WHERE cFirstName = 'Wayne' AND cLastName = 'Dick';
SELECT cFirstName, cLastName, cZipCode FROM customers WHERE cZipCode = (SELECT cZipCode FROM customers WHERE cFirstName = 'Wayne' AND cLastName = 'Dick');
A subquery that returns only one column and one row can be used any time that we need a single value. Another example would be to find the product name and sale price of all products whose unit sale price is greater than the average of all products. We can see that the DISTINCT keyword is needed, since the SELECT attributes are not a super key of the result set:
SELECT DISTINCT prodName, unitSalePrice FROM Products NATURAL JOIN OrderLines WHERE unitSalePrice > the average unit sale price
SELECT AVG(unitSalePrice) FROM OrderLines;
SELECT DISTINCT prodName, unitSalePrice FROM Products NATURAL JOIN OrderLines WHERE unitSalePrice > (SELECT AVG(unitSalePrice) FROM OrderLines);
|Hammer, framing, 20 oz.||11.95|
|Saw, crosscut, 10 tpi||21.25|
Subqueries can also be used when we need more than a single value as part of a larger query. We’ll see examples of these in later pages.