Subquery Concept - SQL Subqueries: Nested Query Power | OneNoughtOne

0/278

Database Management SystemsSubqueries

SQL Subqueries: Nested Query Power

LevelIntermediate

Duration60 mins

TopicSubqueries

1 / 5

Subquery Concept

The Power of Nested Queries

Consider a deceptively simple question: "Find all employees who earn more than the average salary in their department."

At first glance, this seems straightforward. But pause and think about what's involved:

You need to calculate the average salary for each department
For each employee, compare their salary against their specific department's average
Return only those who exceed their department's threshold

This isn't a simple filter—it requires relating each row to an aggregated value computed from other rows. The data you need to evaluate a row depends on computations over other rows.

This is precisely where subqueries shine. A subquery is a query nested inside another query, allowing you to use the result of one query as input to another. They transform SQL from a simple data retrieval language into a powerful compositional system where queries become building blocks for more complex operations.

What You Will Learn

By the end of this page, you will understand the fundamental concept of subqueries—what they are, how they execute, why they're essential, and how they relate to the outer (main) query. This conceptual foundation is critical before exploring the specific types of subqueries.

What Is a Subquery?

A subquery (also called a nested query or inner query) is a complete SELECT statement embedded within another SQL statement. The containing statement is called the outer query, main query, or parent query.

The key insight is that a subquery is a first-class query—it follows all the rules of a standalone SELECT statement. It can have its own FROM, WHERE, GROUP BY, HAVING, and ORDER BY clauses. The only difference is its context: instead of returning results directly to the user, it returns results to be used by the outer query.

Formal Definition:

A subquery is a SELECT statement nested inside another SQL statement (typically SELECT, INSERT, UPDATE, or DELETE) that returns a result set used by the outer statement for evaluation, filtering, or data manipulation.

basic_subquery_anatomy.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Anatomy of a Subquery
-- The outer query uses the result of the inner query
 
SELECT employee_name, salary
FROM employees
WHERE salary > (                    -- Outer query uses comparison
    SELECT AVG(salary)              -- Inner query (subquery)
    FROM employees                  -- Subquery has its own FROM
    WHERE department_id = 10        -- Subquery has its own WHERE
);
 
-- Breaking it down:
-- 1. The subquery: SELECT AVG(salary) FROM employees WHERE department_id = 10
--    This is a complete, valid query that returns a single value (e.g., 75000)
-- 2. The outer query: SELECT ... WHERE salary > 75000
--    Uses the subquery result as if it were a literal value

The Substitution Mental Model

Think of a subquery as a placeholder that gets replaced by its result before the outer query executes. In the example above, (SELECT AVG(salary) FROM employees WHERE department_id = 10) might evaluate to 75000, and then the outer query effectively becomes WHERE salary > 75000. This substitution model helps understand simple subqueries, though correlated subqueries (covered in Module 6) work differently.

The Query Composition Paradigm

Subqueries represent query composition—the ability to combine queries to create more powerful queries. This is analogous to function composition in programming, where you pass the output of one function as input to another.

Without subqueries, you would need to:

Execute the first query
Capture its result in your application
Construct a second query using that result
Execute the second query

This is inefficient, error-prone, and loses the benefits of database optimization. With subqueries, the database handles all of this internally, often with significant performance advantages.

Without Subqueries (Application-Side)

•Query 1: Get average salary → returns 75000
•Application stores result in variable
•Query 2: SELECT * FROM employees WHERE salary > 75000
•Two round trips to database
•Race condition risk (data may change between queries)
•No database-level optimization possible

With Subqueries (Database-Side)

•Single query with nested subquery
•Database evaluates as atomic operation
•One round trip to database
•Consistent snapshot (no race conditions)
•Query optimizer can choose efficient execution
•Often converts to optimized joins internally

The Relational Algebra Connection:

In relational algebra, subqueries correspond to the composition of relational operators. The result of one operation becomes the input to another. This mathematical foundation ensures that subqueries are well-defined—the inner query produces a relation (possibly containing a single value), and the outer query consumes it.

This compositionality is what makes SQL expressive. You can nest queries arbitrarily deep:

