Subquery in WHERE
intermediatesubqueries
2 min read
What is a Subquery in WHERE?
A subquery in the WHERE clause is a nested SELECT that provides values for the outer query's filter condition. The inner query runs first (or is correlated), and its result is used to filter the outer query.
Syntax
SELECT * FROM table_a
WHERE column IN (SELECT column FROM table_b WHERE condition);SELECT * FROM table_a
WHERE column = (SELECT MAX(column) FROM table_b);When to Use
- Filtering based on aggregated values from another table
- Finding rows related to a computed result
- When a JOIN would be more complex or less readable
Key Points
- IN Subquery — Returns rows where the column matches any value from the subquery.
- Scalar Subquery — Must return exactly one row and one column for = comparisons.
- EXISTS — Tests whether the subquery returns any rows at all.
- Performance — PostgreSQL often rewrites IN subqueries as joins internally.
- Correlated — A correlated subquery references the outer query and runs once per outer row.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
Subquery in WHERE Challenge
Write a query that find students enrolled in course 1.
Expected result
Students whose ID appears in enrollments for course 1.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is a Subquery in WHERE?
Initializing database...Each run starts from fresh sample data.
More Examples
Student with the highest age
Find the oldest student using a scalar subquery.
Initializing database...Each run starts from fresh sample data.
Products more expensive than average
Find products priced above the average.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Is a subquery in WHERE slower than a JOIN?
Not necessarily. PostgreSQL's optimizer often converts IN subqueries to joins. Use EXPLAIN ANALYZE to compare.
Can a WHERE subquery return multiple columns?
For IN, the subquery must return one column. For EXISTS, the subquery can select anything (SELECT 1 is conventional).