Nest one query inside another to compare against an aggregate, filter on a set or test for existence.
Open this lesson in KodokonA subquery is a query placed in parentheses inside another one. In a single statement it answers questions like "the orders above the average" or "the customers with at least one order". Run this script in sqliteonline.com or sqlite3 before you begin.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount REAL NOT NULL
);
INSERT INTO customers (id, name) VALUES
(1, 'Alice'), (2, 'Karim'),
(3, 'Mina'), (4, 'Paulo');
INSERT INTO orders (id, customer_id, amount)
VALUES
(1, 1, 49.90), (2, 1, 15.00),
(3, 2, 120.50), (4, 2, 80.00),
(5, 3, 9.90), (6, 1, 200.00);Placed in WHERE, a scalar subquery must return a single value: one row, one column. The engine computes it first, then uses it like any other constant. You cannot do this with a plain WHERE amount > AVG(amount), which is forbidden in SQL.
SELECT id, customer_id, amount
FROM orders
WHERE amount > (
SELECT AVG(amount) FROM orders
);IN compares a column against the set of values returned by the subquery. EXISTS tests whether the correlated subquery (it references c.id, a column of the outer query) returns at least one row: the engine stops as soon as the first match is found.
SELECT name FROM customers
WHERE id IN (
SELECT customer_id FROM orders
WHERE amount > 100
);
SELECT c.name FROM customers AS c
WHERE EXISTS (
SELECT 1 FROM orders AS o
WHERE o.customer_id = c.id
);In FROM, a subquery behaves like a temporary table, called a derived table, and must be given an alias. It is the classic tool for aggregating in two steps: first a total per customer, then the average of those totals.
SELECT ROUND(AVG(t.total), 2) AS avg_basket
FROM (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
) AS t;