Lesson 5 of 14 · 7 min · Summarising data

Subqueries: a query inside a query

Covers: PostgreSQL 18 syntax; standard SQL that also runs on MySQL 8. Updated 13 Nov 2026. Part of the Data Analyst roadmap.

What you will learn

  • Subqueries that return one value, a list or a table
  • IN and EXISTS
  • Correlated subqueries
  • A subquery in FROM (a derived table)
  • When a join or CTE is the clearer choice

In simple words

Sometimes one question depends on the answer to another. "Which orders are bigger than the average order?" First you need the average, then you compare. A subquery lets you write the first question inside the second, in brackets, so the database works it out and uses the answer.

Words to know

  • Subquery: a SELECT written inside another statement, in brackets.
  • Scalar subquery: one that returns a single value.
  • Correlated subquery: one that refers to a column of the outer query and runs once per outer row (logically).
  • Derived table: a subquery in FROM that acts like a temporary table.

A subquery that returns one value

SELECT order_id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

The average of the six orders is 916.67, so this returns 101 (1200.00) and 103 (1500.00). Order 105 (900.00) is just below the average and is not returned. If a scalar subquery returns more than one row, the database raises an error.

A subquery that returns a list: IN

SELECT name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE status = 'paid');

returns Asha and Meena, the customers with at least one paid order. NOT IN gives the opposite, but remember the NULL trap from lesson 2: prefer NOT EXISTS.

EXISTS

EXISTS asks "is there at least one matching row?" and returns true or false. The subquery's selected columns do not matter; SELECT 1 is the convention.

SELECT c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.customer_id = c.customer_id AND o.status = 'paid');

The inner query refers to c.customer_id from the outer query, which makes it correlated. EXISTS stops at the first match, handles NULLs safely, and is the right tool for "has any" and (with NOT) "has none" questions.

Correlated subqueries for per-row answers

A correlated subquery can compute something for each outer row. Each customer's most recent order date:

SELECT c.name,
       (SELECT MAX(o.order_date) FROM orders o WHERE o.customer_id = c.customer_id) AS last_order
FROM customers c;
 name  | last_order
-------+------------
 Asha  | 2025-10-20
 Ravi  | 2025-09-20
 Meena | 2025-10-10
 Kiran |
 Divya |

Logically the subquery runs once per customer. Databases often optimise this, but a join with GROUP BY (or a window function, a later lesson) is usually clearer and faster on large tables.

A subquery in FROM

A subquery can be used like a table if you give it an alias:

SELECT t.customer_id, t.total
FROM (SELECT customer_id, SUM(amount) AS total
      FROM orders
      GROUP BY customer_id) AS t
WHERE t.total > 1500;

Totals are Asha 2500, Ravi 1500 and Meena 1500, so only customer 1 passes. This is how you filter on an aggregate and then do more work on it. MySQL and PostgreSQL both require the alias on a derived table.

Join, subquery or CTE?

Situation Reach for
Need columns from both tables join
Only checking whether a match exists EXISTS
One computed value to compare against scalar subquery
Several steps, each building on the last CTE (a later lesson)

Do not worry about "which is faster" first. Write the clearest version, then look at the query plan if it is slow (index lesson). Modern optimisers often turn equivalent forms into the same plan.

Common mistakes

  • A scalar subquery that returns several rows (error), such as comparing to (SELECT amount FROM orders).
  • NOT IN with a nullable column in the subquery.
  • Forgetting the alias on a derived table.
  • Deeply nested subqueries that nobody can read; switch to a CTE.
  • Correlated subqueries on very large tables without checking the plan.

Try it yourself

  1. Find orders larger than the average order amount.
  2. Find customers who have at least one pending order, using IN and again using EXISTS.
  3. Find customers with no orders using NOT EXISTS.
  4. Show each customer with the number of their orders using a correlated subquery, then rewrite it with a join and GROUP BY.
  5. In a derived table, compute totals per customer and keep those above the overall average total.

Check yourself

What is the difference between IN and EXISTS?

IN compares a value to a list the subquery returns. EXISTS only checks whether the subquery returns any row, and it handles NULLs safely.

What is a correlated subquery?

A subquery that uses a column from the outer query, so its result depends on the current outer row.

What happens if a scalar subquery returns two rows?

The database raises an error, because a single value was expected.

Why does a subquery in FROM need an alias?

It is treated as a table, and every table in a query needs a name.

Interview questions

Find the second highest salary.

One way: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);. A more general way uses DENSE_RANK() (window functions lesson) so you can pick the Nth highest.

When would you choose EXISTS over a join?

When you only need to know whether a related row exists and do not need its columns. It avoids duplicate rows from one-to-many matches.

What is the difference between a subquery and a join in performance?

Often none, because the optimiser rewrites them. Correlated subqueries can be slow on large data; check the plan and consider a join or window function.

Summary

  • A subquery is a SELECT in brackets; it can give one value, a list or a table.
  • IN compares to a list; EXISTS checks for any match and is NULL-safe.
  • Correlated subqueries use outer columns; derived tables need an alias.
  • Use a join or CTE when a query becomes hard to read.

Going deeper

See how much of this you can explain

A free mock interview asks you new questions on SQL for analysts and developers for Data Analyst and scores every answer, with what to fix.