---

🟢 Junior Level

There is no single universal answer: the optimal choice depends on query intent, data cardinality, and relational semantics. In modern database engines like PostgreSQL, the cost-based query optimizer frequently rewrites (unnests) subqueries into physical joins behind the scenes, yielding identical execution plans.

Simple Rule of Thumb

• Use a JOIN when you need to project or manipulate columns from both tables in the final SELECT list.
• Use a Subquery (EXISTS / IN) when you only need to filter or check for the existence of related records without projecting any columns from the referenced table.

-- 1. JOIN: Retrieve customer name AND order total
SELECT c.name, o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- 2. Subquery (EXISTS): Filter for customers who have placed at least one order
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

---

🟡 Middle Level

Subquery Unnesting & Decorrelation

In PostgreSQL, the query planner actively performs Subquery Unnesting (Pull-up). A query written with an IN or EXISTS subquery is systematically rewritten into a physical Semi-Join:

-- Written by the Developer:
SELECT * FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders WHERE total_amount > 200
);

-- How the Optimizer Executes It:
-- Converts directly to a single-pass Hash Semi-Join (O(N + M) complexity)!

Why EXISTS Outperforms JOIN + DISTINCT (Join Amplification)

A very common developer anti-pattern is using an INNER JOIN paired with DISTINCT to check for existence:

-- ❌ ANTI-PATTERN: Joining a 1-to-many relationship duplicates customer rows,
-- forcing an expensive in-memory/disk Sort/Unique deduplication pass:
SELECT DISTINCT c.* 
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- ✅ OPTIMAL: EXISTS compiles directly into an optimized Semi-Join:
SELECT c.* 
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- Short-circuit evaluation: Stops scanning 'orders' for a given customer 
-- immediately upon finding the FIRST matching record!

Optimization Fences: When Unnesting Fails

Certain SQL constructs act as Optimization Fences, preventing PostgreSQL from flattening a subquery into a join and forcing row-by-row execution:

Construct Inside Subquery	Optimizer Reaction	Architectural Alternative
LIMIT / OFFSET	Cannot be pulled up into a standard join	Use LEFT JOIN LATERAL
DISTINCT in correlated subquery	Must execute as an independent subquery	Rewrite using Window Functions or GROUP BY
GROUP BY / Aggregates	Forces per-row correlated execution	Use LEFT JOIN (SELECT ... GROUP BY)
UNION / INTERSECT	Evaluated as an independent subplan	Rewrite using OR or separate CTEs

Scalar Subqueries in SELECT (The $O(N \times M)$ Trap)

-- ❌ SEVERE BOTTLENECK: Executes 1,000,000 separate index seeks for 1,000,000 customers!
SELECT 
    c.name, 
    (SELECT SUM(total_amount) FROM orders o WHERE o.customer_id = c.customer_id) AS total_spent
FROM customers c;
-- Execution Plan: SubPlan (O(N * M))

-- ✅ OPTIMAL: Single Hash Join + HashAggregate pass!
SELECT 
    c.name, 
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
-- Execution Plan: Hash Join + HashAggregate (O(N + M))

---

🔴 Senior Level

Execution Nodes: SubPlan vs InitPlan

In EXPLAIN query plans, identifying whether a subquery is an InitPlan or a SubPlan reveals its computational cost:

1. InitPlan (Static Evaluation — $O(1)$)

Evaluated exactly once before the main query begins. Its computed scalar value is passed as a constant parameter into the outer query:

EXPLAIN SELECT * FROM orders WHERE total_amount > (SELECT AVG(total_amount) FROM orders);
-- InitPlan 1 -> Aggregate -> Seq Scan on orders
-- Seq Scan on orders (Filter: total_amount > $0)

2. SubPlan (Correlated Execution Penalty — $O(N \times M)$)

Evaluated repeatedly for every single row processed by the outer query:

EXPLAIN SELECT c.name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) 
FROM customers c;
-- Seq Scan on customers
--   SubPlan 1 -> Aggregate -> Index Scan on orders (customer_id = c.customer_id)

The Memoize Node (PostgreSQL 14+)

In PostgreSQL 14+, the engine introduced the Memoize execution node to optimize parameterized Nested Loops and LATERAL subqueries by caching results in memory:

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.name, recent_order.total_amount
FROM customers c
LEFT JOIN LATERAL (
    SELECT total_amount FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY created_at DESC LIMIT 1
) recent_order ON true;

-- PostgreSQL 14+ Plan:
-- Nested Loop
--   -> Seq Scan on customers
--   -> Memoize (Hits: 850000, Misses: 150000, Evictions: 0)  <-- IN-MEMORY RESULT CACHING!
--       -> Limit
--           -> Index Scan on orders

When duplicate parameter values occur in the outer table, Memoize serves up to 80–90% of requests directly from in-memory cache, reducing query execution time by orders of magnitude.

CTE Inlining Evolution (PostgreSQL 12+)

• Prior to PostgreSQL 12: Common Table Expressions (WITH ... AS) acted as strict optimization barriers. PostgreSQL was forced to materialize every CTE into a temporary data buffer before proceeding, preventing predicate pushdown.
• PostgreSQL 12+: CTEs are inlined by default (AS NOT MATERIALIZED), allowing the planner to push predicates down into the CTE just like standard subqueries.

