⚡ CrackLab
beginner~4h

Subqueries, EXISTS & Correlated Subqueries

Subqueries are SELECT statements nested within another SQL statement. They can appear in SELECT, FROM, WHERE, and HAVING clauses.

Some questions can't be answered by filtering a single table's own columns — "customers who have placed at least one order" requires checking against a second table's data, row by row. Subqueries are how SQL expresses "filter based on the result of another query," nested directly inside the outer one.

Without subqueries, you'd have to run a separate query, pull the results into application code, and then filter your main query using that result set manually — slow, and it breaks the moment the intermediate result set is too large to comfortably hold in application memory.

Subquery — a query nested inside another query's WHERE, SELECT, or FROM clause. Correlated subquery — a subquery that references a column from the outer query, and is logically re-evaluated once per outer row. EXISTS — checks only whether a subquery returns any row at all, stopping at the first match, without caring about the actual returned values.

Subqueries are SELECT statements nested within another SQL statement. They can appear in SELECT, FROM, WHERE, and HAVING clauses.

Types of subqueries

  • Scalar subquery — returns exactly one row and one column. Used in SELECT or WHERE.

  • Row subquery — returns one row with multiple columns.

  • Table subquery (derived table) — returns a result set used as a table in FROM.

  • Correlated subquery — references columns from the outer query; executes once per outer row.

  • Non-correlated subquery — independent of outer query; executes once.

Subquery vs JOIN

  • Subqueries are often clearer for complex conditions.

  • JOINs are usually faster (optimizer can reorder and hash join).

  • Modern optimizers often convert subqueries to joins internally.

  • EXISTS > IN for large sets (short-circuits on first match).

CTE (Common Table Expressions) from Chapter 6 are often cleaner alternatives to complex subqueries.

Non-correlated subquery execution

  • Inner query executed first (once).

  • Result materialized or used directly.

  • Outer query uses the result.

  • Performance: inner query runs once regardless of outer table size.

Correlated subquery execution

  • Outer query processes each row.

  • For EACH outer row: inner query executed with values from that row.

  • Result used to filter or compute for that row.

  • Performance: inner query executes N times (N = outer rows). O(n²) worst case.

  • Optimizer often transforms to JOIN or semi-join for better performance.

EXISTS execution

  • For each outer row: execute inner query until FIRST match found.

  • Returns TRUE immediately on first match (short-circuit).

  • More efficient than IN for large sets (doesn't materialize all matching rows).

Step 1: Identify what you need to find (scalar value? list of IDs? a derived table?).

Step 2: Write the inner query first and test it independently.

Step 3: Wrap it as a subquery in the appropriate clause.

Step 4: If it references outer columns, it's correlated — watch for performance.

Step 5: Consider EXISTS for membership tests (faster than IN for large sets).

Step 6: Consider CTE for readability when subquery is reused.

Step 7: Use EXPLAIN to verify optimizer converted subquery to join.

  • Use EXISTS over IN for large sets — EXISTS short-circuits; IN materializes all matching values.

  • Non-correlated subqueries over correlated when possible — run once vs run per row.

  • Use CTEs instead of complex nested subqueries — more readable and often same performance.

  • Be careful with NOT IN and NULLs — use NOT EXISTS for safety.

  • Scalar subquery in SELECT runs once per row — cache if used multiple times.

  • Use derived tables (FROM subquery) to pre-aggregate before joining.

  • Name derived table aliases clearly: FROM (...) AS dept_summary.

  • Test inner query independently before nesting.

  • Check EXPLAIN to verify optimizer converted subquery to JOIN.

  • Avoid deeply nested subqueries (more than 3 levels) — use CTEs for clarity.

  • NOT IN with NULL values — if ANY value in the IN list is NULL, NOT IN returns empty result. Always use NOT EXISTS or filter NULLs explicitly.

  • Correlated subquery in SELECT list with poor performance — runs once per output row. Replace with JOIN or window function.

  • Unintentional correlated subquery — accidentally referencing outer table in inner query.

  • Forgetting alias for derived tables — every derived table (FROM subquery) requires an alias.

  • EXISTS SELECT * vs SELECT 1 — same performance; SELECT 1 is convention.

  • Using subquery where JOIN works — JOINs are usually more readable and equally or more efficient.

  • Multi-row subquery with = instead of IN — '= (SELECT ...)' fails if subquery returns multiple rows.

  • EXISTS vs IN: for uncorrelated, similar performance. For correlated: EXISTS is generally better due to short-circuit.

  • Optimizer unnests simple correlated subqueries — converts to semi-join or join automatically.

  • For complex correlated subqueries: manually convert to JOIN or window function.

  • Lateral joins (PostgreSQL LATERAL): execute subquery once per row from the left table; can reference left table columns; more efficient than correlated subquery for complex cases.

  • Materialized CTEs (WITH ... AS MATERIALIZED): force PostgreSQL to evaluate CTE once; prevents re-evaluation for repeated use.

  • Index correlated subquery columns: CREATE INDEX ON employees(dept_id, salary) for correlated average query.

Subqueries built from concatenated user input carry the same SQL injection risk as any other dynamic SQL — parameterize subquery values exactly as strictly as you would a top-level WHERE clause.

A correlated subquery that looks fine on a small test table can degrade sharply as the outer table grows, since it's logically re-run per outer row — watch EXPLAIN ANALYZE specifically for a subquery plan node executed a surprising number of times, and consider rewriting as a JOIN if that count is large.

  • Use pg_stat_statements to identify slow queries with correlated subqueries.

  • Replace complex subqueries with materialized views for frequently run reports.

  • EXISTS check before DML operations: IF EXISTS (SELECT 1 FROM ...) THEN ... pattern.

  • Test behavior with NULLs explicitly — write unit tests for NULL edge cases in subqueries.

  • Use LATERAL JOIN in PostgreSQL for complex per-row computations instead of correlated subquery.

  • Write a correlated subquery to find employees who earn more than the average salary of their department. Then rewrite it as a JOIN with a derived table. Compare EXPLAIN ANALYZE output.

  • Write a query using EXISTS to find all departments that have at least one employee with salary > 90000.

  • Write a query using NOT EXISTS to find employees who have placed NO orders.

  • The query 'SELECT dept_name FROM departments WHERE dept_id NOT IN (SELECT dept_id FROM employees)' returns 0 rows unexpectedly. Debug and fix it using two different approaches.

  • Scalar subquery: returns 1 row, 1 column — use in SELECT or WHERE.

  • Non-correlated: executes once; correlated: executes per outer row (O(n²)).

  • Derived table (FROM subquery): treats result as a table; requires alias.

  • IN vs EXISTS: EXISTS short-circuits on first match; safer with NULLs.

  • NOT IN + NULLs = empty result! Always use NOT EXISTS for safety.

  • Correlated subqueries: optimizer often converts to JOIN — check EXPLAIN.

  • Modern alternative: window functions over complex correlated subqueries.

  • EXISTS SELECT 1 is convention; * gives same performance.

Want a visual for this concept?

Generate a diagram tailored to “Subqueries, EXISTS & Correlated Subqueries” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Sign in to generate a visual →

Practice quiz

Next Step

Continue to String & Date/Time Functions →← Back to all SQL chapters