Description

Login
Back

Intermediate · 18 min

Subqueries

Use a query result inside another query.

Sample database

CREATE TABLE groups (id INTEGER PRIMARY KEY, label TEXT NOT NULL);
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, group_id INTEGER REFERENCES groups(id));
CREATE TABLE sessions (id INTEGER PRIMARY KEY, member_id INTEGER NOT NULL REFERENCES members(id), topic TEXT NOT NULL, minutes INTEGER NOT NULL CHECK(minutes > 0), completed INTEGER NOT NULL CHECK(completed IN (0, 1)), day TEXT NOT NULL);
INSERT INTO groups VALUES (1, 'Morning'), (2, 'Evening');
INSERT INTO members VALUES (1, 'Ada', 'Oslo', 1), (2, 'Bo', 'Rome', 1), (3, 'Cy', NULL, 2), (4, 'Dee', 'Oslo', 2);
INSERT INTO sessions VALUES
  (1, 1, 'Travel', 20, 1, '2026-01-01'),
  (2, 1, 'Food', 10, 0, '2026-01-02'),
  (3, 2, 'Travel', 30, 1, '2026-01-01'),
  (4, 2, 'Music', 15, 1, '2026-01-03'),
  (5, 3, 'Music', 25, 0, '2026-01-02'),
  (6, 3, 'Travel', 15, 1, '2026-01-04');

Compare with a scalar result

A scalar subquery should return one value. An aggregate without grouping is useful for a whole-table comparison. SQLite has particular behavior for multi-row scalar results; write queries that avoid that ambiguity.

SELECT id, minutes FROM sessions WHERE minutes > (SELECT AVG(minutes) FROM sessions) ORDER BY id;

Output

id | minutes
1 | 20
3 | 30
5 | 25

Filter using a result set

IN can use the rows returned by a subquery. The inner query here selects member IDs with completed practice. Duplicated IDs in that result do not duplicate rows in the outer query.

SELECT name FROM members WHERE id IN (SELECT member_id FROM sessions WHERE completed = 1) ORDER BY id;

Output

name
Ada
Bo
Cy

Put it into practice

  1. Read the sample tables and predict the result before running the query.
  2. Solve the task, compare the returned rows, and explain why the solution works.

Try it yourself

Code runs on this device. When you are signed in, drafts sync to your account.

Run queries on a fresh sample database. Table changes last for this run only; your query draft is saved separately. Results show column names followed by rows. NULL means a missing value.

Apply what you learned

Return session IDs and durations above the overall average duration, ordered by ID.

Show solution
SELECT id, minutes FROM sessions WHERE minutes > (SELECT AVG(minutes) FROM sessions) ORDER BY id;

The scalar average is compared with each session duration.

Practice