beginner

WHERE Clause in SQL

By DataPlayArena Technical Review BoardUpdated 2026-07-208 min readRuntime Verified

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, and UNKNOWN dictate row inclusion when NULL values are present.
  • Comparison & Logical Operators: Mastering AND, OR, NOT, IN, BETWEEN, and LIKE.
  • 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 = NULL does not evaluate to TRUE; it evaluates to UNKNOWN!
  • 10 != NULL evaluates to UNKNOWN!
  • A WHERE clause only returns rows where the condition evaluates strictly to TRUE. Any condition evaluating to FALSE or UNKNOWN is rejected.
sql
-- 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

sql
-- 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

sql
-- 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

sql
-- 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:

sql
-- 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 |

sql
-- 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 IN with Subqueries Containing NULLs: If a subquery returns a single NULL value, WHERE col NOT IN (SELECT ...) will return zero rows for the entire table! Always prefer WHERE NOT EXISTS or ensure WHERE col IS NOT NULL in the subquery.
  • Implicit Data Type Conversions: Filtering a VARCHAR column 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:

  1. Sargability: A condition that allows the engine to navigate a B-Tree index seek without computing functions or transformations over column values.
  2. The NOT IN NULL Trap: Under SQL's three-valued logic, x NOT IN (1, 2, NULL) expands to x != 1 AND x != 2 AND x != NULL. Because x != NULL evaluates to UNKNOWN, the entire compound expression evaluates to UNKNOWN, resulting in zero rows returned.
  3. Best Practice Alternative: Always use NOT EXISTS, which relies on two-valued existence logic and handles NULL values safely.

Interactive Practice Challenge

Challenge: Sargable High-Value Order Filter

You have an orders table with a B-Tree index on ordered_at. Rewrite the following non-sargable query into a sargable equivalent that selects all orders from the year 2026 with a status of either 'Shipped' or 'Delivered': SELECT * FROM orders WHERE EXTRACT(YEAR FROM ordered_at) = 2026 AND (status = 'Shipped' OR status = 'Delivered');

DPA

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.