Subqueries & CTEs

Real questions rarely fit into one flat SELECT. You want the employees who earn more than the company average, the customers who have never placed an order, or every person under a given manager in an org chart. Each of these needs an answer to one question before you can ask the next. Subqueries and CTEs let you put that first question inside the second, so the database answers both in a single statement.

This chapter builds the toolkit step by step. You start with scalar subqueries that return a single value, then move to IN and EXISTS for membership checks, and on to correlated subqueries that re-run for every outer row. After that come derived tables in FROM, readable WITH blocks (CTEs), and recursive CTEs that walk hierarchies of any depth. Along the way you will learn to spot the traps that catch most students, such as the NOT IN and NULL surprise.

By the end you will be able to break a messy question into named, testable steps, pick the right form of subquery for the job, and decide when a plain JOIN is the clearer or faster choice. You will also be able to read someone else's nested query and debug it by running its inner part on its own.

Sometimes one question cannot be answered with a single pass over the data. You want the employees in the Sales department, but all you know is the department's name, while the employees table only stores a department id. A subquery solves this: it is a complete SELECT placed inside another statement and wrapped in parentheses. The inner query produces a result, and the outer query uses that result as if you had typed it in yourself.

Every example in these notes uses the same two tables. departments holds id and name. employees holds id, name, dept_id, salary and manager_id, where manager_id points back at another row in the same table.

The classic first subquery looks up a department id by name and hands it to the outer WHERE. The runnable example below builds both tables in an in-memory SQLite database and runs exactly that query. The same db connection is reused in later examples.

import sqlite3

db = sqlite3.connect(':memory:')
db.executescript('''
CREATE TABLE departments (id INTEGER, name TEXT);
INSERT INTO departments VALUES (1,'Sales'),(2,'Engineering'),(3,'HR');
CREATE TABLE employees (id INTEGER, name TEXT, dept_id INTEGER, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1,'Asha',2,9000,NULL),(2,'Ben',1,5000,4),(3,'Chen',1,6000,4),
  (4,'Dara',1,8000,1),(5,'Eli',2,7000,1),(6,'Fay',3,4500,1);
''')

sql = '''SELECT name FROM employees
WHERE dept_id = (SELECT id FROM departments WHERE name = 'Sales')
ORDER BY id'''
for (name,) in db.execute(sql):
    print(name)

Employees whose department is named Sales

The inner SELECT id FROM departments WHERE name = 'Sales' returns the single value 1. The outer query then behaves as though it said WHERE dept_id = 1. If the department were renamed or re-numbered, the query would still work because it never hard-codes the id.

A subquery is not limited to the WHERE clause. It can appear in the SELECT list, in FROM, in WHERE and in HAVING. The important thing to remember is that each position expects a particular shape of result. Put the wrong shape in a position and the database reports an error, or worse, quietly uses only part of what you gave it.

Those expectations come from the three possible result shapes. A scalar is one row with one column, a single value. A single column is any number of rows with one column, which works like a list. A full table has several rows and several columns. The example below runs one query of each shape so you can see what Python receives.

print(db.execute('SELECT MAX(salary) FROM employees').fetchall())
print(db.execute('''SELECT id FROM departments WHERE name IN ('Sales', 'HR') ORDER BY id''').fetchall())
print(db.execute('SELECT name, salary FROM employees WHERE dept_id = 1 ORDER BY id').fetchall())

Scalar, list and table results from the same data

There is also a question of order. When the inner query is uncorrelated, meaning it does not refer to the outer query's columns, it can be evaluated first, on its own, and the outer query then uses its result. You can read it inside-out: work out what the parentheses produce, then read the outer statement with that answer filled in. The database optimizer may rearrange the real work for speed, but the answer must match this reading.

Subqueries are not only for reading data. INSERT, UPDATE and DELETE can all use them to decide which rows to touch or which values to write. The usual pattern is a WHERE ... IN (SELECT ...) that picks the target rows from information stored in another table. The example below keeps a small old table listing ids that should be removed, then deletes the matching employees in one statement.

db.execute('CREATE TABLE old (id INTEGER)')
db.execute('INSERT INTO old VALUES (5), (6)')
cur = db.execute('DELETE FROM employees WHERE id IN (SELECT id FROM old)')
print(cur.rowcount)
print(db.execute('SELECT name FROM employees ORDER BY id').fetchall())

DELETE driven by a subquery

Finally, SQL lets you nest subqueries inside subqueries as deep as you like, but your readers cannot follow that far. One level is easy to read, two levels are manageable, and past two you are making someone count parentheses. The query below has three levels: it finds the employees in departments that contain someone earning above the Sales average. It works, but you have to read it from the middle outward.

SELECT name FROM employees
WHERE dept_id IN (
  SELECT dept_id FROM employees
  WHERE salary > (
    SELECT AVG(salary) FROM employees
    WHERE dept_id = (SELECT id FROM departments WHERE name = 'Sales')
  )
);

Four SELECTs deep: too many to read at a glance

A scalar subquery is a query in parentheses that returns exactly one row and one column. Because the result is a single value, you can put it anywhere the SQL language accepts a single value: in a WHERE comparison, in the SELECT list, in HAVING, or even inside an arithmetic expression.

The classic use is comparing each row against a figure that has to be calculated first. You cannot write WHERE salary > AVG(salary) because aggregates are not allowed in WHERE. The subquery computes the average, and the outer query treats the answer as an ordinary number.

The examples in this section use a tiny employees table with five people. Their salaries add up to 350000, so the average is 70000. This code uses Python's built-in sqlite3 module, and later examples reuse the db connection and the show helper.

import sqlite3

db = sqlite3.connect(':memory:')
db.execute('CREATE TABLE employees (id INTEGER, name TEXT, dept TEXT, salary INTEGER)')
db.executemany('INSERT INTO employees VALUES (?, ?, ?, ?)', [
    (1, 'Asha', 'Eng', 90000),
    (2, 'Ben', 'Eng', 70000),
    (3, 'Chen', 'Ops', 50000),
    (4, 'Dina', 'Ops', 60000),
    (5, 'Eli', 'Sales', 80000),
])

def show(sql):
    for row in db.execute(sql):
        print(row)

print('above average:')
show('SELECT name, salary FROM employees '
     'WHERE salary > (SELECT AVG(salary) FROM employees) ORDER BY name')

print('difference from average:')
show('SELECT name, salary - (SELECT AVG(salary) FROM employees) AS diff '
     'FROM employees ORDER BY id')

The same scalar subquery used in WHERE and in the SELECT list

The first query is the filter form: WHERE salary > (SELECT AVG(salary) FROM employees) keeps only the above-average earners. Ben earns exactly 70000, which is not greater than the average, so he is left out. The second query is the SELECT-list form: the subquery produces the same 70000 for every row, and each person's salary has it subtracted, giving a diff column that is positive above the average and negative below it.

The promise of 'exactly one row' is easy to break. A subquery that finds nothing and a subquery that finds several both behave badly, but in different ways.

