# Where to Use Sub-Queries

A sub-query can stand in for a value, a list or a table. Use it in WHERE with =, IN, NOT IN or EXISTS, as a column in the SELECT, or as a table in the FROM.

Journeyman · T-SQL · SQL · Updated November 3, 2026

## 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:

```sql
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:

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

| Orders |
|---|
| 30 |

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

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

| CompanyName |
|---|
| FISSA Fabrica Inter. Salchichas S.A. |
| Paris spécialités |

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

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

| Orders |
|---|
| 30 |

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:

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

| ProductName | UnitPrice | AveragePrice |
|---|---|---|
| Côte de Blaye | 263.50 | 28.8663 |
| Thüringer Rostbratwurst | 123.79 | 28.8663 |
| Mishi Kobe Niku | 97.00 | 28.8663 |

## 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:

```sql
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;
```

| CompanyName | OrderTotal |
|---|---|
| QUICK-Stop | 117483.39 |
| Save-a-lot Markets | 115673.39 |
| Ernst Handel | 113236.68 |
| Hungry Owl All-Night Grocers | 57317.39 |
| Rattlesnake Canyon Grocery | 52245.90 |

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

| The sub-query returns | Use it |
|---|---|
| One value | With `=`, `<`, `>` in `WHERE`, or as a column in the `SELECT` |
| A column of values | With `IN` or `NOT IN` in `WHERE` |
| Whether any row matches | With `EXISTS` or `NOT EXISTS` |
| A whole result | As a table in the `FROM`, with an alias |

## 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](https://www.ask-codey.com/glossary/scalar-subquery)
- [IN Predicate](https://www.ask-codey.com/glossary/in-predicate)
- [EXISTS Predicate](https://www.ask-codey.com/glossary/exists-predicate)
- [Table Subquery](https://www.ask-codey.com/glossary/table-subquery)

## Cheat sheets

- [Where to Use Sub-Queries](https://www.ask-codey.com/cheat-sheets/sub-queries-in-t-sql-where-to-use-sub-queries-cheat-sheet)

Source: https://www.ask-codey.com/lessons/sub-queries-in-t-sql-where-to-use-sub-queries