Course contentsShow
← Computer Science
Lesson 1603 of 2,87236. Database FundamentalsFree lesson

Subqueries in WHERE Clause

Filtering rows using subqueries with comparison operators and understanding subquery evaluation order.

Subqueries in WHERE Clause

What you'll learn: How to use subqueries inside the WHERE clause to filter rows based on results from another query.


The Core Idea

A subquery in the WHERE clause lets you filter rows by comparing them against values computed from a completely separate query. Instead of hard-coding a specific value to filter by, you derive that value (or set of values) dynamically from the database itself.

Think of it like asking a two-part question: "Show me all employees who earn more than the average salary." You can't answer this without first computing the average—that's what the subquery does behind the scenes.

How It Works

The subquery executes first, producing a result that the outer query then uses for comparison. The evaluation order is inside-out: inner query runs, produces a value (or values), then the outer query uses that result to filter rows.

Basic Pattern

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Here, the subquery (SELECT AVG(salary) FROM employees) runs first, returning a single number. Then the outer query compares each employee's salary against that average.

Using IN for Multiple Values

SELECT product_name
FROM products
WHERE category_id IN (SELECT category_id FROM categories WHERE active = 1);

The subquery returns a list of category IDs, and IN checks if each product's category appears in that list.

Comparison Operators

You can use standard operators: =, >, <, >=, <=, <> with scalar (single-value) subqueries, and IN or NOT IN with subqueries returning multiple rows.

Key Takeaway: Subqueries in the WHERE clause filter rows by comparing them against dynamically computed values, with the subquery always evaluating first before the outer query uses its results.