With zero rows, SQL does not complain. The empty result is silently turned into NULL, and any comparison with NULL (salary > NULL) is neither true nor false but unknown, so no outer row passes. Below, nobody is named Zed, so the subquery yields NULL and the filter returns an empty list instead of an error.

print(db.execute(
    "SELECT (SELECT salary FROM employees WHERE name = 'Zed')").fetchall())

print(db.execute(
    "SELECT name FROM employees "
    "WHERE salary > (SELECT salary FROM employees WHERE name = 'Zed')").fetchall())

A missing row turns into NULL, which matches nothing

The opposite problem is too many rows. Here the inner query returns the salary of every Ops employee, which is two values, and a comparison operator cannot compare one number against two. PostgreSQL, MySQL, SQL Server and Oracle all reject this at run time.

SELECT name FROM employees
WHERE salary > (SELECT salary FROM employees WHERE dept = 'Ops');
-- ERROR: more than one row returned by a subquery used as an expression
-- (MySQL: Subquery returns more than 1 row)

Two Ops rows where one value was expected

The fix is to make the inner query produce one row on purpose. You can collapse the rows with an aggregate such as MAX or MIN, or you can sort and keep only the top row with ORDER BY ... LIMIT 1. Both versions below ask for everyone who earns more than the best-paid Ops employee, and both return the same people.

print('MAX:  ', db.execute(
    "SELECT name FROM employees WHERE salary > "
    "(SELECT MAX(salary) FROM employees WHERE dept = 'Ops') ORDER BY name").fetchall())

print('LIMIT:', db.execute(
    "SELECT name FROM employees WHERE salary > "
    "(SELECT salary FROM employees WHERE dept = 'Ops' "
    "ORDER BY salary DESC LIMIT 1) ORDER BY name").fetchall())

Two ways to force exactly one row

Prefer the aggregate when you want a maximum or minimum, since it states the intent directly. Use ORDER BY ... LIMIT 1 when you need a value from the 'top' row that is not itself an aggregate. Remember that LIMIT is not available in every database; SQL Server uses TOP 1 and Oracle uses FETCH FIRST 1 ROW ONLY.

A scalar subquery that does not refer to the outer query is called uncorrelated. It does not depend on which outer row is being looked at, so its answer cannot change from row to row. The database therefore evaluates it once and reuses the value for every outer row. In the earlier example, the average of 70000 is calculated one time, not five times.

This is why such a subquery is cheap even on a large table. A correlated subquery, which refers to a column of the outer row, is different: it can give a new answer per row, and it is covered in its own section later.

The comparison operators =, >, <, >=, <= and <> all expect a single value on each side, so they only accept a scalar subquery. When the inner query legitimately returns a list, switch to an operator designed for lists.

Here IN handles a multi-row subquery without any error. The inner query finds the departments that have someone earning over 85000, which is only Eng, and the outer query returns everyone in those departments.

print(db.execute(
    "SELECT name FROM employees WHERE dept IN "
    "(SELECT dept FROM employees WHERE salary > 85000) ORDER BY name").fetchall())

IN accepts a subquery that returns several rows

ANY and ALL put a comparison operator in front of a multi-row subquery. The two queries below do the same job as the MAX fix earlier, and they work in PostgreSQL and MySQL but not in SQLite. > ALL means 'greater than every value', so it behaves like > MAX(...). > ANY means 'greater than at least one value', so it behaves like > MIN(...).

-- earns more than every Ops employee
SELECT name FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE dept = 'Ops');

-- earns more than at least one Ops employee
SELECT name FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE dept = 'Ops');

Not supported by SQLite

A subquery in the WHERE clause can produce a whole list of values for the outer query to check against. The operator that does the checking is IN. x IN (subquery) is true when x equals at least one value in the single-column result of the inner query. You do not have to know the list in advance, because the database builds it each time the query runs.

The examples in this section use two small tables. departments has id, name and location, and employees has id, name, dept_id and salary. The employee Imran has no department, so his dept_id is NULL. The department Legal has no employees. Both facts matter later.

Suppose you want every employee who works in a department located in Pune. The inner query finds the Pune department ids. The outer query keeps the employees whose dept_id is on that list. The block below builds the tables in an in-memory SQLite database and runs the query.

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE departments (id INTEGER, name TEXT, location TEXT);
INSERT INTO departments VALUES
  (1,'Engineering','Pune'),(2,'Sales','Mumbai'),(3,'Support','Pune'),
  (4,'HR','Delhi'),(5,'Legal','Chennai');
CREATE TABLE employees (id INTEGER, name TEXT, dept_id INTEGER, salary INTEGER);
INSERT INTO employees VALUES
  (1,'Asha',1,90000),(2,'Ravi',1,80000),(3,'Meena',2,60000),
  (4,'Karan',3,50000),(5,'Divya',4,55000),(6,'Imran',NULL,70000);
