SQL queries inside queries: subqueries, EXISTS, CTEs and recursion

"Which orders are above the average?" "Which customers have never ordered?" "Show every day of the month, even the days with no sales." None of these can be answered with one plain join and one GROUP BY. They need a result that depends on another result.

A weak answer writes one huge query that nobody can read, or uses NOT IN and gets an empty result without knowing why. A strong answer builds the query in named steps, and chooses the right tool for "does a row exist".

At SDE-1 level you should write a simple subquery. At SDE-2 level you should use WITH, EXISTS, and the NOT IN trap. At Senior level you should also write a recursive query for a calendar or a tree.

A subquery is a query inside another query, written in brackets. Think of a manager who says: "Show me the orders bigger than our average order." To answer, the clerk first finds the average. Then the clerk uses that number to pick the orders. The inner question comes first.

A scalar subquery: one value

A subquery that returns exactly one value is a scalar subquery. You can use it wherever a value fits. If the inner query returns more than one row, Postgres stops with an error, but SQLite quietly uses the first row. That is a silent wrong answer, so check that the inner query has one row. A plain AVG cannot sit in WHERE, because WHERE runs before any group exists (lesson 45).

SELECT (SELECT amount FROM orders) AS value_of_24_rows;

value_of_24_rows
----------------
             440

The table has 24 orders, and SQLite returned the amount of the first one. Now the aggregate that cannot sit in WHERE.

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

Error: misuse of aggregate function AVG()

SELECT id, amount FROM orders WHERE amount > (SELECT AVG(amount) FROM orders) ORDER BY amount DESC, id LIMIT 4;

id  amount
--  ------
10     640
 5     600
20     600
 1     440

IN with a subquery, and the NULL trap

IN (SELECT ...) tests if a value is in the list that the inner query returns. Customers who have a cancelled order:

SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE status = 'cancelled') ORDER BY id;

name
-----
Karan
Sana
Priya

Lesson 43 showed that NOT IN with a NULL in the list returns nothing. This is the same trap, but it hides now. The list comes from a column, and one NULL in that column is enough. Remember that the guest order has a NULL customer_id.

EXISTS and NOT EXISTS

EXISTS (subquery) is true when the inner query returns at least one row. What the inner query selects does not matter, so people write SELECT 1. EXISTS is a correlated subquery when the inner query uses a column of the outer row. To read it, imagine that the inner query runs once for each outer row. The database often finds a faster way.

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

name
----
Dev
Neha

NOT EXISTS has no NULL trap: it asks "is there a matching row?", and a NULL never matches. A correlated subquery can also return a value. Orders above the average of their own customer:

SELECT o.id, o.customer_id, o.amount FROM orders AS o
WHERE o.amount > (SELECT AVG(o2.amount) FROM orders AS o2 WHERE o2.customer_id = o.customer_id)
ORDER BY o.id;

id  customer_id  amount
--  -----------  ------
 3            3     360
 5            4     600
 6            5     340
 7            2     400
10            1     640
13            3     360
14            5     340
17            6     340
20            4     600
23            7     260

A subquery in FROM

A subquery in FROM is a derived table. It must have an alias. Here the inner query counts orders for each customer, and the outer query keeps those with 3 or more.

SELECT t.customer_id, t.n FROM (SELECT customer_id, COUNT(*) AS n FROM orders GROUP BY customer_id) AS t WHERE t.n >= 3 ORDER BY t.n DESC, t.customer_id;

customer_id  n
-----------  -
          1  5
          2  4
          3  4
          4  3
          5  3

WITH: a query in named steps

A common table expression (CTE) gives a name to a query, and the main query then uses the name like a table. It reads from top to bottom, but it is not faster by itself. You can write several, separated by commas, and a later one can use an earlier one. A CTE can also be used twice in the main query. Use one name for each idea.

WITH delivered AS (
  SELECT customer_id, amount FROM orders WHERE status = 'delivered' AND customer_id IS NOT NULL
),
per_customer AS (
  SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS money FROM delivered GROUP BY customer_id
)
SELECT c.name, p.orders, p.money
FROM per_customer AS p JOIN customers AS c ON c.id = p.customer_id
WHERE p.money > (SELECT AVG(money) FROM per_customer)
ORDER BY p.money DESC;

name   orders  money
-----  ------  -----
Asha        5   2280
Meena       4   1350

The CTE per_customer is used twice: in the join and in the average. Build a long query like this: write one step, run it, then add the next.

Stacking results: UNION, INTERSECT, EXCEPT

