SQL Subqueries

Use the result of one query inside another with subqueries, IN, EXISTS, derived tables and WITH clauses.

Lesson 15 of 102Advanced19 min read

Some questions take two steps to answer. "Which products cost more than the average price?" First you need the average, then you need the products above it. "Which customers have never ordered a backpack?" First find the backpack buyers, then find everyone else. You could run two queries and copy a number between them, but SQL lets you put one query inside another.

That inner query is called a subquery. Its result feeds straight into the outer query, as a single value, a list of values or even a whole temporary table. Subqueries let you express complex questions in one statement that always uses the latest data.

In this lesson you'll write subqueries that return one value, subqueries that return lists for IN, existence checks with EXISTS, and subqueries that act as tables. You'll finish with common table expressions, which give subqueries names and make long queries much easier to read.

Scalar subqueries

A subquery that returns exactly one value can be used anywhere a single value is allowed. Wrap it in brackets:

SQL
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

The database runs the inner query first, gets the average (about 14.16), then uses it in the outer WHERE. If the data changes, the query still gives the right answer, because the average is recalculated every time. A scalar subquery can also appear in the SELECT list, for example to show each price next to the overall average.

Subqueries with IN and NOT IN

A subquery that returns one column of many rows works with IN:

SQL
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE quantity >= 5);

NOT IN finds rows whose value isn't in the list. Be careful: if the subquery's list contains even one NULL, NOT IN returns no rows at all, because SQL can't be sure a value is "not equal" to an unknown. Filter NULLs out inside the subquery, or use NOT EXISTS, which doesn't have this problem.

EXISTS and correlated subqueries

EXISTS (subquery) is true if the subquery returns at least one row. It's usually written as a correlated subquery, one that refers to the outer query's current row:

SQL
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

For each customer, the inner query checks for at least one order belonging to them. SELECT 1 is a convention: EXISTS only cares whether any row exists, not what it contains. NOT EXISTS gives the opposite, such as customers with no orders.

Subqueries in FROM and the WITH clause

A subquery in FROM acts as a temporary table, often called a derived table. It must be given an alias:

SQL
SELECT category, total_stock
FROM (SELECT category, SUM(stock) AS total_stock
      FROM products GROUP BY category) AS t
WHERE total_stock > 30;

A common table expression (CTE) does the same job more readably, by naming the subquery up front with WITH:

SQL
WITH category_stock AS (
    SELECT category, SUM(stock) AS total_stock
    FROM products GROUP BY category
)
SELECT * FROM category_stock WHERE total_stock > 30;

CTEs read top to bottom like a story, and you can define several, separated by commas. They work in SQLite, MySQL 8+ and PostgreSQL.

SQL Subqueries Syntax

Subquery forms

WHERE col > (SELECT AVG(col) FROM t)
WHERE id IN (SELECT other_id FROM t2)
WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.fk = t1.id)
FROM (SELECT ...) AS alias

WITH name AS (SELECT ...)
SELECT * FROM name;

SQL Subqueries Summary

  • A subquery is a SELECT inside another statement, written in brackets.
  • Scalar subqueries return one value and can be used like a value.
  • IN (subquery) checks a list; NOT IN breaks if the list contains NULL.
  • EXISTS checks whether related rows exist, often with a correlated subquery.
  • Subqueries in FROM need an alias; WITH names them as readable CTEs.

SQL Subqueries Examples

Each example shows the code, the output it produces and what each part does.

Example 1

Products above the average price

SQL
SELECT name,
       price,
       ROUND(price - (SELECT AVG(price) FROM products), 2) AS above_avg
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;
Output
name      | price | above_avg
----------+-------+----------
Backpack  | 34.99 | 20.83
Desk lamp | 24.50 | 10.34
Line-by-line explanation
  1. Line 3A scalar subquery in SELECT shows how far each price is above the average of 14.16.
  2. Line 5The same subquery in WHERE keeps only products above the average.
Example 2

Customers who bought a water bottle

SQL
SELECT name, city
FROM customers
WHERE id IN (
    SELECT customer_id
    FROM orders
    WHERE product_id = (SELECT id FROM products WHERE name = 'Water bottle')
);
Output
name  | city
------+------
Ben   | Leeds
Chloe | Lyon
Line-by-line explanation
  1. Line 6The innermost subquery finds the water bottle's id, which is 4.
  2. Lines 4 to 6The middle subquery lists the customers who ordered it: 3 and 2.
  3. Line 3The outer query shows those customers' details.
Example 3

Customers who have never ordered, with NOT EXISTS

SQL
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
Output
name
----
Ema
Line-by-line explanation
  1. Lines 3 to 5For each customer, look for any order with their id. Only Ema has none.
Example 4

Spending per customer with a CTE

