A query inside a query
A SELECT in parentheses can stand in for a value, a list, or a table.
Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.
What it is for
Comparing against a computed value (the average, a threshold from a settings table) without a second round trip.
How to think about it
Does the inner query give one value, a list, or a table? Write the inner query alone first, check it gives what you expect, then paste it in parentheses where that value belongs.
Worked example
SELECT title FROM filmsThe outer question.
WHERE minutes > (SELECT AVG(minutes) FROM films);The inner query is one number.
Your turn
Films by directors from France.
WHERE director_id (SELECT id FROM directors WHERE country = 'FR')
Run a query against real tables
The trap
A subquery that returns many rows where one value is expected: '= (SELECT ...)' errors. Use IN, or aggregate.
Practise subqueries on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.