UNION ALL puts the rows of two queries one below the other. UNION does the same and removes repeated rows. Both queries need the same number of columns, in the same order. ORDER BY goes only once, at the very end. INTERSECT keeps rows found in both queries. EXCEPT keeps rows of the first query that are not in the second. They treat NULLs as equal.

SELECT city FROM customers UNION SELECT city FROM restaurants ORDER BY city;

city
---------
Bengaluru
Delhi
Mumbai
Pune

SELECT customer_id FROM orders WHERE status = 'delivered' INTERSECT SELECT customer_id FROM orders WHERE status = 'cancelled' ORDER BY customer_id;

customer_id
-----------
          4
          5
          7

SELECT customer_id FROM orders WHERE customer_id IS NOT NULL EXCEPT SELECT customer_id FROM orders WHERE status = 'cancelled' ORDER BY customer_id;

customer_id
-----------
          1
          2
          3
          6

VALUES: a table you type

VALUES makes a small table from a list. It is useful for a list of bands or categories, so that a group with no rows still shows. The names in brackets after bands name its columns.

WITH bands(name, low, high) AS (VALUES ('small', 0, 249), ('medium', 250, 449), ('big', 450, 999), ('huge', 1000, 9999))
SELECT b.name, COUNT(o.id) AS orders
FROM bands AS b LEFT JOIN orders AS o ON o.amount BETWEEN b.low AND b.high
GROUP BY b.name ORDER BY b.low;

name    orders
------  ------
small        5
medium      16
big          3
huge         0

Recursive CTE: a query that uses itself

A recursive CTE repeats. It has two parts joined by UNION ALL. The first part is the start. The second part reads the CTE itself and makes the next rows from the last rows. It stops when the second part returns no new rows, so it must have a stop rule. Without one it never ends. In the Tradeoff runner the 5 second limit stops it.

A series of numbers is the simplest case:

WITH RECURSIVE n(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM n WHERE x < 5
)
SELECT x FROM n;

x
-
1
2
3
4
5

The same idea builds a list of days. A calendar of days is useful because your data has no rows for days without events. The table logins has one row for each day a user logged in. It has 15 rows:

SELECT * FROM logins;

user_id  day
-------  ----------
      1  2026-03-01
      1  2026-03-02
      1  2026-03-03
      1  2026-03-05
      1  2026-03-06
      1  2026-03-07
      1  2026-03-08
      1  2026-03-09
      2  2026-03-01
      2  2026-03-03
      2  2026-03-04
      3  2026-03-02
      3  2026-03-03
      3  2026-03-04
      3  2026-03-05

Which days did user 1 not log in, from 1 to 9 March?

WITH RECURSIVE days(day) AS (
  SELECT '2026-03-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM days WHERE day < '2026-03-09'
)
SELECT day FROM days WHERE day NOT IN (SELECT day FROM logins WHERE user_id = 1);

day
----------
2026-03-04

The same idea finds missing numbers. Build the numbers from 1 to the biggest one, and keep those that have no row in the table. Here the numbers 1, 2, 4 and 7 are the tickets that exist:

WITH RECURSIVE tickets(n) AS (VALUES (1), (2), (4), (7)),
nums(n) AS (
  SELECT 1 UNION ALL SELECT n + 1 FROM nums WHERE n < (SELECT MAX(n) FROM tickets)
)
SELECT n AS missing FROM nums WHERE NOT EXISTS (SELECT 1 FROM tickets AS t WHERE t.n = nums.n);

missing
-------
      3
      5
      6

For a tree, the start is one person and each step follows manager_id. We carry a level to count the steps, and a limit on it as a guard against a loop in bad data. Everyone under Anita, with a path:

WITH RECURSIVE team(id, name, path, level) AS (
  SELECT id, name, name, 0 FROM employees WHERE id = 2
  UNION ALL
  SELECT e.id, e.name, team.path || ' > ' || e.name, team.level + 1
  FROM employees AS e JOIN team ON e.manager_id = team.id
  WHERE team.level < 10
)
SELECT level, path FROM team ORDER BY path;

level  path
-----  ------------------------------------
    0  Anita Rao
    1  Anita Rao > Deepa Iyer
    2  Anita Rao > Deepa Iyer > Tarun Joshi
    1  Anita Rao > Imran Shaikh
    1  Anita Rao > Pooja Nair

A recursive query walks down the team under Anita Rao, one level at a time. Level 0 is the start row, Anita. Level 1 holds the people who report to her: Deepa, Imran and Pooja. Level 2 holds Tarun, who reports to Deepa. At level 3 nobody reports to Tarun, so no new rows come and the query stops.

To go up the tree, start with the person and join the other way (e.id = chain.manager_id).

