Important topics: Subqueries and CTEs

1.1 A query inside a query

A subquery is a complete SELECT used inside another statement. It can return one value, a list of values or a whole table, and where you place it decides which shape the outer query expects. A subquery in the WHERE clause feeds the filter, a subquery in the select list feeds a computed column, and a subquery in FROM acts like a temporary table for the rest of the query. The engine may run the inner query once, or once per outer row, and the difference matters for cost.

The parentheses are required around a subquery, and the inner statement does not take a trailing semicolon. Column names from the outer query are visible to the inner query when the inner one is correlated, which is powerful and easy to misread. When a subquery feels hard to read, the fix is usually a CTE: name the step, then build the next step on top of the name. Every example in this module runs on SQLite 3.53.

1.2 Scalar subqueries return one value

A scalar subquery is used where a single value is expected: a comparison, an arithmetic operand or a select-list column. It should return one row and one column. SELECT (SELECT AVG(fee) FROM courses) is a scalar subquery, and putting it in a select list lets every row compare itself against the overall average.

SQLite is forgiving when the inner query returns several rows: it takes the first row and carries on. PostgreSQL raises an error, because the standard says a scalar subquery must return at most one row. Relying on SQLite's leniency hides a logic error, so add LIMIT 1 and an ORDER BY when you really mean one row, or switch to an aggregate that returns exactly one value. A scalar subquery that returns no rows becomes NULL in SQLite and PostgreSQL both.

SELECT title, fee, ROUND((SELECT AVG(fee) FROM courses), 2) AS avg_fee
FROM courses
ORDER BY fee DESC
LIMIT 4;

title                   | fee  | avg_fee
------------------------+------+--------
Machine Learning Basics | 8999 | 5665.67
Data Structures         | 5999 | 5665.67
Computer Networks       | 5499 | 5665.67
SQL and Databases       | 4999 | 5665.67

SELECT name
FROM students
WHERE student_id = (SELECT student_id FROM enrollments);

name
------------
Aarav Sharma

1.3 IN, NOT IN and the NULL trap

A subquery that returns one column can feed IN: WHERE student_id IN (SELECT student_id FROM enrollments WHERE course_code = 'SQL') keeps students who have at least one SQL enrollment. NOT IN negates the same membership test, and it is where the NULL trap lives. If the subquery returns a NULL among its values, then for a non-matching row the comparison becomes UNKNOWN, and no row passes.

The query below shows the failure. The inner query selects mentor_id from every student, and three of those values are NULL. The outer NOT IN therefore returns zero rows even though several students really are not mentors. Replace it with NOT EXISTS, or add WHERE mentor_id IS NOT NULL inside the subquery so the list contains no NULL. The NULL-safe form is the one to use in production code.

SELECT name FROM students
WHERE student_id IN (SELECT student_id FROM enrollments WHERE course_code = 'SQL')
ORDER BY name;

name
------------
Aarav Sharma
Ishita Bose
Meera Iyer
Sana Khan
Zoya Ali

SELECT name FROM students
WHERE mentor_id NOT IN (SELECT mentor_id FROM students)
ORDER BY name;

(0 rows)

1.4 EXISTS and correlated subqueries

EXISTS (SELECT ...) is TRUE when the subquery returns at least one row, and the engine can stop at the first match. It is often paired with a correlated condition, where the inner query refers to a column from the outer query, such as e.student_id = s.student_id. NOT EXISTS is the safe negation and has no NULL trap, because it asks whether any row exists rather than comparing values.

The correlated form recomputes the inner condition for each outer row, which can be expensive without an index on the inner join column. The practice database has a primary key and a unique constraint on the columns involved, so the lookups are cheap. On a large table, the same logic is often written as a join and the planner may choose either shape; check the plan when the query is hot.

SELECT s.name
FROM students s
WHERE EXISTS (
  SELECT 1 FROM enrollments e
  WHERE e.student_id = s.student_id AND e.course_code = 'DS'
)
ORDER BY s.name;

