Understanding SQL Subqueries: Filtering and Looking Up Data with Nested SELECT Statements

Imagine you're a sales analyst and your manager walks over with a question: "Which of our customers have placed orders above our average order value?" Simple enough question. But when you sit down to write the query, you hit a wall. To filter for customers above the average, you first need to know the average — and that requires its own query. You'd normally run two separate queries: one to find the average, then paste that number into a second query. It works, but it's clunky, manual, and breaks the moment the data changes.

SQL subqueries solve this problem elegantly. A subquery (also called a nested query or inner query) is a SELECT statement written inside another SELECT statement. The outer query uses the result of the inner query — dynamically, at runtime, every time you execute it. No copy-pasting numbers. No two-step process. One clean, self-contained SQL statement that asks and answers both questions at once.

By the end of this lesson, you'll be writing subqueries with confidence. You'll understand how they work under the hood, where to place them, and when to use them instead of other SQL techniques.

What you'll learn:

• What a subquery is and how SQL executes it
• How to use subqueries in the WHERE clause to filter rows dynamically
• How to use subqueries in the SELECT clause to look up calculated values
• The difference between scalar subqueries, multi-row subqueries, and correlated subqueries
• When to use IN, EXISTS, and comparison operators with subqueries

---

Prerequisites

This lesson assumes you're comfortable writing basic SELECT queries with WHERE clauses. If you're new to SQL or need a refresher, start with SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries before continuing here. Some examples also reference aggregate functions like AVG() and COUNT() — if those feel unfamiliar, Grouping and Summarizing Data: COUNT, SUM, AVG, and GROUP BY for Beginners will get you up to speed quickly.

---

The Sample Dataset

Throughout this lesson we'll work with a simple e-commerce database. Here are the three tables we'll use:

customers

customer_id	name	city
1	Alicia Marsh	Chicago
2	Ben Okoro	Houston
3	Clara Reyes	Chicago
4	David Kim	New York

orders

order_id	customer_id	order_total	order_date
101	1	320.00	2024-01-15
102	2	85.00	2024-01-18
103	1	540.00	2024-02-02
104	3	210.00	2024-02-14
105	4	430.00	2024-03-01
106	2	95.00	2024-03-10

products

product_id	product_name	category	unit_price
1	Laptop Stand	Tech	49.99
2	Desk Lamp	Home	29.99
3	USB Hub	Tech	34.99
4	Notebook Set	Office	12.99

---

How a Subquery Works: The Mental Model

Before writing any code, let's build the right mental picture. When SQL encounters a subquery, it executes the inner query first, takes the result, and then uses that result to execute the outer query. Think of it like a set of parentheses in arithmetic: (3 + 4) * 2 — the expression inside the parentheses resolves first, then the outer operation uses that result.

Here's that idea in SQL form:

SELECT name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_total > 300
);

SQL runs the inner query first:

SELECT customer_id FROM orders WHERE order_total > 300;
-- Returns: 1, 1, 4  (orders 101, 103, 105)

Then the outer query effectively becomes:

SELECT name FROM customers WHERE customer_id IN (1, 4);
-- Returns: Alicia Marsh, David Kim

The database handles this translation invisibly and instantly. You write one statement; it does the two-step work behind the scenes.

---

Scalar Subqueries: Getting a Single Value

The simplest type of subquery returns exactly one value — a single row, single column result. This is called a scalar subquery, because it produces a scalar (a single number or string) rather than a list or table.

Scalar subqueries are most often used with comparison operators like =, >, <, or >=. Let's return to the opening problem: finding customers whose orders are above the average order value.

SELECT customer_id, order_total
FROM orders
WHERE order_total > (
    SELECT AVG(order_total)
    FROM orders
);

The inner query calculates the average: (320 + 85 + 540 + 210 + 430 + 95) / 6 = 280. The outer query then filters for orders above 280.

Result:

customer_id	order_total
1	320.00
1	540.00
4	430.00

Notice that you didn't need to know the average in advance, and if new orders are added to the table tomorrow, the query will recalculate automatically.

You can also use scalar subqueries inside the SELECT clause itself. This is a powerful pattern for adding calculated context to every row:

SELECT
    order_id,
    order_total,
    (SELECT AVG(order_total) FROM orders) AS avg_order_total,
    order_total - (SELECT AVG(order_total) FROM orders) AS difference_from_avg
FROM orders;

