Where to Use Sub-Queries

After this lesson you can

• Use a sub-query in WHERE as one value, with IN or NOT IN as a list, or with EXISTS
• Use a sub-query as a column in the SELECT, or as a table in the FROM

Key takeaways

• A sub-query compared with =, < or > must return exactly one value; if it returns two or more, SQL Server stops with an error.
• A sub-query that returns a column of values works with IN or NOT IN.
• If a NOT IN sub-query's column holds a NULL, no rows come back, so add IS NOT NULL to the sub-query when the column allows NULL.
• EXISTS asks whether a correlated sub-query finds any row at all, and stops at the first match.
• A one-value sub-query can be a column in the SELECT, and a sub-query in the FROM works as a table once it has an alias.

A sub-query can go wherever a query needs a value, a list of values, or a table. What it returns decides where it fits.

One value, in WHERE

A sub-query that returns exactly one value can be compared with =, < or >. The orders of one customer, found by company name:

SELECT OrderId, OrderDate
FROM Orders
WHERE CustomerId = (
    SELECT CustomerId
    FROM Customers
    WHERE CompanyName = 'Alfreds Futterkiste'
);

(6 rows.) If the sub-query returned two or more values, SQL Server would stop with an error: = compares with one value only.

A list, with IN

A sub-query that returns a column of values works with IN. The orders of every customer in Canada:

SELECT COUNT(*) AS Orders
FROM Orders
WHERE CustomerId IN (
    SELECT CustomerId
    FROM Customers
    WHERE Country = 'Canada'
);

NOT IN finds the opposite: customers who have never ordered.

SELECT CompanyName
FROM Customers
WHERE CustomerId NOT IN (
    SELECT CustomerId
    FROM Orders
)
ORDER BY CompanyName;

(Careful: if the sub-query's column holds a NULL, NOT IN returns no rows at all. Add WHERE CustomerId IS NOT NULL to the sub-query when the column allows NULL.)

Is there one? EXISTS

EXISTS asks whether a correlated sub-query finds any row at all. It stops at the first match, which can make it faster than IN on big tables:

SELECT COUNT(*) AS Orders
FROM Orders AS o
WHERE EXISTS (
    SELECT *
    FROM Customers AS c
    WHERE c.Country = 'Canada'
        AND c.CustomerId = o.CustomerId
);

The same 30 orders. NOT EXISTS asks the opposite.

A column, in the SELECT

A sub-query that returns one value can be a column. Each expensive product, beside the average of them all:

SELECT ProductName
    , UnitPrice
    , (SELECT AVG(UnitPrice) FROM Products) AS AveragePrice
FROM Products
WHERE UnitPrice > (SELECT AVG(UnitPrice) * 3 FROM Products)
ORDER BY UnitPrice DESC;

A table, in the FROM

A sub-query can even be a table: give it an alias, and join to it. The five customers with the most orders by value, from a sub-query that totals each customer's orders:

SELECT TOP (5) c.CompanyName, t.OrderTotal
FROM Customers AS c
INNER JOIN (
    SELECT o.CustomerId, SUM(od.UnitPrice * od.Quantity) AS OrderTotal
    FROM Orders AS o
    INNER JOIN OrderDetails AS od
        ON o.OrderId = od.OrderId
    GROUP BY o.CustomerId
) AS t
    ON c.CustomerId = t.CustomerId
ORDER BY t.OrderTotal DESC;

Building a big query from small, tested pieces like this keeps it readable.

Check yourself

2 questions to try on the lesson page.

So what

A sub-query is a question inside a question. Match what it returns (one value, a list, a yes or no, a table) to where you put it, and complex questions break into small steps you can test one at a time.

Key terms

• Scalar Subquery
• IN Predicate
• EXISTS Predicate
• Table Subquery

Cheat sheets

• Where to Use Sub-Queries