Subqueries

By the end of this lesson, you will be able to write SQL subqueries to filter data based on results from another query, enabling complex analysis without multiple round-trips to the database.

What it is

A subquery (also known as an inner query or nested query) is a SELECT statement embedded within another SQL statement. It allows you to use the result of one query as input for another. Subqueries can appear in the WHERE, HAVING, FROM, or SELECT clauses. The mental model is "ask a question inside a question": first determine a value or set of values, then use that result to filter or calculate further. Related terms include correlated subqueries (which reference columns from the outer query) and derived tables (subqueries used in the FROM clause).

Why it matters

• Dynamic Filtering: You can filter rows based on calculated aggregates (e.g., "find employees earning above the average salary") without hardcoding numbers.
• Complex Logic: Break down multi-step analytical problems into manageable, readable chunks within a single execution plan.
• Data Integrity: Ensure filters are always up-to-date with current data states, unlike static values which become stale.
• Efficiency: Reduce network overhead by performing multiple logical steps in one database call rather than fetching intermediate results to application code.

Syntax or steps

The most common pattern is a scalar subquery in the WHERE clause. The structure follows:

1. Write the outer query selecting your target columns.
2. Add a WHERE condition using an operator like =, >, or IN.
3. Embed the inner SELECT statement in parentheses immediately after the operator.
4. Ensure the inner query returns a single value (for scalar comparisons) or a list of values (for IN).

Example

-- Find all products priced higher than the average price of all products
SELECT product_name, price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

Part-by-part explanation: 1. SELECT product_name, price FROM products: This is the outer query retrieving specific columns from the main table. 2. WHERE price >: We filter rows where the individual product's price is greater than a certain threshold. 3. (SELECT AVG(price) FROM products): This is the subquery. It calculates the average price across the entire products table. The database executes this first, obtains a single number (e.g., 50.00), and substitutes it into the outer query's comparison.

Common mistakes

• Mismatched Return Types: Using = with a subquery that returns multiple rows causes an error. Use IN instead if the subquery returns a list.
• Missing Parentheses: Subqueries must always be enclosed in parentheses. Forgetting them leads to syntax errors.
• Performance Issues: Correlated subqueries (those referencing the outer query) can execute once per row, causing significant slowdowns on large datasets. Consider using JOINs for better performance in these cases.
• NULL Handling: If a subquery returns NULL, comparisons like = NULL evaluate to unknown/false. Use IS NULL explicitly if needed.

When to use it

Subqueries are ideal when the logic is hierarchical or when you need to compare against an aggregate. Joins are often preferred for combining data from different tables.

Feature	Subquery	Join
Best For	Filtering based on aggregates or existence checks	Combining columns from related tables
Readability	Clear for simple nested logic	Clear for relational mapping
Performance	Can be slower if correlated	Generally optimized well by engines

Practice

Guided Exercise: Write a query to find all customers who have placed orders with a total amount greater than the average order total. Hint: Use a subquery in the WHERE clause comparing order_total to (SELECT AVG(order_total) FROM orders). Challenge: Modify the previous query to only consider orders placed in the last 30 days. Solution Hint: Add a date filter (WHERE order_date >= CURRENT_DATE - INTERVAL '30' DAY) inside the subquery to ensure the average reflects recent trends.

Quick check

Question: What happens if a subquery used with the = operator returns more than one row? Answer: The database throws an error because the equality operator expects a single scalar value. You should use IN or ANY/ALL instead.

Summary

Subqueries allow you to nest queries to create dynamic, data-driven filters and calculations. They are essential for answering questions that depend on aggregated statistics or sets of values, though they should be used judiciously compared to joins for performance reasons.