Multiple Subqueries in WHERE
You'll learn how to combine multiple independent subqueries in a single WHERE clause to create sophisticated, multi-condition filters.
Why Use Multiple Subqueries?
Sometimes one filtering condition isn't enough. Just as you've combined simple conditions with AND/OR operators, you can combine entire subqueries to answer complex questions like: "Show me products that are above average price AND are in categories with more than 50 items."
Each subquery operates independently, returning its own result, and the logical operators (AND, OR, NOT) determine how these results work together.
The Core Pattern
You write each subquery as a separate filtering condition, then link them with logical operators:
SELECT product_name, price, category_id
FROM products
WHERE price > (SELECT AVG(price) FROM products)
AND category_id IN (SELECT category_id
FROM products
GROUP BY category_id
HAVING COUNT(*) > 50);
Here, the first subquery finds the average price, the second identifies high-volume categories, and AND ensures both conditions must be true.
Combining with OR and NOT
You can create even more nuanced logic:
WHERE price > (SELECT MAX(price) FROM discounted_products)
OR supplier_id IN (SELECT supplier_id FROM premium_suppliers)
This finds products that are either priced above all discounted items or come from premium suppliers.
Think of It Like Security Clearance
Imagine a building requiring multiple independent checks: your badge must be valid (first subquery) AND your department must be authorized for this floor (second subquery). Each check queries a different system, but both must pass.
Key Takeaway: Multiple subqueries in WHERE let you combine independent, complex conditions using AND, OR, and NOT—each subquery answers a separate question, and logical operators unite them into one powerful filter.