How Does the WHERE Clause Work in SQL?
Understand how the SQL WHERE clause filters rows by condition, handles NULLs, differs from HAVING, and uses indexes, with a clear example and interview tips.
Expected Interview Answer
The WHERE clause filters rows in a query, keeping only those that satisfy a specified condition before the result set is returned.
WHERE is evaluated on each candidate row, and only rows where the condition is TRUE are passed through — rows evaluating to FALSE or UNKNOWN (NULL) are excluded. It supports comparison operators, logical operators (AND, OR, NOT), and predicates like BETWEEN, IN, LIKE, and IS NULL. WHERE runs before GROUP BY and aggregation, so it filters individual rows, not groups; HAVING filters groups afterward.
- Returns only relevant rows
- Reduces data scanned and transferred
- Can leverage indexes for speed
- Combines conditions with AND/OR/NOT
- Applies to SELECT, UPDATE, and DELETE
AI Mentor Explanation
Think of a selector scanning every player's stats and keeping only those with a batting average above fifty for the shortlist. Each player is checked against the rule and dropped if they fail it. The WHERE clause is that selector: it tests every row against a condition and keeps only the ones that pass, so your result holds just the qualifying records.
Step-by-Step Explanation
Step 1
Write the condition
Express a boolean predicate using columns, operators, and values.
Step 2
Evaluate per row
The engine tests each candidate row; only rows that evaluate to TRUE are kept.
Step 3
Combine predicates
Join multiple conditions with AND, OR, and NOT, minding operator precedence.
Step 4
Handle NULLs
Use IS NULL / IS NOT NULL because comparisons with NULL yield UNKNOWN, not TRUE.
Step 5
Return the filtered set
Rows that pass flow into GROUP BY, ORDER BY, and the final result.
What Interviewer Expects
- Knows WHERE filters rows before grouping
- Distinguishes WHERE from HAVING
- Understands NULL yields UNKNOWN, not TRUE
- Can use BETWEEN, IN, LIKE, and IS NULL
- Aware WHERE can use indexes for performance
Common Mistakes
- Using WHERE to filter aggregated results instead of HAVING
- Comparing to NULL with = instead of IS NULL
- Misjudging AND/OR precedence without parentheses
- Wrapping indexed columns in functions and killing index usage
- Assuming LIKE with a leading wildcard uses an index
Best Answer (HR Friendly)
“The WHERE clause is how you tell a SQL query which rows you care about. You give it a condition, and it returns only the rows that meet that condition, ignoring the rest.”
Code Example
SELECT product_name, price
FROM products
WHERE category = 'Books'
AND price BETWEEN 10 AND 30
AND stock IS NOT NULL
ORDER BY price ASC;Follow-up Questions
- What is the difference between WHERE and HAVING?
- Why does comparing a column to NULL with = fail?
- How does WHERE affect index usage?
- What do BETWEEN, IN, and LIKE do?
- Does WHERE run before or after GROUP BY?
MCQ Practice
1. When is the WHERE clause evaluated relative to GROUP BY?
WHERE filters individual rows before grouping; HAVING filters groups after aggregation.
2. How should you test a column for NULL in WHERE?
Comparisons with NULL yield UNKNOWN; IS NULL is the correct predicate.
3. A row whose WHERE condition evaluates to UNKNOWN is:
Only rows evaluating to TRUE are kept; FALSE and UNKNOWN rows are excluded.
Flash Cards
What does WHERE do? — Filters rows, keeping only those whose condition evaluates to TRUE.
WHERE vs HAVING? — WHERE filters rows before grouping; HAVING filters groups after aggregation.
How to test for NULL? — Use IS NULL / IS NOT NULL, never = NULL.
What can defeat an index in WHERE? — Wrapping the indexed column in a function or using a leading-wildcard LIKE.