SELECT * FROM A WHERE x IN (SELECT y FROM B WHERE z = (SELECT MAX(z) FROM C))

Each level evaluates from innermost to outermost (conceptually), building up the final result.

Subquery Execution Model

Understanding how subqueries execute is crucial for writing efficient SQL. The execution model differs based on whether the subquery references columns from the outer query.

Non-Correlated (Uncorrelated) Subqueries:

A non-correlated subquery is independent of the outer query—it can be executed on its own and produces the same result regardless of the outer query's context. These subqueries are conceptually executed once, and their result is used by the outer query.

Correlated Subqueries:

A correlated subquery references columns from the outer query, creating a dependency. These subqueries are conceptually executed once per row of the outer query, with the outer row's values substituted into the subquery. (We'll explore correlated subqueries in depth in Module 6.)

execution_model_comparison.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- NON-CORRELATED SUBQUERY
-- The subquery doesn't reference the outer query at all
-- Conceptually executed ONCE
 
SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees  -- No reference to outer query
);
-- Execution: 
--   1. Compute AVG(salary) from all employees → 65000
--   2. Scan employees, return rows where salary > 65000
 
 
-- CORRELATED SUBQUERY  
-- The subquery references the outer query's table
-- Conceptually executed ONCE PER OUTER ROW
 
SELECT e.employee_name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary) 
    FROM employees 
    WHERE department_id = e.department_id  -- References outer query!
);
-- Conceptual Execution (not necessarily actual):
--   For each employee row e:
--     1. Compute AVG(salary) for e's department
--     2. Compare e.salary against that average
--     3. Include row if comparison is true

Conceptual vs. Actual Execution

The 'once per row' execution model for correlated subqueries is conceptual. Modern query optimizers often transform correlated subqueries into joins or use semi-join optimizations, making actual execution much more efficient than naive row-by-row processing. However, understanding the conceptual model helps you reason about correctness and predict when optimization might struggle.

The Evaluation Order:

Logically, SQL evaluates queries in this order:

FROM clause — Determine the source tables and their joins
WHERE clause — Filter rows (subqueries in WHERE evaluated here)
GROUP BY — Group remaining rows
HAVING — Filter groups (subqueries in HAVING evaluated here)
SELECT — Compute output columns (subqueries in SELECT evaluated here)
ORDER BY — Sort the results

Subqueries can appear at multiple stages, and their evaluation fits into this logical order.

Subquery Return Types

Not all subqueries are created equal. What a subquery returns determines where and how it can be used. This is a critical concept that affects SQL syntax and semantics.

There are three fundamental categories based on the result cardinality:

Scalar Subquery — Returns exactly ONE value (one row, one column)
Row Subquery — Returns exactly ONE row with multiple columns
Table Subquery — Returns multiple rows (possibly zero) with one or more columns

Each type has different use cases and syntactic requirements.

Subquery Return Types and Their Usage
Return Type	Result Shape	Common Usage	Operators
Scalar	1 row × 1 column	Comparison, SELECT list, SET clause	=, >, <, >=, <=, <>
Row	1 row × N columns	Row comparison, tuple matching	= (row), IN (row list)
Table	M rows × N columns	IN, EXISTS, ANY/ALL, FROM clause	IN, EXISTS, ANY, ALL

subquery_return_types.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- SCALAR SUBQUERY: Returns one value
-- Can be used anywhere a single value is expected
 
SELECT employee_name, 
       salary,
       (SELECT AVG(salary) FROM employees) AS company_avg  -- Scalar in SELECT
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);        -- Scalar in WHERE
 
 
-- ROW SUBQUERY: Returns one row with multiple columns
-- Used for tuple comparisons
 
SELECT * FROM employees
WHERE (department_id, job_id) = (
    SELECT department_id, job_id 
    FROM employees 
    WHERE employee_id = 101
);
 
 
-- TABLE SUBQUERY: Returns multiple rows
-- Used with set operators like IN, EXISTS, ANY, ALL
 
SELECT employee_name FROM employees
WHERE department_id IN (
    SELECT department_id 
    FROM departments 
    WHERE location = 'New York'
);

Runtime Errors from Cardinality Violations

