WHERE Filtering — Use WHERE to filter rows with comparisons and boolean logic - SQL

SECTION 1

Historical Context & Motivation

The ability to filter rows from a dataset is one of the most fundamental operations in data management, yet it was not always as straightforward as writing a WHERE clause. Before relational databases existed, programmers had to write procedural code that iterated through records in flat files or hierarchical databases, manually testing each field against desired criteria. This approach was error-prone, tightly coupled to the physical storage format, and nearly impossible to optimize. Edgar F. Codd's relational model changed everything by separating the logical description of data from its physical storage, enabling a declarative language in which users specify what they want rather than how to retrieve it.

1970 • Codd's Relational Model

Edgar F. Codd publishes "A Relational Model of Data for Large Shared Data Banks," introducing the selection operator (σ) in relational algebra — the formal ancestor of WHERE.

1974 • SEQUEL at IBM

Donald Chamberlin and Raymond Boyce at IBM design SEQUEL (Structured English Query Language), which includes a WHERE clause that maps the selection operator to English-like syntax.

1979 • Oracle V2 Ships

Oracle releases the first commercially available SQL-based RDBMS, bringing WHERE-based filtering to production enterprise systems.

1986 • SQL-86 Standard (ANSI)

ANSI publishes the first SQL standard, formally codifying the WHERE clause syntax with comparison operators and boolean connectives AND, OR, and NOT.

1992–Present • SQL-92 and Beyond

Successive standards (SQL-92, SQL:1999, SQL:2023) add BETWEEN, LIKE, IN, IS NULL, CASE expressions, and three-valued logic refinements to the WHERE clause.

The central question that Codd's selection operator — and by extension the WHERE clause — addresses is deceptively simple: how can a user describe, in a single declarative statement, exactly which rows of a table are relevant to a given question? Understanding WHERE filtering means understanding how comparison operators and boolean connectives combine into predicates that the database engine evaluates row by row — or, more practically, how the query optimizer can evaluate them in bulk using indexes and scan strategies.

SECTION 2

Core Principles & Definitions

The WHERE clause operates on a straightforward mental model: for every candidate row produced by the FROM clause, the database engine evaluates the predicate (a boolean expression) attached to WHERE. If the predicate evaluates to TRUE, the row passes into the result set; if it evaluates to FALSE or UNKNOWN, the row is excluded. This tri-valued evaluation is a direct consequence of SQL's treatment of NULL — an important subtlety we will revisit throughout the lesson.

Predicate

A boolean expression that evaluates to TRUE, FALSE, or UNKNOWN for each row. It is composed of one or more comparison operations combined with boolean connectives.

Comparison Operators

The six standard operators — =, <> (or !=), <, >, <=, >= — compare a column value against a literal, another column, or a subquery result.

Boolean Connectives

AND, OR, and NOT combine simple predicates into compound expressions. AND narrows results (intersection), OR broadens them (union), and NOT inverts a condition.

Three-Valued Logic (3VL)

Any comparison involving NULL yields UNKNOWN rather than TRUE or FALSE. WHERE only passes rows that evaluate to TRUE, so UNKNOWN rows are silently excluded.

Short-Circuit Evaluation

Although the SQL standard does not mandate evaluation order, most engines can reorder and short-circuit predicates for performance — a fact that matters when writing efficient queries.

✦

KEY TAKEAWAY

Think of the WHERE clause as a bouncer at a nightclub door. Every row in the table walks up to the entrance. The bouncer checks the predicate — the guest list criteria — and only lets in rows that satisfy every specified condition. Rows with NULL in a tested column are like guests whose ID is missing — the bouncer can't confirm they belong, so they're turned away (UNKNOWN ≠ TRUE).

SECTION 3

Visual Explanation — Row Filtering Pipeline

The pipeline shows how every row in the source table is tested against the WHERE predicate. The bottom detail table traces the evaluation of each sub-expression per row, demonstrating that Dan's NULL gpa produces UNKNOWN for the comparison, which AND propagates to FALSE.

The diagram above captures the conceptual flow of WHERE evaluation. On the left, the source table contains all six student rows. Each row enters the predicate evaluation box in the center, where two conditions are checked and combined with AND. Only rows for which the combined result is TRUE pass through to the result set on the right. Pay particular attention to Dan's row: because his gpa is NULL, the comparison gpa >= 3.0 yields UNKNOWN, not FALSE. Under SQL's three-valued logic, UNKNOWN AND TRUE is still UNKNOWN, so the row is excluded. This is a common source of bugs for developers who assume NULL behaves like zero or an empty string.

