SQL from Zero › Module 5: Combining tables
Subqueries and CASE WHEN
Use one query's answer inside another, and label rows with your own categories.
Lesson 27 of 33 · about 15 minutes
Video: Questions inside questionsThe narrated video for this lesson is coming soon. The lesson below covers the same ideas.
The lesson
A subquery is a query inside brackets whose answer is used by the outer query.
"Which books cost more than average?" needs the average first. You can't write WHERE price > AVG(price), because WHERE checks one row at a time. A subquery works it out first:
SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books);
The database runs the inner query, gets one number, and then uses it like any other value.
A subquery can return a list for IN:
SELECT title
FROM books
WHERE author_id IN (SELECT id FROM authors WHERE country = 'Nigeria');
CASE WHEN labels rows with your own categories. It checks each condition in turn and uses the first one that's true:
SELECT title, price,
CASE
WHEN price < 10 THEN 'Budget'
WHEN price < 15 THEN 'Standard'
ELSE 'Premium'
END AS price_band
FROM books;
A $12 book isn't under 10, but it is under 15, so it's 'Standard'. ELSE catches everything left. Always finish with END.
Key ideas
- A subquery in brackets runs first, and its answer is used by the outer query.
- Use a one-value subquery with > or =, and a list subquery with IN.
- CASE WHEN ... THEN ... ELSE ... END labels rows; the first true condition wins.
Practise
Loading practice…
Quick check
1. In this query, what runs first?
SELECT title FROM books WHERE price > (SELECT AVG(price) FROM books);2. A book costs $8. Which label does it get?
CASE WHEN price < 10 THEN 'Budget' WHEN price < 15 THEN 'Standard' ELSE 'Premium' END