Subqueries in WHERE for Dynamic Filtering

What is a Subquery and Why Do We Need It?

A subquery, also known as an inner query, is simply a SELECT statement nested inside another SQL statement. Its purpose is to provide data—often a single value or a list of values—that the outer query can then use.

The main reason for using a subquery is that the filtering criteria for your main query depends on the result of another query.

This approach allows you to break down a complex problem into smaller, more logical steps, which aligns with the structured thinking you'd use in programming. Instead of one monolithic, hard-to-read query, you construct a main query that relies on a simpler, helper query for a specific piece of information.

Constructing a Subquery in the WHERE Clause

Let's stick with the classic example: "Find all products that have a price greater than the average price of all products."

Logically, you need to perform two steps:

1. Calculate the average price of all products.
2. Find all products where the price is greater than the value from Step 1.

You could do this by running two separate queries.
First, to find the average:

SELECT AVG(price) FROM product;
-- Let's say this returns 25.00

Then, you would manually plug that value into your main query:

SELECT product_name, price 
FROM product 
WHERE price > 25.00;

This works, but it's inefficient and static. If the prices change, you have to run the first query again and update the second one. A subquery lets you combine these two steps into a single, dynamic query.

The video below walks through this exact process, showing how to embed the first query directly into the WHERE clause of the second.

The final query looks like this:

SELECT product_name, price
FROM product
WHERE price > (SELECT AVG(price) FROM product);

Here, (SELECT AVG(price) FROM product) is the subquery. The database executes it first, it returns a single value (the average price), and the outer query then uses this value to filter the products.

Let's reinforce this with a text-based example that follows the same logic.

How it Works: The Execution Flow

Your interest in low-level systems makes this next part particularly relevant. How does the database engine actually handle a query with a subquery? For the type of subquery we're discussing today (a non-correlated subquery), the process is very straightforward and logical.

1. The subquery (inner query) executes first, and only once. The database runs SELECT AVG(price) FROM product to get a single result.
2. The result of the subquery is substituted into the outer query. The database essentially treats the outer query as if you had typed the result yourself: WHERE price > 25.00.
3. The main query (outer query) executes. Now with a simple condition, the database scans the product table to find the matching rows.

This modular execution is efficient. The subquery's result is held in memory temporarily while the main query runs.

The following video provides an excellent visual metaphor for this process.

Key Rules and Constraints

When writing subqueries in the WHERE clause with comparison operators (=, >, <, etc.), there is a critical rule you must follow.

The subquery must return a single value.

This is known as a scalar subquery—it returns a result set of exactly one row and one column. Our AVG() example works perfectly because aggregate functions like AVG(), SUM(), MAX(), etc. (without a GROUP BY) are designed to return a single value.

If your subquery were to return multiple values, for example (SELECT price FROM product WHERE category = 'Electronics'), the database would raise an error because it doesn't know how to compare a single price to a list of prices using the > operator. price > (100, 150, 200) is logically invalid.

Here are the essential guidelines to remember:

• A subquery must be enclosed in parentheses ().
• When used with a standard comparison operator (=, !=, >, <), it must be a scalar subquery (return a single value).
• The subquery is placed on the right side of the comparison operator.

SELECT ticket_id, response_time_hours
FROM support_tickets
WHERE response_time_hours < (SELECT AVG(response_time_hours) FROM support_tickets);

Conclusion

Today you've learned how to make your queries significantly more dynamic and powerful. By nesting one query inside another, you can filter your data based on conditions that are calculated on the fly.

Key Takeaways:

• A subquery is a SELECT statement nested inside another query.
• They are used when your WHERE clause depends on a value you must first calculate from your data.
• For non-correlated subqueries, the inner query runs first, and its result is used by the outer query.
• When using comparison operators like >, <, or =, the subquery must be a scalar subquery, meaning it must return exactly one row and one column.

Preview of the Next Lesson:
In this lesson, our subquery was restricted to returning a single value. But what if you need to compare against a list of values? For instance, "find all orders placed by customers who live in Germany." Your subquery would need to return a list of all customer IDs from Germany. This is not a scalar subquery. In the next lesson, we will learn how to handle this by using special operators like IN and NOT IN with our subqueries.