Subqueries and WITH

Introduction

A subquery is a SELECT inside another statement, in brackets. The DBMS works it out and uses its result.

Some questions depend on the answer to another question: who scored above the average, which customers have no orders. Subqueries let one query use another's result.

How it works

A subquery that gives one value

"Who scored above the average?" needs the average first:

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT,
  dept TEXT,
  marks INTEGER
);
CREATE TABLE departments (
  code TEXT PRIMARY KEY,
  building TEXT
);
INSERT INTO students (name, dept, marks) VALUES
  ('Rahim', 'CSE', 85), ('Karim', 'EEE', 72), ('Nadia', 'CSE', 91),
  ('Tania', 'BBA', 64), ('Sumon', 'CSE', 58), ('Rina', 'EEE', 79);
INSERT INTO departments VALUES ('CSE', 'Science'), ('EEE', 'Science'), ('BBA', 'Business');

SELECT ROUND(AVG(marks), 2) AS average FROM students;

-- Above the average of everyone
SELECT name, marks FROM students
WHERE marks > (SELECT AVG(marks) FROM students)
ORDER BY marks DESC;

-- Students whose department is in the Science building
SELECT name, dept FROM students
WHERE dept IN (SELECT code FROM departments WHERE building = 'Science');

• (SELECT AVG(marks) FROM students) gives one value, so it can stand anywhere a number can.
• WHERE dept IN (SELECT ...) uses a subquery that gives a list: one column, any number of rows.
• NOT IN is the opposite, with one trap: if the list contains a NULL, NOT IN is never true and the result is empty. NOT EXISTS, below, has no such trap.

Correlated subqueries

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT,
  dept TEXT,
  marks INTEGER
);
CREATE TABLE clubs (
  student_id INTEGER REFERENCES students(id),
  club TEXT
);
INSERT INTO students (name, dept, marks) VALUES
  ('Rahim', 'CSE', 85), ('Karim', 'EEE', 72), ('Nadia', 'CSE', 91),
  ('Tania', 'BBA', 64), ('Sumon', 'CSE', 58), ('Rina', 'EEE', 79);
INSERT INTO clubs VALUES (1, 'Programming'), (3, 'Programming'), (3, 'Debate'), (6, 'Robotics');

-- Above the average of their own department
SELECT s.name, s.dept, s.marks FROM students s
WHERE s.marks > (SELECT AVG(marks) FROM students WHERE dept = s.dept);

-- In at least one club
SELECT name FROM students s
WHERE EXISTS (SELECT 1 FROM clubs c WHERE c.student_id = s.id);

-- In no club
SELECT name FROM students s
WHERE NOT EXISTS (SELECT 1 FROM clubs c WHERE c.student_id = s.id);

The first query's subquery mentions s.dept, a column of the outer query's row. It is a correlated subquery: it is worked out again for every outer row ("the average of this student's department").

EXISTS (subquery) is true when the subquery returns at least one row; what the rows contain does not matter, so people write SELECT 1. NOT EXISTS finds rows with no match, like the LEFT JOIN ... IS NULL trick.

Syntax

SELECT name FROM students WHERE marks > (SELECT AVG(marks) FROM students);

WITH totals AS (SELECT seller, SUM(amount) AS total FROM sales GROUP BY seller)
SELECT seller FROM totals WHERE total > 1000;

Example

A long query is easier to read in steps. WITH (a common table expression) gives a subquery a name, used like a table in the query that follows:

CREATE TABLE sales (
  seller TEXT,
  month TEXT,
  amount INTEGER
);
INSERT INTO sales VALUES
  ('Rahim', '2026-01', 500), ('Rahim', '2026-02', 700),
  ('Nadia', '2026-01', 900), ('Nadia', '2026-02', 400),
  ('Karim', '2026-01', 300), ('Karim', '2026-02', 350);

WITH totals AS (
  SELECT seller, SUM(amount) AS total FROM sales GROUP BY seller
)
SELECT seller, total
FROM totals
WHERE total > (SELECT AVG(total) FROM totals)
ORDER BY total DESC;

Many subqueries can also be written as joins, and the DBMS often runs both the same way. Choose the one that is easiest to read.

Line by line

1. sales has two months of sales for each of three sellers.
2. WITH totals AS (...) names a step: the total sales of each seller.
3. The main query reads totals as if it were a table.
4. (SELECT AVG(total) FROM totals) uses the same step again, to get the average total.
5. The totals are 1200 for Rahim, 1300 for Nadia and 650 for Karim, so the average is 1050. Nadia and Rahim are above it, and come out from the largest total down.

Expected result

seller  total
------  -----
Nadia   1300
Rahim   1200
(2 rows)

In the real world

Reports that compare a value with an average, find missing records or rank items are often written with subqueries and WITH. Long analytical queries in data teams are usually a chain of WITH steps, each one easy to test on its own.

Common mistakes

• Using a subquery that returns several rows where one value is expected.
• Using NOT IN with a list that can contain NULL, which returns no rows at all.
• Forgetting to link a correlated subquery to the outer row, so it gives the same answer for every row.

Best practices

• Prefer NOT EXISTS to NOT IN when the column may contain NULL.
• Give WITH steps names that say what they hold.
• Check that a correlated subquery really needs to run for every row, and use a join or GROUP BY when it does not.

Summary

Subqueries let a query use another query's result: one value, a list for IN, or a test with EXISTS. Correlated subqueries run per outer row, and WITH names steps to keep long queries readable.

Mark lesson complete