SECTION 4

How WHERE Works — Syntax, Operators & Boolean Logic

General Syntax

WHERE CLAUSE TEMPLATE

SELECT column_list FROM table_name WHERE predicate ;

The predicate is any expression that evaluates to TRUE, FALSE, or UNKNOWN per row. It may contain comparison operators, boolean connectives, and special operators such as BETWEEN, IN, LIKE, and IS [NOT] NULL.

Comparison Operators

Operator	Meaning	Example
`=`	Equal to	`WHERE status = 'active'`
`<> or !=`	Not equal to	`WHERE dept <> 'HR'`
`<`	Less than	`WHERE age < 30`
`>`	Greater than	`WHERE salary > 50000`
`<=`	Less than or equal to	`WHERE credits <= 120`
`>=`	Greater than or equal to	`WHERE gpa >= 3.0`

Standard SQL comparison operators

Boolean Connectives & Precedence

OPERATOR PRECEDENCE (HIGHEST TO LOWEST)

NOT > AND > OR

NOT binds tightest, AND binds next, and OR binds loosest. Use parentheses to override precedence and clarify intent.

Three-Valued Logic Truth Tables

A	B	A AND B	A OR B	NOT A
TRUE	TRUE	TRUE	TRUE	FALSE
TRUE	FALSE	FALSE	TRUE	FALSE
TRUE	UNKNOWN	UNKNOWN	TRUE	FALSE
FALSE	FALSE	FALSE	FALSE	TRUE
FALSE	UNKNOWN	FALSE	UNKNOWN	TRUE
UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN

SQL three-valued logic truth tables — UNKNOWN rows are highlighted

Convenience Predicates

SQL provides several syntactic shortcuts that desugar into combinations of comparisons and boolean connectives. BETWEEN low AND high is equivalent to col >= low AND col <= high (inclusive on both ends). IN (v1, v2, v3) is equivalent to col = v1 OR col = v2 OR col = v3. LIKE 'pattern' performs pattern matching with % (any sequence of characters) and _ (any single character). Finally, IS NULL and IS NOT NULL are the only correct way to test for the absence or presence of a value, since col = NULL always yields UNKNOWN.

SECTION 5

Detailed Breakdown — Predicate Composition & Evaluation Order

The parse tree shows how the query engine decomposes a compound WHERE predicate into a binary tree of operators. Leaf nodes are simple comparisons; internal nodes are boolean connectives. Parentheses override the default precedence (NOT > AND > OR) to group the OR sub-expression.

The parse tree visualization highlights a critical lesson: operator precedence determines the shape of the predicate tree, and therefore the semantics of the filter. Consider the difference between WHERE age > 21 AND dept = 'CS' OR dept = 'EE' (which, without parentheses, groups as (age > 21 AND dept = 'CS') OR dept = 'EE') versus the intended WHERE age > 21 AND (dept = 'CS' OR dept = 'EE'). The first form would include all EE department rows regardless of age — a subtle but impactful bug.

💡

Best Practice

Always use explicit parentheses when mixing AND and OR in the same WHERE clause. Even if you have the precedence rules memorized, your teammates maintaining the code six months later may not. Parentheses make intent unambiguous and serve as self-documenting syntax.

SECTION 6

Worked Example — Multi-Condition WHERE Query

Suppose we have a table employees with columns id, name, department, salary, and hire_date. We want to find all employees in either the Engineering or Research departments who earn at least $70,000 and were hired on or after 2020-01-01.

Building a Multi-Condition WHERE Clause

1
Step 1 — Identify the conditions

We have three independent conditions: (a) department is 'Engineering' or 'Research', (b) salary is at least 70000, and (c) hire_date is on or after '2020-01-01'. All three must be true simultaneously, so they connect with AND. Condition (a) is internally an OR between two values.
2
Step 2 — Write the skeleton query

Start with the basic SELECT … FROM … WHERE template: SELECT name, department, salary, hire_date FROM employees WHERE ...;
3
Step 3 — Compose the predicate with explicit parentheses

Because we mix AND and OR, parentheses around the OR sub-expression are essential to prevent AND from binding the salary condition to only one department. The complete predicate is: (department = 'Engineering' OR department = 'Research') AND salary >= 70000 AND hire_date >= '2020-01-01'
4
Step 4 — Refactor using IN for readability

The OR pair on department can be rewritten with the IN operator for conciseness: department IN ('Engineering', 'Research'). This is semantically identical and easier to extend if more departments are needed later.
5
Step 5 — Assemble the final query

Combining everything yields a clean, readable query.

SELECT name, department, salary, hire_date FROM employees WHERE department IN ('Engineering', 'Research') AND salary >= 70000 AND hire_date >= '2020-01-01';
6
Step 6 — Verify with a trace

Consider a row: name='Aria', department='Engineering', salary=82000, hire_date='2021-06-15'. Evaluation: IN → TRUE, salary >= 70000 → TRUE, hire_date >= '2020-01-01' → TRUE. TRUE AND TRUE AND TRUE → TRUE. The row is included. Now consider name='Ben', department='Sales', salary=90000, hire_date='2022-03-01'. IN → FALSE, so FALSE AND TRUE AND TRUE → FALSE. Ben is correctly excluded despite his high salary.

SECTION 7

Common Pitfalls, Strengths & Limitations

Pitfall / Topic	Problem	Solution
NULL comparisons	Writing col = NULL or col <> NULL always returns UNKNOWN, silently excluding rows you may want.	Use IS NULL / IS NOT NULL. Or use COALESCE(col, default) to replace NULLs before comparing.
Precedence errors	Mixing AND and OR without parentheses leads to unintended predicate grouping.	Always parenthesize OR groups explicitly: (A OR B) AND C.
Implicit type coercion	Comparing a string column to an integer (e.g., WHERE zipcode = 10001) may prevent index usage due to type casting.	Match literal types to column types: WHERE zipcode = '10001'.
Functions on indexed columns	Wrapping a column in a function (e.g., WHERE YEAR(hire_date) = 2023) disables index seeks, causing full table scans.	Rewrite as a range: WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01'.
NOT IN with NULLs	If the subquery or list in NOT IN contains a NULL, the entire predicate evaluates to UNKNOWN for every row, returning zero results.	Use NOT EXISTS instead, or ensure the subquery excludes NULLs: WHERE col NOT IN (SELECT x FROM t WHERE x IS NOT NULL).

Common WHERE clause pitfalls and their solutions

✦

KEY TAKEAWAY

The WHERE clause is remarkably powerful for its simplicity, but its interaction with NULL values and operator precedence introduces semantic traps that even experienced developers encounter. Think of NULL as a fog: any arithmetic or comparison with fog produces more fog (UNKNOWN), and the only tool that can see through it is the IS operator.

SECTION 8

Connection to Advanced Filtering & Query Optimization

The WHERE clause you have learned forms the foundation upon which several advanced SQL features build. As you move into more complex query patterns, understanding WHERE's behavior becomes essential for reasoning about JOIN conditions, HAVING clauses, window function FILTER expressions, and subquery correlation. The query optimizer also relies on predicate analysis — known as predicate pushdown — to move filter conditions as close to the data source as possible, dramatically reducing I/O.

Feature	WHERE (This Lesson)	Advanced Counterpart
Row-level filtering	WHERE filters individual rows before grouping.	HAVING filters groups after GROUP BY aggregation.
Join filtering	WHERE with multi-table queries applies after the cross product (old-style joins).	ON clause in explicit JOINs filters during the join operation, affecting outer join semantics.
Scalar predicates	Comparisons with literals and column references.	Correlated subqueries in WHERE (EXISTS, IN with subquery) introduce row-dependent sub-filters.
Static expressions	Predicates evaluated once per row scan.	Window function FILTER clauses apply predicates within partitioned window frames.
Optimization	Simple predicates can leverage B-tree indexes for O(log n) lookups.	Predicate pushdown, partition pruning, and bloom filters extend this to distributed systems (e.g., Spark, BigQuery).

WHERE clause vs. advanced filtering constructs

As you advance, keep in mind that every filtering mechanism in SQL ultimately reduces to the same logical framework you have learned here: predicates composed of comparisons and boolean connectives, evaluated under three-valued logic. Mastering WHERE is not just a beginner step — it is the conceptual bedrock for every query you will write.

SECTION 9

Practice Problems

All problems reference a table products with columns: id INT, name VARCHAR, category VARCHAR, price DECIMAL, stock INT, discount DECIMAL (nullable), and release_date DATE.

Question 1: CONCEPTUAL

Explain why WHERE discount = NULL returns zero rows even if some products have a NULL discount. What is the correct alternative?

Show answer

In SQL's three-valued logic, any comparison with NULL — including = — yields UNKNOWN, not TRUE or FALSE. The WHERE clause only passes rows that evaluate to TRUE, so UNKNOWN rows are excluded. This means discount = NULL never produces TRUE for any row, returning an empty result set. The correct way to test for NULL is WHERE discount IS NULL. The IS NULL operator is specifically designed to evaluate to TRUE when the operand is NULL.

Question 2: BASIC

Write a query that returns the name and price of all products in the 'Electronics' category with a price strictly greater than 100.

Show answer

SELECT name, price FROM products WHERE category = 'Electronics' AND price > 100; This query uses AND to combine two conditions: an equality check on category and a greater-than comparison on price. Both must be TRUE for a row to appear in the result set. The AND operator ensures we are narrowing the result to the intersection of both criteria.

Question 3: INTERMEDIATE

Write a query that returns all products that are either in the 'Books' or 'Music' category, have stock between 10 and 500 (inclusive), and have a non-NULL discount. Order the result by price descending.

Show answer

SELECT * FROM products WHERE category IN ('Books', 'Music') AND stock BETWEEN 10 AND 500 AND discount IS NOT NULL ORDER BY price DESC;

Here, IN replaces a pair of OR conditions for readability, BETWEEN provides an inclusive range check equivalent to stock >= 10 AND stock <= 500, and IS NOT NULL correctly filters out rows where discount is absent. The ORDER BY is processed after the WHERE filter and does not affect which rows are included.

Question 4: APPLIED

A marketing team asks: 'Show me products released in Q1 2024 (January through March) that either have a discount OR cost less than $25, but exclude anything in the Clearance category.' Write the query and explain why parentheses are essential.

Show answer

SELECT name, category, price, discount, release_date FROM products WHERE release_date >= '2024-01-01' AND release_date < '2024-04-01' AND (discount IS NOT NULL OR price < 25) AND category <> 'Clearance';

Parentheses around the OR sub-expression are essential because AND has higher precedence than OR. Without them, the query would parse as (release_date conditions AND discount IS NOT NULL) OR (price < 25 AND category <> 'Clearance'), which would include cheap products from any date and any non-Clearance category — a much broader and incorrect result. The date range uses >= and < (rather than BETWEEN) to correctly capture all timestamps within Q1 without accidentally including April 1.

Question 5: CRITICAL THINKING

Consider the predicate WHERE NOT (price > 50 OR discount IS NULL). Apply De Morgan's law to rewrite this predicate without the outer NOT. Then explain what happens to a row where price is NULL and discount is NULL — does it pass the filter? Justify using the three-valued logic truth table.

Show answer

By De Morgan's law, NOT (A OR B) ≡ NOT A AND NOT B. Applying this: WHERE price <= 50 AND discount IS NOT NULL (note that NOT (price > 50) becomes price <= 50, and NOT (discount IS NULL) becomes discount IS NOT NULL). Now consider a row with price = NULL and discount = NULL. For the original predicate: price > 50 → UNKNOWN, discount IS NULL → TRUE. UNKNOWN OR TRUE → TRUE. NOT TRUE → FALSE. The row is excluded. For the rewritten predicate: price <= 50 → UNKNOWN, discount IS NOT NULL → FALSE. UNKNOWN AND FALSE → FALSE. The row is also excluded. Both forms agree, confirming the equivalence. The key insight is that IS NULL / IS NOT NULL always return TRUE or FALSE (never UNKNOWN), so De Morgan's law can be applied safely to these predicates. However, if the NOT had been wrapping a regular comparison involving NULL, the UNKNOWN propagation could have made the equivalence less intuitive — always trace through the truth table to verify.

SUMMARY

Lesson Summary

Summary

The WHERE clause is SQL's primary mechanism for row-level filtering, descended directly from the selection operator (σ) in relational algebra. It evaluates a predicate — a boolean expression composed of comparison operators (=, <>, <, >, <=, >=) and boolean connectives (AND, OR, NOT) — for every candidate row. Only rows that evaluate to TRUE are included; both FALSE and UNKNOWN (from NULL comparisons) are silently excluded.

Key takeaways include: use IS NULL / IS NOT NULL instead of = NULL; always parenthesize OR groups when mixing with AND to avoid precedence bugs; leverage convenience predicates like BETWEEN, IN, and LIKE for readability; and avoid wrapping indexed columns in functions to preserve query optimizer efficiency. Mastery of the WHERE clause — especially its interaction with three-valued logic — is the prerequisite for every advanced filtering construct in SQL, from HAVING to correlated subqueries to window function FILTER clauses.