# 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 November 3, 2026

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

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

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

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

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

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

## 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](https://www.ask-codey.com/glossary/subquery)
- [Correlated Subquery](https://www.ask-codey.com/glossary/correlated-subquery)

## Cheat sheets

- [Types of Sub-Queries](https://www.ask-codey.com/cheat-sheets/sub-queries-in-t-sql-types-of-sub-queries-cheat-sheet)

Source: https://www.ask-codey.com/lessons/sub-queries-in-t-sql-types-of-sub-queries