Subqueries and WITH: A Query Inside a Query

When the filter value is itself an answer

Consider finding the movies rated above average. That WHERE clause cannot be written with the tools so far, because the average is not a number you know. It is itself an answer the database has to compute.

Fetching the average in application code and pasting it into a second query works, but it costs two round trips and the value can go stale in between. A subquery solves this: a complete SELECT, wrapped in parentheses, sitting inside another query.

SELECT title, rating FROM movies
WHERE rating > (SELECT AVG(rating) FROM movies);

The database runs the inner query first, gets one value back, and uses it exactly where a constant would go. A subquery returning a single value like this is called a scalar subquery, and it can appear anywhere a number could: in WHERE, in the SELECT list, even inside ORDER BY.

One query, and a value that is always current.

Movies rated above the average

The first query prints the average on its own, then the divider, then the subquery version finds the movies above it without the number 8.2 ever being typed.

CREATE TABLE movies (
  title TEXT,
  year INTEGER,
  rating REAL,
  genre TEXT
);

INSERT INTO movies VALUES ('Inside Out', 2015, 8.1, 'animation');
INSERT INTO movies VALUES ('The Matrix', 1999, 8.7, 'scifi');
INSERT INTO movies VALUES ('Arrival', 2016, 7.9, 'scifi');
INSERT INTO movies VALUES ('Paddington 2', 2017, 7.8, 'family');
INSERT INTO movies VALUES ('Alien', 1979, 8.5, 'scifi');

SELECT ROUND(AVG(rating), 2) FROM movies;
SELECT '---';
SELECT title, rating FROM movies
WHERE rating > (SELECT AVG(rating) FROM movies)
ORDER BY rating DESC;

Output

8.2
---
The Matrix|8.7
Alien|8.5

Only The Matrix at 8.7 and Alien at 8.5 clear the 8.2 average. Inside Out at 8.1 misses it by a tenth, which is a good reminder that "above average" is usually a minority of rows rather than half of them.

Note that the inner query has no WHERE of its own, so it averages the whole table while the outer query filters. Giving the subquery its own filter, such as averaging only sci-fi, is perfectly legal and changes the threshold each row is compared against.

Subqueries that return a list, feeding IN

A subquery can also return a whole column of values, and it then plugs into IN from lesson 2-2. That gives a second way to write the find-the-missing query from lesson 6-1:

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

Read it from the inside out: collect every customer_id that appears in orders, then keep the customers whose id is not in that list.

One real-world caution applies. If the list contains a NULL, NOT IN returns no rows at all, because of the NULL comparison rules from lesson 2-3, since the database cannot prove that an id is unequal to an unknown value.

That is why many engineers default to the LEFT JOIN ... IS NULL pattern for find-the-missing work, and keep NOT IN for columns that genuinely cannot be NULL.

Finding the missing rows with NOT IN

Dan is customer 4 from lesson 6-1 and has no orders, so his id never appears in the subquery's list and NOT IN keeps him.

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  city TEXT
);

CREATE TABLE orders (
  id INTEGER,
  customer_id INTEGER,
  item TEXT,
  price INTEGER
);

INSERT INTO customers VALUES (1, 'Ana', 'Lima');
INSERT INTO customers VALUES (2, 'Ben', 'Tokyo');
INSERT INTO customers VALUES (3, 'Cara', 'Paris');
INSERT INTO customers VALUES (4, 'Dan', 'Oslo');

INSERT INTO orders VALUES (101, 1, 'coffee', 5);
INSERT INTO orders VALUES (102, 2, 'sandwich', 8);
INSERT INTO orders VALUES (103, 1, 'bagel', 4);
INSERT INTO orders VALUES (104, 3, 'coffee', 5);
INSERT INTO orders VALUES (105, 2, 'coffee', 5);

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

Output

Dan

The inner query produces the list 1, 2, 1, 3, 2, and the outer query keeps every customer whose id is absent from it. Duplicates in that list are harmless, since IN only asks about membership.

Compare this with the LEFT JOIN version in lesson 6-1. Both return Dan, and the subquery reads more like the English sentence while the left join is usually easier for the database to optimize and is immune to the NULL trap described above.

WITH: naming a subquery

