Filtering with WHERE, and the trouble with NULL

WHERE keeps the rows where a condition is true. You can combine conditions with AND, OR and NOT, and use helpers that make common filters readable.

SELECT order_id, total_amount, country
FROM orders
WHERE ordered_at >= '2026-01-01'
  AND ordered_at <  '2026-04-01'
  AND country IN ('DE', 'FR', 'NL')
  AND total_amount BETWEEN 20 AND 500;

Notice the date range: greater than or equal to the first day, strictly less than the day after the last. This half open range works whether the column is a date or a timestamp, and never misses orders placed at 23:59 on the last day.

Pattern matching

SELECT name FROM products WHERE name ILIKE '%coffee%';

LIKE is case sensitive; ILIKE is PostgreSQL's case insensitive version. In other databases you would write LOWER(name) LIKE '%coffee%'.

NULL means unknown

NULL is not zero and not an empty string. It means the value is unknown or missing. Any comparison with NULL returns unknown, which WHERE treats as false. That leads to the most common SQL surprise:

-- Returns nothing, even for orders with no coupon
SELECT * FROM orders WHERE coupon_code = NULL;

-- Correct
SELECT * FROM orders WHERE coupon_code IS NULL;

The same logic hides rows in negative filters. WHERE coupon_code <> 'SPRING' does not return orders where coupon_code is NULL. If you want those too, say so:

WHERE coupon_code <> 'SPRING' OR coupon_code IS NULL
-- or, in PostgreSQL:
WHERE coupon_code IS DISTINCT FROM 'SPRING'

Replacing NULL in output

COALESCE returns the first non NULL argument, which is handy for display:

SELECT order_id, COALESCE(coupon_code, 'none') AS coupon
FROM orders;

Make it a habit: whenever you filter on a column, ask whether it can be NULL and what you want to happen to those rows. Many wrong numbers in real reports come from rows that silently vanished because of NULL.