SQL Subqueries Explained: Queries Inside Queries

Quick Answer

A SQL subquery is a query written inside another query, usually wrapped in parentheses. You use it to compute a value or a list that the outer query then filters or reports on. Subqueries can live in the WHERE, FROM, or SELECT clause, and they come in two types: non-correlated (runs once on its own) and correlated (runs once per outer row). For single-value lookups a subquery is often clearest, but for pulling columns across tables a JOIN is usually clearer and faster.

What is a SQL subquery?

A sql subquery is simply a query written inside another query. The inner query (the subquery) runs first and hands its result to the outer query, which then uses that result to filter, calculate, or display data. You will also hear it called a nested query or inner query.

Here is the smallest useful example. Suppose you want every product priced above the average price:

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

The part in parentheses, SELECT AVG(price) FROM products, is the subquery. It returns a single number, and the outer query compares each product's price against it. You could not write this cleanly as one flat query, because you need a summary of the whole table and the individual rows at the same time.

Subqueries can appear in three places: the WHERE clause, the FROM clause, and the SELECT list. We will look at each. New to basics like SELECT and WHERE? Our free SQL course covers them from scratch.

Subquery in the WHERE clause

This is the most common home for a subquery. The idea: the inner query produces a value or a list, and the outer WHERE filters against it.

Comparison operators (single value)

When the subquery returns exactly one value, use =, >, <, and friends:

SELECT name, city
FROM customers
WHERE city = (SELECT city FROM customers WHERE name = 'Aarav');

Gotcha: if that subquery returns more than one row, the database throws an error. Use single-value subqueries only when you are sure the result is one row.

IN (a list of values)

When the subquery returns many rows, use IN:

SELECT name
FROM customers
WHERE customer_id IN (
  SELECT customer_id FROM orders WHERE amount > 5000
);

This reads almost like English: show customers whose id is in the list of ids that placed an order above 5000.

Subquery in the FROM clause

A subquery in the FROM clause acts like a temporary table that exists only for this one query. It is called a derived table, and it must be given an alias.

SELECT city, avg_amount
FROM (
  SELECT c.city, AVG(o.amount) AS avg_amount
  FROM customers c
  JOIN orders o ON o.customer_id = c.customer_id
  GROUP BY c.city
) AS city_stats
WHERE avg_amount > 3000;

Here the inner query builds a small result set of one row per city with its average order value. The outer query then keeps only cities averaging above 3000. This is handy when you need to filter on an aggregate (AVG, SUM, COUNT), which you cannot do directly in a plain WHERE.

Note the alias. The AS city_stats is required — most databases reject a derived table without a name.

Subquery in the SELECT list

You can also drop a subquery into the SELECT list to compute an extra column for each row. It must return a single value, which is why this is called a scalar subquery.

SELECT
  c.name,
  (SELECT COUNT(*)
   FROM orders o
   WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c;

For every customer, the inner query counts that customer's orders. Notice how the subquery refers to c.customer_id from the outer query — that makes it a correlated subquery, which we explain next.

Gotcha: a SELECT-list subquery runs once per outer row, so on large tables it can be slow. A LEFT JOIN with GROUP BY often produces the same result faster.

Correlated vs non-correlated subqueries

This is the single most important distinction to understand.

A non-correlated subquery is independent. It does not reference the outer query, so it runs once, produces its result, and the outer query uses it. Our average-price example is non-correlated — SELECT AVG(price) FROM products makes sense on its own.

A correlated subquery depends on the outer query. It references a column from the outer row, so it re-runs once for every row the outer query processes:

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

Here the inner query cannot run alone — it needs c.customer_id from the current outer row. For each customer, the database checks "does at least one matching order exist?" That is exactly what EXISTS is for.

Why it matters: correlated subqueries are more flexible but can be slower, because they run repeatedly. Non-correlated subqueries run just once.

Choosing IN, EXISTS, or a comparison

Three tools cover almost every subquery filter. Here is when to reach for each.

• Comparison (=, >, <): use when the subquery returns a single value, like an average or a maximum.
• IN: use when the subquery returns a list and you want to check membership.
• EXISTS: use when you only care whether any matching row exists, not the values themselves. It stops at the first match, so it can be faster.

The classic NOT IN trap: if the subquery can return a NULL, then NOT IN returns no rows at all — not what you expect.

-- If any customer_id in orders is NULL, this returns nothing:
SELECT name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

The safer choice is NOT EXISTS, which handles NULLs correctly:

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

Subquery vs JOIN: the same result, two ways

Many subqueries can be rewritten as a JOIN, and vice versa. Here is the same question — "which customers spent over 5000 on a single order?" — done both ways.

With a subquery:

SELECT name
FROM customers
WHERE customer_id IN (
  SELECT customer_id FROM orders WHERE amount > 5000
);

With a JOIN:

SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 5000;

Both return the same names. Notice the JOIN needs DISTINCT: if one customer has three orders above 5000, the join produces three rows, so you must remove duplicates. The subquery with IN never duplicates, which is why it often reads more cleanly for "does a match exist" questions.

Which should you use? A simple rule

Here is a simple rule of thumb for beginners:

• Need columns from more than one table in your result? Use a JOIN.
• Only filtering the main table by "does a match exist" or "is it in this set"? A subquery with IN or EXISTS is clear and safe.
• Filtering on an aggregate like average or count? Use a subquery in FROM (a derived table).

Performance is usually not the deciding factor on modern databases — the query planner often rewrites a subquery into a join internally. Write the version that is easiest to read first, then optimise only if a real query is measurably slow.

A few final gotchas to remember:

• Single-value subqueries must return exactly one row, or you get an error.
• Derived tables (subqueries in FROM) need an alias.
• Prefer NOT EXISTS over NOT IN when NULLs are possible.

Want to practise these on real tables? Work through the hands-on lessons in our free SQL course.

Frequently Asked Questions