SQL · Guides · October 30, 2026 · 13 min read

SQL subquery: subqueries in WHERE, SELECT and FROM, with examples

A subquery is a SELECT written inside another query, in brackets. The outer query uses its result the way it would use a value, a list or a table. That gives you three places where subqueries show up, and three shapes they come in:

• in WHERE, as a single value (total_amount > (SELECT AVG(...))) or a list (aircraft_code IN (SELECT ...)), or with EXISTS;
• in SELECT, as a single value computed for each row;
• in FROM, as a whole table you query further (a derived table).

On top of that, a subquery is either independent (it runs once and does not care about the outer row) or correlated (it refers to a column of the outer query and is logically re-evaluated for every outer row). Once you can tell those cases apart, almost every subquery you meet reads easily.

All examples run on the airline database from the Nomto SQL course: 33,121 flights between 104 airports in summer 2026, 262,788 bookings, tickets, flight segments and a loyalty programme. Money is in US dollars. The SQL is PostgreSQL syntax, and every result below was produced by running the query.

Subquery in WHERE: compare with a single value

The classic first subquery: which bookings are above the average booking amount? You cannot write WHERE total_amount > AVG(total_amount), because aggregates are not allowed in WHERE. So you compute the average in a subquery:

SELECT COUNT(*) AS bookings_above_avg
FROM bookings
WHERE total_amount > (SELECT AVG(total_amount) FROM bookings);

The inner query returns exactly one row and one column: $790.26. That is a scalar subquery, and SQL lets you put it anywhere a single value is allowed. The database runs it once, then compares every booking with that number. About a third of the 262,788 bookings are above average, which is normal for money: a few large bookings pull the mean up.

The same pattern finds the row that holds an extreme value:

SELECT book_ref, book_date, total_amount
FROM bookings
WHERE total_amount = (SELECT MAX(total_amount) FROM bookings);

If two bookings shared the maximum, you would get both, which is often what you want and is the difference from ORDER BY total_amount DESC LIMIT 1.

IN: compare with a list

When the subquery returns one column but many rows, use IN. Which aircraft models fly out of Sheremetyevo (SVO)?

SELECT aircraft_code, model
FROM aircrafts
WHERE aircraft_code IN (
  SELECT aircraft_code FROM flights WHERE departure_airport = 'SVO'
)
ORDER BY aircraft_code;

The inner query returns 2,981 codes, one per SVO departure, with lots of repeats. IN does not care: each model either appears in the list or it does not, so each model comes out once.

Compare that with the join you might write instead:

SELECT COUNT(*) AS rows_returned, COUNT(DISTINCT a.aircraft_code) AS models
FROM aircrafts a
JOIN flights f ON f.aircraft_code = a.aircraft_code
WHERE f.departure_airport = 'SVO';

The join returns one row per matching flight, so you would need DISTINCT to get back to eight models. This is the main practical difference between the two: a subquery in WHERE filters rows without multiplying them; a join can multiply them. When you only need to know whether a match exists, a subquery (or EXISTS, below) is the cleaner tool. The SQL joins guide covers this fan-out in detail.

A list subquery can carry its own GROUP BY and HAVING. Which cities have more than one airport, and how many?

SELECT city, COUNT(*) AS airports
FROM airports
WHERE city IN (
  SELECT city FROM airports GROUP BY city HAVING COUNT(*) > 1
)
GROUP BY city
ORDER BY city;

ANY and ALL

IN is shorthand for = ANY (...). The ANY and ALL forms also work with >, < and the other comparisons. Which aircraft fly further than every Airbus in the fleet?

SELECT model, range
FROM aircrafts
WHERE range > ALL (SELECT range FROM aircrafts WHERE model LIKE 'Airbus%')
ORDER BY range DESC;

> ALL (...) is the same as > (SELECT MAX(...)), and most people find the MAX version easier to read. You will meet ANY and ALL in older code and in interview questions more often than in new analytics.

EXISTS and NOT EXISTS: is there at least one matching row?

EXISTS (subquery) is true if the subquery returns at least one row. What the subquery selects does not matter, which is why people write SELECT 1. EXISTS is almost always correlated: the inner query refers to the outer row.

How many loyalty members have never bought a ticket?

SELECT COUNT(*) AS members_without_tickets
FROM loyalty_members m
WHERE NOT EXISTS (
  SELECT 1 FROM ticket_members tm WHERE tm.member_id = m.member_id
);

Read it row by row: for each member m, look for any ticket linked to m.member_id; keep the member if there is none. Out of 53,000 members, 11,426 have joined the programme but have no tickets in this data.

EXISTS also handles conditions that sit two tables away. How many bookings include at least one business-class segment?

