Introduction to Subqueries in WHERE
What you'll learn: How to use nested queries inside WHERE clauses to filter rows based on dynamic, calculated criteria.
What Is a Subquery?
A subquery (also called a "nested query" or "inner query") is a complete SELECT statement embedded inside another SQL query. In the WHERE clause specifically, subqueries let you filter rows based on results computed on-the-fly from another table or the same table.
Think of it like asking a two-step question: "Show me all employees whose salary is above the company average." First, you need to calculate the average (step 1), then use that result to filter employees (step 2). A subquery does both in one query.
Basic Syntax
SELECT column1, column2
FROM table_name
WHERE column_name operator (SELECT column FROM other_table WHERE condition);
The subquery (in parentheses) runs first and returns a value or set of values. The outer query then uses that result to filter rows.
Why Use Subqueries in WHERE?
- Dynamic filtering: Filter based on calculated values (like averages, maximums) rather than hardcoded numbers
- Compare across tables: Filter rows in one table based on criteria from another
- Self-contained logic: Keep related calculations within a single query instead of multiple separate queries
Simple Example Pattern
Instead of manually finding the average salary and then writing a second query, you can nest:
WHERE salary > (SELECT AVG(salary) FROM employees)
The subquery calculates the average; the outer query filters based on that result.
Key Takeaway: Subqueries in WHERE clauses let you filter rows dynamically using results from nested SELECT statements, enabling complex comparisons without hardcoding values.