Using a subquery that returns multiple rows where a scalar is expected causes a runtime error: 'Subquery returned more than 1 row'. This is one of the most common subquery bugs. Always verify your subquery's cardinality matches its usage context. Use LIMIT 1, MAX/MIN, or ensure your WHERE clause guarantees single-row results.

Why Use Subqueries?

Subqueries aren't just syntactic sugar—they solve problems that are difficult or impossible to express with only joins and simple filters. Understanding their unique strengths helps you choose the right tool for each situation.

Unique Capabilities of Subqueries

•Aggregation in Filters — Compare individual rows against aggregated values (averages, counts, maximums) computed from the same or related tables.
•Existence Checks — Efficiently test whether related data exists without actually retrieving it (EXISTS subqueries).
•Derived Tables — Create intermediate result sets that can be joined or filtered, enabling complex multi-step transformations.
•Row-Level Computations — Add computed columns from related data to each output row without changing the main result structure.
•Set Comparisons — Check if a value belongs to a dynamically computed set of values (IN subqueries).
•Modular Query Design — Break complex logic into understandable, testable pieces that compose into the final query.

Subqueries vs. Joins — The Key Distinction:

Joins combine data from multiple tables horizontally, potentially changing the row count through one-to-many relationships. Subqueries use data from other queries to filter, compute, or provide values—often without changing the fundamental structure of the outer query's result.

Consider finding employees in departments with more than 10 people:

With Join:

SELECT DISTINCT e.employee_name
FROM employees e
JOIN (
    SELECT department_id, COUNT(*) as cnt 
    FROM employees 
    GROUP BY department_id 
    HAVING COUNT(*) > 10
) d ON e.department_id = d.department_id;

With Subquery:

SELECT employee_name
FROM employees
WHERE department_id IN (
    SELECT department_id 
    FROM employees 
    GROUP BY department_id 
    HAVING COUNT(*) > 10
);

Both work, but the subquery version is often cleaner when you don't need data from the inner query—just its filtering effect.

Subquery Syntax and Encapsulation

Every subquery must be enclosed in parentheses. This is non-negotiable syntax in SQL—the parentheses delimit where the subquery begins and ends, allowing the parser to distinguish nested structure.

Encapsulation Rules:

The entire subquery SELECT statement goes inside parentheses
The subquery can have its own clauses: FROM, WHERE, GROUP BY, HAVING, ORDER BY
ORDER BY in a subquery is typically ignored unless combined with LIMIT/FETCH (results are sets, which are unordered)
Column aliases in the SELECT list are visible only within the subquery (unless it's a derived table with exposed columns)

subquery_syntax_rules.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- CORRECT: Subquery properly enclosed in parentheses
SELECT * FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE name = 'Sales');
 
 
-- CORRECT: Subquery with its own clauses
SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = 20
    -- ORDER BY would be ignored here (no LIMIT)
);
 
 
-- CORRECT: Derived table subquery in FROM clause (requires alias)
SELECT dept_stats.department_id, dept_stats.avg_salary
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_stats                             -- Alias required for derived tables
WHERE dept_stats.avg_salary > 50000;
 
 
-- ERROR: Missing parentheses
-- SELECT * FROM employees WHERE salary > SELECT AVG(salary) FROM employees;
 
-- ERROR: Missing alias for derived table
-- SELECT * FROM (SELECT department_id, COUNT(*) FROM employees GROUP BY department_id);

Derived Table Naming

When a subquery appears in the FROM clause (creating a 'derived table' or 'inline view'), most databases require you to give it an alias. This alias becomes the table name for referencing columns in the outer query. This requirement varies slightly by database—MySQL requires it, PostgreSQL requires it, but Oracle allows omitting it in some contexts.

Subquery vs. Join: Performance Considerations

A common question is whether subqueries are slower than joins. The answer is nuanced and depends heavily on the query structure and database optimizer.

Modern Query Optimization:

Sophisticated database engines (PostgreSQL, MySQL 8+, SQL Server, Oracle) often transform subqueries into equivalent joins during query optimization. This means the same execution plan may result from either syntax. The optimizer chooses based on statistics and cost estimation, not on whether you wrote a subquery or a join.

