Subqueries in SELECT, WHERE and FROM

A subquery is a SELECT written in parentheses inside another statement. What it gives the outer query depends on where it stands. In SELECT or WHERE, a scalar subquery returns a single value, one row with one column, which the outer query uses like a constant: price_paise > (SELECT avg(price_paise) FROM products) compares every product with the average. In FROM, a subquery is a derived table: a result with its own name that the outer query reads like a table, which lets you filter, join or aggregate a result that is itself an aggregate. Subqueries let one statement do what would otherwise take two queries and a value copied between them, and the engines run the inner query for you.

Press Run on any example to try it: the queries execute in sql.js 1.14.2, a build of SQLite 3.49.1 for the browser, and what PostgreSQL 18 and MySQL 8.4 do instead is taken from their manuals.

Three places for a subquery

Subqueries in SELECT, WHERE and FROM differ in what they return, and so in what the outer query may do with them:

The third kind, a subquery that tests membership, is the subject of the next lesson. This one covers the first two.

A scalar subquery is one value

The two queries below use the same subquery, once in WHERE and once in SELECT:

-- Nine products of a grocery shop, with prices in paise.
CREATE TABLE products (
  id          INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  price_paise INTEGER NOT NULL
);
INSERT INTO products VALUES
  (1, 'Masala tea 250 g',    'drinks', 18000), (2, 'Filter coffee 500 g', 'drinks', 42000),
  (3, 'Green tea 100 g',     'drinks', 24000), (4, 'Basmati rice 5 kg',   'grains', 65000),
  (5, 'Wheat atta 10 kg',    'grains', 48000), (6, 'Red lentils 1 kg',    'grains', 14000),
  (7, 'Ghee 1 l',            'dairy',  62000), (8, 'Paneer 200 g',        'dairy',   9000),
  (9, 'Curd 400 g',          'dairy',   5000);

-- 1. A scalar subquery in WHERE: the products that cost more than the average product.
SELECT name, price_paise / 100.0 AS rupees
FROM products
WHERE price_paise > (SELECT avg(price_paise) FROM products)
ORDER BY price_paise DESC;

-- 2. The same subquery in SELECT, where it supplies one value for every row.
SELECT name,
       price_paise / 100.0                                              AS rupees,
       round((SELECT avg(price_paise) FROM products) / 100.0, 2)        AS average_rupees,
       round((price_paise - (SELECT avg(price_paise) FROM products)) / 100.0, 2) AS difference
FROM products
ORDER BY difference DESC
LIMIT 4;

┌─────────────────────┬────────┐
│        name         │ rupees │
├─────────────────────┼────────┤
│ Basmati rice 5 kg   │ 650.0  │
│ Ghee 1 l            │ 620.0  │
│ Wheat atta 10 kg    │ 480.0  │
│ Filter coffee 500 g │ 420.0  │
└─────────────────────┴────────┘
┌─────────────────────┬────────┬────────────────┬────────────┐
│        name         │ rupees │ average_rupees │ difference │
├─────────────────────┼────────┼────────────────┼────────────┤
│ Basmati rice 5 kg   │ 650.0  │ 318.89         │ 331.11     │
│ Ghee 1 l            │ 620.0  │ 318.89         │ 301.11     │
│ Wheat atta 10 kg    │ 480.0  │ 318.89         │ 161.11     │
│ Filter coffee 500 g │ 420.0  │ 318.89         │ 101.11     │
└─────────────────────┴────────┴────────────────┴────────────┘

The average of the nine prices is 318.89 rupees, and four products cost more. In the second query the subquery supplies that average to every row, so each product can show its distance from it. Scalar subqueries in SELECT lists work like a column whose value is the same on every row. Because this subquery does not refer to the outer row, the engine computes it once and reuses the result; SQLite’s manual says so in those words.

A derived table is a result you can query

A derived table turns a result into something the outer query can treat as a table. That is how you filter on an aggregate without HAVING, and how you aggregate twice:

-- Fourteen orders from five cities, with amounts in paise.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  city         TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1,  'Pune',    45000), (2,  'Kochi',   12000), (3,  'Pune',    30000), (4,  'Jaipur',   8000),
  (5,  'Pune',    99000), (6,  'Kochi',   15000), (7,  'Indore',  52000), (8,  'Kochi',    7000),
  (9,  'Jaipur',  26000), (10, 'Pune',    18000), (11, 'Indore',  21000), (12, 'Guwahati', 64000),
  (13, 'Jaipur',  33000), (14, 'Kochi',   40000);

-- 1. A derived table: revenue per city, computed by the inner query and read like a table by the outer one.
SELECT city, orders, rupees
FROM (SELECT city, count(*) AS orders, sum(amount_paise) / 100.0 AS rupees
      FROM orders
      GROUP BY city) AS per_city
WHERE orders >= 3
ORDER BY rupees DESC;

