Query Structure: Advanced

A correlated subquery is a subquery that references a column of the outer query anywhere in its body. The reference usually sits in its WHERE, though one in its select list, HAVING or ON correlates it just as much. The subqueries from the intermediate tier run once: the database computes the list or the scalar, then the outer query uses it. A correlated one cannot run on its own, because a reference such as a.plan has no value until the outer query is sitting on a particular row. So the database evaluates it once per outer row, with that row's values filled in. The outer row keeps its own detail while picking up a value computed from its group.

The running example for this lesson is a SaaS billing team's accounts table: 8 accounts on 3 plans, each with a seat count. The question is which accounts are larger than the average account on their own plan. GROUP BY plan returns 3 rows, one per plan, and loses the accounts. The correlated subquery keeps all 8 rows and computes the plan average for each one.

Read the result by plan. The 3 Team accounts average 25 seats, and Globex at 40 sits above it. The 3 Enterprise accounts average 200, and only Hooli at 300 clears that. Both Free accounts have 1 seat, so each equals its average and neither is above it. The subquery ran 8 times, once per account, and each run saw a different a.plan.

Trace account 3. The outer scan reaches Globex, so a.plan is team. The inner query scans accounts as p, keeps the 3 Team rows, averages 10, 25 and 40 to 25, and hands 25 back. The comparison 40 > 25 holds and Globex is kept. On Wonka the inner query returns 1, the comparison 1 > 1 fails, and the row is dropped. Nothing in the result is a group: every row is still an account, with every column of that account available to the select list.

Executed literally, the pattern is a nested loop. With 8 accounts the inner query scans 8 rows on each of 8 passes, so the database touches 64 rows to answer a question about 8. The work grows with the square of the table: at a million accounts the literal plan visits the row count squared. SQL Server and Trino recognise this shape and decorrelate it. They rewrite the subquery as one GROUP BY plan joined back on plan, which computes each average once. PostgreSQL is narrower. It turns correlated EXISTS and NOT EXISTS into semi-joins and anti-joins, and keeps a correlated scalar aggregate like this one as a loop that runs once per outer row.

In PostgreSQL the plan-average query above shows exactly that loop: EXPLAIN prints a SubPlan under the scan of accounts, evaluated for each of the 8 rows. The remedy there is the hand-written form on the right of the comparison below. A CTE groups by plan and is joined back on plan, and it plans as a HashAggregate and a Hash Join. Engines that do decorrelate also have limits. A correlation on an inequality, a LIMIT inside the subquery, or a correlated subquery nested 2 levels deep can leave the optimizer with no equivalent join, and then the loop really runs. A correlated subquery that is slow on a large table is worth rewriting as a CTE, then comparing the 2 plans.

The most common mistake with this pattern leaves the query running and returning the wrong number on every row. Drop the aliases and write WHERE plan = plan inside the subquery, and the query still parses, still runs, and returns one number repeated 8 times.

The same per-row mechanism powers EXISTS, where the subquery returns no value and the outer row is kept or dropped on whether any inner row matched. WHERE NOT EXISTS (SELECT 1 FROM invoices AS i WHERE i.account_id = a.account_id) keeps the accounts that have never been invoiced, and the database stops scanning invoices at the first match for each account. EXISTS has a lesson of its own; what matters here is that the correlation is the same reference to a, and the same aliasing rule protects it.

EXCEPT takes 2 queries and returns the rows of the first that do not appear in the second. It belongs to the same family as UNION: both queries must produce the same number of columns with compatible types, the column names come from the first query, and the output is deduplicated unless ALL is added. Where UNION adds the second set, EXCEPT subtracts it, and the subtraction is directional. A EXCEPT B and B EXCEPT A answer different questions.

The billing team's funnel logs every signup attempt in all_signups and every email confirmation in confirmed_signups. User 2 signed up twice, on March 2 and March 10, and confirmed once. Users 1 and 3 never confirmed, and user 4 confirmed a day after signing up. The switcher runs 3 formulations over those rows: plain EXCEPT, EXCEPT ALL, and the numbered-row rewrite that reproduces EXCEPT ALL on an engine without it.

