(SELECT ...)

Subqueries

SQLite in your browser
Lesson concept What you'll do
1 / 4

Filter by a number or a list that another query works out.

Put a whole query in brackets inside WHERE. It runs first, and its answer is used.

The database
8 rows
Click a name to type it
The database
9 rows
Click a name to type it
Your query
Hint: click the green edit button on the board, or type it yourself
Query output Ready to run

Run your query to see the result table.

Press the green Run button or Ctrl+Enter.

Study notes

How (SELECT ...) works

One answer: the inner query runs first, then drops into the outer one

  1. 1. The inner query runs first

    SELECT AVG(pages)
FROM books
    AVG(pages)
    312

  2. 2. Its answer goes into WHERE

    SELECT title, pages
FROM books
WHERE pages > (312);
    titlepages
    Dracula418
    Emma474
    Dune412

Change the books and the average changes with them. A typed-in 312 would not.

A list: IN checks each row against the inner query's column

  1. 1. The inner query gives a list

    SELECT book_id
FROM loans
    book_id
    2
    5
    2
    7

  2. 2. IN keeps rows whose id is in it

    SELECT title
FROM books
WHERE book_id IN (2, 5, 7);
    title
    Emma
    Carrie
    The Hobbit

  • What is a subquery?

    A query in brackets inside another query. It runs first, and its answer is used by the outer query, like a cell that feeds a formula.

  • Where can a subquery go?

    Where a value goes: WHERE pages > (SELECT AVG(pages) FROM books). Where a list goes: WHERE book_id IN (SELECT book_id FROM loans). Or where a table goes, after FROM.

  • Why not type the number in?

    A typed number is only right for today's data. The subquery works it out again every time the query runs.

  • Can a subquery give more than one value?

    After = or > it must give one row and one column. After IN it can give a whole column.

  • How is this like Excel?

    Like =FILTER(A2:B11, B2:B11 > AVERAGE(B2:B11)): the AVERAGE is worked out first, then each row is compared with it.

Other databases

How other databases do it

The lessons run on SQLite. Most of SQL is the same everywhere; these are the places where this lesson's SQL differs.

A subquery in FROM needs a name: FROM (SELECT ...) AS t
DatabaseHowNote
SQLite (these lessons)OptionalThe name can be left out, but give one anyway.
PostgreSQLOptionalRequired before PostgreSQL 16.
MySQL / MariaDBRequiredEvery derived table must have its own alias.
SQL ServerRequired
OracleOptionalAnd write it without AS: FROM (SELECT ...) t.
Sybase ASERequired

Know someone fighting with a spreadsheet? Send them this page.