This adds two columns to your result: the overall average, and how far each order deviates from it. It's a quick way to spot outliers without restructuring your whole query.

---

Multi-Row Subqueries with IN

When a subquery returns multiple rows (but still one column), you use the IN operator to check whether a value matches any row in that list. You saw a preview of this earlier. Let's look at a fuller example.

Suppose you want to find all customers who have placed at least one order:

SELECT name, city
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id
    FROM orders
);

Result:

name	city
Alicia Marsh	Chicago
Ben Okoro	Houston
Clara Reyes	Chicago
David Kim	New York

In this case all four customers happen to have orders, but the technique scales: if you had 10,000 customers and only 3,000 had ordered, this query would filter correctly.

You can also flip the logic with NOT IN to find customers who have never placed an order:

SELECT name, city
FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id
    FROM orders
);

Using IN with a Different Table

IN becomes especially powerful when the inner and outer queries touch different tables. Let's say you want to find all orders that include products from the "Tech" category. Assuming you have an order_items table linking orders to products:

SELECT order_id, order_total
FROM orders
WHERE order_id IN (
    SELECT DISTINCT order_id
    FROM order_items
    WHERE product_id IN (
        SELECT product_id
        FROM products
        WHERE category = 'Tech'
    )
);

Notice the nesting: a subquery inside a subquery. SQL evaluates from the innermost query outward. This works, though as queries get deeper, Common Table Expressions (CTEs) can make the logic much easier to read.

---

Correlated Subqueries: When the Inner Query Needs the Outer Row

So far, every subquery we've written is independent — the inner query doesn't need any information from the outer query. It runs once, produces a result, and hands it to the outer query.

A correlated subquery is different. It references a column from the outer query, which means it runs once per row that the outer query processes. This makes correlated subqueries more powerful, but also more expensive on large datasets.

Here's a practical example: find each customer's most recent order date.

SELECT
    c.name,
    (
        SELECT MAX(o.order_date)
        FROM orders o
        WHERE o.customer_id = c.customer_id
    ) AS last_order_date
FROM customers c;

Result:

name	last_order_date
Alicia Marsh	2024-02-02
Ben Okoro	2024-03-10
Clara Reyes	2024-02-14
David Kim	2024-03-01

The subquery here references c.customer_id — a column from the outer customers table. For each customer row the outer query processes, the subquery runs with that specific customer_id plugged in. The result is a personalized lookup for every single row.

---

EXISTS: A Smarter Way to Check for Matches

The EXISTS operator pairs naturally with correlated subqueries. Instead of asking "what values does the subquery return?", EXISTS simply asks "does the subquery return any rows at all?" It's a true/false check.

Let's find all customers who have placed at least one order over $400:

SELECT name, city
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.order_total > 400
);

Result:

name	city
Alicia Marsh	Chicago
David Kim	New York

A few things worth noting here:

• The SELECT 1 inside the subquery is intentional. Since EXISTS only cares whether any rows come back, the actual columns don't matter. Writing SELECT 1 is a convention that signals to the reader (and sometimes to the database optimizer) that we're just checking for existence.
• This query is a correlated subquery — the inner query references c.customer_id from the outer query.
• NOT EXISTS works as the logical inverse, identical to the NOT IN pattern but without the NULL trap.

---

Subqueries in the FROM Clause: Derived Tables

There's one more placement for subqueries that beginners often overlook: the FROM clause. When you put a subquery in the FROM clause, the result acts like a temporary table — sometimes called a derived table or inline view.

Let's say you want to find customers whose total spending across all orders exceeds $500. You can't filter on an aggregate directly in the WHERE clause, but you can build a derived table first:

SELECT c.name, customer_totals.total_spent
FROM customers c
JOIN (
    SELECT customer_id, SUM(order_total) AS total_spent
    FROM orders
    GROUP BY customer_id
) AS customer_totals ON c.customer_id = customer_totals.customer_id
WHERE customer_totals.total_spent > 500;

Result:

name	total_spent
Alicia Marsh	860.00

The inner query produces a summary of total spending per customer. The outer query joins that summary back to the customers table to get names, then filters for those over $500. This is a very common pattern in analytics reporting, and it pairs naturally with the SQL JOINs Explained with Real-World Examples techniques you may already know.

---

Subqueries vs. JOINs: When to Use Which

A common question at this stage: "Can't I just use a JOIN instead?" Often, yes. The IN subquery from earlier could be rewritten as a JOIN:

