Lesson 13 of 20
In progress
Subqueries and CTEs
Use a query inside another query, prefer EXISTS to IN for large sets, and name the steps of a complex query with a CTE.
A subquery is a query used as a value, a list or a table. It lets you build an answer in steps without leaving SQL, and it is often clearer than a join with a filter bolted on.
What you will learn
- Use scalar, list and table subqueries.
- Choose between
INandEXISTS. - Name intermediate steps with a CTE.
The data
Editable. Runs in your browser.
CREATE TABLE members (
id INTEGER PRIMARY KEY,
team_id INTEGER NOT NULL,
name TEXT NOT NULL,
salary_paise INTEGER NOT NULL
);
INSERT INTO teams (id, name, budget_paise) VALUES
(1, 'Platform', 9000000),
(2, 'Growth', 4000000),
(3, 'Support', 2500000);
INSERT INTO members (id, team_id, name, salary_paise) VALUES
(1, 1, 'Ana', 2200000),
(2, 1, 'Ravi', 1800000),
(3, 2, 'Meera', 1600000),
(4, 3, 'Sam', 900000);
Press Run to see the output.
A scalar subquery
A scalar subquery returns one value and can be used anywhere a value is allowed.
Editable. Runs in your browser.
Press Run to see the output.
The inner query computes 1,625,000. The outer query compares each salary with it. The subquery runs once, not once per row, because it does not refer to the outer query.
Scalar subqueries are handy in the SELECT list too:
Editable. Runs in your browser.
Press Run to see the output.
Here the subquery is correlated: it references t.id, so the engine evaluates it per team. That is fine for a handful of teams and expensive for a large table, where a join and GROUP BY is better.
A list subquery
Editable. Runs in your browser.
Press Run to see the output.
IN compares each row with the list the subquery returns. It reads well and is a natural translation of the question.
NOT IN is where NULLs bite: if the subquery can return a NULL, every comparison becomes unknown and the outer query returns nothing. A NOT NULL column or a WHERE ... IS NOT NULL in the subquery prevents that.
EXISTS
Editable. Runs in your browser.
Press Run to see the output.
EXISTS is true as soon as one matching row is found. It stops early, so it is usually faster than IN on a large set, and it is immune to the NULL problem because it never compares values.
NOT EXISTS is the reliable way to write "has none":
Editable. Runs in your browser.
Press Run to see the output.
That returns no teams here, since every team has at least one member, and it stays correct when NULLs appear in the correlated columns.
Table subqueries
A subquery in FROM behaves like a table and must have an alias.
Editable. Runs in your browser.
Press Run to see the output.
This is how you filter on an aggregate without HAVING, and it is a clean way to pre-aggregate before a join so the join does not multiply rows.
CTEs name the steps
WITH puts the subquery before the main query and gives it a name, which reads far better once there are two or three steps.
Editable. Runs in your browser.
Press Run to see the output.
Read top to bottom: compute per-team totals, then join them to teams and subtract. The same query as nested subqueries is a wall of parentheses.
A CTE can be referenced more than once in the main query, which is impossible for a plain subquery in FROM. That makes it the tool for a summary used in two places.
Two caveats: a CTE is not automatically faster, and in some databases it is materialised. Write it for clarity first, then check the plan if the query is slow.
Choosing between the forms
- One value compared once: scalar subquery.
- A small, static list:
IN. - "At least one" or "none at all" on a large table:
EXISTS/NOT EXISTS. - Several steps or reuse: CTE.
- A filtering relationship you also need columns from: a join.
Name each CTE after the thing it produces — per_team, monthly_revenue — rather than temp or q1. The name is read twice, once where it is defined and once where it is used, and a good one makes the second reading unnecessary work.
Using NOT IN against a subquery that can return NULL. The result is empty and the query looks correct. Use NOT EXISTS instead.
Find teams whose payroll is above 3,000,000 paise, using a CTE, and list their name, people and payroll.
Show solution
Editable. Runs in your browser.
Press Run to see the output.
Platform is the only team above the threshold, with 4,000,000 across two people. The CTE aggregates once, and the filter applies to the aggregated total rather than to individual salaries.
Recap
- Subqueries can produce a value, a list or a table; correlated ones run per outer row.
EXISTSstops at the first match and avoids theNOT INNULL trap.- CTEs name each step and can be reused; they do not guarantee speed.
Next, you will change data with INSERT, UPDATE and DELETE, carefully.
Are CTEs slower than subqueries?
Can a subquery return several columns?
Progress is saved in this browser only. No account, no email.