Subqueries
SQL: lesson 6 of 10
A query inside a query, answered before the outer one runs.
Lesson 6 of 10 · 5 min
Subqueries
Step 1 of 10
averagenot known yet
Which turbines underperformed? “Below average” is not a number you can type.
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)]Your turn
Put the steps in the right order.
- Compare each turbine's output against that one number
- Run the bracketed query and get a single value back
- Keep only the rows where the comparison held
- Start reading rows from the turbine table
Mini quiz
1 / 3