Subquery in SQL: Single-Row, Correlated, and Nested Queries (with Examples)

Writing a subquery in sql is one of the most powerful techniques in relational database programming. When business stakeholders ask questions that require multiple steps of aggregation—such as "Which customers placed orders larger than the average order value?" or "Which employees earn more than the median salary of their department?"—a single flat query is insufficient. Subqueries allow you to calculate intermediate metrics on the fly and immediately feed them into outer filtering or projection logic.

However, subqueries can quickly become a performance bottleneck if constructed carelessly. Nested subqueries that run once per row (correlated subqueries) can degrade query speed from milliseconds to minutes across large tables, and subtle three-valued logic behaviors with NOT IN can return zero rows unexpectedly.

In this tutorial, you will learn the four clauses where a subquery in sql can be placed, contrast correlated versus non-correlated execution plans, master EXISTS vs IN, and discover when to refactor subqueries into Common Table Expressions (CTEs) or window functions.

For broader interview prep, explore our 30 SQL Interview Questions for Analysts and review our foundational SQL Cheat Sheet.

Subquery in SQL: 4 Execution Locations

A subquery can be placed inside four distinct clauses of a SQL statement: WHERE, FROM, SELECT, and HAVING.

1. Subquery in the WHERE Clause (Filtering with Aggregates)

The most common use case: filtering individual rows against a dynamic aggregate calculated from the dataset.

-- Find orders with an amount greater than the overall company average
SELECT 
    order_id, 
    customer_id, 
    amount
FROM orders
WHERE amount > (
    SELECT AVG(amount) 
    FROM orders
);

The database executes the inner query (SELECT AVG(amount) FROM orders) first, obtains a single scalar number (e.g. 245.50), and substitutes it into the outer WHERE amount > 245.50.

2. Subquery in the FROM Clause (Derived Tables)

When placed in FROM, the subquery acts as a temporary table created on the fly.

-- Calculate the average number of orders placed per customer
SELECT 
    AVG(order_count) AS avg_orders_per_customer
FROM (
    SELECT 
        customer_id, 
        COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) AS customer_summaries;

3. Subquery in the SELECT Clause (Scalar Projections)

Retrieves a single scalar value per row to display alongside detail columns.

SELECT 
    employee_name,
    salary,
    (SELECT AVG(salary) FROM employees) AS company_avg_salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

While functional, calculating repeated scalar subqueries in SELECT is often better written using window functions: AVG(salary) OVER ().

4. Subquery in the HAVING Clause

Filters grouped results against an aggregated subquery threshold.

SELECT 
    department,
    SUM(salary) AS dept_payroll
FROM employees
GROUP BY department
HAVING SUM(salary) > (
    SELECT AVG(dept_total)
    FROM (
        SELECT SUM(salary) AS dept_total
        FROM employees
        GROUP BY department
    ) AS totals
);

Types of Subqueries: Correlated vs Non-Correlated

Understanding the difference between correlated and non-correlated subqueries is a favorite topic in technical interviews.

-- Non-Correlated: Executes ONCE total
SELECT * FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);
 
-- Correlated: Executes ONCE PER CANDIDATE ROW
SELECT e.employee_id, e.name, e.salary, e.department
FROM employees e
WHERE e.salary > (
    SELECT AVG(sub.salary)
    FROM employees sub
    WHERE sub.department = e.department -- Reference to outer row!
);

The Mechanics of a Correlated Subquery

In the correlated example above, the inner query references e.department from the outer table.

1. The database picks row 1 of the outer table (say, Meera in Engineering).
2. It runs the inner query substituting Engineering to find the average engineering salary.
3. If Meera's salary is higher, row 1 is kept.
4. The engine advances to row 2 and repeats the entire calculation.

If the table contains 100,000 rows, an unindexed correlated subquery might execute 100,000 queries. In modern data warehouses, replacing correlated subqueries with window functions or pre-aggregated joins yields massive performance gains.

Subquery Operators: IN, ANY, ALL, and EXISTS

When an inner subquery returns multiple rows, simple scalar operators (=, >, <) will fail. You must use multi-row comparison operators:

IN vs EXISTS Example

-- Using IN:
SELECT customer_name 
FROM customers 
WHERE customer_id IN (
    SELECT DISTINCT customer_id 
    FROM orders 
    WHERE order_date >= '2026-01-01'
);
 
-- Using EXISTS (Faster on large datasets with indexes):
SELECT c.customer_name 
FROM customers c
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id 
      AND o.order_date >= '2026-01-01'
);

EXISTS does not need to return column data; standard convention is to write SELECT 1. The query engine halts scanning the orders table the moment it encounters the first matching order for customer c.

Common Pitfalls and How to Fix Them

1. Single-Row Subquery Returns Multiple Rows

-- FAILS with: ERROR: more than one row returned by a subquery used as an expression
SELECT * FROM products
WHERE category_id = (SELECT category_id FROM categories WHERE name LIKE 'A%');

If two categories start with 'A', = fails.

• Fix: Either change = to IN, or guarantee a single row with LIMIT 1.

2. The NOT IN with NULL Disaster

Consider this query to find customers who have never placed an order:

-- DANGEROUS!
SELECT customer_id, name 
FROM customers 
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders
);

If the orders table contains a single row where customer_id IS NULL, NOT IN evaluates to UNKNOWN for every single candidate row. The query returns 0 rows, even if hundreds of customers never ordered!

• Fix: Always use NOT EXISTS or filter NULLs:

SELECT c.customer_id, c.name 
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Subquery vs CTE vs Window Function

To explore how CTEs compare in practice, read our comprehensive SQL CTE Guide.

When Analysts Use Subqueries in Real Work

Identifying Inactive or Churned Accounts. Using NOT EXISTS to find users who registered 90 days ago but have zero recorded sessions or purchases in event logs.

Outlier Detection. Filtering out fraudulent credit card transactions whose value exceeds three standard deviations from the user's historical average:

WHERE amount > (SELECT AVG(amount) + 3 * STDDEV(amount) FROM transactions WHERE user_id = t.user_id)

Two-Stage Aggregations. Computing the maximum of departmental averages in workforce analytics reports.

To practice writing subqueries against real database schemas, solve interactive challenges on our SQL Practice Platform.

Subquery in SQL: Performance Benchmarking vs Joins and Window Functions

Database query optimizers handle subqueries differently depending on whether they are correlated or non-correlated:

1. Un-nesting and Decorrelation: Modern optimizers (PostgreSQL, MySQL 8+, Oracle) attempt to rewrite independent IN subqueries into semijoins or hash joins automatically.
2. Correlated Subquery Bottlenecks: A correlated subquery in the SELECT or WHERE clause that forces an $O(N \times M)$ nested-loop scan across 1,000,000 records will lock CPU threads. Rewriting the calculation into an inner LEFT JOIN on a pre-aggregated subquery or using window functions (ROW_NUMBER(), SUM() OVER (...)) reduces runtime from 45 seconds to 300 milliseconds.
3. Defensive NULL Handling with NOT IN: Never write WHERE id NOT IN (SELECT parent_id FROM t) if parent_id can contain a single NULL. Three-valued logic causes NOT IN (..., NULL) to evaluate to UNKNOWN, returning zero rows. Always use NOT EXISTS instead.

Discover more query patterns in our SQL Tutorials hub and practice on Topfolio Practice.

Related SQL Tutorials

• What Is SQL? The Complete Beginner to Pro Guide
• SQL Joins Explained with Practical Examples
• SQL Window Functions Guide
• SQL Cheat Sheet for Analysts
• Explore All Guides in the SQL Tutorials Hub