The second highest value

Sort, skip one, and take one: ORDER BY ... LIMIT 1 OFFSET 1. Put it in a scalar subquery. If there is no second value, the subquery returns no row and the outer SELECT shows NULL, which is the answer you want.

SELECT (SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1) AS second_highest,
       (SELECT DISTINCT salary FROM employees WHERE department = 'Management' ORDER BY salary DESC LIMIT 1 OFFSET 1) AS second_in_management;

second_highest  second_in_management
--------------  --------------------
        160000  NULL

Which tool to use

• Join: you need columns from the other table. Rows can repeat if the other side has many matches.
• IN: a short list of values. Add IS NOT NULL inside the subquery if it can return NULL.
• EXISTS or NOT EXISTS: "has any" and "has none". Safe with NULLs, and no repeated rows.
• CTE: any query with more than one idea. It costs nothing and helps the reader.

In Postgres and MySQL

• Postgres has generate_series(1, 5) for a series of numbers or dates, so it needs no recursive CTE for them. MySQL has no such function.
• Postgres and MySQL need the word RECURSIVE after WITH. SQLite accepts it. MySQL gives an error after 1,000 rounds by default.
• MySQL has INTERSECT and EXCEPT only from version 8.0.31.
• A derived table needs an alias in MySQL. Postgres needed one before version 16.

The owner asks three things.

Question 1: "Who never ordered?" First try, with NOT IN:

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

name
----
(no rows)

No rows. This is the answer to the predict box. The guest order has a NULL customer_id, so the list has a NULL and every test is unknown. Two fixes work.

SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);

name
----
Dev
Neha

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

name
----
Dev
Neha

Both return Dev and Neha. The NOT EXISTS form cannot fail this way, so we prefer it.

Question 2: "Orders for each day of 1 to 7 March, with 0 for days with none." The calendar first, then a left join, then a count.

WITH RECURSIVE days(day) AS (
  SELECT '2026-03-01' UNION ALL SELECT date(day, '+1 day') FROM days WHERE day < '2026-03-07'
)
SELECT d.day, COUNT(o.id) AS orders
FROM days AS d LEFT JOIN orders AS o ON date(o.ordered_at) = d.day
GROUP BY d.day ORDER BY d.day;

day         orders
----------  ------
2026-03-01       0
2026-03-02       1
2026-03-03       1
2026-03-04       0
2026-03-05       1
2026-03-06       0
2026-03-07       1

COUNT(o.id) gives 0 for a day with no order, as lesson 45 explained. A plain GROUP BY on the orders would not show 1, 4 and 6 March at all.

Question 3: "Who is above the average order of their own customer, and who is in the chain above Tarun?" The first part was the correlated subquery above. For the chain upward:

WITH RECURSIVE chain(id, name, manager_id, level) AS (
  SELECT id, name, manager_id, 0 FROM employees WHERE id = 9
  UNION ALL
  SELECT e.id, e.name, e.manager_id, chain.level + 1 FROM employees AS e JOIN chain ON e.id = chain.manager_id
)
SELECT level, name FROM chain ORDER BY level;

level  name
-----  -----------
    0  Tarun Joshi
    1  Deepa Iyer
    2  Anita Rao
    3  Rohit Mehra

Tarun reports to Deepa, Deepa to Anita, and Anita to Rohit. Rohit has no manager, so the recursion stops there.

• It solves: questions that depend on another result, empty groups, and trees of unknown depth, with a readable top-to-bottom query.
• It costs: a correlated subquery may run once for each outer row, which is slow on big tables. A recursive query can run for ever if the data has a loop. A CTE can be run again each time it is used, depending on the database.
• I would change it when: the query is slow. Then I look at the query plan, and I may change a correlated subquery into a join with a grouped table. For a tree that is read often, I would store the path or the depth in the table.
• Say what each CTE means in one phrase.

"NOT IN and NOT EXISTS are the same." They differ when the list has a NULL. NOT IN returns nothing, NOT EXISTS still works.

"A CTE is faster than a subquery." Not by itself. It makes the query easier to read. The speed depends on the database and the plan.

"A recursive CTE stops by itself." It stops only when the second part returns no new rows. A tree with a loop, or a missing stop rule, runs until a limit stops it.

1. What is the difference between UNION and UNION ALL? Where does ORDER BY go?
2. A recursive CTE starts at 1 and adds 1 while x < 5. How many rows does it return? How many if the rule is x <= 5?
3. A report of "users who did not buy" uses WHERE user_id NOT IN (SELECT user_id FROM purchases) and returns no rows, but you know such users exist. What is the likely cause, and what do you write instead?