Nesting reads inside-out, and past one level that gets hard to follow. WITH (formally a common table expression, or CTE) lets you name a subquery up front and then use the name like a table, so the query reads top to bottom:

WITH totals AS (
  SELECT customer_id, SUM(price) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT customers.name, totals.total
FROM totals
JOIN customers ON customers.id = totals.customer_id;

First compute per-customer totals and call the result totals, then join it to customers for names. Same answer as lesson 5-3's join + GROUP BY, but each step has a name, and in real codebases, where queries run to dozens of lines, WITH is what keeps them readable. You can even stack several: WITH a AS (...), b AS (...) SELECT ....

The same report written with a CTE

The WITH clause builds the per-customer totals first, and the outer query then joins the names on. Compare it with the version in lesson 5-3: the same rows, a different shape.

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  city TEXT
);

CREATE TABLE orders (
  id INTEGER,
  customer_id INTEGER,
  item TEXT,
  price INTEGER
);

INSERT INTO customers VALUES (1, 'Ana', 'Lima');
INSERT INTO customers VALUES (2, 'Ben', 'Tokyo');
INSERT INTO customers VALUES (3, 'Cara', 'Paris');
INSERT INTO customers VALUES (4, 'Dan', 'Oslo');

INSERT INTO orders VALUES (101, 1, 'coffee', 5);
INSERT INTO orders VALUES (102, 2, 'sandwich', 8);
INSERT INTO orders VALUES (103, 1, 'bagel', 4);
INSERT INTO orders VALUES (104, 3, 'coffee', 5);
INSERT INTO orders VALUES (105, 2, 'coffee', 5);

WITH totals AS (
  SELECT customer_id, SUM(price) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT customers.name, totals.total
FROM totals
JOIN customers ON customers.id = totals.customer_id
ORDER BY customers.name;

Output

Ana|9
Ben|13
Cara|5

totals behaves like a small temporary table that exists for the duration of this one statement, with the columns customer_id and total. Nothing is created on disk and nothing needs cleaning up afterwards.

Dan is absent because he has no order rows to group, so totals has no row for him and the inner join drops him. A left join from customers to totals would list him with a NULL total instead.

The real argument for a CTE is readability. Once a query grows past two or three stages, naming each step lets a reader follow it top to bottom instead of unpicking nested parentheses inside out.

When NOT IN silently returns nothing

If WHERE id NOT IN (SELECT customer_id FROM orders) suddenly returns zero rows after a data change, the likely cause is a NULL appearing in orders.customer_id.

NOT IN can never be true against a list that contains a NULL. NULL means unknown, per lesson 2-3, so the database cannot prove that any id is unequal to it, and the whole condition comes back neither true nor false for every row.

The failure mode is nasty because it looks like a legitimate answer. "No customers have failed to order" is a plausible sentence, and nothing in the output hints at a data problem.

The LEFT JOIN ... IS NULL pattern from lesson 6-1 has no such trap, which is why many engineers reach for it by default and keep NOT IN for columns that genuinely cannot be NULL.

Customers who have ordered at least once

Flipping NOT IN to IN turns the find-the-missing query into its opposite.

CREATE TABLE customers (
  id INTEGER,
  name TEXT,
  city TEXT
);

CREATE TABLE orders (
  id INTEGER,
  customer_id INTEGER,
  item TEXT,
  price INTEGER
);

INSERT INTO customers VALUES (1, 'Ana', 'Lima');
INSERT INTO customers VALUES (2, 'Ben', 'Tokyo');
INSERT INTO customers VALUES (3, 'Cara', 'Paris');
INSERT INTO customers VALUES (4, 'Dan', 'Oslo');

INSERT INTO orders VALUES (101, 1, 'coffee', 5);
INSERT INTO orders VALUES (102, 2, 'sandwich', 8);
INSERT INTO orders VALUES (103, 1, 'bagel', 4);
INSERT INTO orders VALUES (104, 3, 'coffee', 5);
INSERT INTO orders VALUES (105, 2, 'coffee', 5);

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

Output

Ana
Ben
Cara

The subquery is unchanged, collecting every customer_id that appears in orders, and this time the outer query keeps the ids that are in that list. Dan is left out.

Plain IN is also safe with NULLs in the list, unlike NOT IN. A NULL simply fails to match anything, which is the behavior you want here, so this direction of the query needs no special caution.