Subqueries

The Idea

A subquery is a complete query used as a value. Wrapped in brackets, it runs first and hands its answer to the outer query. An uncorrelated one runs once. A correlated one references the outer row, so it runs again for every row — convenient to write, easy to make expensive.

Real-World Example

A wind farm operator asks which turbines underperformed. "Below average" is not a number you can type: it depends on what the other turbines did this month. So you compute the farm's average first, then compare each machine against it.

The Code

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