SELECT COUNT(*) AS bookings_with_business
FROM bookings b
WHERE EXISTS (
  SELECT 1
  FROM tickets t
  JOIN ticket_flights tf ON tf.ticket_no = t.ticket_no
  WHERE t.book_ref = b.book_ref
    AND tf.fare_conditions = 'Business'
);

A booking with five business segments is counted once. Writing the same thing as a join would need COUNT(DISTINCT b.book_ref) to undo the duplicates.

The NOT IN trap

NOT EXISTS and NOT IN look interchangeable, and usually they give the same answer. Which aircraft model never appears in the schedule?

SELECT aircraft_code
FROM aircrafts
WHERE aircraft_code NOT IN (SELECT aircraft_code FROM flights);

The Airbus A320 is in the fleet but flies nothing this summer. That worked because flights.aircraft_code has no NULLs. Now try the same pattern on a column that does. In app_events, member_id is NULL for visitors who were not logged in. Which loyalty members have no app events?

SELECT COUNT(*) AS members_without_events
FROM loyalty_members
WHERE member_id NOT IN (SELECT member_id FROM app_events);

Zero, and it is wrong. The NOT EXISTS version gives the real answer:

SELECT COUNT(*) AS members_without_events
FROM loyalty_members m
WHERE NOT EXISTS (
  SELECT 1 FROM app_events e WHERE e.member_id = m.member_id
);

x NOT IN (list) means “x differs from every element”. If the list contains a NULL, the comparison x <> NULL is unknown, so the whole condition is never true and every row is dropped. The inner query here returns 912,625 NULLs. The fix is either NOT EXISTS, or NOT IN (SELECT member_id FROM app_events WHERE member_id IS NOT NULL), which returns the same 19,376. The habit worth building: use NOT EXISTS for “rows with no match”. The guide to NULL in SQL explains the three-valued logic behind this.

Correlated subqueries: compare each row with its own group

A scalar subquery can be correlated too. How many segments cost more than the average for their own class?

SELECT tf.fare_conditions, COUNT(*) AS segments
FROM ticket_flights tf
WHERE tf.amount > (
  SELECT AVG(t2.amount)
  FROM ticket_flights t2
  WHERE t2.fare_conditions = tf.fare_conditions
)
GROUP BY tf.fare_conditions
ORDER BY segments DESC;

The inner query uses tf.fare_conditions from the outer row, so the average it returns depends on that row: $159.60 for Economy, $327.41 for Comfort, $511.43 for Business. Give both copies of the table different aliases (tf and t2); otherwise you cannot say which one you mean.

“Logically re-evaluated for every row” does not mean the database literally runs the inner query a million times. PostgreSQL and DuckDB both rewrite many correlated subqueries into joins. Still, if a correlated subquery is slow, the same logic is often clearer as a window function: amount > AVG(amount) OVER (PARTITION BY fare_conditions) in a derived table. See SQL window functions.

Subquery in SELECT: a value per row

A scalar subquery in the SELECT list adds one computed value to each output row. How many seats does each model have, and how many flights does it operate?

SELECT a.model,
       (SELECT COUNT(*) FROM seats s WHERE s.aircraft_code = a.aircraft_code) AS seats,
       (SELECT COUNT(*) FROM flights f WHERE f.aircraft_code = a.aircraft_code) AS flights
FROM aircrafts a
ORDER BY seats DESC;

This is a neat way to put two counts from two different tables side by side. Joining aircrafts to both seats and flights and grouping would multiply seats by flights and give nonsense. Note also that the A320 gets a 0, not a missing row: COUNT(*) over no rows is 0.

An uncorrelated scalar subquery in SELECT is handy for shares of a total:

SELECT model,
       (SELECT COUNT(*) FROM seats s WHERE s.aircraft_code = a.aircraft_code) AS seats,
       ROUND(100.0 * (SELECT COUNT(*) FROM seats s WHERE s.aircraft_code = a.aircraft_code)
             / (SELECT COUNT(*) FROM seats), 1) AS pct_of_all_seats
FROM aircrafts a
ORDER BY seats DESC
LIMIT 4;

It works, but the seat count is now written twice. When you see the same subquery repeated, that is the moment to move it into a CTE or a derived table.

Subquery in FROM: a derived table

A subquery in FROM returns a whole table, and the outer query treats it like any other table. This is how you aggregate twice: first per booking, then across bookings. How many tickets does a typical booking contain?

SELECT tickets_in_booking, COUNT(*) AS bookings
FROM (
  SELECT book_ref, COUNT(*) AS tickets_in_booking
  FROM tickets
  GROUP BY book_ref
) AS per_booking
GROUP BY tickets_in_booking
ORDER BY tickets_in_booking;

The inner query produces one row per booking with its ticket count. The outer query groups those rows again. You cannot do this in a single GROUP BY, because COUNT(COUNT(*)) is not allowed. The same derived table gives the average, 1.396 tickets per booking, with SELECT ROUND(AVG(tickets_in_booking), 3) FROM (...) AS per_booking.

