SQL Subqueries Explained: Scalar, IN, EXISTS and Correlated

Some filters cannot be typed as a constant. "Which turbines produced less than the average?" depends on what every other turbine did, so the average has to be computed before the comparison can happen. A subquery, a complete query in brackets used inside another, is the direct way to say that. Interviews use them to test where each kind can go, whether you spot a correlated subquery that runs once per row, and the NOT IN trap that silently returns nothing.

The problem it solves

WHERE filters rows one at a time, before any grouping, so WHERE mwh < AVG(mwh) is an error: when WHERE runs, the average does not exist yet. The order of execution explains why. Two queries from application code would work, at the cost of two round trips and a race if the data changes in between. A subquery keeps it in one statement: the inner query produces the value or the set, and the outer query uses it as if it had been typed in.

The intuition

Subqueries come in a few shapes, sorted by what they return:

• Scalar: exactly one row and one column, so it can stand anywhere a single value can. WHERE mwh < (SELECT AVG(mwh) FROM turbine). If it returns more than one row, most databases raise an error; SQLite quietly uses the first row it produces.
• IN: one column, any number of rows. WHERE name IN (SELECT turbine FROM repair) keeps rows whose value appears in the set.
• EXISTS: any shape; it only asks whether at least one row comes back.
• Derived table: a subquery in FROM, joined like a temporary table.

The other axis is correlation. An uncorrelated subquery stands on its own, so it can run once. A correlated subquery names a column of the outer query, as in "the average of this turbine's own site", so unless the optimiser rewrites it, it runs again for every outer row: fine on five rows, and the shape of the N+1 query problem on five million. The usual rewrite computes the per-group values once, as a derived table joined back, or as a window function.

Then the trap. NOT IN compares the value with every row of the set. If the set contains a NULL, that comparison is unknown, not false, so no row can pass. NOT EXISTS only asks whether a matching row exists, and NULL never matches, so it behaves as intended. The SQL cheat sheet lists the subquery forms next to joins.

Watch it run

The animation opens with the question: which turbines underperformed? "Below average" is not a number you can type, because it depends on what the other machines did, so the bracketed query runs first. It reads all four rows and returns exactly one value, 300.0. A scalar subquery must return one row and one column, because that is where a value goes. And you cannot write WHERE mwh < AVG(mwh) instead, because row filtering happens before the aggregate exists. With the number in hand, the outer query walks the rows. T1 at 410.0 is above it. T2 at 180.0 is below, and it is kept. T3 is above; T4 at 215.0 is below, so it joins the underperformers. Two rows come back, and the inner query ran exactly once for the whole statement. The last frame names the contrast: a correlated subquery names an outer column, so it re-runs for every single row.

The code

Run with Python's sqlite3 module on SQLite 3.37. The lesson's four turbines, now with a site each. The scalar subquery returns 300.0 and the filter keeps two rows; the aggregate in WHERE is rejected, and only the exception class is printed because the message differs between versions:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE turbine (name TEXT PRIMARY KEY, site TEXT, mwh REAL);
  INSERT INTO turbine VALUES ('T1', 'north', 410.0), ('T2', 'north', 180.0),
                             ('T3', 'south', 395.0), ('T4', 'south', 215.0);
""")
print(db.execute("""
  SELECT name, mwh FROM turbine
  WHERE mwh < (SELECT AVG(mwh) FROM turbine)          -- inner runs first: 300.0
""").fetchall())                                       # [('T2', 180.0), ('T4', 215.0)]

try:
    db.execute("SELECT name FROM turbine WHERE mwh < AVG(mwh)")
except sqlite3.OperationalError as e:
    print(type(e).__name__)                            # OperationalError

Counting how often the inner query runs: a Python function registered as tick is called each time the subquery produces its average. With a fifth turbine added, the uncorrelated version runs once; the correlated version, comparing each turbine with its own site, runs five times, once per outer row. A grouped derived table and a window function give the same answer:

db.execute("INSERT INTO turbine VALUES ('T5', 'south', 330.0)")
calls = 0
def tick(x):
    global calls
    calls += 1
    return x
db.create_function("tick", 1, tick)

rows = db.execute("""
  SELECT name FROM turbine
  WHERE mwh < (SELECT tick(AVG(mwh)) FROM turbine)    -- uncorrelated
""").fetchall()
print(rows, calls)                                     # [('T2',), ('T4',)] 1

calls = 0
rows = db.execute("""
  SELECT t.name, t.site FROM turbine t
  WHERE t.mwh < (SELECT tick(AVG(s.mwh)) FROM turbine s
                 WHERE s.site = t.site)               -- correlated: names t.site
  ORDER BY t.name