The first tab returns users 1 and 3. User 2 does not appear, because EXCEPT removes every copy of a row that has any match on the right, and user 2 has one. The second tab swaps the sides and returns nothing: both confirmed users also signed up, so subtracting the signups leaves no row. The order of the sides decides the question. The third tab is the counted version. Written EXCEPT ALL, the operator subtracts occurrence counts, so 2 copies of user 2 on the left minus 1 on the right leaves 1: the March 10 attempt that nobody confirmed. PostgreSQL, Trino and DuckDB accept EXCEPT ALL. On an engine without it, the tab numbers each occurrence with ROW_NUMBER and subtracts the pairs. That second attempt becomes the row (2, 2), which has no partner on the right and survives, so the tab returns 1, 2 and 3.

The animation plays the first tab. The 5 rows of all_signups come in, each is compared against the 2 confirmed rows, and rows 2 and 4 find a match and leave. Rows 1 and 3 remain, and the second copy of user 2 is removed as well, since the match is on the value, not on a particular row.

Set operators compare rows with IS NOT DISTINCT FROM semantics: 2 NULL values count as equal, so a NULL on the left is removed by a NULL on the right. That differs from WHERE user_id = ..., where NULL matches nothing. The difference is why EXCEPT is a safer reconciliation tool than NOT IN, which returns no rows at all when the right side contains a single NULL.

Any question shaped as what is in A and missing from B is an EXCEPT. A cleaning step reads a raw table and writes a curated one, and SELECT * FROM raw EXCEPT SELECT * FROM curated lists the rows it dropped, compared column by column, with no join condition to write. A migration copies a table to a new system, and running the subtraction in both directions produces a full diff: rows lost and rows invented. A contract test compares the rows a job produced today against the rows it was expected to produce, and an empty result in both directions is a pass.

Sometimes the answer needs columns of the left row beyond the ones being compared, or a filter on the right side that the left does not share. In that case the NOT EXISTS form from the previous section does the same subtraction on a key and keeps every column of the outer row. Pick by what the output needs: EXCEPT for a row-level diff with no join to write, NOT EXISTS for a subtraction on a key that returns the full left row.

The signup table counts attempts, so the query that lists unconfirmed attempts, including repeats, needs EXCEPT ALL and the sides in the right order. Arrange the tiles. Two of them do not belong.

INTERSECT returns the rows that both of its queries produce, deduplicated. It follows the same column rules as UNION and EXCEPT, and like them compares whole rows, with NULL equal to NULL. Among the 3 set operators it has the highest precedence in the SQL standard: A UNION B INTERSECT C means A UNION (B INTERSECT C) in PostgreSQL and Trino, so parentheses around any mixed chain are worth their 2 characters.

The users table holds 5 registered users. user_sessions holds 4 session rows for users 2, 2, 4 and 6, and user_purchases holds 5 purchase rows for users 2, 2, 2, 4 and 7. User 6 has sessions and no users row, and user 7 has purchases and no row either, which is the kind of orphan the previous section's EXCEPT would surface. The switcher asks 3 questions of those tables.

The first tab returns 2 and 4, the registered users with at least one session. User 6 is dropped because the left side never produces it. The second tab returns 2 and 4 again: users with both sessions and purchases. The third tab returns 2, 2 and 4, the result INTERSECT ALL gives on engines that support it. Here it is rebuilt by numbering each user's rows on both sides and intersecting the numbered pairs. INTERSECT ALL keeps each row as many times as the smaller side holds it. User 2 appears 2 times in sessions and 3 times in purchases, so the pairs (2, 1) and (2, 2) match and (2, 3) has no partner. User 4 appears once on each side.

An INNER JOIN on user_id with DISTINCT answers the same question and leaves 2 choices open that INTERSECT closes. The join compares only the columns in its ON, so users.plan and user_sessions.session_date can both come out; INTERSECT can only compare rows whose columns line up, which is why each tab projects to user_id first. The join also fans out: user 2 with 2 sessions and 3 purchases produces 6 joined rows before DISTINCT, while INTERSECT never multiplies. For a key-only overlap check, INTERSECT is the shorter and the safer query; for anything that needs columns from both sides, the join.

Funnel analysis is the usual home: viewers who also purchased, trial accounts that also opened a support ticket, keys present in both of 2 systems before a migration cutover. Chaining is allowed, so sessions INTERSECT purchases INTERSECT tickets finds the users present in all 3, a question that EXCEPT cannot express without nesting.

