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

By

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, ProductName and UnitPrice, in that order.
  • Keep only the products whose UnitPrice is more than the average UnitPrice of the products with the same CategoryId.
  • 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.

Key terms

Cheat sheets