-- 2. An aggregate of aggregates needs two steps: the average and the best of the five city totals.
SELECT count(*) AS cities, round(avg(rupees), 2) AS average_city, max(rupees) AS best_city
FROM (SELECT city, sum(amount_paise) / 100.0 AS rupees
      FROM orders
      GROUP BY city) AS per_city;

-- 3. In one step it is an error: aggregates cannot be nested.
SELECT avg(sum(amount_paise)) FROM orders GROUP BY city;

┌────────┬────────┬────────┐
│  city  │ orders │ rupees │
├────────┼────────┼────────┤
│ Pune   │ 4      │ 1920.0 │
│ Kochi  │ 4      │ 740.0  │
│ Jaipur │ 3      │ 670.0  │
└────────┴────────┴────────┘
┌────────┬──────────────┬───────────┐
│ cities │ average_city │ best_city │
├────────┼──────────────┼───────────┤
│ 5      │ 940.0        │ 1920.0    │
└────────┴──────────────┴───────────┘

Parse error near line 28: misuse of aggregate function sum()

The inner query of the first statement adds up the orders of each city, and the outer query sees a small table, per_city, with five rows and the columns city, orders and rupees. Its WHERE keeps the three cities with at least three orders. The second statement needs two steps by nature: the average of the city totals, 940 rupees, is an aggregate over aggregates. Written in one step, as the third statement tries, it fails in every engine, because aggregates cannot be nested; SQLite calls it a misuse of sum(), PostgreSQL says that aggregate function calls cannot be nested, and MySQL reports error 1111.

The name after the parentheses, per_city, is the derived table’s alias. Give one every time: MySQL refuses a derived table without it.

When the one-value promise breaks

A scalar subquery promises one row. The data does not always keep that promise, and the engines react differently:

• When it returns two or more rows, PostgreSQL and MySQL stop with an error, while SQLite quietly uses the first row it finds.
• When it returns no rows, the value is NULL in every engine, and a comparison with NULL keeps nothing.
• Joining two tables that both have several rows per customer multiplies the rows, so totals must be aggregated in derived tables first.
• Names differ too: MySQL needs an alias for every derived table, and SQLite rejects a column list after it.

Your turn

-- Products priced above the average of their own category: name, above_by_rupees.
SELECT name,
       (price_paise - (SELECT avg(price_paise) FROM products)) / 100.0 AS above_by_rupees
FROM products
WHERE price_paise > (SELECT avg(price_paise) FROM products);

# Sample tests: each one builds a small products table, runs your query on it and compares the rows it returns (in any
# order).
tests:
  - name: compares each product with its own category
    setup: |
      CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL, price_paise INTEGER NOT NULL);
      INSERT INTO products VALUES
        (1, 'Masala tea', 'drinks', 20000), (2, 'Filter coffee', 'drinks', 40000),
        (3, 'Basmati rice', 'grains', 90000), (4, 'Wheat atta', 'grains', 50000), (5, 'Red lentils', 'grains', 10000);
    expected:
      columns: [name, above_by_rupees]
      rows:
        - ['Filter coffee', 100]
        - ['Basmati rice', 400]
  - name: leaves out a product that costs exactly the average
    setup: |
      CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL, price_paise INTEGER NOT NULL);
      INSERT INTO products VALUES
        (1, 'Ghee', 'dairy', 60000), (2, 'Paneer', 'dairy', 30000), (3, 'Curd', 'dairy', 0),
        (4, 'Butter', 'dairy', 30000), (5, 'Green tea', 'drinks', 25000);
    expected:
      columns: [name, above_by_rupees]
      rows:
        - ['Ghee', 300]
  - name: rounds to the paisa when the average is not a whole number
    setup: |
      CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL, price_paise INTEGER NOT NULL);
      INSERT INTO products VALUES
        (1, 'Jaggery', 'sweets', 10000), (2, 'Sugar', 'sweets', 12000), (3, 'Honey', 'sweets', 25000);
    expected:
      columns: [name, above_by_rupees]
      rows:
        - ['Honey', 93.33]

Two rows or none: a scalar subquery in three engines Pro: opens with a Pro, Premium or Ultimate pass

Derived tables that stop double counting, with numbers Pro: opens with a Pro, Premium or Ultimate pass

Derived tables that run in PostgreSQL, MySQL and SQLite Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• A scalar subquery returns one value and can stand wherever a value can, in SELECT, WHERE or ORDER BY; a subquery that does not refer to the outer row is computed once.
• A derived table is a subquery in FROM with a name; it makes two-step questions, such as an average of totals or a filter on an aggregate, into one statement.
• Two rows from a scalar subquery are an error in PostgreSQL and MySQL and a silent first row in SQLite; no rows give NULL everywhere.
• Name every derived table, so that the same query runs in MySQL, PostgreSQL and SQLite.