Types of Sub-Queries

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;

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

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;

Products appears twice, with two aliases: p for the product being checked, p2 for the products averaged.

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

• Subquery
• Correlated Subquery

Cheat sheets

• Types of Sub-Queries