""").fetchall()
print(rows, calls)                                     # [('T2', 'north'), ('T4', 'south')] 5

JOINED = """
  SELECT t.name, t.site FROM turbine t
  JOIN (SELECT site, AVG(mwh) AS avg_mwh FROM turbine GROUP BY site) a
    ON a.site = t.site
  WHERE t.mwh < a.avg_mwh ORDER BY t.name"""
WINDOWED = """
  SELECT name, site FROM (
    SELECT name, site, mwh, AVG(mwh) OVER (PARTITION BY site) AS avg_mwh FROM turbine)
  WHERE mwh < avg_mwh ORDER BY name"""
print(db.execute(JOINED).fetchall() == rows == db.execute(WINDOWED).fetchall())   # True

IN and NOT IN against a repair log, and then the trap: one repair row with no turbine name makes NOT IN return nothing at all, while NOT EXISTS still returns the three turbines that were never repaired:

db.executescript("""
  CREATE TABLE repair (turbine TEXT);
  INSERT INTO repair VALUES ('T1'), ('T4');
""")
print(db.execute("SELECT name FROM turbine WHERE name IN (SELECT turbine FROM repair) ORDER BY name").fetchall())
# [('T1',), ('T4',)]
print(db.execute("SELECT name FROM turbine WHERE name NOT IN (SELECT turbine FROM repair) ORDER BY name").fetchall())
# [('T2',), ('T3',), ('T5',)]

db.execute("INSERT INTO repair VALUES (NULL)")        # one repair row with no turbine
print(db.execute("SELECT name FROM turbine WHERE name NOT IN (SELECT turbine FROM repair)").fetchall())
# []
print(db.execute("""
  SELECT name FROM turbine t
  WHERE NOT EXISTS (SELECT 1 FROM repair r WHERE r.turbine = t.name)
  ORDER BY name""").fetchall())
# [('T2',), ('T3',), ('T5',)]

Checked against plain Python on 300 seeded random tables with NULL outputs and NULL repair rows. The correlated subquery and the join must match per-site averages computed by hand, and NOT EXISTS must match "never repaired", as must NOT IN unless the log holds a NULL:

import random

CORRELATED = """
  SELECT t.name FROM turbine t
  WHERE t.mwh < (SELECT AVG(s.mwh) FROM turbine s WHERE s.site = t.site) ORDER BY t.name"""
random.seed(24)
ok = True
for _ in range(300):
    g = sqlite3.connect(":memory:")
    g.execute("CREATE TABLE turbine (name TEXT, site TEXT, mwh REAL)")
    g.execute("CREATE TABLE repair (turbine TEXT)")
    data = [(f"T{i}", random.choice("nsew"), random.choice([None] + list(range(0, 500, 25))))
            for i in range(random.randint(1, 12))]
    fixes = [random.choice([None] + [n for n, _, _ in data]) for _ in range(random.randint(0, 4))]
    g.executemany("INSERT INTO turbine VALUES (?, ?, ?)", data)
    g.executemany("INSERT INTO repair VALUES (?)", [(f,) for f in fixes])

    want = []                                          # brute force: plain Python
    for name, site, mwh in data:
        same = [m for _, s, m in data if s == site and m is not None]
        if mwh is not None and same and mwh < sum(same) / len(same):
            want.append(name)
    got = [r[0] for r in g.execute(CORRELATED)]
    ok &= got == sorted(want) == [r[0] for r in g.execute(JOINED.replace("t.name, t.site", "t.name"))]

    unfixed = sorted(n for n, _, _ in data if n not in fixes)
    ok &= [r[0] for r in g.execute(
        "SELECT name FROM turbine t WHERE NOT EXISTS "
        "(SELECT 1 FROM repair r WHERE r.turbine = t.name) ORDER BY name")] == unfixed
    not_in = [r[0] for r in g.execute(
        "SELECT name FROM turbine WHERE name NOT IN (SELECT turbine FROM repair) ORDER BY name")]
    ok &= not_in == ([] if None in fixes else unfixed)   # one NULL empties NOT IN
print(ok)                                              # True

The complexity

• Uncorrelated scalar or IN: the inner query runs once; IN then needs a cheap lookup per outer row.
• Correlated, as written: n outer rows times the cost of the inner query. An index on the correlated column, site here, turns each run into a lookup instead of a scan.
• Rewritten as a join or window function: one pass to build the per-group values, one to compare. Many optimisers rewrite it themselves; check the plan rather than assuming.

Where it goes wrong

• NOT IN over a column that can be NULL. The result is empty. Use NOT EXISTS, or filter the NULLs out inside the subquery.
• A scalar subquery that returns several rows. An error in most databases, and whichever row comes first in SQLite, which is arbitrary without ORDER BY.
• Correlating by accident. A misspelt alias that resolves to the outer table turns a one-off query into a per-row one.
• Aggregates in WHERE. Filter on an aggregate with HAVING or a subquery; see GROUP BY and HAVING.

When it shows up in interviews

Classic SQL prompts are built on subqueries: employees earning more than their department's average, customers who never placed an order, the second-highest salary. The follow-up is nearly always "can you write it without the subquery?", which wants a join against a grouped derived table or a window function, and a sentence on why the rewrite may be faster.

How to say it in an interview

"The average does not exist while WHERE runs, so I compute it in a scalar subquery, which runs once and returns one value. For 'above their own department's average' the subquery must reference the outer row, which makes it correlated and conceptually re-run per row, so I index the correlated column or rewrite it as a join against a grouped derived table, or a window function. For 'never did X' I use NOT EXISTS rather than NOT IN, because a single NULL in the subquery makes NOT IN return no rows."