An array column stores a list inside one cell. The accounts table carries a features column of type ARRAY(VARCHAR), so Northwind's row holds ['sso', 'audit_log', 'sla'] in a single field. That shape is compact to store and awkward to query: COUNT cannot count inside a cell, and GROUP BY cannot group by an element. UNNEST turns an array into a table with one row per element, and joined to the row it came from, it repeats that row once per element. The grain of the result changes from one row per account to one row per account and feature.

The 3 Team accounts from the first section carry these arrays: Northwind has 3 features, Acme has 1 and Globex has 2. The query below unnests them. The alias f(feature) names the derived table and its single column. CROSS JOIN is the join type because UNNEST here is a lateral source: it produces rows for the current outer row only, so there is no condition to write.

COUNT(*) per feature is correct: each row is one account holding one feature, so counting rows counts accounts per feature. The seat column tells a different story. Adding the 3 figures gives 135 seats, against a true total of 75, because Northwind's 10 seats were counted 3 times and Globex's 40 twice. Any aggregate of an account-level column has to happen before the unnest, or against DISTINCT account_id after it, or it inflates by the array length of each row. Per-feature seat totals are legitimate when the question is how many seats each feature covers, and wrong the moment they are summed.

WITH ORDINALITY adds the element's 1-based position, so the first feature an account enabled, when the array is kept in enable order, is the row with pos = 1. An empty array produces zero rows, and so does a NULL array, which means CROSS JOIN UNNEST drops an account with no features the way an inner join drops an unmatched row. LEFT JOIN UNNEST ... ON TRUE keeps the account with a NULL feature, which matters whenever the report has to list every account.

The syntax varies by engine more than most of SQL does. Trino and PostgreSQL both accept CROSS JOIN UNNEST(features) AS f(feature) and WITH ORDINALITY; PostgreSQL also allows unnest(features) in the select list. BigQuery writes CROSS JOIN UNNEST(features) AS feature, with the alias naming the element directly, and Snowflake uses LATERAL FLATTEN. The behaviour is the same in all of them: one row per element, the outer row copied alongside.

The drill hands over an accounts table with a features array column and a query that selects the account and its feature but is missing its join. Supply the clause that produces one row per account and feature.

A SELECT with no FROM evaluates its select list once and returns 1 row. There is no table to scan, so every column is a literal, an expression over literals, a function of the environment such as CURRENT_DATE, or a scalar subquery. PostgreSQL, Trino, BigQuery, SQL Server, DuckDB and current MySQL all accept it. Oracle before 23ai requires FROM DUAL, a one-row dummy table that exists for this purpose and nothing else.

The row carries today's date in the first column, then 14400, 1188.00 and team. This is the query that checks an expression before it goes into a 300-line report, that answers what day the scheduler thinks it is, and that confirms a connection is alive. SELECT 1 is the liveness probe most connection pools send, for the same reason: it runs without touching a table or taking a lock.

Stacking SELECT statements with UNION ALL produces a table that exists only inside the query. Column names come from the first SELECT, and the later ones supply values in the same order. The query below declares the 4 plans the billing system is supposed to offer and subtracts the plans that accounts actually use, with the EXCEPT from earlier in the lesson.

The result is partner, a plan nobody has bought. The same inline rows serve as test fixtures: a unit test for a transformation feeds it a 4-row CTE instead of loading a table, and the expected output is another inline set, compared with EXCEPT in both directions. The VALUES form, SELECT * FROM (VALUES ('free', 0), ('team', 20)) AS t(plan, price), is shorter for the same job and is supported by PostgreSQL, Trino and SQL Server.

A scalar subquery is a complete query folded into one cell, so a FROM-less SELECT can hold several of them and return one row of figures from several tables, with no join and no fan-out. The pattern shows up in monitoring dashboards and in the first line of a backfill script that records what the tables looked like before the run.

Generating a date spine is the other side of the previous section: SEQUENCE builds an array of 7 dates and UNNEST turns it into 7 rows, with no calendar table in the database. A LEFT JOIN from that spine to daily revenue is how a report shows a zero for the day with no sales, which GROUP BY on the sales table alone would skip. PostgreSQL has generate_series for the same job and BigQuery has GENERATE_DATE_ARRAY, each feeding its own UNNEST.