Subqueries, EXISTS and derived tables

A subquery is a SELECT in parentheses used inside another statement. Where it sits decides what it must return.

• Scalar subquery — in a SELECT list or a comparison. It must return at most one row and one column; no row means NULL, more than one row is an error.
• Table subquery — after IN, EXISTS, or in FROM. It returns a set of rows.

Correlated subqueries

A subquery that refers to a column of the outer query is correlated: logically it runs once per outer row, with that row's values.

1CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL);2CREATE TABLE orders (3  id          int PRIMARY KEY,4  customer_id int,5  amount      int NOT NULL6);7INSERT INTO customers VALUES (1, 'Nino'), (2, 'Ana'), (3, 'Luka'), (4, 'Mari');8INSERT INTO orders VALUES (10, 1, 120), (11, 1, 80), (12, 2, 300), (13, NULL, 50);

1SELECT2  c.name,3  (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id)    AS orders,4  (SELECT MAX(o.amount) FROM orders o WHERE o.customer_id = c.id) AS largest5FROM customers c6ORDER BY c.id;

COUNT(*) over no rows is 0, but MAX over no rows is NULL — Luka and Mari have no orders, so they get both. A correlated subquery in the SELECT list is easy to read; on a large table, the planner may or may not turn it into a join, so check the execution plan (the indexes lesson shows how to read one) before trusting it with a million rows.

EXISTS and NOT EXISTS

EXISTS (subquery) is true when the subquery returns at least one row. It does not care what the row contains — SELECT 1 is the usual idiom — and it never returns unknown. That makes NOT EXISTS the safe way to ask "which rows have no match?":

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

Compare it with NOT IN on the same data, where one order has no customer:

1CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL);2CREATE TABLE orders (3  id          int PRIMARY KEY,4  customer_id int,5  amount      int NOT NULL6);7INSERT INTO customers VALUES (1, 'Nino'), (2, 'Ana'), (3, 'Luka'), (4, 'Mari');8-- order 13 is a guest checkout: no customer9INSERT INTO orders VALUES (10, 1, 120), (11, 1, 80), (12, 2, 300), (13, NULL, 50);

1SELECT2  (SELECT COUNT(*) FROM customers c3    WHERE c.id NOT IN (SELECT o.customer_id FROM orders o)) AS not_in,4  (SELECT COUNT(*) FROM customers c5    WHERE NOT EXISTS (SELECT 1 FROM orders o6                      WHERE o.customer_id = c.id))         AS not_exists;

The guest order's NULL customer id ends up in the NOT IN list, and the NULL lesson explained what that does: every comparison becomes unknown and nobody qualifies. NOT EXISTS finds the two customers who never ordered.

Derived tables

A subquery in FROM is a derived table. It needs an alias, and it is how you aggregate the result of an aggregation — for example "the average of each customer's total", which is not the same number as "the average order".

1CREATE TABLE orders (2  id          int PRIMARY KEY,3  customer_id int NOT NULL,4  amount      int NOT NULL5);6INSERT INTO orders VALUES7  (1, 1, 10), (2, 1, 10), (3, 1, 10), (4, 1, 10), (5, 1, 10),8  (6, 1, 10), (7, 1, 10), (8, 1, 10), (9, 1, 10), (10, 1, 10),9  (11, 2, 90),10  (12, 3, 20);

1SELECT2  (SELECT AVG(amount) FROM orders) AS avg_per_order,3  (SELECT AVG(t.total)4     FROM (SELECT customer_id, SUM(amount) AS total5           FROM orders6           GROUP BY customer_id) AS t)  AS avg_per_customer;

Ten small orders from one customer pull the per-order average down to 17.5; averaged per customer it is 70. Which one a question means is the first thing to settle.

Common mistakes

• NOT IN (subquery) on a nullable column. One NULL and the result is empty, as shown above. Use NOT EXISTS.
• A scalar subquery that can return two rows. It works in testing and fails in production on the first customer with two matching rows ("more than one row returned by a subquery used as an expression").
• Forgetting the derived table's alias. Standard SQL requires one, and MySQL and SQL Server reject a derived table without it; PostgreSQL accepts it since version 16, so a query can pass here and fail elsewhere.
• Averaging the wrong grain. AVG(amount) over orders is not the average customer total; aggregate per customer first.

What interviewers ask

• What is the difference between IN and EXISTS? IN compares a value with a list; EXISTS asks whether a correlated query returns any row. The real difference shows with NOT: NOT IN breaks on a NULL, NOT EXISTS does not.
• One that references the outer query's columns, so its result depends on the current outer row.