WHERE and Comparison Operators

Learning outcomes

A WHERE clause does not change stored rows. It evaluates a predicate for each candidate row and retains only rows for which that predicate is TRUE.

From source rows to qualifying rows

SELECT    product_id,    product_name,    unit_priceFROM productWHERE unit_price >= 49.00;

The predicate unit_price >= 49.00 is evaluated for every candidate product. It produces a logical result; only TRUE rows continue into the output.

Comparison operators

Equality is type- and collation-sensitive

SELECT    customer_id,    full_name,    segmentFROM customerWHERE segment = 'consumer';

Text equality depends on the database engine, data type, collation, and sometimes configuration. Do not assume that case, accents, or trailing spaces are treated identically across systems.

SELECT    product_id,    product_nameFROM productWHERE product_name = 'database foundations' COLLATE NOCASE;

NULL is not equal to anything

-- This predicate is never TRUE.SELECT customer_id, full_nameFROM customerWHERE city = NULL;

SELECT customer_id, full_nameFROM customerWHERE city IS NULL;SELECT customer_id, full_name, cityFROM customerWHERE city IS NOT NULL;

Ordinary comparisons involving NULL evaluate to UNKNOWN, not TRUE or FALSE. Because WHERE keeps only TRUE rows, UNKNOWN rows are filtered out.

Numeric boundaries require deliberate operators

-- Includes products priced exactly at 49.SELECT sku, product_name, unit_priceFROM productWHERE unit_price >= 49;-- Excludes products priced exactly at 49.SELECT sku, product_name, unit_priceFROM productWHERE unit_price > 49;

Boundary mistakes often survive casual testing. Test values below, equal to, and above every important threshold.

Timestamp filtering works best with half-open ranges

SELECT    sale_id,    customer_id,    sold_atFROM saleWHERE sold_at >= '2026-08-05 00:00:00'  AND sold_at <  '2026-08-06 00:00:00';

The lower boundary is included and the next day’s boundary is excluded. This pattern avoids guessing the final representable time of a day and adapts well to higher timestamp precision.

Computed values can participate in predicates

SELECT    s.sale_id,    p.product_name,    s.quantity,    p.unit_price,    s.quantity * p.unit_price * (1 - s.discount_rate)        AS net_amountFROM sale AS sJOIN product AS p  ON p.product_id = s.product_idWHERE s.quantity * p.unit_price * (1 - s.discount_rate) >= 100;

The alias net_amount is part of the output and is generally not available to WHERE in portable SQL, because filtering is logically evaluated before the select list. Repeat the expression, place it in a subquery, or use a common table expression later in the course.

Safe parameters, not string concatenation

SELECT    product_id,    product_name,    unit_priceFROM productWHERE unit_price >= ?;

Applications should bind user values through driver parameters. The exact placeholder syntax varies by driver, but the principle is consistent: SQL structure stays separate from data values.

Practice lab

Create the Chapter 5 database with the setup script used throughout this chapter, then write queries for these requirements:

1. Products whose price is less than 30.
2. Business customers.
3. Customers whose city is unknown.
4. Sales on or after August 4, 2026.
5. Sales with a calculated net amount of at least 100.

SELECT sku, product_name, unit_priceFROM productWHERE unit_price < 30;SELECT customer_id, full_nameFROM customerWHERE segment = 'business';SELECT customer_id, full_nameFROM customerWHERE city IS NULL;SELECT sale_id, sold_atFROM saleWHERE sold_at >= '2026-08-04 00:00:00';SELECT s.sale_id,       s.quantity * p.unit_price * (1 - s.discount_rate) AS net_amountFROM sale AS sJOIN product AS p ON p.product_id = s.product_idWHERE s.quantity * p.unit_price * (1 - s.discount_rate) >= 100;

Common failures

Using = NULL

Use IS NULL or IS NOT NULL.

Relying on implicit conversion

Comparing numbers to loosely formatted text can produce engine-specific results. Choose meaningful data types and bind values with compatible types.

Forgetting boundary cases

Requirements such as “after,” “from,” “through,” and “at least” encode inclusive or exclusive boundaries.

Assuming text comparison rules

Case and collation behavior must be designed, not guessed.

Summary and references

• WHERE retains only rows whose predicate evaluates TRUE.
• Use portable comparison operators and test boundaries deliberately.
• Use IS NULL and IS NOT NULL for missing values.
• Prefer half-open timestamp ranges and parameterized values.

References

• SQLite: WHERE clause filtering
• SQLite: SQL expression language
• PostgreSQL: comparison functions and operators