Derived tables also combine with joins. Here the departures are counted per airport first, and only then joined to airports for the city name:

SELECT a.city, d.departures
FROM (
  SELECT departure_airport, COUNT(*) AS departures
  FROM flights
  WHERE status = 'Arrived'
  GROUP BY departure_airport
) AS d
JOIN airports a ON a.airport_code = d.departure_airport
ORDER BY d.departures DESC
LIMIT 5;

Moscow appears three times because it has three airports (DME, SVO, VKO), and the grouping happened per airport. If you want one row per city, group by a.city after the join instead: Moscow then has 3,990 departures. Neither is wrong; the subquery makes it obvious which level you aggregated at.

LATERAL: a subquery that sees the outer row in FROM

A normal derived table cannot refer to other tables in the same FROM. LATERAL lifts that restriction, which makes “top N per group” easy to express. The two most frequent destinations from SVO and LED:

SELECT ap.airport_code, ap.city, top.arrival_airport, top.flights
FROM airports ap
CROSS JOIN LATERAL (
  SELECT f.arrival_airport, COUNT(*) AS flights
  FROM flights f
  WHERE f.departure_airport = ap.airport_code
  GROUP BY f.arrival_airport
  ORDER BY flights DESC, f.arrival_airport
  LIMIT 2
) AS top
WHERE ap.airport_code IN ('SVO', 'LED')
ORDER BY ap.airport_code, top.flights DESC;

LATERAL works in PostgreSQL, DuckDB and MySQL 8; SQL Server spells it CROSS APPLY. For most analysts the ROW_NUMBER() approach to top N is more common, but LATERAL is worth recognising.

Subquery, JOIN or CTE?

Performance is rarely the deciding factor: modern optimizers turn IN, EXISTS and many correlated subqueries into joins internally. Choose the form that states the question most directly, then check the plan if it is slow.

Common mistakes

1. A scalar subquery that returns more than one row

SELECT (SELECT aircraft_code FROM flights WHERE departure_airport = 'SVO') AS x;

PostgreSQL stops with “more than one row returned by a subquery used as an expression”, and DuckDB with a similar message. A subquery used as a single value must return at most one row: aggregate it (MAX, COUNT), filter it down to one row, or switch to IN if you really meant a list. If it returns no rows, the value is NULL, which can silently empty a WHERE.

2. NOT IN over a column with NULLs

Covered above: one NULL in the list makes NOT IN return nothing. Use NOT EXISTS.

3. An unqualified column that silently refers to the outer query

SELECT COUNT(*) AS models
FROM aircrafts
WHERE aircraft_code IN (SELECT aircraft_code FROM airports);

airports has no aircraft_code column, yet the query runs and returns all nine models. SQL looked for aircraft_code in the inner table, did not find it, and took it from the outer aircrafts row, so the condition became “aircraft_code equals itself”. Always qualify columns with table aliases inside subqueries; then a typo becomes an error instead of a wrong answer.

4. A derived table without an alias

SELECT COUNT(*) FROM (SELECT 1 FROM aircrafts) runs in DuckDB and in PostgreSQL 16 and later. PostgreSQL 15 and older, and some other databases, reject it with “subquery in FROM must have an alias”. Writing AS t costs nothing and works everywhere.

5. ORDER BY inside a subquery

An ORDER BY inside a derived table does not guarantee the order of the final result. Sort in the outermost query. Inside a subquery, ORDER BY only matters together with LIMIT, as in the LATERAL example.

FAQ

What is a subquery in SQL?

A SELECT statement nested inside another statement, in brackets. The outer query uses its result as a single value, a list of values, or a table, depending on where the subquery is placed.

What is the difference between a correlated and a non-correlated subquery?

A non-correlated subquery does not reference the outer query and can be run on its own; it is evaluated once. A correlated subquery references a column of the outer query (WHERE t2.fare_conditions = tf.fare_conditions), so its result depends on the current outer row.

Is a subquery slower than a JOIN?

Not by nature. PostgreSQL, DuckDB and other modern engines rewrite most IN and EXISTS subqueries into joins. Write the version that expresses the question best, and look at the query plan only if it is actually slow.

When should I use EXISTS instead of IN?

Use EXISTS when the condition depends on the outer row or involves several columns, and always prefer NOT EXISTS over NOT IN for “rows with no match”, because NOT IN breaks when the list contains a NULL.

Can a subquery return multiple columns?

In FROM, yes: a derived table can have any number of columns. In WHERE with IN, a subquery normally returns one column, though PostgreSQL also supports row comparisons such as (a, b) IN (SELECT x, y ...). In SELECT, a subquery must return a single value.

Also in Español, Português (Brasil), Tiếng Việt, 简体中文