188: Subqueries, EXISTS, IN, Correlated Queries, and LATERAL

Learning outcomes

By the end of this lesson, you should be able to:

explain and apply scalar subqueries in a realistic implementation;
explain and apply IN and NOT IN in a realistic implementation;
explain and apply EXISTS in a realistic implementation;
explain and apply correlated subqueries in a realistic implementation;
explain and apply derived tables in a realistic implementation.

You should also be able to recognize when PostgreSQL's LATERAL gives a dependent query an explicit relational shape, and when a join-based alternative is easier to reason about or performs better.

Prerequisites and retrieval

This lesson assumes the earlier 01–06 foundation and the preceding lessons in this module. Before you read, retrieve one concrete example from a previous project where this kind of concern appeared. Perhaps you needed one calculated value, to filter by membership, to check whether a related row existed, or to select a few related rows for each parent. The goal is not to memorize vocabulary in isolation. It is to make a defensible choice inside a PostgreSQL-backed transactional application, where schema design, correctness, query plans, and concurrency all matter.

Terminology

Scalar subqueries: A scalar subquery returns one column and must produce at most one row. If the query produces more than one row, PostgreSQL raises an error rather than silently choosing one.
IN and NOT IN: IN tests whether a value belongs to the set produced by a subquery or list. NOT IN has a subtle interaction with SQL's three-valued logic: a NULL in the subquery can make the result unknown rather than true.
EXISTS: EXISTS asks whether at least one matching row exists. The selected columns inside an EXISTS query do not matter; the presence of a row does. The database can stop looking once it has established that a match exists.
Correlated subqueries: A correlated subquery references a value from the current row of its outer query. That dependency is useful for per-row decisions, but it must be evaluated with the data volume and execution plan in mind.
Derived tables: A subquery in FROM creates an intermediate relation with a defined output shape. That relation can then be joined, filtered, or aggregated by the surrounding query.
LATERAL: PostgreSQL LATERAL allows a FROM item to depend on rows produced by items that appear before it. It is useful for top-N-per-parent queries, set-returning functions, and dependent subqueries whose relational dependency should be explicit.

Mental model

Treat Subqueries, EXISTS, IN, Correlated Queries, and LATERAL as a design problem with observable inputs, outputs, invariants, and failure modes. A subquery is not automatically a performance problem or a performance solution. It is a compositional query expression, and the right form depends on the result you need: existence, a single scalar value, an intermediate relation, or a dependent computation for each outer row.

The useful distinction is between the shape of the requirement and the syntax used to express it. If the requirement is “does any related row exist?”, EXISTS communicates that directly. If it is “which one value belongs in this result?”, a scalar subquery may be appropriate, provided its cardinality is guaranteed. If it is “build a relation and use it in another query,” a derived table is often clearer. If each parent needs its own limited or ordered child query, LATERAL can make that dependency visible.

A strong implementation makes assumptions visible, narrows uncertainty at boundaries, and leaves enough evidence—tests, types, constraints, metrics, or diagrams—to prove why the design is safe. In particular, do not leave a uniqueness or non-NULL assumption implicit when query semantics depend on it.

A useful interview and production sequence is:

requirement -> constraints -> model -> implementation -> failure analysis -> verification

Do not jump from a requirement directly to a library call or a query pattern copied from a different schema. First state what must remain true. Then choose the mechanism that enforces it, and finally inspect both its results and its plan.

Deep dive

1. Scalar subqueries

Suppose a result needs one value calculated from related rows. A scalar subquery can put that value directly in a SELECT list or expression. The formal rule is strict: it must return one column and at most one row. Returning no rows produces NULL in a scalar expression, while returning two or more rows fails at runtime.

That last behavior is valuable because it exposes a broken cardinality assumption, but it also means the query should not rely on “there will probably be one.” Enforce the assumption with a key or unique constraint where it is a data rule, or make the query's aggregation or filtering explicit where multiple rows are valid. An ORDER BY by itself does not make a multi-row subquery scalar; if selecting one row is the requirement, the selection rule must be deliberate.

Decision rule: Use scalar subqueries deliberately when they make the contract or invariant easier to prove. If they only reduce typing while hiding an assumption about uniqueness, absence, or ordering, prefer the more explicit design.

2. IN and NOT IN

IN is a membership question. It is useful when the outer value should match any value returned by a subquery or listed explicitly. Duplicate values in the subquery do not change membership, although they may still point to a data-model issue or affect the work needed to produce the set.

This is where people usually get confused: NOT IN is not simply the textual opposite of IN when NULL is possible. SQL uses three-valued logic. If the subquery contains a NULL, PostgreSQL may evaluate a non-matching comparison as unknown, so a row that looks like it should pass the NOT IN filter can be excluded. If the intended meaning is “there is no related row satisfying this condition,” NOT EXISTS is often clearer and avoids that anti-join trap. If NOT IN is the chosen form, make the non-NULL contract explicit and test it.

