SQL subqueries explained for beginners

Queries inside queries

Remember that the result of a query is itself a little table? That means you can put a query inside another one. The inner one is called a subquery, and it lives in brackets.

Say you want every song longer than average. You know how to get the average:

SELECT AVG(seconds) FROM songs;

You can't write WHERE seconds > AVG(seconds), because aggregates aren't allowed in WHERE (it runs row by row, remember). But you can drop the whole query in:

SELECT title, seconds
FROM songs
WHERE seconds > (SELECT AVG(seconds) FROM songs);

The database runs the inner query first, gets one number, then uses it in the outer query as if you had typed it in. The best part: if you add more songs, the average updates by itself. Hard-coding 212.3 would go stale.

A subquery that returns a list pairs nicely with IN:

SELECT title FROM songs
WHERE artist_id IN (SELECT id FROM artists WHERE country = 'Japan');

The inner query makes a list of ids; the outer one keeps songs whose artist_id is in it. Flip it to NOT IN to find the opposite. One warning there: if the list contains even a single NULL, NOT IN matches nothing at all, the same NULL weirdness as before. Filtering NULLs out inside the subquery fixes it.

Many subqueries can also be written as joins, and neither is "the right one". Pick the version you can read out loud and understand. Subqueries are often easiest when the question is in two steps: first work out this, then use it.

Tip: write and run the inner query on its own first. If that part is right, the outer part is usually easy.

Tables in this lesson

artists 6 rows

id	name	country
1	Lofi Owl	Japan
2	Gravel	UK
3	Kai Neon	USA
4	Pixel Choir	Japan
5	Velvet Static	Ireland
6	The Toasters	UK

songs 10 rows

id	title	artist_id	seconds	plays
1	Homework Can Wait	1	152	12000
2	Thunder Lunchbox	2	254	870
3	Love You Like WiFi	3	231	9100
4	The Final Boss	4	176	3300
5	Monday Again	2	189	2100
6	Rainy Bus Window	1	245	640
7	Glow Up	3	166	15000
8	The Long Goodbye	5	302	450
9	Skate Park Sunset	5	210	1800
10	Lovesick Robot	6	198	5400

Show the SQL that built them

CREATE TABLE artists (id INTEGER PRIMARY KEY, name TEXT, country TEXT);
INSERT INTO artists VALUES
 (1, 'Lofi Owl', 'Japan'),
 (2, 'Gravel', 'UK'),
 (3, 'Kai Neon', 'USA'),
 (4, 'Pixel Choir', 'Japan'),
 (5, 'Velvet Static', 'Ireland'),
 (6, 'The Toasters', 'UK');
CREATE TABLE songs (id INTEGER PRIMARY KEY, title TEXT, artist_id INTEGER, seconds INTEGER, plays INTEGER);
INSERT INTO songs VALUES
 (1, 'Homework Can Wait', 1, 152, 12000),
 (2, 'Thunder Lunchbox', 2, 254, 870),
 (3, 'Love You Like WiFi', 3, 231, 9100),
 (4, 'The Final Boss', 4, 176, 3300),
 (5, 'Monday Again', 2, 189, 2100),
 (6, 'Rainy Bus Window', 1, 245, 640),
 (7, 'Glow Up', 3, 166, 15000),
 (8, 'The Long Goodbye', 5, 302, 450),
 (9, 'Skate Park Sunset', 5, 210, 1800),
 (10, 'Lovesick Robot', 6, 198, 5400);

Try it yourself

Edit it. Break it. Run it again.

-- Songs longer than the average song
SELECT title, seconds
FROM songs
WHERE seconds > (SELECT AVG(seconds) FROM songs);

Your turn

Type it yourself. That is the whole trick.

1Crowd favourites

Return the title and plays of every song with more plays than the average number of plays. Work the average out with a subquery, don't type the number in.

SELECT title, plays
FROM songs
WHERE plays > 1000;

2UK sounds

Return the title of every song by an artist from the UK, using a subquery with IN.

SELECT title
FROM songs
WHERE artist_id IN ();