Types of Sub-Queries
A sub-query is a query inside another query, in parentheses. An uncorrelated one runs once on its own; a correlated one uses the outer query's row and runs once per row.
Journeyman · T-SQL · SQL · Updated
After this lesson you can
- Write a sub-query in parentheses, and tell an uncorrelated sub-query from a correlated one
Key takeaways
- A sub-query is a query inside another query, in parentheses, and the outer query uses its answer.
- An uncorrelated sub-query doesn't depend on the outer query: SQL Server runs it once, and you can run it on its own to test it.
- A correlated sub-query uses a value from the outer query's row, such as
p.CategoryId, so it runs once per row and can't run on its own. - Use a correlated sub-query when each row needs its own answer, such as its own category's average; use a join when a join will do.
A sub-query is a query written inside another query, in parentheses. The inner query answers a question, and the outer query uses the answer.
Uncorrelated: runs once, on its own
Say you want the Dairy Products, but you don't know that category's id. One query finds the id:
SELECT CategoryId
FROM Categories
WHERE CategoryName = 'Dairy Products';
Another lists products by id. Put the first inside the second, and you don't need to know the id at all:
SELECT ProductName, UnitPrice
FROM Products
WHERE CategoryId = (
SELECT CategoryId
FROM Categories
WHERE CategoryName = 'Dairy Products'
)
ORDER BY UnitPrice DESC;
| ProductName | UnitPrice |
|---|---|
| Raclette Courdavault | 55.00 |
| Queso Manchego La Pastora | 38.00 |
| Gudbrandsdalsost | 36.00 |
| Mozzarella di Giovanni | 34.80 |
(10 rows in all.) The sub-query is uncorrelated: it doesn't depend on the outer query. You can select it and run it by itself, which makes it easy to test. SQL Server runs it once and uses its answer.
The same question can be written as a join:
SELECT p.ProductName, p.UnitPrice
FROM Products AS p
INNER JOIN Categories AS c
ON p.CategoryId = c.CategoryId
WHERE c.CategoryName = 'Dairy Products';
Correlated: uses the outer row
A correlated sub-query uses a value from the outer query's current row, so it can't run on its own. This one finds each product's category name:
SELECT TOP (4) ProductName
, (
SELECT CategoryName
FROM Categories AS c
WHERE c.CategoryId = p.CategoryId
) AS CategoryName
FROM Products AS p
ORDER BY ProductId;
| ProductName | CategoryName |
|---|---|
| Chai | Beverages |
| Chang | Beverages |
| Aniseed Syrup | Condiments |
| Chef Anton's Cajun Seasoning | Condiments |
The inner query uses p.CategoryId, from the outer query's row, so it runs once per row. On a big table that can be slow; here, a join does the same job, and usually better.
Correlated, when a join won't do
Some questions need the correlation. Which products cost more than twice their own category's average? Each product's comparison needs a different average:
SELECT p.ProductName, p.UnitPrice, p.CategoryId
FROM Products AS p
WHERE p.UnitPrice > (
SELECT AVG(p2.UnitPrice) * 2
FROM Products AS p2
WHERE p2.CategoryId = p.CategoryId
)
ORDER BY p.CategoryId;
| ProductName | UnitPrice | CategoryId |
|---|---|---|
| Côte de Blaye | 263.50 | 1 |
| Sir Rodney's Marmalade | 81.00 | 3 |
| Thüringer Rostbratwurst | 123.79 | 6 |
| Carnarvon Tigers | 62.50 | 8 |
Products appears twice, with two aliases: p for the product being checked, p2 for the products averaged.
| Uncorrelated | Correlated | |
|---|---|---|
| Uses the outer query's row | No | Yes |
| Can be run on its own | Yes | No |
| Runs | Once | Once per outer row |
Do it: find the products priced above their own category's average
The pricing team wants to know which products cost more than the average price of their own category. A drink is compared with the other drinks, and a sauce with the other sauces, never with everything.
The Products table has each product's ProductName, its CategoryId (1 is Beverages, 2 is Condiments) and its UnitPrice. It holds these rows (the Tables tab shows the SQL that makes it):
| ProductName | CategoryId | UnitPrice |
|---|---|---|
| Chai | 1 | 18.00 |
| Chang | 1 | 19.00 |
| Guaraná Fantástica | 1 | 4.50 |
| Sasquatch Ale | 1 | 14.50 |
| Aniseed Syrup | 2 | 10.00 |
| Chef Anton's Cajun Seasoning | 2 | 22.00 |
| Genen Shouyu | 2 | 15.50 |
| Vegie-spread | 2 | 43.90 |
Each category has its own average:
| CategoryId | Prices | Average |
|---|---|---|
| 1 | 18.00 + 19.00 + 4.50 + 14.50 = 56.00, over 4 products | 14.00 |
| 2 | 10.00 + 22.00 + 15.50 + 43.90 = 91.40, over 4 products | 22.85 |
For example, Chai costs 18.00, more than its category's 14.00, so Chai is on the list. Chef Anton's Cajun Seasoning costs 22.00, which is more than the average of all eight products (about 18.43) but not more than its own category's 22.85, so it isn't.
The starter compares every product with the average of all eight products, so Chef Anton's Cajun Seasoning gets in and Chai doesn't. That's the part to change.
Your query should give exactly these rows, in this order (the Results tab may show 18.00 as 18; it's the same number):
| ProductName | UnitPrice |
|---|---|
| Chai | 18.00 |
| Chang | 19.00 |
| Sasquatch Ale | 14.50 |
| Vegie-spread | 43.90 |
Your job: write one SELECT query on Products.
- Show two columns,
ProductNameandUnitPrice, in that order. - Keep only the products whose
UnitPriceis more than the averageUnitPriceof the products with the sameCategoryId. - Work out that average with a correlated sub-query, so each product gets its own category's average.
- Sort the rows by
ProductName, A to Z.
Practiced live on the lesson page: write the SQL right there and check it.
Check yourself
1 question to try on the lesson page.
So what
A sub-query lets one query ask another for an answer. Test uncorrelated ones on their own first; use correlated ones when each row needs its own answer, and a join when a join will do.