Subquery in WHERE

Tying the Chapter Together

Every WHERE-clause pattern seen so far in this chapter — single-row comparisons (17.2), multi-row IN (17.3), correlated comparisons (17.4), EXISTS/NOT EXISTS (17.5/17.6), and ANY/ALL (17.7/17.8) — is really one big family: a subquery used inside a WHERE clause to filter outer rows. This topic is the "umbrella" view of that family, plus a composite example showing them working together.

Sample data used throughout this chapter:

employees

departments

The Filtering Toolkit, All in One Place

A Composite Example

Find employees who work in a department whose total budget is at least 200000, AND who earn more than that department's average salary:

sql
SELECT name, department, salary
FROM employees e
WHERE department IN (SELECT name FROM departments WHERE budget >= 200000)
  AND salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);

Departments with budget >= 200000: Engineering (300000), Sales (200000). Within those, above their own department average: Alice (Engineering, 95000 > 78333.33) and Eve (Sales, 88000 > 67666.67).

Result: Alice, Eve.

This single query stacks an uncorrelated multi-row IN subquery with a correlated single-row comparison subquery — both living in the same WHERE clause, combined with AND.

Choosing the Right Operator Is the Real Skill

The syntax of "put a subquery in WHERE" is trivial; the actual skill covered across 17.2–17.8 is picking the right comparison operator for the subquery's guaranteed row-count and the question being asked:

• Guaranteed one row? Plain =/>/< is fine, but never assume — verify it's truly unique.
• Could be many rows, and you want "any match"? IN or EXISTS.
• Could be many rows, and you want "beats the weakest" or "beats the strongest"? ANY / ALL.
• Care only about presence, not values, possibly with NULL risk? EXISTS / NOT EXISTS.

Edge Cases

• Multiple WHERE-clause subqueries can be combined with AND/OR, each independently correlated or not, as shown above.
• A WHERE-clause subquery can itself contain a nested subquery (in its own WHERE or FROM), to any depth the engine supports — readability suffers fast beyond 2–3 levels.
• Precedence matters: parenthesize combinations of subqueries and AND/OR/NOT carefully, exactly as with any other boolean expression.

Key Takeaways / Q&A

Q: Are IN, ANY, ALL, EXISTS, and plain comparison operators competitors, or a toolkit? A: A toolkit — each is right for a different guarantee about the subquery's row count and a different question being asked.

Q: Can you mix multiple different subquery styles in one WHERE clause? A: Yes, freely, combined with AND/OR, as the composite example shows.

Q: What's the single biggest mistake in this whole family? A: Using a plain comparison operator (=, >) against a subquery that isn't actually guaranteed to return one row — fine until the data changes and it suddenly throws a runtime error (17.2).