Chapter 09 · Subqueries, CTEs, and Set Operations
Scalar, Row, and Table Subqueries
A subquery is a SELECT statement embedded inside another statement. Its meaning depends on the context that consumes its result: one value, one row, or a table-shaped relation.
Learning outcomes
A subquery is classified by the shape expected by its surrounding expression. Cardinality is part of correctness: a scalar context expects one column and one value; a row context expects a fixed tuple width; a table context accepts many rows and columns.
Distinguish scalar, row-value, and table subqueries by their consuming context.
Use uncorrelated scalar subqueries in SELECT and WHERE clauses.
Use row-value subqueries for multi-column membership tests.
Use derived tables in FROM and state their result grain explicitly.
Three subquery shapes
| Shape | Typical location | Expected result | Example use |
|---|---|---|---|
| Scalar | SELECT list, WHERE comparison, expression | One column; logically one value | Compare a price with the catalog average. |
| Row value | Tuple comparison or multi-column IN | A fixed number of columns | Match each customer to their latest timestamp. |
| Table | FROM, JOIN, IN, EXISTS | Any compatible set of rows | Aggregate sales first, then join the summary. |
Scalar subquery as a computed value
SELECT product_name, unit_price, (SELECT ROUND(AVG(unit_price), 2) FROM product) AS catalog_averageFROM productORDER BY product_id;The inner query is uncorrelated: it has no reference to the outer product row. Conceptually, it produces one value that can be reused for every output row.
Scalar subquery in a predicate
SELECT product_id, product_name, unit_priceFROM productWHERE unit_price > ( SELECT AVG(unit_price) FROM product)ORDER BY unit_price DESC, product_id;The inner aggregate returns exactly one row even when the input is empty. Its value would be NULL for an empty product table, making the comparison unknown and returning no rows.
Cardinality is a contract
Scalar contract
One column should produce one value for the surrounding expression.
No-row result
A scalar subquery with no row becomes NULL.
Multiple-row risk
Most engines reject multiple rows in a scalar context.
Dialect caveat
SQLite uses the first row, so an accidental multi-row scalar query may hide a bug.
Write a scalar subquery whose logic guarantees one row, usually with an aggregate, a unique predicate, or a deterministic ORDER BY ... LIMIT 1. PostgreSQL and several other systems report an error when more than one row reaches a scalar context.
Row-value subquery
SELECT s.customer_id, s.sale_id, s.sold_atFROM sale AS sWHERE (s.customer_id, s.sold_at) IN ( SELECT customer_id, MAX(sold_at) FROM sale GROUP BY customer_id)ORDER BY s.customer_id, s.sale_id;The left tuple has two fields, so the subquery must also return two columns. If two sales tie for the latest timestamp, both rows satisfy the tuple membership test.
Table subquery in FROM
SELECT c.customer_id, c.full_name, totals.sale_count, totals.units_purchasedFROM customer AS cJOIN ( SELECT customer_id, COUNT(*) AS sale_count, SUM(quantity) AS units_purchased FROM sale GROUP BY customer_id) AS totals ON totals.customer_id = c.customer_idORDER BY totals.units_purchased DESC, c.customer_id;The derived table has one row per customer ID. Naming that grain prevents an accidental many-to-many join and makes the outer query easier to reason about.
Subquery versus join
| Question | Subquery-oriented form | Join-oriented form |
|---|---|---|
| One global value per row | Scalar subquery in SELECT | CROSS JOIN a one-row aggregate. |
| Membership | IN or row-value IN | INNER JOIN, with duplicate risk. |
| Existence only | EXISTS | Semi-join concept; ordinary JOIN may multiply rows. |
| Reusable summarized relation | Derived table or CTE | JOIN the named summary. |
Practice database
Run this setup once in a disposable SQLite database. Chapter 9 uses customer and sales data for subqueries, an employee hierarchy for recursion, and two lead lists for set operations.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS event_lead;DROP TABLE IF EXISTS web_lead;DROP TABLE IF EXISTS employee;DROP TABLE IF EXISTS sale;DROP TABLE IF EXISTS product;DROP TABLE IF EXISTS customer;CREATE TABLE customer ( customer_id INTEGER PRIMARY KEY, full_name TEXT NOT NULL, city TEXT, segment TEXT NOT NULL CHECK (segment IN ('consumer', 'business')), email TEXT NOT NULL UNIQUE) STRICT;CREATE TABLE product ( product_id INTEGER PRIMARY KEY, product_name TEXT NOT NULL, category TEXT NOT NULL, unit_price REAL NOT NULL CHECK (unit_price >= 0)) STRICT;CREATE TABLE sale ( sale_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customer(customer_id), product_id INTEGER NOT NULL REFERENCES product(product_id), quantity INTEGER NOT NULL CHECK (quantity > 0), status TEXT NOT NULL CHECK (status IN ('paid', 'pending', 'refunded')), sold_at TEXT NOT NULL) STRICT;CREATE TABLE employee ( employee_id INTEGER PRIMARY KEY, employee_name TEXT NOT NULL, manager_id INTEGER REFERENCES employee(employee_id), job_title TEXT NOT NULL) STRICT;CREATE TABLE web_lead (email TEXT NOT NULL) STRICT;CREATE TABLE event_lead (email TEXT NOT NULL) STRICT;INSERT INTO customer VALUES (1, 'Nadia Rahimi', 'Tehran', 'consumer', 'nadia@example.com'), (2, 'Omar Haddad', 'Berlin', 'business', 'omar@example.com'), (3, 'Lina Chen', NULL, 'consumer', 'lina@example.com'), (4, 'Ava Morgan', 'Berlin', 'consumer', 'ava@example.com'), (5, 'Noah Silva', 'Lisbon', 'business', 'noah@example.com'), (6, 'Marta Costa', NULL, 'business', 'marta@example.com');INSERT INTO product VALUES (10, 'Database Foundations', 'course', 49.00), (11, 'SQL Query Practice', 'course', 69.00), (12, 'SQL Reference Card', 'book', 15.00), (13, 'SQLite Lab Bundle', 'lab', 29.00), (14, 'Data Quality Workbook', 'book', 24.50), (15, 'Database Operations', 'course', 89.00);INSERT INTO sale VALUES (100, 1, 10, 1, 'paid', '2026-08-01 09:15:00'), (101, 2, 11, 3, 'paid', '2026-08-01 10:45:00'), (102, 1, 12, 2, 'paid', '2026-08-02 11:30:00'), (103, 4, 10, 1, 'pending', '2026-08-03 13:05:00'), (104, 2, 13, 2, 'paid', '2026-08-03 15:20:00'), (105, 5, 15, 1, 'paid', '2026-08-04 16:40:00'), (106, 3, 14, 4, 'refunded', '2026-08-05 08:10:00'), (107, 5, 12, 5, 'paid', '2026-08-05 12:00:00'), (108, 2, 15, 1, 'pending', '2026-08-06 09:25:00'), (109, 4, 13, 3, 'paid', '2026-08-06 14:50:00');INSERT INTO employee VALUES (1, 'Amina Yusuf', NULL, 'Chief Data Officer'), (2, 'Jonas Weber', 1, 'Data Engineering Manager'), (3, 'Sara Kim', 1, 'Analytics Manager'), (4, 'Reza Nouri', 2, 'Data Engineer'), (5, 'Elena Rossi', 2, 'Platform Engineer'), (6, 'Maya Patel', 3, 'Analytics Engineer'), (7, 'Leo Martin', 3, 'BI Developer'), (8, 'Daria Novak', 4, 'Junior Data Engineer');INSERT INTO web_lead VALUES ('nadia@example.com'), ('omar@example.com'), ('new-web@example.com'), ('shared@example.com'), ('shared@example.com');INSERT INTO event_lead VALUES ('omar@example.com'), ('lina@example.com'), ('new-event@example.com'), ('shared@example.com'), ('shared@example.com');Each table exposes a different query-composition problem: one-to-many sales, customers without matches, a self-referencing hierarchy, and overlapping lists with duplicates.
Practice lab
- List products priced above the average course price.
- Show each customer beside the total number of customers.
- Return the latest sale row for every customer.
- Build a derived table with paid units per customer and join it to customer names.
- Explain the grain of each inner query before running it.
SELECT c.full_name, paid.paid_unitsFROM customer AS cJOIN ( SELECT customer_id, SUM(quantity) AS paid_units FROM sale WHERE status = 'paid' GROUP BY customer_id) AS paid ON paid.customer_id = c.customer_idORDER BY paid.paid_units DESC, c.customer_id;Checkpoint
Identify the expected shape
- What makes a subquery scalar?
- What does a zero-row scalar subquery produce?
- Why is a multi-row scalar query dangerous in SQLite?
- What width must a row-value subquery return?
- Why should a derived table have an explicit grain?
Review the answers
A scalar subquery returns one column used as one value. Zero rows becomes NULL. SQLite taking the first row can conceal a cardinality error. Tuple widths must match. An explicit grain prevents incorrect joins and duplicate multiplication.
Summary and references
- Context determines whether a subquery acts as a value, row, or table.
- Scalar subqueries need a guaranteed one-value contract.
- Row-value subqueries support multi-column comparisons and membership.
- Derived tables are temporary relations and should be named and grained clearly.