SQL: SUBQUERIES
Master nested relational queries: scalar subqueries in SELECT, dynamic filters in WHERE, derived tables in FROM, correlated queries, EXISTS vs IN mechanics, and Subquery vs JOIN trade-offs.
What is a Subquery?
A subquery (also called an inner query or nested query) is a SELECT statement embedded within an outer SQL statement. Subqueries can be nested inside SELECT, INSERT, UPDATE, or DELETE statements, as well as inside other subqueries.
A subquery is always enclosed in parentheses (...). In basic execution models, the inner query runs first, calculates its result set, and passes that intermediate data directly into the outer query to drive filtering, projection, or joins.
| Subquery Category | Output Shape | Common Placement |
|---|---|---|
| Scalar Subquery | Single value (1 row, 1 column) | SELECT list, WHERE comparison (=, >, <) |
| Row / Column List | Multiple rows, 1 column | WHERE clause with IN, NOT IN, ANY, ALL |
| Table Subquery (Derived Table) | Multiple rows and multiple columns | FROM clause or JOIN clause (requires alias) |
| Correlated Subquery | Dependent on outer query row values | WHERE clause with EXISTS, or SELECT column |
Subquery Execution Mechanics
- Parentheses Syntax:Subqueries must always be enclosed in parentheses: (SELECT ...).
- Self-Contained Logic:Uncorrelated subqueries evaluate once and can be run independently in an editor for rapid debugging.
Subquery in WHERE Clause
Placing a subquery in the WHERE clause allows you to filter rows dynamically against calculated metrics rather than hardcoding static threshold numbers.
In the query below, the inner query computes the company-wide average salary. The outer query then filters for employees who earn strictly more than that computed average.
Subquery in WHERE Clause Example
SQLFilter employees earning above the company-wide average salary
Single-Value Comparison Rules
- Comparison Operators:Standard operators (=, !=, <, >, <=, >=) require the subquery to return a scalar value.
- Multiple Row Error:If an inner query returns more than one row with =, the database throws an error: 'subquery returns more than 1 row'.
Subquery in SELECT Clause (Scalar Subquery)
A scalar subquery placed within the SELECT column list acts as a dynamically calculated column. It must return exactly one value per row.
In this example, each employee record is displayed alongside the company-wide average salary and the mathematical difference between their compensation and that benchmark.
Scalar Subquery in SELECT Projection
SQLProject an overall company metric next to individual employee records
Scalar Subquery Constraints
- Strict Cardinality:Must return at most 1 row and 1 column; returning multiple values aborts query execution.
- Performance Note:Uncorrelated scalar subqueries are evaluated once and cached; correlated scalar subqueries execute row-by-row.
Subquery in FROM Clause (Derived Table)
When a subquery is placed inside the FROM clause, it acts as a temporary virtual table called a derived table (or inline view). The outer query treats this derived table exactly like a physical table on disk.
Every derived table in the FROM clause must be assigned an alias. Omitting the alias triggers an immediate syntax error across virtually all SQL database engines.
Derived Table in FROM Clause
SQLAggregate department stats in a subquery, then query the aggregated summary
Derived Table Best Practices
- Mandatory Alias:Always supply a table alias (e.g. AS dept_summary) after the closing parenthesis.
- Pre-Aggregation Pattern:Use derived tables to aggregate child records before joining to parent tables to avoid row multiplication.
Correlated Subquery
Unlike an independent subquery that executes once and hands its output to the outer query, a correlated subquery references one or more columns from the outer query.
Because it depends on values from the current outer row, the database engine must execute the inner query repeatedly - once for every candidate row evaluated by the outer query.
In the query below, we find employees who earn strictly more than the average salary of their own respective department. Notice the outer alias reference e_outer.department_id inside the inner subquery.
Correlated Subquery Example
SQLFind employees earning more than their own department average
Performance Implications of Correlation
- Row-by-Row Execution:If the outer query scans 50,000 rows, the inner query may execute 50,000 times without planner caching.
- Window Function Alternative:Modern SQL often replaces correlated subqueries with AVG(salary) OVER (PARTITION BY dept_id) for faster single-pass scans.
Subquery with IN, EXISTS, ANY, ALL
When an inner subquery returns multiple rows, scalar comparison operators fail. SQL provides dedicated multi-row comparison operators to evaluate lists:
- IN: Evaluates to TRUE if the outer column matches any value in the subquery list.
- EXISTS: Evaluates to TRUE if the subquery returns at least one row, stopping immediately upon finding the first match.
- ANY (or SOME): Evaluates to TRUE if the comparison condition holds for at least one value returned by the subquery.
- ALL: Evaluates to TRUE only if the comparison condition holds for all values returned by the subquery.
Subquery with IN and EXISTS
SQLIdentify departments with active staff using multi-row operators
Portable Equivalents for ANY and ALL
- Dialect Availability:ANY and ALL are supported in PostgreSQL, MySQL, and SQL Server. SQLite does not support ANY/ALL syntax directly.
- Portable Pattern for > ALL:WHERE col > ALL (SELECT val ...) is functionally identical to WHERE col > (SELECT MAX(val) ...).
- Portable Pattern for > ANY:WHERE col > ANY (SELECT val ...) is functionally identical to WHERE col > (SELECT MIN(val) ...).
Portable ALL / ANY Replacement
SQLFind employees earning more than all employees in Department 2
Nested Subqueries
Subqueries can be nested inside other subqueries across multiple levels (Outer → Middle → Inner). The engine resolves the innermost subquery first, feeds its output up to the intermediate subquery, and finally passes that result to the outer query.
Two-Level Nested Subquery
SQLFind students enrolled in courses with above-average credit values
Nesting Depth and Maintainability
- Limit Nesting Depth:Avoid nesting more than 2-3 levels deep. Deeply nested subqueries are difficult to read and debug.
- Refactoring to CTEs:When queries become complex, refactor nested subqueries into Common Table Expressions (WITH clauses) for modular readability.
Subquery vs JOIN
Many data retrieval problems can be solved using either a subquery or a JOIN. Understanding when each approach is appropriate helps write clean and performant SQL.
| Evaluation Criteria | Subquery | JOIN |
|---|---|---|
| Column Output | Can only project columns from the outer table | Can project columns from all joined tables simultaneously |
| Row Multiplicity Risk | Preserves original row count (no duplication) | One-to-many joins duplicate parent rows unless aggregated |
| Execution Optimization | Modern query optimizers often unnest subqueries to joins | Optimizers have full freedom to reorder table access paths |
| Best Applied For | Existence checks, pre-aggregation, self-contained sub-filters | Combining multi-table fields into a unified output view |
Subquery vs JOIN Equivalence
SQLContrast an IN subquery with an INNER JOIN on customer order data
The Row Duplication Trap
- Avoid Accidental Joins:When you only need to filter parent rows based on children (e.g. customers with orders), using WHERE EXISTS or IN prevents unintended row multiplication.
- Join When Projecting:If you need order_date or order_status in the final output, a JOIN is mandatory.
EXISTS vs IN
Both EXISTS and IN filter records based on values in another table, but they work with fundamentally different operational models.
| Aspect | EXISTS | IN |
|---|---|---|
| Evaluation Mode | Boolean probe: stops scanning on the first matching row | Builds or hashes a complete set of candidate values |
| NULL Handling with Negation | NOT EXISTS is fully safe with NULL values in subqueries | NOT IN fails completely if the subquery returns any NULL |
| Projection Requirement | SELECT 1 or SELECT * (columns are ignored) | Must select the exact matching column |
The Critical NOT IN vs NOT EXISTS Trap
SQLDemonstrating why NOT EXISTS is safer when foreign keys contain NULL
The SQL Three-Valued Logic Trap
- Why NOT IN Fails:In SQL, x NOT IN (1, 2, NULL) is evaluated as (x != 1 AND x != 2 AND x != NULL). Because any comparison with NULL yields UNKNOWN, the entire condition evaluates to UNKNOWN and drops all rows.
- Best Practice Rule:Always use NOT EXISTS (or LEFT JOIN ... WHERE key IS NULL) when writing anti-joins against columns that may contain NULL values.
Knowledge Check: SQL Subqueries
1. What is the strict return requirement for a scalar subquery in SELECT?
2. How does a correlated subquery execute compared to an independent subquery?
3. What dangerous result occurs when a NOT IN subquery contains a NULL value?
4. What is a derived table in SQL?
5. Why is an EXISTS semi-join often preferred over an INNER JOIN for filtering?
6. Which subquery clause always requires an assigned table alias?