When Subqueries Can Be Slower:

Correlated subqueries without optimization — If the optimizer can't flatten a correlated subquery, it may execute row-by-row
Complex nesting — Deeply nested subqueries may confuse the optimizer's cost model
Missing indexes — Subqueries that scan large tables without index support

Subquery vs. Join Performance Patterns
Scenario	Better Approach	Reason
Check existence only	EXISTS subquery	EXISTS stops at first match; JOIN retrieves all matches
Need data from both tables	JOIN	Subquery would require duplicating logic or multiple queries
Filter by aggregation	Either (optimizer equivalent)	Modern optimizers transform to same plan
Self-referential comparison	Correlated subquery or JOIN	Depends on data distribution and indexes
Complex derived calculations	Subquery (clarity)	Performance similar; subquery more readable

The Real-World Approach

Write the query in whichever form is clearest and most maintainable. Then examine the execution plan. If performance is inadequate, try alternative formulations. Don't prematurely optimize for perceived subquery overhead—profile first, then optimize based on evidence.

Common Subquery Patterns

Certain subquery patterns appear repeatedly across different domains. Recognizing these patterns accelerates query design and helps you choose the right approach.

Pattern 1: Comparison Against Aggregate

The most common pattern—compare each row against a summarized value from the same or related table.

pattern_comparison_aggregate.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Find products priced above the category average
SELECT product_name, price, category_id
FROM products p
WHERE price > (
    SELECT AVG(price)
    FROM products
    WHERE category_id = p.category_id  -- Correlated to outer query
);
 
-- Find orders larger than the customer's average order
SELECT order_id, customer_id, total_amount
FROM orders o
WHERE total_amount > (
    SELECT AVG(total_amount)
    FROM orders
    WHERE customer_id = o.customer_id
);

Pattern 2: Membership Check (IN Subquery)

Check if a value belongs to a dynamically computed set of values.

pattern_membership_check.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Find customers who have placed orders in the last month
SELECT customer_name, email
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
);
 
-- Find products that have never been ordered
SELECT product_name
FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id
    FROM order_items
    WHERE product_id IS NOT NULL  -- Important! NULL in NOT IN causes issues
);

Pattern 3: Maximum/Minimum Row Selection

Retrieve the full row containing the maximum or minimum value.

pattern_max_min_row.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
-- Find the employee(s) with the highest salary
SELECT employee_name, salary, department_id
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
 
-- Find the most recent order for each customer
SELECT o.*
FROM orders o
WHERE o.order_date = (
    SELECT MAX(o2.order_date)
    FROM orders o2
    WHERE o2.customer_id = o.customer_id
);

NOT IN and NULL Trap

If the subquery in NOT IN returns any NULL values, the entire NOT IN condition evaluates to UNKNOWN (effectively false for all rows), returning no results. Always filter out NULLs in NOT IN subqueries or use NOT EXISTS instead, which handles NULLs correctly.

Summary: Subquery Fundamentals

We've established the conceptual foundation for understanding subqueries. Let's consolidate the key points:

Key Takeaways

•Subqueries are nested queries — Complete SELECT statements embedded in another SQL statement, enclosed in parentheses.
•Query composition — Subqueries enable building complex queries from simpler building blocks, executed atomically by the database.
•Two execution models — Non-correlated subqueries execute once; correlated subqueries conceptually execute per outer row (though optimizers may transform them).
•Three return types — Scalar (one value), row (one row, multiple columns), and table (multiple rows) subqueries have different usage contexts.
•Unique problem-solving capabilities — Subqueries excel at aggregation-based filtering, existence checks, and derived table creation.
•Performance is context-dependent — Modern optimizers often transform subqueries to joins; write for clarity first, then optimize based on execution plans.

What's Next:

With the conceptual foundation in place, we'll dive deep into each subquery type. The next page explores scalar subqueries—queries that return exactly one value and can be used anywhere a single value is expected.

Page Complete

You now understand what subqueries are, how they execute, and why they're essential to SQL's expressive power. This conceptual foundation prepares you for mastering the specific subquery types covered in the following pages.