SQL
WITH order_totals AS (
    SELECT o.customer_id, o.quantity * p.price AS amount
    FROM orders o
    JOIN products p ON p.id = o.product_id
),
customer_totals AS (
    SELECT customer_id, SUM(amount) AS spent
    FROM order_totals
    GROUP BY customer_id
)
SELECT c.name, ROUND(t.spent, 2) AS spent
FROM customer_totals t
JOIN customers c ON c.id = t.customer_id
ORDER BY spent DESC;
Output
name  | spent
------+------
Ana   | 46.99
Ben   | 29.5
Dev   | 24.5
Chloe | 24.0
Line-by-line explanation
  1. Lines 1 to 5The first CTE works out the value of every order line.
  2. Lines 6 to 10The second CTE adds them up per customer, using the first CTE like a table.
  3. Lines 11 to 14The final query attaches names and sorts by spending.

Common SQL Subqueries Mistakes

A scalar subquery that returns several rows

Don't
SELECT name FROM products
WHERE price = (SELECT price FROM products WHERE category = 'Kitchen');
Do
SELECT name FROM products
WHERE price IN (SELECT price FROM products WHERE category = 'Kitchen');

With two Kitchen products, = receives two values. Use IN for lists.

NOT IN with NULLs in the list

Don't
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);   -- empty if any customer_id is NULL
Do
SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

A derived table without an alias

Don't
SELECT * FROM (SELECT category, COUNT(*) FROM products GROUP BY category);
Do
SELECT * FROM (SELECT category, COUNT(*) AS n FROM products GROUP BY category) AS counts;

MySQL and PostgreSQL require every derived table to have an alias.

SQL Subqueries in Real Projects

Subqueries in analysis

Analysts use subqueries and CTEs constantly: customers who spent more than the average, products that have never sold, the first order of every customer. Long reports are often written as a chain of CTEs, each one a small, named step, so colleagues can read and check the logic one piece at a time.

SQL Subqueries FAQ

What is the difference between a subquery and a CTE?

A CTE, written with WITH, is a named subquery placed before the main query. It does the same job but reads from top to bottom, can be referenced several times, and can be recursive. Use a CTE when a subquery becomes long or is needed more than once; see the CTEs lesson.

Is EXISTS faster than IN?

In modern databases they often run the same way, because the query planner rewrites one into the other. The bigger difference is behaviour: NOT EXISTS handles NULLs safely, while NOT IN returns no rows if the subquery produces a NULL. Choose the one that states your intent clearly.

Can a subquery appear in the SELECT list?

Yes, as long as it returns a single value per row. A correlated subquery such as (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count runs for each outer row. That's fine for small tables; for large ones, a join with GROUP BY is often faster.

Can I use a subquery in UPDATE or DELETE?

Yes: DELETE FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE city = 'Lyon');. MySQL doesn't allow a subquery that reads from the same table being updated or deleted, error 1093; wrap that subquery in a derived table or use a join instead.

How deeply can subqueries be nested?

Databases allow many levels, but readability suffers after two or three. Unnest them with CTEs, giving each step a descriptive name, so the query reads like a series of steps rather than a puzzle from the inside out.

SQL Subqueries Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the query so it finds products cheaper than the average.

SELECT name FROM products
WHERE price < ( AVG(price) FROM products);
Fill in the blanksmedium

Complete the CTE.

 cheap  (
    SELECT * FROM products WHERE price < 10
)
SELECT name FROM cheap;
Predict the outputmedium

How many rows does this return for the example shop?

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);
Multiple choicemedium

Why is SELECT 1 used inside EXISTS?

Multiple choicehard

What is a correlated subquery?

SQL Subqueries Quiz

5 questions to check what you learned.

  1. Where must a subquery be written?

    • After a semicolon
    • Inside brackets
    • In a separate file
    • Only after WHERE

  2. What does a scalar subquery return?

    • A list of values
    • A whole table
    • A single value
    • True or false

  3. What problem can NOT IN (subquery) have?

    • It returns no rows if the subquery's list contains NULL
    • It's always slow
    • It ignores duplicates
    • It only works with numbers

  4. What does WITH define?

    • A new permanent table
    • An index
    • A named subquery (CTE) used by the following query
    • A transaction

  5. What must a subquery in the FROM clause have in MySQL and PostgreSQL?

    • A WHERE clause
    • An ORDER BY
    • A LIMIT
    • An alias

Related SQL topics

12JOINCombine rows from related tables with INNER JOIN and LEFT JOIN, using keys and table aliases.19ViewsSave a query as a named view, query it like a table, and use views to simplify reports and limit access.23Window FunctionsCalculate rankings, running totals and comparisons with previous rows using OVER, PARTITION BY and ORDER BY.
Esc
↑↓ to navigateEnter to open