Master row-by-row outer query correlation, short-circuit boolean EXISTS tests, NULL-safe NOT EXISTS anti-joins, and eliminate 3VL traps.
Open in Present mode · 400 practice questions · Live class schedule · Try the playground
Jo class mein bola, yahan nukte mein likha hai.
What I said in class, written here as key points.
Dynamic row-by-row outer variable binding, boolean short-circuit semi-joins, and NULL-safe anti-joins in PostgreSQL 18.
Welcome to Session 26. In Session 24 and 25, we covered independent subqueries and derived tables. Today we connect inner subqueries directly to outer row tuples.
A four-pillar architectural progression from outer scope binding to boolean short-circuiting and optimizer plans.
Contrast static uncorrelated subqueries (Session 24) with dynamic correlated subqueries (Session 26). Explain variable scope in programming languages as an analogy.
In Session 25, we mastered inline derived tables in FROM. Today we bind subqueries dynamically to outer row tuples.
SELECT o.order_id, d.item_rev FROM sales.orders o JOIN ( SELECT order_id, SUM(net_amount) AS item_rev FROM sales.order_items GROUP BY order_id ) d ON o.order_id = d.order_id;
Pre-aggregated child records into an isolated inline derived table at order grain, preventing 1:N join fan-out data duplication.
SELECT e.employee_id, e.salary FROM stores.employees e WHERE e.salary > ( SELECT AVG(e2.salary) FROM stores.employees e2 WHERE e2.dept_id = e.dept_id );
Reference outer row attributes dynamically (e.dept_id) to evaluate relative peer benchmarks and short-circuit boolean checks.
Emphasize scope visibility. In derived tables, the subquery executes independently first. In correlated subqueries, the inner query references outer columns.
SELECT e.employee_id, e.dept_id, e.salary FROM stores.employees e WHERE e.salary > ( SELECT AVG(sub.salary) FROM stores.employees sub WHERE sub.dept_id = e.dept_id ); -- Correlated SubPlan restores equity!
Set up the emotional stakes: explain how rewarding employees based on flat global averages caused warehouse managers to quit. Then reveal how one WHERE clause restored equity.
How PostgreSQL binds outer tuple references and executes parameterized subplans.
stores.employees e and extracts its department key (e.g. dept_id = 1).SELECT AVG(salary) WHERE dept_id = 1 (₹67,489.22).Walk through employee 2451 (Henry Ganguly, dept 1, salary ₹2,42,825). Show that Henry qualifies against dept 1 average (₹67,489.22).
Click execution modes below to contrast naive row-by-row nested loop evaluation against planner optimizations.
e and extracts its correlation column values.Toggle between mode 1 (SubPlan loop) and mode 2 (Derived Table alternative). Point out that scalar WHERE subqueries do not unnest into semi-joins, and discuss production indexing advice.
Binding inner query filters to outer table aliases to calculate relative peer thresholds.
SELECT employee_id, first_name, last_name, dept_id, salary FROM stores.employees e WHERE salary > ( SELECT AVG(salary) FROM stores.employees WHERE dept_id = e.dept_id ) ORDER BY dept_id ASC, salary DESC, employee_id ASC LIMIT 10;
stores.employees e) so inner clauses can unambiguously reference its columns.WHERE inner.dept_id = e.dept_id.>, =), the subquery must emit exactly one row and one column.EXPLAIN output, correlated subqueries appear as SubPlan 1, confirming dynamic per-row execution.Point out e.dept_id inside the inner subquery. Explain that this is what makes the subquery correlated rather than self-contained.
Contrast instant short-circuit boolean checks against full-set membership scans.
SELECT DISTINCT deduplication.EXISTS queries into high-speed Hash Semi-Joins automatically.Teach 'one match is enough, 1 row per customer'. EXISTS returns 47,521 customers vs 1,50,000 orders in INNER JOIN. For Scene 9, EXISTS yields 27,674 customers vs 40,434 join rows. IN and EXISTS plan identically as semi-joins.
Testing row existence without fan-out cartesian multiplication or memory-heavy deduplication.
SELECT
c.customer_id,
c.first_name,
c.last_name,
c.tier
FROM customers.customers c
WHERE EXISTS (
SELECT 1
FROM sales.orders o
WHERE o.cust_id = c.customer_id
AND o.net_total >= 100000
)
ORDER BY c.customer_id ASC
LIMIT 10;
EXISTS evaluates strictly to TRUE or FALSE. It never returns data rows or column values to the outer query.SELECT 1 signals that columns do not matter. The optimizer ignores the projection list completely.INNER JOIN to orders, EXISTS never duplicates customer records, preserving the 1-row-per-customer grain.Merge Semi Join or Hash Semi Join, achieving single-pass execution.Address the common interview myth: 'SELECT 1 is faster than SELECT * in EXISTS'. Explain that PostgreSQL's optimizer treats them identically.
Understanding why three-valued logic breaks NOT IN, and why NOT EXISTS is completely immune.
val != NULL yields UNKNOWN, silently collapsing the entire output to zero rows.This is intermediate SQL's most dangerous trap: (x != 1 AND x != NULL) yields UNKNOWN. RetailMart cust_id has 0 NULLs today (NOT IN returns 2,479); but a single guest NULL drops NOT IN to 0 rows, while NOT EXISTS remains immune.
Isolating non-matching parent records safely without three-valued logic vulnerabilities.
SELECT c.customer_id, c.first_name, c.last_name, c.tier FROM customers.customers c WHERE NOT EXISTS ( SELECT 1 FROM sales.orders o WHERE o.cust_id = c.customer_id ) ORDER BY c.customer_id ASC LIMIT 10;
LEFT JOIN ... WHERE o.cust_id IS NULL plans as a Hash Anti Join, while WHERE o.order_id IS NULL plans as Hash Right Join + Filter. NOT EXISTS plans as a clean Hash Anti Join either way (2,479 rows).Advise students to adopt NOT EXISTS as their personal standard for anti-joins. It prevents subtle 3VL bugs that slip through code reviews.
Decorating entity records with contextual peer baselines directly in the projection list.
price - (SELECT AVG...)) to measure premium spread.Demonstrate how having both the absolute metric (price) and relative benchmark (brand average) on the same row empowers business analysts.
Projecting dynamic cohort averages and calculating row-level deviations in a single scan.
SELECT
p.product_id,
p.product_name,
p.brand_id,
p.price,
ROUND((
SELECT AVG(p2.price)
FROM products.products p2
WHERE p2.brand_id = p.brand_id
), 2) AS brand_avg_price,
ROUND(p.price - (
SELECT AVG(p3.price)
FROM products.products p3
WHERE p3.brand_id = p.brand_id
), 2) AS price_variance
FROM products.products p
ORDER BY price_variance DESC, p.product_id ASC
LIMIT 10;
SELECT projection list generates a dynamic computed column per row.price - (SELECT ...)).AS brand_avg_price) so client applications receive a clean column header.Teach the architectural rule of thumb: correlated SELECT subqueries are fine for filtered UI views (LIMIT 20); use derived tables for batch pipelines.
Avoid the four most frequent anti-patterns encountered in correlated subquery engineering.
WHERE dept_id = dept_id inside the subquery binds to the inner table itself, causing a 1=1 tautology rather than a correlation.NOT IN (SELECT nullable_col) returns zero rows the moment a single NULL exists. Always use NOT EXISTS instead.EXISTS preserves parent grain naturally.NOT EXISTS for anti-join queries.Warn students about testing inner queries alone: beginners often think their query is broken when pgAdmin complains about the missing outer table.
Transition students from theory to live coding drills in the interactive practice arena.
Work through 4 graded challenges below. Test your queries live in the interactive terminal.
Give students 15 minutes to complete the 4 challenges. Circulate and check whether they remember to correlate inner WHERE clauses to outer aliases.
Authentic technical questions asked by senior engineering and data analytics interview panels.
Have students practice explaining Question 3 (3VL NOT IN Trap). It is one of the most common senior SQL interview questions.
Click cards to test your retention of outer query correlation, boolean EXISTS tests, and anti-join mechanics.
Outer Variable Reference! An inner WHERE or SELECT clause references a column alias from the outer query.InitPlan runs once at startup and caches the value; SubPlan re-executes dynamically per outer candidate row.Boolean Semantics! EXISTS only tests for row existence (TRUE/FALSE); scanning further matching child rows is unnecessary.Developer Convention! The query planner ignores the projection list completely; both execute identically.Three-Valued Logic (3VL)! val != NULL evaluates to UNKNOWN, collapsing the entire AND condition to UNKNOWN.Set Cardinality Check! NOT EXISTS checks whether matching rows count = 0, evaluating strictly to TRUE or FALSE.Fatal Runtime Error! Scalar comparisons require exactly 1 row; returning 2+ rows halts execution immediately.Semi-Join! The planner flattens correlated EXISTS into an in-memory Hash Semi-Join or Merge Semi-Join.YES! It acts as a scalar expression computing relative cohort benchmarks (e.g. brand average price) per row.Large Batch Aggregations! Over lakhs of rows, pre-aggregating in FROM avoids repeated SubPlan evaluations.Review card 2 (short-circuiting) and card 5 (3VL NOT IN trap). Both are critical for writing bug-free production queries.
Master these six rules to structure performant, bug-free dynamic subqueries in PostgreSQL.
EXISTS needs only one qualifying row per outer row, so it never multiplies rows: each customer comes back once, however many orders they have.NOT EXISTS for anti-joins. Never use NOT IN on nullable columns due to 3VL collapse.Have students snapshot this slide: it is the comprehensive mental cheat sheet for correlated execution and semi-join architecture.
A production syntax reference card for dynamic correlation, semi-joins, and anti-joins.
SELECT
e.employee_id,
e.first_name || ' ' || e.last_name AS employee_name,
e.dept_id,
ROUND(e.salary, 2) AS salary,
ROUND((
SELECT AVG(e2.salary)
FROM stores.employees e2
WHERE e2.dept_id = e.dept_id
), 2) AS dept_avg_salary,
ROUND(e.salary - (
SELECT AVG(e3.salary)
FROM stores.employees e3
WHERE e3.dept_id = e.dept_id
), 2) AS dept_variance
FROM stores.employees e
WHERE EXISTS (
SELECT 1
FROM stores.employees e_peer
WHERE e_peer.dept_id = e.dept_id
AND e_peer.salary > e.salary
)
ORDER BY e.dept_id ASC, e.salary DESC, e.employee_id ASC
LIMIT 10;
WHERE col > (SELECT AVG(...) WHERE sub.k = out.k)WHERE EXISTS (SELECT 1 FROM child c WHERE c.k = p.k)WHERE NOT EXISTS (SELECT 1 FROM ...)(SELECT AVG(...) WHERE ...) AS cohort_avgval - (SELECT AVG(...) WHERE ...) AS varianceBookmark this quick reference for practical syntax lookup during live coding and homework challenges.
Solidify your mastery with these 4 production challenges across RetailMart schemas.
Identify all employees who earn strictly more than the average salary of their own department using a correlated subquery.
SELECT e.employee_id, e.first_name || ' ' || e.last_name AS employee_name, e.dept_id, ROUND(e.salary, 2) AS salary FROM stores.employees e WHERE e.salary > ( SELECT AVG(e2.salary) FROM stores.employees e2 WHERE e2.dept_id = e.dept_id ) ORDER BY e.dept_id ASC, e.salary DESC, e.employee_id ASC LIMIT 10;
Use NOT EXISTS to generate a verified roster of registered customers who have never placed a single order in sales.orders.
SELECT c.customer_id, c.first_name || ' ' || c.last_name AS customer_name, c.tier FROM customers.customers c WHERE NOT EXISTS ( SELECT 1 FROM sales.orders o WHERE o.cust_id = c.customer_id ) ORDER BY c.customer_id ASC LIMIT 10;
Project product catalog details alongside brand average price and price variance using correlated SELECT subqueries.
Identify customers who have placed orders with net_total >= ₹1,00,000 using EXISTS to guarantee zero duplicate fan-out.
Assign these 4 challenges for student practice before the next live session on LATERAL Joins.
Milestones across the 59-session master curriculum
Celebrate completing Session 26! Point out that they now possess the tools to solve complex peer-relative filtering and anti-joins.
Unlocking dynamic subquery execution in the FROM clause with cross-referencing capabilities.
Send students off with excitement for LATERAL joins in Session 27.