""")

def run(sql):
    return [r[0] for r in db.execute(sql)]

print(run("SELECT name FROM employees WHERE dept_id IN "
          "(SELECT id FROM departments WHERE location = 'Pune') ORDER BY id"))

Employees whose department is in Pune

IN compares one value with a list of values, so the subquery has to return exactly one column. If it returns two, the database refuses to run the statement and raises an error. The only exception is row-value syntax, where you compare a pair on the left with a pair on the right. Both columns are then checked together, as in (a, b) IN (SELECT x, y FROM t).

try:
    db.execute("SELECT name FROM employees WHERE dept_id IN "
               "(SELECT id, name FROM departments)")
except sqlite3.OperationalError as e:
    print("error:", e)

Two columns in the inner query

The inner list is only used to answer a yes-or-no question about each outer row, so repeated values change nothing. A join works differently. It produces one result row for every matching pair. Take the question "which departments have at least one employee?" Engineering has two employees, so IN lists it once and a join lists it twice.

print("IN  :", run("SELECT name FROM departments WHERE id IN "
                   "(SELECT dept_id FROM employees) ORDER BY id"))
print("JOIN:", run("SELECT d.name FROM departments d "
                   "JOIN employees e ON e.dept_id = d.id ORDER BY d.id, e.id"))

The inner list is 1, 1, 2, 3, 4 and NULL, yet each department appears once with IN

The inner list above contained a NULL, and IN handled it without trouble. A NULL in the list can never make a match, but it does not spoil the other values. The negated form, covered next, does not behave this way.

NOT IN keeps the rows whose value appears nowhere in the list. It looks like a simple mirror of IN, but it is the source of one of the most common SQL bugs. Say you want the departments that have no employees. Run the natural query and you get nothing at all, even though Legal has no staff.

print("NOT IN:", run("SELECT name FROM departments WHERE id NOT IN "
                    "(SELECT dept_id FROM employees)"))

Legal should appear here

The cause is Imran, whose dept_id is NULL. If the inner list contains even a single NULL, NOT IN returns no rows at all. The query does not fail and gives no warning. It just returns an empty result, which is easy to mistake for "every department has employees".

SQL expands x NOT IN (1, NULL) into x <> 1 AND x <> NULL. A comparison with NULL is never true or false. It is UNKNOWN, because the database cannot say whether an unknown value differs from x. WHERE only keeps rows where the whole condition is TRUE. When the second part is UNKNOWN, the AND can be FALSE or UNKNOWN but never TRUE, so no row survives.

The table shows that the middle row is the one that hurts. The value 3 is clearly not in the list (1, NULL) as far as a person can tell. Yet the answer comes out UNKNOWN rather than TRUE, so the row is dropped.

The first fix is to remove the NULLs from the inner list with WHERE col IS NOT NULL. The second is to switch to NOT EXISTS, which asks whether a matching row exists and never compares a value to NULL. Both give the right answer here.

print("IS NOT NULL:", run("SELECT name FROM departments WHERE id NOT IN "
                        "(SELECT dept_id FROM employees WHERE dept_id IS NOT NULL)"))
print("NOT EXISTS :", run("SELECT d.name FROM departments d WHERE NOT EXISTS "
                        "(SELECT 1 FROM employees e WHERE e.dept_id = d.id)"))

Both fixes find Legal

The next section covers EXISTS and NOT EXISTS in full. For now, treat NOT EXISTS as the default choice when the inner column might hold NULLs.

IN only tests equality. Sometimes you want to compare with > or < against a whole list. ANY and ALL do this. x > ANY (subquery) is true when x is greater than at least one value in the list. x > ALL (subquery) is true only when x is greater than every value in the list.

Here is a query for employees who earn more than everyone in Sales. The Sales department has only Meena, who earns 60000, so the list is a single value. The result is everyone paid above her.

SELECT name, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 2)
ORDER BY salary DESC;

PostgreSQL and MySQL syntax. SQLite does not support ANY or ALL.

ANY and ALL tie back to what you have already seen. = ANY means "equal to at least one value", which is exactly what IN means. <> ALL means "different from every value", which is what NOT IN means. That is why <> ALL has the same NULL trap: one NULL in the list makes the result UNKNOWN for every row.

Sometimes you do not care what the matching rows contain. You only want to know whether any exist. EXISTS is built for that question. You put a subquery inside it, and it returns TRUE if the subquery produces at least one row, or FALSE if it produces none. It never hands back the rows themselves, only that yes-or-no answer, so it sits in a WHERE clause as a filter.

Here is the classic case: list the departments that have at least one employee. The inner query looks for an employee whose dept_id matches the department being checked. If it finds one, the department stays in the result.

SELECT d.name
FROM departments d
WHERE EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.dept_id = d.id
);

Departments that have at least one employee

Notice that the inner query mentions d.id from the outer query. That makes it a correlated subquery: it is checked once per outer row, using that row's values. The next section covers correlation in depth, but EXISTS is the place most people meet it first.

Because EXISTS only asks whether a row came back, the columns in the inner SELECT list are ignored. SELECT 1, SELECT * and SELECT e.name all give the same answer. Writing SELECT 1 is a convention that tells the reader you are only testing for presence.

There is also a performance side to this. EXISTS only needs one match, so the database can stop at the first matching row (it short-circuits) instead of reading every match. On a large inner table, where a department may have thousands of employees, that early exit can save a lot of work.

Put NOT in front and you flip the question: keep the outer row only when the inner query finds nothing. This is the standard way to find rows with no match, such as departments with zero employees.

SELECT d.name
FROM departments d
WHERE NOT EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.dept_id = d.id
);

Departments with zero employees

You can also write this with NOT IN, and for clean data the two return the same rows. The difference shows up the moment the inner list contains a NULL. x NOT IN (1, 2, NULL) can never be TRUE: for any x that is not 1 or 2, the comparison with NULL is unknown, and unknown rows are filtered out. One NULL in the inner list makes NOT IN return nothing at all. EXISTS compares with = inside the subquery, so a NULL row simply fails to match and is ignored.

The example below builds three departments and four employees. One employee, Dina, has no department, so her dept_id is NULL. Watch what each query returns.

import sqlite3

con = sqlite3.connect(':memory:')
con.executescript('''
CREATE TABLE departments (id INTEGER, name TEXT);
CREATE TABLE employees (id INTEGER, name TEXT, dept_id INTEGER);
INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'Legal');
INSERT INTO employees VALUES (1, 'Asha', 1), (2, 'Ben', 1), (3, 'Chen', 2), (4, 'Dina', NULL);
''')

def names(sql):
    return [r[0] for r in con.execute(sql + ' ORDER BY d.id')]

a = names('SELECT d.name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id)')
b = names('SELECT d.name FROM departments d WHERE EXISTS (SELECT * FROM employees e WHERE e.dept_id = d.id)')
print('EXISTS:', a)
print('Same with SELECT *:', a == b)
print('NOT EXISTS:', names('SELECT d.name FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id)'))
print('IN:', names('SELECT d.name FROM departments d WHERE d.id IN (SELECT dept_id FROM employees)'))
print('NOT IN:', names('SELECT d.name FROM departments d WHERE d.id NOT IN (SELECT dept_id FROM employees)'))

Dina's NULL dept_id breaks NOT IN but not NOT EXISTS

NOT EXISTS correctly reports Legal, but NOT IN returns an empty list because of the single NULL. The same data, the same intent, and one version silently returns the wrong answer.

For non-NULL data, IN and EXISTS give identical results, so the choice is mostly about which one reads naturally and which one is safe. Use the table as a rule of thumb.

It is tempting to say EXISTS is always faster because it stops early. Modern optimizers are smarter than that: they often rewrite both forms into the same semi-join plan, so the two queries run identically. Whether the rewrite happens depends on your database and version. The honest way to know is to look at the plan.

EXPLAIN
SELECT d.name FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

EXPLAIN
SELECT d.name FROM departments d
WHERE d.id IN (SELECT dept_id FROM employees);

Compare the two plans before choosing on speed (some databases use EXPLAIN QUERY PLAN or EXPLAIN ANALYZE)

Every subquery so far could be pasted into a console and run by itself. A correlated subquery cannot. It mentions a column that belongs to the outer query, so on its own the database would not know what that column means. That outer reference is the whole idea: the inner query asks a question about *the row currently being looked at*.

The way to picture it is a loop. For each row the outer query reads, the database takes that row's value, plugs it into the inner query, runs the inner query, and uses the answer to decide whether to keep the row. Conceptually the inner query re-runs once per outer row. Real engines are free to be smarter, but this is the model that predicts the result.

Here is the classic case: find employees who earn more than the average of their own department. A single global average would not do, because each department needs its own. The setup below builds a small employees table and a sales table that the later examples reuse, and a tiny show helper that prints rows.

import sqlite3

db = sqlite3.connect(':memory:')
db.executescript('''
CREATE TABLE employees (id INTEGER, name TEXT, dept_id INTEGER,
                        salary INTEGER, hire_date TEXT, bonus INTEGER);
INSERT INTO employees (id, name, dept_id, salary, hire_date) VALUES
  (1,'Asha',10,50000,'2020-03-01'),(2,'Ben',10,70000,'2019-06-15'),
  (3,'Chen',10,90000,'2022-01-10'),(4,'Dia',20,40000,'2021-05-20'),
  (5,'Eli',20,60000,'2018-11-30'),(6,'Fay',30,55000,'2023-02-01');
CREATE TABLE sales (emp_id INTEGER, amount INTEGER);
INSERT INTO sales VALUES (1,100),(1,250),(2,300),(4,80),(4,120);
''')

def show(sql):
    for row in db.execute(sql):
        print(row)

show('''SELECT name, salary FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees
                WHERE dept_id = e.dept_id)
ORDER BY id''')

Above-average earners per department

Department 10 averages 70000, so only Chen beats it. Department 20 averages 50000, so only Eli does. Fay is alone in department 30, so she equals her own average and is not strictly above it.

In the query above, dept_id appears twice inside the subquery's neighbourhood: once as the inner table's own column and once as e.dept_id from the outer row. The alias e is the only thing that tells them apart. When a bare column name could belong to either query, SQL resolves it to the innermost table that has such a column. So if you drop the e., the inner dept_id binds to the inner table, and the condition compares a column with itself.

show('''SELECT name, salary FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees
                WHERE dept_id = dept_id)
ORDER BY id''')

Same query with the alias forgotten

The query still runs and still returns rows, which is what makes this mistake dangerous. dept_id = dept_id is true for every row, so the inner query averages the whole company (about 60833) instead of one department. Ben now sneaks in because 70000 beats the company average, even though he sits exactly on his department's average.

Correlated subqueries are the natural tool for latest row per group. For each employee, look up the greatest hire_date inside their own department and keep the row only if it matches. The question never needs a GROUP BY in the outer query, so you still get full rows back, not just the grouped columns.

show('''SELECT name, dept_id, hire_date FROM employees e
WHERE hire_date = (SELECT MAX(hire_date) FROM employees
                   WHERE dept_id = e.dept_id)
ORDER BY id''')

Most recently hired person in each department

The same pattern works for changing data. In a correlated UPDATE, the subquery runs for each row being updated and supplies that row's new value. Here every employee gets a bonus equal to the largest single sale they made, matched through s.emp_id = e.id.

db.execute('''UPDATE employees AS e
SET bonus = (SELECT MAX(amount) FROM sales s
             WHERE s.emp_id = e.id)''')
show('SELECT name, bonus FROM employees ORDER BY id')

Correlated UPDATE using the outer row's id

Notice the None values. Chen, Eli and Fay made no sales, so MAX over zero rows is NULL, and the UPDATE writes that NULL into their bonus. If you would rather have 0, wrap the subquery in COALESCE(..., 0). Some databases write the alias as UPDATE employees e; the idea is identical.

Because of the once-per-outer-row model, the naive cost is roughly O(n × m): n outer rows, each scanning up to m inner rows. With 1,000 employees and 1,000 candidates in the inner scan, that is about a million comparisons for a modest table. The expensive part is the inner lookup by the correlated column, so that is where an index pays off.

CREATE INDEX idx_employees_dept ON employees (dept_id);

Lets each inner lookup jump straight to one department

With an index on dept_id, each inner run finds its department's rows directly instead of scanning the whole table, so the work drops from n × m toward n × (rows in one department). It is the single cheapest fix and costs one line, though the exact plan always depends on your database's optimizer.

Often the better move is to avoid re-running anything. Compute each department's average once in a pre-aggregated derived table, then join the employees to it. The data is scanned about once for the aggregate and once for the join.

show('''SELECT e.name, e.salary FROM employees e
JOIN (SELECT dept_id, AVG(salary) AS avg_sal
      FROM employees GROUP BY dept_id) d
  ON d.dept_id = e.dept_id
WHERE e.salary > d.avg_sal
ORDER BY e.id''')

Same answer as the correlated version, aggregated once

A window function reaches the same result without a join. AVG(salary) OVER (PARTITION BY dept_id) attaches each department's average to every row in a single pass, and an outer query filters on it.

SELECT name, salary FROM (
  SELECT name, salary,
         AVG(salary) OVER (PARTITION BY dept_id) AS avg_sal
  FROM employees
) t
WHERE salary > avg_sal;

Window function version, one scan

So far every subquery sat in WHERE or SELECT and handed back a value or a list. A subquery can also sit in the FROM clause. There it is called a derived table: the database runs the inner query, treats its result as a temporary table, and lets the outer query select from it, join it and filter it. The table exists only while that one statement runs.

Most databases, including MySQL and PostgreSQL, insist that you give the derived table an alias, written FROM (SELECT ...) AS t. Without it you get a syntax error such as Every derived table must have its own alias. PostgreSQL 16 relaxed this rule, but writing the alias is still the habit to keep, because you need it to refer to the columns anyway.

The classic use is joining a table to an aggregate of another table. Say you want each department's name next to its average salary. The names live in departments and the averages must be computed from employees, so you compute the averages in a derived table and join it. The examples in this section run against a tiny SQLite database with three departments and six employees.

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE departments (id INTEGER, name TEXT)")
db.execute("CREATE TABLE employees (id INTEGER, name TEXT, dept_id INTEGER, salary INTEGER)")
db.executemany("INSERT INTO departments VALUES (?, ?)", [(1, 'Sales'), (2, 'Eng'), (3, 'HR')])
db.executemany("INSERT INTO employees VALUES (?, ?, ?, ?)", [(1, 'Ana', 2, 90), (2, 'Ben', 2, 110), (3, 'Cy', 2, 100), (4, 'Dee', 1, 60), (5, 'Eli', 1, 80), (6, 'Fay', 3, 50)])

def show(sql):
    for row in db.execute(sql):
        print(row)

show("SELECT d.name, t.avg_sal FROM departments d JOIN (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) t ON t.dept_id = d.id ORDER BY d.id")

The inner SELECT collapses six employees into one row per department; the join then attaches the department name.

WHERE is evaluated before grouping and before window functions run, so it cannot see AVG(salary) or ROW_NUMBER() directly. Writing WHERE salary > AVG(salary) is an error. A derived table is the way around this: once the aggregate or window value has been computed inside the subquery, it is just an ordinary column of the derived table, and the outer WHERE can filter on it freely.

Here we keep only the employees who earn more than their own department's average. The derived table supplies the average, the join matches it to each employee, and the outer WHERE compares the two. Cy earns exactly the Eng average of 100, so the strict > leaves Cy out.

show("SELECT e.name, e.salary, t.avg_sal FROM employees e JOIN (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) t ON t.dept_id = e.dept_id WHERE e.salary > t.avg_sal ORDER BY e.id")

Reuses the db and show helper from the first example.

The same trick works for window functions. To get the best-paid employee in each department, number the rows inside the subquery with ROW_NUMBER(), then keep rn = 1 outside. You cannot put WHERE rn = 1 in the same query that defines rn, because the window function has not run yet when WHERE is evaluated.

Every column that a derived table exposes needs a unique name, because the outer query refers to the columns by name. Plain columns already have one, but an expression such as AVG(salary) has no usable name, so give it an alias like AS avg_sal. Two columns with the same name, for example e.id and d.id both selected from a join inside the subquery, make the derived table ambiguous and most databases reject it.

A plain derived table is built independently. Inside its parentheses it cannot mention columns from other tables listed in the same FROM, so FROM customers c, (SELECT ... WHERE o.customer_id = c.id) t is an error: c is not visible there. This is the difference from a correlated subquery in WHERE, which can see the outer row.

The keyword LATERAL lifts that restriction. A lateral derived table is evaluated once for each row of the tables to its left, and the inner query may use that row's columns. It behaves like a correlated subquery that is allowed to return many rows and many columns. SQL Server has no LATERAL keyword but offers CROSS APPLY (drops left rows with no match, like an inner join) and OUTER APPLY (keeps them, like a left join).

The textbook case is the top 3 orders for every customer. A window function can do it, but LATERAL reads very naturally: for each customer, run a small query that sorts that customer's orders and keeps three. LEFT JOIN LATERAL ... ON TRUE keeps customers who have no orders at all, with NULL in the order columns.

-- PostgreSQL / MySQL 8.0.14+
SELECT c.name, o.id, o.total
FROM customers c
LEFT JOIN LATERAL (
  SELECT id, total
  FROM orders
  WHERE orders.customer_id = c.id   -- c comes from the left
  ORDER BY total DESC
  LIMIT 3
) AS o ON TRUE;

-- SQL Server
SELECT c.name, o.id, o.total
FROM customers c
OUTER APPLY (
  SELECT TOP 3 id, total
  FROM orders
  WHERE orders.customer_id = c.id
  ORDER BY total DESC
) AS o;

SQLite has no LATERAL, so this one is shown rather than run.

A derived table lives and dies inside the statement that contains it. You cannot reference it from the next query, and within the same statement you cannot name it twice either. If you need the same intermediate result in two places, for instance once to join and once to compare against a total, you have to paste the whole subquery a second time, each copy with its own alias.

SELECT a.dept_id, a.avg_sal, a.avg_sal - b.company_avg AS diff
FROM (SELECT dept_id, AVG(salary) AS avg_sal
      FROM employees GROUP BY dept_id) AS a
CROSS JOIN (SELECT AVG(avg_sal) AS company_avg
      FROM (SELECT dept_id, AVG(salary) AS avg_sal
            FROM employees GROUP BY dept_id) AS c) AS b;

The department-average query is written twice. Change one copy and the two silently disagree.

Duplicated text is hard to read and easy to get out of sync, and the database may compute it twice. Deep nesting also forces you to read the query from the inside out. Both problems are exactly what the next section's CTEs (WITH name AS (...)) solve: they give the subquery a name up front, let you use it as many times as you like in the statement, and keep the main query reading top to bottom.

A CTE (common table expression) is a named, temporary result that you define at the top of a statement and then use like a table. You write WITH name AS (SELECT ...) first, and the main query that follows can read from name as if it were a real table. Nothing is saved anywhere; the name is just a label for a query you want to reuse or keep tidy.

The classic use is comparing each row with a group figure. Say you want every employee who earns more than the average of their own department. First you compute one average per department, then you compare each person against it. A CTE lets you write those two steps in that order.

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) a
  FROM employees
  GROUP BY dept_id
)
SELECT e.name
FROM employees e
JOIN dept_avg d ON d.dept_id = e.dept_id
WHERE e.salary > d.a;

dept_avg is defined first, then joined like any table

Here is the same query running against a small table. Department 10 averages 80000, department 20 averages 55000 and department 30 has only Fay, who equals her own average, so she is not above it. Only Asha and Eli beat their department.

Notice the shape of the statement: the helper query sits at the top with a name, and the main query reads almost like plain English. That is the whole idea of a CTE, and the rest of this section builds on it.

One CTE is useful, but the feature really pays off when a problem has several steps. You list more CTEs after the first one, separated by commas, and you write the word WITH only once. The pattern is WITH a AS (...), b AS (SELECT ... FROM a) SELECT ... FROM b.

Two rules make chains work. A later CTE can read an earlier one, but not the other way round, so you order them as a pipeline. And the main query can reference the same CTE several times, for example in a join with itself or alongside another CTE, without you repeating its definition.

The example below finds each department's share of the total payroll. It also shows the column list form: writing dept_totals(dept, total) gives the output columns new names, so the query inside does not need aliases. The first CTE is used twice, once by grand and once by the main query.

query = '''
WITH dept_totals(dept, total) AS (
  SELECT dept_id, SUM(salary) FROM employees GROUP BY dept_id
),
grand(total) AS (
  SELECT SUM(total) FROM dept_totals
)
SELECT t.dept, t.total, ROUND(100.0 * t.total / g.total, 1)
FROM dept_totals t, grand g
ORDER BY t.dept
'''
for dept, total, pct in con.execute(query):
    print(dept, total, pct)

reuses the connection from the first example

The main benefit of a CTE is readability. A query with nested subqueries has to be read inside-out: you dive into the deepest parentheses first and climb back out. With CTEs the same logic reads top to bottom, one named step after another, and each name tells the reader what that step produces.

A CTE also has a fixed scope. It exists only for the single statement that follows it. Once that statement finishes, the name is gone, and a separate query cannot use it. If you need the same helper in the next statement, you must write the WITH again.

try:
    con.execute('SELECT * FROM dept_avg')
except sqlite3.OperationalError as e:
    print('error:', e)

dept_avg was only visible inside the earlier statement

CTEs and derived tables (a subquery in the FROM clause) can express the same queries, so the choice is mostly about how pleasant the query is to work with.

Writing a CTE says what you want, not how the engine must compute it. Engines differ in whether they calculate the CTE once and keep the rows (materialize it) or paste its query into the place where it is used (inline it). This changes speed, not the answer, in ordinary read-only queries.

In PostgreSQL you can override the choice. MATERIALIZED forces the CTE to be computed once and stored for the rest of the statement, which helps when the step is expensive and used many times. NOT MATERIALIZED lets the planner merge it into the outer query, which helps when the outer WHERE can filter early.

WITH big AS MATERIALIZED (
  SELECT dept_id, SUM(salary) total FROM employees GROUP BY dept_id
)
SELECT * FROM big WHERE total > 100000;

WITH recent AS NOT MATERIALIZED (
  SELECT * FROM employees
)
SELECT * FROM recent WHERE id = 5;

PostgreSQL 12+ syntax; other engines do not accept these hints

Some data is shaped like a tree: an org chart where each person points to a manager, a category tree where each category points to a parent, or a bill of materials where a product points to the parts it is built from. You rarely know how deep such a tree goes. A plain join can climb exactly one level per join, so it cannot say "keep going until you reach the top". A recursive CTE solves this by referencing its own name inside its definition, so it can walk a tree or a graph of any depth with one query.

The examples below use a small employees table. The CEO has no manager, so manager_id is NULL. Everyone else points to the id of their manager.

Every recursive CTE has the same three parts. It starts with WITH RECURSIVE and a name. Inside the parentheses sits an anchor member, then the two members are joined by UNION ALL (or UNION where the database allows it, to drop duplicate rows), then the recursive member, which selects from the CTE's own name. After the closing parenthesis, an ordinary outer SELECT reads the finished result.

WITH RECURSIVE t AS (
  SELECT ...            -- anchor member: runs once
  UNION ALL
  SELECT ...            -- recursive member
  FROM t JOIN ...       -- refers to t itself
  WHERE ...
  )
SELECT ... FROM t;

The skeleton. SQL Server and Oracle leave out the RECURSIVE keyword and write plain WITH.

The anchor member is an ordinary query that runs exactly once. Its rows seed the result, and they are the starting point of the walk. For the org chart that is the CEO, the only row with no manager: WHERE manager_id IS NULL.

The recursive member takes the rows produced by the previous round and joins them to the base table to find the next layer. Here that means finding everyone whose manager is one of the people we just found: JOIN employees e ON e.manager_id = t.id. On its first run, t holds only the anchor rows. On the second run, t holds only the rows the first run produced, not the whole result so far.

WITH RECURSIVE t AS (
  SELECT id, name
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name
  FROM employees e
  JOIN t ON e.manager_id = t.id
)
SELECT name FROM t;

Everyone under the CEO, however deep.

The database repeats the recursive step again and again, each time feeding it only the rows from the round before. It stops when a round returns no new rows. Then all the rounds are unioned together into the final result. For our data, Asha has two reports, they have three between them, and Fay has no reports, so the fourth round comes back empty and the walk ends.

The plain result does not tell you who sits where. You can carry extra columns from one round to the next. A level column starts at 0 in the anchor and becomes t.level + 1 in the recursive member, so each round is one step deeper than the last.

The same trick builds a path string that displays the chain from the root down to each row. Start it with the root's name in the anchor, then in the recursive member glue the parent's path, a separator and the new name together: t.path || ' > ' || e.name. Each row ends up carrying its whole chain of managers as text, ready to print.

WITH RECURSIVE t AS (
  SELECT id, name, 0 AS level, name AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, t.level + 1, t.path || ' > ' || e.name
  FROM employees e
  JOIN t ON e.manager_id = t.id
)
SELECT name, level, path FROM t ORDER BY path;

Depth and the chain from the CEO, in one query.

Recursion is not only for hierarchies. With no base table, the anchor is just a constant and the recursive step adds one to it. The WHERE x < 5 is what ends the walk: when x reaches 5, the next round produces nothing.

WITH RECURSIVE n(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM n WHERE x < 5
)
SELECT x FROM n;

Generates the numbers 1 to 5. The column list n(x) names the CTE's column.

A tree ends because every walk eventually reaches a row with no children. Real data is not always a clean tree. If Asha's manager_id were set to 6 by mistake, then Asha manages Ben, who manages Dev, who manages Fay, who manages Asha, and the walk goes round forever. The same happens in a graph of flights or links where one route leads back to a place already visited. Such a query never returns on its own.

You have several ways to guard against a loop, and they can be combined.

WITH RECURSIVE t AS (
  SELECT id, name, 0 AS level
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, t.level + 1
  FROM employees e
  JOIN t ON e.manager_id = t.id
  WHERE t.level < 10
)
SELECT name, level FROM t;

The same org-chart walk with a depth cap: even a looping row stops after 10 levels.

Prefer UNION ALL. It simply appends each round's rows, which is the cheapest thing to do. Plain UNION removes duplicates against the rows already collected, which means extra comparison work on every round. It does have one side effect: if a round produces only rows that were already seen, nothing new is added and the walk ends, so UNION can stop some cycles. That is a nice safety net but not a replacement for a depth cap, because a path or level column makes each row look new again. Some databases, such as SQL Server and Oracle, do not allow UNION in the recursive part at all.

Many questions can be answered with either a JOIN or a subquery, so the choice comes down to what you need back. A JOIN stitches two tables side by side, so columns from both sides can appear in the output. A subquery is a helper: it filters the outer query or computes a value, and nothing from the inner table is visible to the outer SELECT.

The first example builds a tiny database that the rest of this section reuses: three departments (Legal has nobody) and five employees (Eli has no department, so dept_id is NULL). The JOIN lists each employee next to their department. The IN query can only tell us which departments have staff, it cannot show who they are.

import sqlite3
con = sqlite3.connect(':memory:')
con.execute('CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT)')
con.execute('CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept_id INTEGER, salary INTEGER)')
con.executemany('INSERT INTO departments VALUES (?, ?)', [(1, 'Sales'), (2, 'Eng'), (3, 'Legal')])
con.executemany('INSERT INTO employees VALUES (?, ?, ?, ?)', [(1, 'Asha', 1, 50), (2, 'Ben', 1, 60), (3, 'Cara', 2, 90), (4, 'Dev', 2, 70), (5, 'Eli', None, 40)])

def show(label, sql):
    print(label, con.execute(sql).fetchall())

show('join:', 'SELECT d.name, e.name FROM departments d JOIN employees e ON e.dept_id = d.id ORDER BY d.name, e.name')
show('in:', 'SELECT name FROM departments WHERE id IN (SELECT dept_id FROM employees) ORDER BY name')

Both queries start from departments, but only the JOIN can return employee names.

A JOIN returns one result row per matching pair. If a department has two employees, joining departments to employees returns that department twice. That is correct for a JOIN, but it surprises people who only wanted a list of departments that have staff. The usual patch is DISTINCT, which hides the repeats after the fact. EXISTS never creates the repeats, so it needs no patch.

There is also a tidy equivalence worth knowing. WHERE id IN (SELECT dept_id FROM employees) gives the same rows as an INNER JOIN, as long as the inner side is first de-duplicated. Without the DISTINCT in the derived table the join would repeat rows again. The NULL dept_id for Eli matches nothing in either form, so it quietly drops out.

show('join:', 'SELECT d.name FROM departments d JOIN employees e ON e.dept_id = d.id ORDER BY d.name')
show('distinct:', 'SELECT DISTINCT d.name FROM departments d JOIN employees e ON e.dept_id = d.id ORDER BY d.name')
show('exists:', 'SELECT d.name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id) ORDER BY d.name')
show('dedup join:', 'SELECT d.name FROM departments d JOIN (SELECT DISTINCT dept_id FROM employees) t ON t.dept_id = d.id ORDER BY d.name')

Four ways to ask which departments have staff. Only the plain join repeats.

Comparing each row with an aggregate, such as employees paid above the average, is where subqueries read most naturally. You state the comparison directly: salary greater than the average salary. A join can do it too, but you must build a one-row derived table for the average and join to it, which hides the intent. A CTE that names the average is just as clear when the value is reused.

show('subquery:', 'SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) ORDER BY name')
show('join:', 'SELECT e.name, e.salary FROM employees e JOIN (SELECT AVG(salary) AS a FROM employees) x ON e.salary > x.a ORDER BY e.name')

The average salary is 62, so only Cara and Dev qualify.

The reverse question is the anti-join: find rows with no match, such as departments without employees. Two forms are safe. NOT EXISTS asks whether a matching row is absent. LEFT JOIN ... WHERE right.id IS NULL keeps every left row and then keeps only those where the right side found nothing. Both treat NULLs correctly. NOT IN is the dangerous one: if the inner list contains even one NULL, the comparison becomes unknown for every row and the result is empty.

show('not exists:', 'SELECT d.name FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id)')
show('left join:', 'SELECT d.name FROM departments d LEFT JOIN employees e ON e.dept_id = d.id WHERE e.id IS NULL')
show('not in:', 'SELECT name FROM departments WHERE id NOT IN (SELECT dept_id FROM employees)')

Eli's NULL dept_id poisons the NOT IN version.

Beginners often assume a join is always faster than a subquery. Modern optimizers usually flatten IN and EXISTS subqueries into joins internally, so the two spellings often run the same plan. The exception is a correlated subquery with an aggregate inside, such as a MAX per department. Some engines keep it as a loop that reruns the inner query for each outer row, which gets slow on big tables.

Ranking and running totals are the classic cases where a correlated subquery is replaced by a window function. Instead of asking the table again for each row, the window function computes the answer in one pass over the data. Below, the correlated version finds each department's top earner, and the window version gets the same people with RANK. The running total would otherwise need a subquery that sums all earlier rows for every row.

show('correlated:', 'SELECT name, dept_id FROM employees e WHERE salary = (SELECT MAX(salary) FROM employees WHERE dept_id = e.dept_id) ORDER BY name')
show('window:', 'SELECT name, dept_id FROM (SELECT name, dept_id, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS r FROM employees WHERE dept_id IS NOT NULL) WHERE r = 1 ORDER BY name')
show('running:', 'SELECT name, SUM(salary) OVER (ORDER BY id) FROM employees ORDER BY id')

The window version scans employees once instead of once per row.

Since the spelling matters less than the plan, do not guess. Write the version that reads most clearly, look at what the database says it will do, and change the query only if the plan shows a real problem.

Most subquery bugs fall into two groups. Some make the database refuse the query with an error. Others are worse, because the query runs and quietly returns the wrong rows. This page covers the first kind and the nastiest silent one, which is NOT IN. Each mistake comes with the fix.

SQL uses three-valued logic: a comparison with NULL is neither true nor false but unknown. id NOT IN (1, 2, NULL) expands to id <> 1 AND id <> 2 AND id <> NULL. The last part is always unknown, so the whole condition can never be true and the query returns zero rows. One stray NULL in the inner list is enough.

In the example below, one order has no customer (customer_id is NULL). We look for customers who never ordered. Only Chen qualifies, yet the NOT IN version finds nobody.

A scalar subquery sits where a single value is expected, such as = (SELECT ...) or in the SELECT list. If the inner query produces two or more rows, the database cannot pick one and stops with an error. This usually means the inner WHERE is too loose or the join condition is missing a column.

-- Fails when a customer has more than one order
SELECT name,
       (SELECT total FROM orders o WHERE o.customer_id = c.id) AS order_total
FROM customers c;
-- PostgreSQL: more than one row returned by a subquery used as an expression
-- MySQL: Subquery returns more than 1 row

-- Fixed by saying which single value you want
SELECT name,
       (SELECT MAX(total) FROM orders o WHERE o.customer_id = c.id) AS biggest_order
FROM customers c;

Aggregate, LIMIT, or tighten the condition so only one row can match.

A subquery in FROM becomes a temporary table, and every table needs a name so the outer query can refer to its columns. MySQL and PostgreSQL both reject a derived table without an alias, as a syntax error.

IN compares one value (or one tuple of the same width) against the inner result. If the subquery returns several columns, the shapes do not match and the database reports a column count error. Name the single column you are matching on.

The next mistakes do not always produce an error. A correlated subquery can bind to the wrong table, a recursive query can run forever, and a CTE can be slower than the version you thought it improved.

When a name appears in a subquery, the database looks in the innermost tables first. If the inner table has a column with that name, it wins, even when you meant the outer row. Writing dept_id = dept_id inside a subquery compares the inner column with itself, which is true for every row, so the correlation disappears and no error appears. The query below should find employees paid above their own department's average.

The wrong version compares everyone against the company-wide average of 105, so Di (70, above the department average of 60) is missed. Giving the outer table an alias and writing e.dept_id makes the intent explicit.

A recursive CTE keeps running its recursive step on the rows it just produced, and stops only when that step returns nothing new. Two things stop it from ending: a recursive step with no WHERE limit on a graph that never runs out, and cyclic data, where A leads to B, B to C and C back to A. The usual defences are a depth column with a cap, or removing repeated rows.

UNION ALL keeps every row. UNION removes duplicates, which costs a comparison against everything produced so far. That has two effects. A repeated node is dropped, so a cycle stops by itself. But the same dedupe also collapses legitimate repeats, for example a node reachable by two different paths. The example walks a three-node cycle and then a diamond-shaped graph where node 4 can be reached through 2 or through 3.

A WITH clause is mainly about readability: it gives a name to a step so the query reads top to bottom. It does not promise a speed-up. Depending on the database and version, a CTE may be folded into the outer query like a view, computed once and stored, or recomputed every time it is referenced. Referencing the same expensive CTE three times can therefore do the work three times.

A nested query fails or returns the wrong rows somewhere in its layers. Instead of staring at the whole statement, take it apart and test each layer against real data. Work from the inside out so you always know which layer introduced the problem.

The same idea works for a chain of CTEs. Leave the WITH clause alone and change only the final SELECT so it reads from one step at a time. Look at each step in turn, with a LIMIT so a large table does not flood the screen.

A correlated subquery runs once for each outer row. With 10,000 customers and 1,000,000 orders, a lookup on orders.customer_id without an index scans the whole orders table 10,000 times, which is about ten billion row checks. With an index each lookup jumps straight to that customer's orders. Ask the database for its plan with EXPLAIN before guessing.

A subquery is a SELECT written inside another statement. The outer query uses whatever the inner one returns. The shape of that result decides where the subquery may sit: one value, one column of values, or just a yes/no.

The sample data used on every page of this sheet is two small tables. employees has id, name, dept_id, boss_id and salary, and Asha has no boss. departments has three rows, and Legal has no employees. The first program builds them and runs one query per form.

import sqlite3

db = sqlite3.connect(':memory:')
db.executescript('''
CREATE TABLE departments (id INTEGER, name TEXT);
INSERT INTO departments VALUES (1,'Sales'),(2,'Support'),(3,'Legal');
CREATE TABLE employees (id INTEGER, name TEXT, dept_id INTEGER, boss_id INTEGER, salary INTEGER);
INSERT INTO employees VALUES
  (1,'Asha',1,NULL,9000),(2,'Ben',1,1,6000),(3,'Cara',1,1,8000),
  (4,'Dev',2,1,5000),(5,'Esha',2,4,7000),(6,'Farid',2,4,4000);
''')

def run(label, sql):
    rows = [r[0] if len(r) == 1 else r for r in db.execute(sql)]
    print(label + ':', rows)

run('scalar in WHERE', 'SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) ORDER BY id')
run('scalar in SELECT', 'SELECT name, salary - (SELECT AVG(salary) FROM employees) FROM employees WHERE id <= 2 ORDER BY id')
run('IN', 'SELECT name FROM departments WHERE id IN (SELECT dept_id FROM employees WHERE salary > 7500)')
run('NOT IN with a NULL in the list', 'SELECT name FROM employees WHERE id NOT IN (SELECT boss_id FROM employees)')
run('NOT IN, NULLs removed', 'SELECT name FROM employees WHERE id NOT IN (SELECT boss_id FROM employees WHERE boss_id IS NOT NULL) ORDER BY id')
run('NOT EXISTS', 'SELECT d.name FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id)')

One query per form, all against the same two tables

The company average is 6500, so the scalar subquery behaves like a constant and three people beat it. In the SELECT list the same kind of subquery repeats its single value on every row. The fourth line is the trap: Asha's boss_id is NULL, so id NOT IN (..., NULL) can never be true and the query returns nothing. EXISTS only asks whether a matching row is there, so NULLs cannot spoil it.

A correlated subquery refers to a column of the outer query, so conceptually it is re-evaluated for each outer row (the database may plan it smarter, but the meaning is the same). Always give both tables an alias, otherwise salary could mean either side. The query below finds people paid more than the average of their own department.

run('correlated', '''SELECT e.name, e.dept_id FROM employees e
  WHERE e.salary > (SELECT AVG(x.salary) FROM employees x WHERE x.dept_id = e.dept_id)
  ORDER BY e.id''')

run('derived table', '''SELECT d.name, t.avg_pay
  FROM departments d
  JOIN (SELECT dept_id, ROUND(AVG(salary)) AS avg_pay FROM employees GROUP BY dept_id) AS t
    ON t.dept_id = d.id
  ORDER BY d.id''')

run('chained CTEs', '''WITH dept_pay AS (
    SELECT dept_id, SUM(salary) AS total FROM employees GROUP BY dept_id
) , big AS (
    SELECT dept_id FROM dept_pay WHERE total > 20000
)
SELECT name FROM departments WHERE id IN (SELECT dept_id FROM big)''')

Correlated check, derived table and a two-step CTE, reusing db and run from the first program

Sales averages about 7667, so Asha (9000) and Cara (8000) pass but Ben (6000) does not. Support averages about 5333, so only Esha (7000) passes. The inner query uses x and the outer one e, which keeps x.dept_id = e.dept_id unambiguous.

A derived table is a subquery in the FROM clause. It acts as a temporary table for that one query, and the alias after it (AS t) is required. A CTE names the same idea up front with WITH name AS (...), so the main query reads top to bottom. Chain several by separating them with commas, and later ones may use earlier ones, as big uses dept_pay. A CTE exists only for the single statement it belongs to.

A recursive CTE walks a hierarchy such as an org chart. It has an anchor query that supplies the starting rows, then UNION ALL, then a recursive step that joins the CTE to the table to find the next level. The database repeats the step on the newest rows and stops when a round produces no new rows.

run('org chart', '''WITH RECURSIVE chain(id, name, depth) AS (
    SELECT id, name, 0 FROM employees WHERE boss_id IS NULL
    UNION ALL
    SELECT e.id, e.name, c.depth + 1
    FROM employees e JOIN chain c ON e.boss_id = c.id
    WHERE c.depth < 10
)
SELECT name, depth FROM chain ORDER BY depth, id''')

The depth limit is the cycle guard

Asha is level 0, her three reports are level 1, and Dev's two reports are level 2. If bad data made two people each other's boss, the step would never run dry. The c.depth < 10 condition guards against that cycle, and so does carrying a visited-path column and skipping rows already on it.

Choosing between a join and a subquery comes down to what you need back. Join when the result must show columns from both sides. Use EXISTS or IN when the other table only decides which rows survive, because a join would repeat a row once per match.

run('join, columns from both', 'SELECT d.name, e.name FROM departments d JOIN employees e ON e.dept_id = d.id WHERE e.salary > 7500 ORDER BY e.id')
run('EXISTS, filter only', 'SELECT d.name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id AND e.salary > 7500)')
run('anti-join NOT EXISTS', 'SELECT e.name FROM employees e WHERE NOT EXISTS (SELECT 1 FROM employees s WHERE s.boss_id = e.id) ORDER BY e.id')
run('anti-join LEFT JOIN', 'SELECT e.name FROM employees e LEFT JOIN employees s ON s.boss_id = e.id WHERE s.id IS NULL ORDER BY e.id')

Same data, four ways to ask

The two anti-joins both answer who manages nobody, and both stay correct when boss_id has NULLs. The NOT IN version of the same question returned an empty list on page one, which is why it is never the choice over a nullable column.

When a nested query returns something unexpected, work from the inside out before you rewrite anything.

-- 1. the inner piece on its own, with a real value in place of e.dept_id
SELECT AVG(salary) FROM employees WHERE dept_id = 1;

-- 2. the plan for the full statement
EXPLAIN SELECT e.name FROM employees e
WHERE e.salary > (SELECT AVG(x.salary) FROM employees x WHERE x.dept_id = e.dept_id);

Check the pieces first, then the plan; the plan output differs by database

Each question below gives a short scenario, then a query, then the answer. Work out your own answer first, and cover the answer below each query until you have one.

The departments table has three rows: Sales, HR and Legal. In employees, people work in Sales and HR, and one employee has dept_id set to NULL. You want the departments that have no employees.

A student wants every employee who earns more than the average of their own department.

Read this recursive CTE closely and trace the rows it builds, round by round.

Someone wants the employees who earn the same as the person in department 2. Department 2 has three employees.

• A scalar subquery must return one row and one column; zero rows become NULL, and several rows raise an error.
• NOT IN returns nothing if the inner list holds a NULL, so prefer NOT EXISTS or filter the NULLs out.
• EXISTS only tests whether a row is present, so the inner SELECT list is ignored and it never multiplies outer rows.
• In a correlated subquery, always alias the tables and qualify outer columns, or the name silently binds to the inner table.
• A derived table needs an alias, and a CTE gives the same power with named, top-to-bottom steps that live for one statement.
• A recursive CTE is an anchor plus a recursive step joined with UNION ALL; it stops when no new rows appear, so guard against cycles.
• Use a join when you need columns from both tables, use EXISTS or IN when you only filter, and check EXPLAIN before rewriting.