-- Explicitly force materialization when a costly calculation is referenced multiple times:
WITH costly_calc AS MATERIALIZED (
    SELECT customer_id, SUM(total_amount) AS total 
    FROM orders 
    GROUP BY customer_id
)
SELECT * FROM active_customers a JOIN costly_calc c ON a.id = c.customer_id
UNION ALL
SELECT * FROM vip_customers v JOIN costly_calc c ON v.id = c.customer_id;

---

4 Tricky Questions

1. In EXPLAIN output, what is the critical difference between an InitPlan node and a SubPlan node?

Answer:

• InitPlan: An independent, uncorrelated subquery that executes only once before the outer plan begins. Its result is stored in a temporary memory slot and treated as a constant scalar value throughout the execution of the main query (incurring $O(1)$ overhead relative to outer rows).
• SubPlan: A correlated subquery that cannot be unnested into a join. It is executed repeatedly for every single row emitted by the outer query plan node. If the outer plan processes 1,000,000 rows, the SubPlan runs 1,000,000 separate times, producing an $O(N \times M)$ operational bottleneck.

2. Why does SELECT * FROM users u WHERE EXISTS (...) outperform SELECT DISTINCT u.* FROM users u INNER JOIN orders o ...?

Answer:
Because of Join Amplification and deduplication overhead:

• When a user has 500 orders, an INNER JOIN produces 500 duplicate user rows in intermediate memory. To satisfy SELECT DISTINCT, the database must execute an expensive HashAggregate or disk-spilling Sort (Unique) to collapse those 500 rows back down to 1.
• EXISTS compiles into a Semi-Join. The execution engine employs short-circuit logic: the moment it encounters the first matching order for a user, it immediately emits the user row and advances to the next user without scanning the remaining 499 orders.

3. How did PostgreSQL 12 change the optimization of Common Table Expressions (CTEs), and when should you still use AS MATERIALIZED?

Answer:
Before PostgreSQL 12, CTEs were always materialized as temporary relations, acting as optimization fences that blocked predicate pushdown and join reordering.
In PostgreSQL 12+, CTEs are inlined by default (NOT MATERIALIZED) if they are side-effect-free (SELECT only) and referenced without optimization barriers.
You should explicitly specify WITH ... AS MATERIALIZED in two primary cases:

1. When an expensive subquery is referenced two or more times in different branches of a query (e.g., in UNION ALL or multiple joins) to ensure it executes only once.
2. When you deliberately want to enforce an optimization barrier to prevent the planner from choosing a bad join order or pushing down a volatile function.

4. What is the Memoize node introduced in PostgreSQL 14, and how does it optimize parameterized LATERAL joins?

Answer:
Memoize is an in-memory execution cache node placed directly above the inner plan node of a parameterized Nested Loop join.
When an outer relation feeds parameters into an inner LATERAL subquery (e.g., WHERE o.customer_id = c.customer_id), Memoize hashes the incoming parameter. If the parameter has been seen before (a Cache Hit), Memoize immediately returns the cached inner tuples without accessing the table heap or B-tree index. If it is a Cache Miss, the inner query executes and the result is stored in the cache. This transforms repetitive inner index seeks from $O(N \log M)$ into near-$O(1)$ memory lookups.

---

🎯 Interview Cheat Sheet

Core Guidelines

• Use JOIN: When projecting columns from both relations.
• Use Subquery (EXISTS): When checking for existence without projecting child data (avoids JOIN + DISTINCT row duplication).
• Avoid Scalar Subqueries in SELECT: Replace (SELECT COUNT(*) FROM child WHERE parent_id = ...) with LEFT JOIN ... GROUP BY to avoid $O(N \times M)$ SubPlan execution.
• NOT IN Hazard: Never use NOT IN with subqueries; a single NULL causes the entire predicate to evaluate to UNKNOWN (returning 0 rows). Always use NOT EXISTS.

Execution Plan Identifiers

• InitPlan: Runs once prior to query execution ($O(1)$) $\rightarrow$ Fast.
• SubPlan: Correlated subquery executing per outer row ($O(N \times M)$) $\rightarrow$ Dangerous bottleneck.
• Memoize (PG 14+): Caches parameterized nested loop lookups in memory.
• CTE Inlining (PG 12+): Inlined by default; use MATERIALIZED only when reusing an expensive subquery across multiple joins.

Red Flags (What NOT to Say)

• ❌ “Subqueries are always slower than joins.” (Modern optimizers unnest correlated subqueries into identical semi-joins).
• ❌ “CTEs are always materialized into temporary tables on disk.” (In PostgreSQL 12+, CTEs are inlined by default).
• ❌ “NOT IN is faster than NOT EXISTS.” (NOT IN cannot optimize easily and breaks when NULL values exist).

Related Topics

• What JOIN types exist — Semi-joins, Anti-joins, and physical join strategies
• Difference between INNER JOIN and LEFT JOIN — Join Amplification and NULL handling
• What is a correlated subquery — Correlation mechanics and decorrelation tuning
• What is Explain plan — Detecting SubPlan nodes and Memoize statistics