WHERE Clause in SQL
Overview
The WHERE clause filters records from a query's source tables before any grouping, aggregation, or projection takes place. In the relational query lifecycle, the WHERE clause is the primary driver of query performance: when written properly using sargable (Search Argument Able) predicates, the database engine can leverage B-Tree indexes to seek directly to matching rows. When written poorly, it forces the database into slow, I/O-heavy full table scans.
What You Will Learn
In this lesson, you will master:
- Relational Filtering Mechanics: How predicates evaluate rows before aggregation occurs.
- Sargability: How to write query predicates that allow database query planners to use B-Tree index seeks.
- SQL Three-Valued Logic: How
TRUE,FALSE, andUNKNOWNdictate row inclusion whenNULLvalues are present. - Comparison & Logical Operators: Mastering
AND,OR,NOT,IN,BETWEEN, andLIKE. - WHERE vs HAVING: Dissecting pre-aggregation row filtering vs post-aggregation group filtering.
SQL Three-Valued Logic (3VL) & The NULL Value Trap
Standard boolean logic has two states: TRUE and FALSE. SQL uses Three-Valued Logic with three states: TRUE, FALSE, and UNKNOWN.
In SQL, NULL represents missing or unknown data. Therefore:
NULL = NULLdoes not evaluate toTRUE; it evaluates toUNKNOWN!10 != NULLevaluates toUNKNOWN!- A
WHEREclause only returns rows where the condition evaluates strictly toTRUE. Any condition evaluating toFALSEorUNKNOWNis rejected.
-- Scenario: Finding employees who do NOT have a manager
-- INCORRECT: Returns ZERO rows because (manager_id = NULL) evaluates to UNKNOWN!
SELECT * FROM employees WHERE manager_id = NULL;
-- CORRECT: Use explicit IS NULL operator
SELECT * FROM employees WHERE manager_id IS NULL;
-- Finding employees who have a manager
SELECT * FROM employees WHERE manager_id IS NOT NULL;
Sargability: Writing High-Performance Index-Seeking Predicates
A predicate is sargable (Search ARGument ABLE) if the database engine can utilize an index seek to satisfy it.
1. Function Wrapping on Indexed Columns
-- NON-SARGABLE: The database must calculate DATE(created_at) for EVERY row in the table!
-- Forces a FULL TABLE SCAN
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-20';
-- SARGABLE: Range comparison leaves the column untouched
-- Uses a fast B-TREE INDEX SEEK!
SELECT * FROM orders
WHERE created_at >= '2026-07-20 00:00:00'
AND created_at < '2026-07-21 00:00:00';
2. Arithmetic on Columns
-- NON-SARGABLE: Arithmetic modifies the column
SELECT * FROM products WHERE price * 1.2 > 100;
-- SARGABLE: Isolate the column on one side of the operator
SELECT * FROM products WHERE price > (100 / 1.2);
3. Wildcard LIKE Patterns
-- SARGABLE: Trailing wildcard can use B-Tree index seek
SELECT * FROM customers WHERE last_name LIKE 'Smith%';
-- NON-SARGABLE: Leading wildcard CANNOT use index seek (Forces full scan)
SELECT * FROM customers WHERE last_name LIKE '%Smith';
Operator Precedence: AND vs OR
AND has higher operator precedence than OR. Omitting parentheses leads to severe data leakage bugs:
-- Unintended Query: Evaluates as (status = 'active') OR (department = 'Engineering' AND salary > 90000)
-- Returns active employees from ANY department, regardless of salary!
SELECT * FROM employees
WHERE status = 'active' OR department = 'Engineering' AND salary > 90000;
-- Correct Query: Parentheses explicitly control evaluation order
SELECT * FROM employees
WHERE (status = 'active' OR department = 'Engineering')
AND salary > 90000;
WHERE vs HAVING: When to Use Which?
| Feature | WHERE Clause | HAVING Clause |
| :--- | :--- | :--- |
| Execution Point | Executes before grouping (GROUP BY) | Executes after grouping (GROUP BY) |
| Row Filtering | Filters individual base rows | Filters aggregated groups |
| Aggregate Functions| Cannot use aggregate functions (SUM, AVG, COUNT) | Can use aggregate functions (HAVING SUM(sales) > 1000) |
| Performance | Reduces dataset size early, saving memory and compute | Evaluates aggregates over groups |
-- Optimal query combining both:
SELECT department_id, COUNT(*) AS total_employees, AVG(salary) AS avg_sal
FROM employees
WHERE status = 'ACTIVE' -- Step 1: Filter out inactive employees EARLY
GROUP BY department_id
HAVING COUNT(*) >= 5 -- Step 2: Keep only large departments
AND AVG(salary) > 85000; -- Step 3: Filter groups by aggregate metric
Common Mistakes & Gotchas
- Using
NOT INwith Subqueries Containing NULLs: If a subquery returns a singleNULLvalue,WHERE col NOT IN (SELECT ...)will return zero rows for the entire table! Always preferWHERE NOT EXISTSor ensureWHERE col IS NOT NULLin the subquery. - Implicit Data Type Conversions: Filtering a
VARCHARcolumn with an integer (WHERE phone_number = 1234567890) forces the database engine to convert every string row to an integer, completely invalidating index seeks.
Interview Perspective
SQL Interview Question: What makes a query sargable, and why does NOT IN fail when a subquery contains NULL?
Key Talking Points for Candidates:
- Sargability: A condition that allows the engine to navigate a B-Tree index seek without computing functions or transformations over column values.
- The NOT IN NULL Trap: Under SQL's three-valued logic,
x NOT IN (1, 2, NULL)expands tox != 1 AND x != 2 AND x != NULL. Becausex != NULLevaluates toUNKNOWN, the entire compound expression evaluates toUNKNOWN, resulting in zero rows returned. - Best Practice Alternative: Always use
NOT EXISTS, which relies on two-valued existence logic and handlesNULLvalues safely.
Interactive Practice Challenge
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.