-- Subquery approach
SELECT name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_total > 300);

-- JOIN approach
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_total > 300;

Both return the same result. So which should you use?

• Use a subquery when you're filtering based on existence or a calculated value, and you don't need columns from the inner table in your final output. The intent is cleaner and easier to read.
• Use a JOIN when you need columns from multiple tables in your SELECT clause, or when you're working with large datasets where join optimization is better understood by your database's query planner.
• Use EXISTS instead of IN when working with large inner result sets or tables with potential NULL values.

Neither approach is universally better — context matters. As you advance, tools like Advanced Filtering and Sorting will help you evaluate which pattern performs best for your specific situation.

---

Hands-On Exercise

Work through these exercises using the sample dataset from this lesson. Write each query before checking the explanation.

Exercise 1 — Scalar Subquery: Write a query that returns all orders where the order_total is greater than the maximum order total placed by customer 2 (Ben Okoro).

SELECT order_id, customer_id, order_total
FROM orders
WHERE order_total > (
    SELECT MAX(order_total)
    FROM orders
    WHERE customer_id = 2
);

The inner query finds Ben's highest order: $95. The outer query returns all orders above $95.

Exercise 2 — IN with Subquery: Write a query that returns the names of all customers who live in the same city as customer 1 (Alicia Marsh), but excludes Alicia herself.

SELECT name
FROM customers
WHERE city = (
    SELECT city
    FROM customers
    WHERE customer_id = 1
)
AND customer_id != 1;

The inner query returns "Chicago". The outer query finds other Chicago customers: Clara Reyes.

Exercise 3 — Correlated Subquery: Write a query that lists each order alongside the customer's name, using a correlated subquery in the SELECT clause instead of a JOIN.

SELECT
    o.order_id,
    o.order_total,
    (
        SELECT c.name
        FROM customers c
        WHERE c.customer_id = o.customer_id
    ) AS customer_name
FROM orders o;

Exercise 4 — EXISTS: Write a query that returns only those customers who have never placed an order.

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

---

Common Mistakes & Troubleshooting

"Subquery returns more than one row" You used a scalar subquery (with =, >, etc.) but your inner query returned multiple rows. Fix it by adding an aggregate function like MAX() or MIN(), or switch to IN if multiple values are expected.

"Every derived table must have its own alias" You forgot to give a name to a subquery in the FROM clause. Add AS some_name immediately after the closing parenthesis.

NOT IN returning zero rows unexpectedly Your inner query likely returns at least one NULL. Add WHERE column_name IS NOT NULL to the inner query, or rewrite using NOT EXISTS.

Referencing the wrong table in a correlated subquery Without aliases, SQL may resolve column names ambiguously. Always alias your tables and use the aliases consistently when writing correlated subqueries.

Subquery runs slowly on large data A correlated subquery runs once per outer row, which can be thousands or millions of executions. Consider rewriting as a JOIN with a derived table, or explore Common Table Expressions (CTEs) for Cleaner SQL as a readable alternative. For deeper performance analysis, Subqueries and Correlated Subqueries: Writing Queries Within Queries covers optimization strategies in detail.

---

Summary & Next Steps

Subqueries are one of those SQL features that unlock a whole new level of expressiveness. Once you internalize the pattern — write the inner question first, then wrap it with the outer question — complex analytical problems start to feel approachable.

Here's what you've covered:

• Scalar subqueries return a single value and work with =, >, < operators
• Multi-row subqueries return a list of values and work with IN and NOT IN
• Correlated subqueries reference the outer query and run once per row — powerful but performance-sensitive
• EXISTS and NOT EXISTS check for presence without caring about the actual values returned
• Derived tables place a subquery in the FROM clause to create a temporary, queryable result set

From here, a few natural next steps:

• Explore Common Table Expressions (CTEs) for Cleaner SQL — CTEs let you name and reuse subquery logic, making complex queries dramatically easier to read and maintain
• Dive into Master SQL Aggregate Functions: GROUP BY, HAVING, COUNT, SUM, AVG to combine aggregation with subquery filtering for powerful reporting queries
• When you're ready for advanced patterns, Advanced Subqueries and CTEs: Mastering Complex SQL Query Architecture covers window-based subqueries, nested CTEs, and performance tuning strategies

The goal is always the same: write one coherent SQL statement that asks the full question, and let the database do the multi-step work for you.