CrackKit
0% complete
SQL & PostgreSQL Mastery

WHERE, operators & the NULL trap

WHERE is where queries get useful — it keeps only the rows you care about. This lesson covers the full filtering toolkit, and the one value that breaks every beginner's intuition: NULL.

Comparison and logical operators

sql
SELECT * FROM products WHERE price > 500;
SELECT * FROM orders   WHERE status = 'paid';
SELECT * FROM orders   WHERE status != 'cancelled';   -- or <>

-- combine with AND / OR (AND binds tighter than OR — use parens!)
SELECT * FROM products
WHERE category = 'books' AND price < 300;

SELECT * FROM orders
WHERE (status = 'paid' OR status = 'shipped') AND total > 1000;

Note strings use single quotes ('paid'). Double quotes mean an identifier (a column name), not a string — a frequent early mistake.

The convenience operators

sql
-- IN — match any of a list (cleaner than chained ORs)
SELECT * FROM orders WHERE status IN ('paid', 'shipped', 'delivered');

-- BETWEEN — inclusive range on both ends
SELECT * FROM products WHERE price BETWEEN 200 AND 500;   -- 200 and 500 included

-- LIKE — pattern matching: % = any chars, _ = one char
SELECT * FROM customers WHERE name LIKE 'A%';    -- starts with A
SELECT * FROM customers WHERE name LIKE '%son';  -- ends with son
SELECT * FROM customers WHERE name ILIKE 'a%';   -- ILIKE = case-insensitive (Postgres)

NULL: the absence of a value

NULL means "unknown / no value" — and it is not equal to anything, not even another NULL. This is the single biggest SQL gotcha:

sql
SELECT * FROM orders WHERE total = NULL;      -- WRONG: returns nothing, ever
SELECT * FROM orders WHERE total IS NULL;     -- RIGHT
SELECT * FROM orders WHERE total IS NOT NULL; -- RIGHT
NULL = NULL → NULL (not TRUE!) NULL > 5 → NULL x IS NULL → TRUE / FALSE ✓ the only correct test a WHERE clause keeps a row only when the condition is exactly TRUE

Any comparison with NULL yields NULL (not TRUE), so the row is dropped. Because of this, WHERE status != 'paid' silently excludes rows where status is NULL — you often need WHERE status != 'paid' OR status IS NULL.

Burn this in: use IS NULL / IS NOT NULL, never = NULL. And remember that NULL quietly disappears from != filters. Handling nulls correctly is what separates queries that look right from queries that are right. COALESCE(total, 0) substitutes a default when a value might be null.

PreviousNext lesson