name
------------
Aarav Sharma
Neel Joshi
Sana Khan

SELECT name FROM students
WHERE NOT EXISTS (
  SELECT 1 FROM students m WHERE m.mentor_id = students.mentor_id
)
ORDER BY name;

name
------------
Aarav Sharma
Dev Patel
Meera Iyer

1.5 Derived tables in FROM

A subquery in the FROM clause produces a derived table, also called an inline view. The outer query can filter, join and sort it like a real table. Give it an alias: standard SQL requires one, and readers benefit from the name. The pattern is useful when an aggregate has to be filtered by its own result, because WHERE cannot refer to an aggregate but the outer query can refer to a column of the derived table.

The derived table might be materialised or flattened into the outer query, depending on the engine and the plan. A more readable way to write the same idea is a CTE, which names the subquery at the top instead of embedding it in the FROM line.

SELECT course_code, n
FROM (SELECT course_code, COUNT(*) AS n FROM enrollments GROUP BY course_code) AS counts
WHERE n >= 3
ORDER BY n DESC, course_code;

course_code | n
------------+--
SQL         | 5
PY          | 4
DS          | 3

1.6 WITH names the steps

A common table expression, written with WITH ... AS ( ... ), gives a subquery a name that later parts of the statement can use. A single CTE replaces a derived table; several CTEs read top to bottom like a pipeline. CTEs can be joined to each other, and a CTE can read an earlier CTE defined above it. SQLite and PostgreSQL both support WITH; MySQL supports it from version 8.0, and older MySQL is the common gap.

Readability is the main benefit: each step gets a name, so the final SELECT describes how the named pieces combine. Performance can be either way. SQLite normally treats a CTE like an inline view, while PostgreSQL folds a CTE referenced once into the outer query unless MATERIALIZED is written, and NOT MATERIALIZED forces the inline choice. When a CTE is slow, check the plan before assuming the name is the problem, and remember that WITH RECURSIVE is a separate feature for walking hierarchies.

WITH counts AS (
  SELECT course_code, COUNT(*) AS n
  FROM enrollments
  GROUP BY course_code
)
SELECT course_code, n
FROM counts
WHERE n >= 3
ORDER BY n DESC, course_code;

course_code | n
------------+--
SQL         | 5
PY          | 4
DS          | 3

WITH totals AS (
  SELECT course_code, COUNT(*) AS total
  FROM enrollments
  GROUP BY course_code
),
active AS (
  SELECT course_code, COUNT(*) AS active
  FROM enrollments
  WHERE status = 'active'
  GROUP BY course_code
)
SELECT t.course_code, t.total, a.active
FROM totals t
JOIN active a ON a.course_code = t.course_code
ORDER BY t.total DESC, t.course_code;

course_code | total | active
------------+-------+-------
SQL         | 5     | 3
PY          | 4     | 2
DS          | 3     | 3
ML          | 1     | 1
WEB         | 1     | 1

1.7 Subqueries in other places

A scalar subquery can sit in the select list, as the average fee example showed, or inside HAVING when a group has to be compared with a global number. INSERT ... SELECT fills a table from a query, UPDATE ... WHERE col IN (subquery) changes a set selected elsewhere, and DELETE ... WHERE NOT EXISTS (subquery) removes orphaned rows. Each of those is the same idea in a different clause, and the WHERE rules of that clause still apply: an aggregate cannot appear in WHERE, but it can appear inside a subquery in WHERE.

The practical rule is to build complex statements in steps. Write the inner query on its own, check its rows, then place it. When a CTE grows past two or three steps, split the statement into a view or a temporary table if the engine and the surrounding process allow it.

1.8 Common mistakes

• Writing NOT IN against a subquery that can return NULL and getting an empty result.
• Assuming SQLite and PostgreSQL treat a multi-row scalar subquery the same way.
• Forgetting the alias on a derived table.
• Correlating a subquery by accident when the column name exists in both queries.
• Replacing every join with a subquery and paying for repeated work.