🪆Subqueries

Rewriting the nested loop

Engines strive to Decorrelate subqueries. A subquery like WHERE ID IN (SELECT ID FROM ...) is logically a "loop," but the optimizer rewrites it into a Semi-Join (stop at first match) or an Anti-Join (keep only if no match found). This turns an O(N²) problem into O(N) using a Hash Join.

🏗️Physical Operators: Semi-Joins

Matching without duplicating.

Operator	Logical SQL	Mechanical Goal
Hash Semi-Join	EXISTS / IN	Build a hash of the inner side; for each outer row, probe and stop immediately on first hit.
Hash Anti-Join	NOT EXISTS / NOT IN	Keep the outer row only if the hash probe finds ZERO matches.

The NOT IN / NULL Trap

Standard SQL dictates that if any value in your NOT IN (SELECT ...) list is NULL, the entire predicate returns Unknown. This results in an empty result set, even if other values don't match!

Use NOT EXISTS or check for NULLs

Prefer NOT EXISTS, which uses 3-valued logic more safely, or ensure the subquery filters out nulls: WHERE col IS NOT NULL.

📎Examples on sample lab / trial data

Read each shape for intent, not syntax alone.

Nest SELECT statements in predicates, projections, or FROM clauses. Correlated subqueries reference outer columns and execute conceptually per outer row, optimize carefully.

SQL

Scalar subquery in SELECT

SELECT study_id,
       (SELECT COUNT(*) FROM enrollments e WHERE e.study_id = s.study_id) AS n
FROM studies s;

✓Illustrative result set

study_id	n
ST-1	0
ST-2	144

SQL

IN subquery

SELECT patient_id
FROM vitals
WHERE visit_id IN (
  SELECT visit_id FROM visits WHERE visit_type = 'ICU'
);

✓Illustrative result set

patient_id
501
502

SQL

Derived table in FROM

SELECT bucket, COUNT(*) AS studies
FROM (
  SELECT study_id,
         CASE WHEN budget_musd < 1 THEN 'S' WHEN budget_musd < 10 THEN 'M' ELSE 'L' END AS bucket
  FROM studies
) x
GROUP BY bucket;

✓Illustrative result set

bucket	studies
S	40
M	22
L	6

TL;DR

A subquery is a SELECT nested inside another statement, scalar (one row/column), row, or table-shaped. IN/EXISTS correlate tests; derived tables live in FROM. Correlated subqueries reference outer columns and can become nested-loop heavy, rewrite to joins or lateral queries when plans show repeated scans.