Advanced Subqueries
Leverage scalar, row, correlated and EXISTS nested subqueries to solve complex data comparisons.
SQL Query Architecture & Optimization Guide
Subqueries are queries nested inside outer SELECT, FROM, or WHERE statements. They enable modular computation, correlated lookups, and set membership checks using EXISTS, IN, and scalar subselects.
Non-correlated subqueries execute once and pass static results to the outer query. Correlated subqueries reference columns from the outer row, requiring row-by-row re-evaluation unless the query planner decorrelates them into standard joins.
If a subquery returns even a single NULL value, a NOT IN condition evaluates to UNKNOWN for all rows, returning zero results. Always use NOT EXISTS or ensure the subquery explicitly filters out NULLs.
Interactive WebAssembly SQL Playground
Frequently Asked Questions on Advanced Subqueries
Why does NOT IN return no rows when a subquery contains NULL?
If the inner dataset contains a NULL, SQL evaluates value NOT IN (1, NULL) as value != 1 AND value != NULL, which produces UNKNOWN. Consequently, the entire condition evaluates to falsy. Use NOT EXISTS to avoid this issue.
What is the difference between EXISTS and IN for subqueries?
EXISTS returns true as soon as the first matching record is located (short-circuit evaluation). IN evaluates the full list of values returned by the subquery before evaluating the outer condition.
What is a correlated subquery?
A correlated subquery references one or more columns from the outer query, meaning it depends on the current outer row and cannot be evaluated independently.