Decision rule: Use IN and NOT IN deliberately when they make the contract or invariant easier to prove. If the expression only reduces typing while hiding NULL behavior or membership assumptions, prefer the more explicit design.

3. EXISTS

EXISTS expresses a semijoin: keep an outer row when at least one related row satisfies the condition. The columns selected by the subquery are irrelevant to the existence test, so the query communicates intent more directly when written around the relationship and its predicates. Conceptually, the database can stop looking after the first match; the actual plan still depends on the optimizer, indexes, statistics, and predicates.

Use EXISTS when you need to filter by a relationship without returning the related rows. It also avoids accidental row multiplication that can occur when a plain join is used only as a filter and the joined table has several matches. For the opposite question, NOT EXISTS usually maps naturally to “no matching row exists.”

Decision rule: Use EXISTS deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an output-shape or performance assumption, compare it with an explicit join and inspect the plan.

4. Correlated subqueries

A correlated subquery references a value from the current row of its outer query. That lets the inner query answer a question such as “what related data applies to this customer?” for each customer in the outer result. The correlation is the defining feature; without the reference to the outer row, the subquery is independent.

Many correlated forms can be transformed by the optimizer into joins, semijoins, or other efficient plans. That is not a promise that every form will scale well. The predicates, indexes, row estimates, and outer result size determine the work. Always inspect plans for large datasets, because a form that is readable on a small fixture can still become expensive if it causes repeated work or prevents a useful access path.

Decision rule: Use correlated subqueries deliberately when they make the contract or invariant easier to prove. If they only reduce typing while hiding a possible per-row cost, compare an equivalent join or derived-table design and verify with representative data.

5. Derived tables

A subquery in FROM creates a derived table, or intermediate relation. It is useful when a query naturally has stages: first calculate or aggregate a relation, then join that shaped result to another relation. Give the derived table a clear alias and keep its output columns understandable. Deeply nested, opaque expressions make cardinality and ownership harder to inspect.

A derived table is not automatically materialized as a separate physical table. PostgreSQL may inline or otherwise optimize the expression, so choose this form for clarity and relational shape rather than assuming it creates a performance boundary. If you need a physical or optimization boundary, that is a separate decision with separate evidence.

Decision rule: Use derived tables deliberately when they make the contract or invariant easier to prove. If they only reduce typing while hiding an intermediate cardinality or filtering assumption, prefer a clearer explicit query shape.

6. LATERAL

A normal FROM item is generally evaluated independently of the items that follow it. PostgreSQL LATERAL changes that relationship: a lateral item may refer to columns from preceding FROM items, and PostgreSQL evaluates that dependent expression in the context of each preceding row. This is particularly useful when each parent needs its own ordered and limited child result, such as the latest two orders per customer.

LATERAL is also useful with set-returning functions and other dependent subqueries where making the row-by-row relationship explicit improves the query's structure. It is not a synonym for “faster,” and it does not remove the need to consider indexes, row counts, ordering, and plan shape. A join, window function, or pre-aggregated derived table may be a better fit for another requirement.

Decision rule: Use LATERAL deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding dependent execution cost or an alternative relational shape, compare the alternatives and inspect the plan.

Worked example

Consider a PostgreSQL-backed transactional application where schema design, correctness, query plans, and concurrency all matter. Start by writing the requirement in one sentence, then list the input and output contracts. Identify which concept owns each failure mode instead of reaching for a familiar query pattern by habit.

The important move is separation: parsing or validation belongs at the boundary; domain rules belong in the domain or service layer; persistence rules belong in the database or repository; presentation rules belong in the client. Mixing these concerns can make a happy-path demo look shorter, but it makes edge cases much harder to reason about. For example, a client-side filter is not a substitute for a database condition, and a query that happens to return one row in test data does not prove a scalar cardinality invariant.

Here is a straightforward join-and-aggregate query that lists active customers, including those with no orders, and counts their orders:

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, c.name
ORDER BY order_count DESC;

The LEFT JOIN preserves an active customer when no order matches, and COUNT(o.id) returns zero for that customer because the joined order columns are NULL. The WHERE condition applies to the customer relation here, so it does not turn the outer join into an accidental inner join. The grouping also makes the output contract explicit: one result row per active customer.

This example is intentionally join-based. The same application may later need EXISTS to filter customers with at least one paid order, NOT EXISTS for customers with no paid order, a derived table for pre-aggregated order totals, or LATERAL for the latest two orders per customer. Choose among those forms based on the result shape, not on a rule that subqueries are always better or worse than joins.

Walk the example with at least four cases: the normal path, an empty or missing value, a duplicate/retry/concurrent path where relevant, and a dependency failure. For each case, state which layer detects the problem and what the caller observes. Also inspect the generated SQL and execution plan when the dataset is representative. This is the level of explanation expected in a senior code review or technical interview.

Production perspective

Production correctness is broader than “the code works on my machine.” Ask how the design behaves during deploys, retries, partial failure, stale clients, concurrent requests, malformed data, schema changes, and high cardinality. A subquery's result can be logically correct while the surrounding transaction still has a race, or while the plan becomes unacceptable when one customer has millions of related rows. Prefer explicit contracts, bounded resource usage, structured errors, and measurable behavior. Optimize only after you can identify the bottleneck or risk with evidence.

When the topic involves an external dependency, define a timeout and cancellation strategy. When it involves persistence, define transaction and consistency expectations: state whether the query must observe one consistent snapshot, whether a retry is safe, and which constraint protects the invariant. When it involves user-visible state, define loading, empty, error, stale, and success states. When it involves security, assume the client can be modified and all network input is untrusted. Parameterize values and do not let a query shape become an excuse to interpolate untrusted input.

Guided lab

Write “customers with no paid order”, “latest two orders per customer”, and “products used by at least one active order” using EXISTS/NOT EXISTS and LATERAL. Compare equivalent join-based plans. For the latest-two query, make the per-customer ordering and limit explicit; for the existence queries, ensure the predicates describe the related rows whose presence or absence matters.

Complete the lab with this discipline:

Write the requirement and two non-requirements.
List input, output, and error contracts before implementation.
Implement the smallest correct vertical slice.
Add at least one invalid-input test and one edge-case test.
Instrument or inspect the behavior instead of guessing. For the database portion, compare actual execution plans on representative data.
Refactor one hidden assumption into an explicit type, constraint, function, or configuration.
Explain one alternative design and why you did not choose it.
Record a short “what would break at 10× scale?” note.

Pay particular attention to NULL, duplicate related rows, parents with no children, and a parent with many children. These cases reveal whether the query expresses existence, membership, aggregation, or per-parent selection correctly.

Edge cases and failure modes

Scalar subqueries: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Verify what happens when no row is returned and when the supposed single-row result unexpectedly contains duplicates.
IN and NOT IN: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Include a subquery result containing NULL, because that is the case most likely to invalidate an assumed NOT IN result.
EXISTS: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Confirm that multiple matching rows do not duplicate the outer result.
Correlated subqueries: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Inspect whether the plan performs acceptably as the outer relation grows.
Derived tables: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Check the intermediate relation's cardinality and whether filters are applied where you expect.

Common mistakes and debugging

Solving the example instead of the requirement: a copied pattern can be syntactically correct but architecturally wrong.
Hiding uncertainty with assertions, broad exception handlers, permissive schemas, or “temporary” any values.
Treating NOT IN as a safe anti-join without checking whether the subquery can return NULL.
Using a join only as an existence test and then discovering that multiple related rows multiplied the outer result.
Assuming that a correlated subquery is either always executed once per outer row or always optimized away; the actual execution plan is the evidence.
Testing only the happy path and therefore discovering contracts only after integration.
Optimizing before measuring, or selecting a scalable mechanism without a scale requirement.
Letting client-side behavior stand in for server-side authorization, validation, or persistence guarantees.

For debugging, reproduce the smallest failing case, inspect the actual value or execution plan, trace the boundary where the invariant first becomes false, and fix the owning layer rather than adding a downstream patch. For a query, inspect the parameters, NULL values, duplicate rows, estimated versus actual row counts, join strategy, scan type, and timing. If the result is wrong, first reduce the data until the wrong row appears or disappears; if the result is slow, compare the plan before and after the smallest targeted change.

Interview questions

What problem do scalar subqueries solve, and what trade-off or failure mode would make you choose a different approach?
What problem do IN and NOT IN solve, and what trade-off or failure mode would make you choose a different approach?
What problem does EXISTS solve, and what trade-off or failure mode would make you choose a different approach?
What problem do correlated subqueries solve, and what trade-off or failure mode would make you choose a different approach?
What problem do derived tables solve, and what trade-off or failure mode would make you choose a different approach?

Checkpoint

Without notes, explain Subqueries, EXISTS, IN, Correlated Queries, and LATERAL to another developer in five minutes. Your explanation must include one invariant, one edge case, one production failure mode, and one alternative design. Then implement a small example without copying the lesson code. Be prepared to explain the result shape, the relevant cardinality or NULL assumptions, and what you would inspect if the query behaved differently at scale.

Mastery checklist

I can define the core terms precisely.
I can choose a design from requirements instead of from habit.
I can implement and test the normal path and edge cases.
I can explain the runtime, storage, or complexity cost.
I can identify which layer owns validation, errors, and recovery.
I can compare at least two reasonable alternatives.
I can explain how the design changes at larger scale or stricter reliability.