Learn SQL: Subquery
A question inside a question
"Which books are thicker than average?" Easy, you think, until you realise you have to work out the average yourself first. Or you let the database answer two questions at once.
A subquery gives one value
A subquery is a query between brackets, inside another query. The database runs it first and uses the result as if you had typed it in yourself.
This subquery calculates the average number of pages. The outer query keeps the books above it.
SELECT title, pages FROM books WHERE pages > (SELECT AVG(pages) FROM books)| title | pages |
|---|---|
| De stille rivier | 312 |
| Het laatste licht | 401 |
| Draken van het Noorden | 624 |
| The Glass Harbour | 352 |
| De code van Mercator | 336 |
| Sterrenstof | 512 |
| Winter Roads | 298 |
| Moord op de markt | 304 |
A subquery with IN gives a list
If the subquery returns several rows, you use IN, just like with a list you type yourself. Here the subquery gives the ids of books that ever came back late. The outer query finds their titles.
That way you combine two tables without a JOIN.
SELECT title FROM books WHERE id IN (SELECT book_id FROM loans WHERE returned_on > due_on)| title |
|---|
| De stille rivier |
| Nachtwacht in Brugge |
| Draken van het Noorden |
| De code van Mercator |
| Sterrenstof |
Try it yourself
Which members have a book at home right now (not returned yet)? Show their name.
Ready for more?
The full lesson has 6 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson