Subqueries in WHERE clause filter rows based on data from another query. Think of it as asking: "Show me customers who are in this list" or "Find products that exist in this category". The subquery provides the filtering criteria.

Four main operators work with WHERE subqueries:

1. IN - "Is this value in the list?" - WHERE id IN (SELECT...)

2. EXISTS - "Do any rows exist?" - WHERE EXISTS (SELECT...)

3. Comparison (=, >, <) - "How does this compare?" - WHERE salary > (SELECT AVG...)

4. ANY/ALL - "Compare to any/all values" - WHERE price > ANY (SELECT...)

WHERE subqueries are the most common type because filtering is fundamental to SQL. Every time you need to filter based on related data, you'll use a WHERE subquery.

**IN Operator - Membership Testing:**

IN checks if a value exists in a list returned by subquery:

WHERE customer_id IN (SELECT customer_id FROM orders)

How it works:

1. Subquery executes: Returns list like (1, 3, 5, 7, 9)

2. For each row, check if customer_id is in that list

3. If yes, include row in results

Best for:

- Small to medium result sets (<1000 values)

- Simple membership checks

- When subquery returns distinct values

Performance:

- Subquery executes once

- Creates in-memory hash table for lookup

- Fast for small lists, slower for large lists

**EXISTS Operator - Existence Checking:**

EXISTS checks if subquery returns any rows:

WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = c.id)

How it works:

1. For each outer row, execute subquery

2. If subquery returns at least one row, condition is true

3. Stops at first match (doesn't need to find all rows)

Best for:

- Large result sets

- Correlated subqueries

- When you only care about existence, not actual values

- NOT EXISTS patterns (finding rows without matches)

Performance:

- Stops at first match (efficient)

- Works well with indexes

- Better than IN for large datasets

**Comparison Operators with Scalar Subqueries:**

Use =, >, <, >=, <= with subqueries that return single value: