WHERE Clause Filtering

What WHERE Actually Does

The WHERE clause is a predicate evaluator. For every row the database considers, it evaluates the condition and keeps the row only if the condition is TRUE. Rows where the condition is FALSE or UNKNOWN are discarded before any grouping, aggregation, or sorting happens.

This ordering matters. WHERE runs after FROM (which identifies the source rows) but before SELECT (which shapes the output). That means:

• WHERE can reference any column from the source tables, even columns not in the SELECT list.
• WHERE cannot reference column aliases defined in SELECT, because those do not exist yet.
• WHERE reduces the working set early, which is why filtering before aggregation is both a correctness and performance concern.

The Basic Form

SELECT column_list
FROM table_name
WHERE condition;

The condition is any expression that evaluates to TRUE, FALSE, or UNKNOWN for a given row. Comparison operators, range predicates, and boolean logic all produce such expressions.

A Sample Dataset

The examples in this topic use an orders table:

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT NOT NULL,
    order_date    DATE NOT NULL,
    status        VARCHAR(20) NOT NULL,
    total_amount  DECIMAL(10,2) NOT NULL,
    region        VARCHAR(20)
);

INSERT INTO orders VALUES
    (1, 101, '2024-01-15', 'completed', 250.00, 'East'),
    (2, 102, '2024-02-03', 'pending',   180.50, 'West'),
    (3, 101, '2024-02-20', 'completed',  95.00, 'East'),
    (4, 103, '2024-03-10', 'cancelled', 420.00, 'North'),
    (5, 104, '2024-03-25', 'completed', 310.75, 'South'),
    (6, 102, '2024-04-05', 'pending',   150.00, 'West'),
    (7, 105, '2024-04-18', 'completed', 500.00, 'East'),
    (8, 103, '2024-05-02', 'completed',  75.25, 'North');

Eight rows, four statuses, four regions. Enough to show every filtering pattern without being large enough to obscure the logic.

The Six Comparisons

SQL provides six comparison operators. Each returns TRUE or FALSE for a given row.

!= and <> are equivalent in standard SQL. Most style guides pick one and use it consistently.

Equality and String Matching

The = operator performs exact matching. For strings, that means case-sensitive, character-for-character equality.

SELECT order_id, customer_id, total_amount
FROM orders
WHERE status = 'completed';

This returns orders 1, 3, 5, 7, and 8 — every row where status is exactly 'completed'. A row with status 'Completed' or 'COMPLETED' would not match. Case sensitivity is a common source of unexpected empty results.

Numeric Comparisons

Numeric comparisons work as expected, but be careful with floating-point types. DECIMAL and NUMERIC types compare exactly. FLOAT and REAL types can have representation error, so total_amount = 0.1 might fail for a value that was stored as 0.1. For monetary data, always use DECIMAL.

-- Orders over $200
SELECT order_id, total_amount
FROM orders
WHERE total_amount > 200;
-- Returns orders 1, 4, 5, 7

Date Comparisons

Dates compare chronologically. String literals in ISO format (YYYY-MM-DD) are automatically cast to dates by most databases.

-- Orders from March 2024 onward
SELECT order_id, order_date
FROM orders
WHERE order_date >= '2024-03-01';
-- Returns orders 4, 5, 6, 7, 8

Date comparisons respect the full timestamp if the column includes time. order_date >= '2024-03-01' includes all times on March 1st and later. To get exactly one day, use a range: order_date >= '2024-03-01' AND order_date < '2024-03-02'.

Inequality and Negative Logic

!= and <> are not the same as NOT. status != 'cancelled' returns rows where status is not 'cancelled'. But if status can be NULL, those rows are excluded too — NULL != 'cancelled' evaluates to UNKNOWN, not TRUE. This is the three-valued logic trap, and it is covered in depth in the null semantics topic.

BETWEEN for Ranges

BETWEEN is a shorthand for a closed range: x BETWEEN low AND high means x >= low AND x <= high. Both endpoints are inclusive.

-- Orders between $100 and $300, inclusive
SELECT order_id, total_amount
FROM orders
WHERE total_amount BETWEEN 100 AND 300;
-- Returns orders 1 (250.00), 2 (180.50), 6 (150.00)

BETWEEN works on numbers, dates, and strings. For strings it uses lexicographic order: name BETWEEN 'A' AND 'M' returns names starting with letters A through M.

The NOT BETWEEN form excludes the range:

SELECT order_id, total_amount
FROM orders
WHERE total_amount NOT BETWEEN 100 AND 300;
-- Returns orders 3 (95.00), 4 (420.00), 5 (310.75), 7 (500.00), 8 (75.25)

Why BETWEEN Is Controversial

BETWEEN is inclusive on both ends, which is correct for discrete values (integers, dates) but surprising for continuous values. price BETWEEN 10 AND 20 includes exactly 10.00 and exactly 20.00. For timestamps, event_time BETWEEN '2024-01-01' AND '2024-01-31' includes all of January 31st, which is probably not what you want. The safer pattern for timestamps is a half-open range:

WHERE event_time >= '2024-01-01' AND event_time < '2024-02-01'

This includes every instant in January and excludes February 1st entirely. It also works correctly when the column has sub-second precision.

IN for Set Membership

IN tests whether a value matches any element of a list. It is equivalent to a chain of OR conditions but is shorter and often faster.

-- Orders in East or West regions
SELECT order_id, region
FROM orders
WHERE region IN ('East', 'West');
-- Returns orders 1, 2, 3, 6, 7

The equivalent OR form:

WHERE region = 'East' OR region = 'West'

Both return the same rows. IN is preferred when the list is long or when the list comes from a subquery.

NOT IN and the NULL Trap

NOT IN excludes rows matching any list element. It has a dangerous interaction with NULL.

SELECT order_id
FROM orders
WHERE region NOT IN ('East', 'West', NULL);

This returns zero rows. Why? For each row, the database evaluates region NOT IN ('East', 'West', NULL), which is region != 'East' AND region != 'West' AND region != NULL. The last condition is UNKNOWN for every row, and UNKNOWN AND anything is UNKNOWN or FALSE — never TRUE. So no row passes.

The rule: never put NULL in a NOT IN list. If the list comes from a subquery that might return NULL, filter them out first: WHERE region NOT IN (SELECT region FROM allowed WHERE region IS NOT NULL).

AND, OR, NOT

Boolean operators combine conditions. AND requires both sides to be TRUE. OR requires at least one side to be TRUE. NOT negates a single condition.

-- Completed orders over $200
SELECT order_id, status, total_amount
FROM orders
WHERE status = 'completed' AND total_amount > 200;
-- Returns orders 1, 5, 7

-- Pending or cancelled orders
SELECT order_id, status
FROM orders
WHERE status = 'pending' OR status = 'cancelled';
-- Returns orders 2, 4, 6

-- Not pending
SELECT order_id, status
FROM orders
WHERE NOT status = 'pending';
-- Returns orders 1, 3, 4, 5, 7, 8

Precedence: NOT > AND > OR

SQL evaluates NOT first, then AND, then OR. This is the same precedence as arithmetic: unary minus, then multiplication/division, then addition/subtraction.

-- Without parentheses
WHERE status = 'completed' OR status = 'pending' AND total_amount > 200;

-- The database reads it as:
WHERE status = 'completed' OR (status = 'pending' AND total_amount > 200);

The first form returns all completed orders plus pending orders over $200. The second form (with parentheses) returns orders that are either completed or pending, but only if they are also over $200. Different logic, different results.

Always Use Parentheses

The single most important rule for boolean logic in SQL: when mixing AND and OR, use parentheses. Do not rely on precedence. Precedence is a parsing rule; parentheses are a communication tool.

-- Unclear intent
WHERE region = 'East' OR region = 'West' AND total_amount > 200;

-- Clear intent
WHERE (region = 'East' OR region = 'West') AND total_amount > 200;

The second query states exactly what you mean. A reader does not need to know precedence rules to understand it. In code reviews, unexplained mixed AND/OR without parentheses is a defect.

De Morgan's Laws

Sometimes a NOT expression is easier to read when distributed. De Morgan's laws give the transformation:

• NOT (A AND B) is equivalent to NOT A OR NOT B
• NOT (A OR B) is equivalent to NOT A AND NOT B

-- These are equivalent
WHERE NOT (status = 'completed' AND total_amount > 200);
WHERE status != 'completed' OR total_amount <= 200;

Use whichever form matches the business question. "Orders that are not both completed and over $200" reads naturally as the first. "Orders that are either not completed or not over $200" reads as the second.

From Question to Query

Analytical filtering starts with a business question. The translation process is:

1. Identify the entity (orders, customers, events).
2. Identify the conditions (status, date range, amount threshold).
3. Identify the boolean relationships (AND for simultaneous conditions, OR for alternatives).
4. Write the query with parentheses around every OR group.

Example: High-Value Active Customers

Question: "Show me completed orders from the East region over $200 placed after February 1st."

SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE status = 'completed'
  AND region = 'East'
  AND total_amount > 200
  AND order_date > '2024-02-01';

Four AND conditions. Every condition must be true. Returns orders 7 only (order 1 is before February 1st, order 3 is under $200).

Example: Exclusion Report

Question: "Show me orders that are either pending or cancelled, but only if they are not from the West region."

SELECT order_id, status, region
FROM orders
WHERE (status = 'pending' OR status = 'cancelled')
  AND region != 'West';

The parentheses around the OR group are essential. Without them, the query would read status = 'pending' OR (status = 'cancelled' AND region != 'West'), which returns all pending orders regardless of region.

Example: Date Window with Status Exclusion

Question: "Show me completed orders from Q1 2024 (January through March) that are not from the North region."

SELECT order_id, order_date, region
FROM orders
WHERE status = 'completed'
  AND order_date >= '2024-01-01'
  AND order_date < '2024-04-01'
  AND region != 'North';

The half-open date range (>= '2024-01-01' AND < '2024-04-01') is the correct pattern for a quarter. It includes every instant from January 1st through March 31st 23:59:59.999... and excludes April 1st entirely.

Common Filtering Mistakes

1. Forgetting parentheses around OR groups mixed with AND.
2. Using = with NULL — always use IS NULL or IS NOT NULL.
3. Case-sensitive string matching when the data has inconsistent casing.
4. Closed ranges on timestamps — use half-open ranges instead.
5. NOT IN with a subquery that might return NULL.

Each of these produces a query that runs without error but returns wrong results. Wrong results are worse than errors because they look correct until someone checks the numbers.