# Where to use Sub-Queries

Where a sub-query can go, as a lookup value or list in WHERE, an EXISTS check, a column value, or a table to join.

Journeyman · SQL · MySQL · Updated November 16, 2026

By Gregor Dzierzon

Sub-queries can be used anywhere a dataset or a value is needed. We will explore a few scenarios of how a sub-query could be used.

## Lookup Single Value in a WHERE clause

Sub-queries are often used in WHERE clauses to provide a lookup value. The benefit is that the outer query can be simplified because it does not need to deal with JOINed rows or columns.

In this scenario the sub query can only return a single value, the customer id of a specific company name:

```sql
SELECT *
FROM orders
WHERE customer_id = (
    SELECT customer_id
    FROM customers
    WHERE company_name = 'Alfreds Futterkiste'
);
```

## Lookup Multiple Values in a WHERE clause

In this scenario, we should find orders for all customers in Canada. The sub-query can be used to provide a column of `customer_id` values to the `IN` operator.

```sql
SELECT *
FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE country = 'Canada'
);
```

## Check for matches in a WHERE clause

The `IN` operator can be slow if the sub-query returns a very large list. `EXISTS` can be faster because it doesn't compare every value: it stops looking as soon as it finds one match.

```sql
SELECT *
FROM orders AS o
WHERE EXISTS (
    SELECT *
    FROM customers AS c
    WHERE country = 'Canada'
        AND o.customer_id = c.customer_id
);
```

## Column Value

A sub-query can perform a lookup to find the value for a column in a SELECT statement. This type of sub-query must be a scalar query (it can only return a single value).

```sql
SELECT product_name
    , unit_price
    , (SELECT AVG(unit_price) FROM products) AS average_price
FROM products;
```

## JOIN Statement

A JOIN statement usually points to another table to join, but a query can join any other dataset, including a query. Some queries can get so long that it is easier to manage them by breaking them down into sub-queries instead.

The following example breaks one long query into three smaller ones.

```sql
SELECT *
FROM (
    SELECT customer_id
        , company_name
    FROM customers
) AS c
INNER JOIN (
    SELECT o.order_id
        , o.customer_id
        , o.order_date
        , od.product_id
        , od.unit_price AS sales_price
        , od.quantity
        , od.discount
    FROM orders AS o
    INNER JOIN order_details AS od
        ON o.order_id = od.order_id
) AS o
    ON c.customer_id = o.customer_id
INNER JOIN (
    SELECT p.product_id
        , p.product_name
        , c.category_name
    FROM products AS p
    INNER JOIN categories AS c
        ON p.category_id = c.category_id
) AS p
    ON o.product_id = p.product_id;
```

## INNER JOIN vs Sub-Query

As noted above, many of the queries we have seen can be written either with an `INNER JOIN` or with a sub-query.

If I want to query a list of all product names and prices whose prices are higher than the average price in each category, I must first determine what the average product price is in each category.

```sql
SELECT category_id
    , AVG(unit_price) AS average_price
FROM products
GROUP BY category_id;
```

![sub-query average price per category dataset](https://fsdtrainingmedia.blob.core.windows.net/public/images/2026/09/e5dbf76a66aa466096e974cfa204911a.png "sub-query average price per category dataset")

With this information, you can now find all products with prices higher than the average.

```sql
SELECT p.category_id
    , p.product_name
    , p.unit_price
    , ap.average_price
FROM products AS p
INNER JOIN (
    SELECT category_id
        , AVG(unit_price) AS average_price
    FROM products
    GROUP BY category_id
) AS ap
    ON p.category_id = ap.category_id
WHERE p.unit_price > ap.average_price
ORDER BY p.category_id
    , unit_price DESC;
```

![Top 10 rows of product with higher than average prices](https://fsdtrainingmedia.blob.core.windows.net/public/images/2026/09/06022dc9718d4ca9883718407ea78f99.png "Top 10 rows of product with higher than average prices")

The average has to be calculated for each category before any product can be compared with it, and a sub-query is the clearest way to do that.

## Do it: list the orders from customers in Mexico

The sales team in Mexico wants a list of the orders its customers have placed. `orders` doesn't hold a country, only each order's `customer_id`, and the country is in `customers`.

Two tables hold what the list needs:

- `customers`: each customer's `customer_id`, `company_name` and `country`.
- `orders`: each order's `order_id`, the `customer_id` of the customer who placed it, and its `order_date`.

They hold these rows (the **Tables** tab shows the SQL that makes them):

| customer_id | company_name | country |
|---|---|---|
| ALFKI | Alfreds Futterkiste | Germany |
| ANATR | Ana Trujillo Emparedados y helados | Mexico |
| ANTON | Antonio Moreno Taquería | Mexico |
| AROUT | Around the Horn | UK |
| BERGS | Berglunds snabbköp | Sweden |

| order_id | customer_id | order_date |
|---|---|---|
| 10278 | BERGS | 2016-08-12 |
| 10308 | ANATR | 2016-09-18 |
| 10355 | AROUT | 2016-11-15 |
| 10365 | ANTON | 2016-11-27 |
| 10507 | ANTON | 2017-04-15 |
| 10625 | ANATR | 2017-08-08 |
| 10643 | ALFKI | 2017-08-25 |

It takes two steps:

| Step | Query | Gives |
|---|---|---|
| 1 | The ids of the customers in Mexico, from `customers` | `ANATR`, `ANTON` |
| 2 | The orders whose `customer_id` is one of those, from `orders` | 10308, 10365, 10507, 10625 |

Mexico has **two** customers, so step 1 gives a list of values, not one value.

Your query should give exactly these rows, in this order:

| order_id | customer_id |
|---|---|
| 10308 | ANATR |
| 10365 | ANTON |
| 10507 | ANTON |
| 10625 | ANATR |

**Your job:** write one `SELECT` query on `orders`, with step 1 as a sub-query inside it.

- Show two columns, `order_id` and `customer_id`, in that order.
- Keep only the orders whose `customer_id` is in the list of customers whose `country` is `'Mexico'`.
- Sort the rows by `order_id`, smallest first.

Practiced live on the lesson page: write the SQL right there and check it.

## Cheat sheets

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

Source: https://www.ask-codey.com/lessons/working-with-sub-queries-where-to-use-sub-queries
