Filtering with WHERE Clause
1. Using Comparison Operators
| Op | Meaning |
|---|
| = | Equal |
| <> or != | Not equal |
| < <= > >= | Less/greater than (or equal) |
SELECT * FROM orders WHERE total_cents >= 1000 AND status <> 'cancelled';
2. Using Logical Operators
| Op | Truth Table Note |
|---|
| AND | NULL AND TRUE = NULL |
| OR | NULL OR TRUE = TRUE |
| NOT | NOT NULL = NULL |
3. Using IN Operator
SELECT * FROM orders WHERE status IN ('new','paid','shipped');
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE tier='gold');
4. Using NOT IN Operator
SELECT * FROM orders WHERE status NOT IN ('cancelled','refunded');
Warning: NOT IN with a subquery returning any NULL returns no rows. Prefer NOT EXISTS or filter NULLs.
5. Using BETWEEN Operator
| Equivalent | Detail |
|---|
| x BETWEEN a AND b | x >= a AND x <= b (inclusive) |
SELECT * FROM orders WHERE placed_at BETWEEN '2026-01-01' AND '2026-01-31';
6. Using NOT BETWEEN
SELECT * FROM orders WHERE total_cents NOT BETWEEN 1 AND 999;
7. Using LIKE Pattern Matching
| Token | Match |
|---|
| % | Any sequence (incl. empty) |
| _ | Exactly one character |
| ESCAPE '\' | Escape literal % or _ |
SELECT * FROM customers WHERE email LIKE '%@example.com';
8. Using ILIKE
SELECT * FROM customers WHERE email ILIKE '%@EXAMPLE.com'; -- case-insensitive
Note: Use citext column or expression index (lower(email)) for indexed case-insensitive lookups.
9. Using NOT LIKE and NOT ILIKE
SELECT * FROM customers WHERE email NOT LIKE '%@spam%';
10. Using IS NULL
SELECT * FROM customers WHERE deleted_at IS NULL;
| Wrong | Right |
|---|
| x = NULL | x IS NULL |
11. Using IS NOT NULL
SELECT * FROM customers WHERE phone IS NOT NULL;
12. Using IS DISTINCT FROM
SELECT * FROM users WHERE old_email IS DISTINCT FROM new_email;
| Behavior | vs = |
|---|
| Treats NULL as value | NULL IS DISTINCT FROM NULL = false |
13. Using IS NOT DISTINCT FROM
SELECT * FROM audit WHERE new_val IS NOT DISTINCT FROM old_val;
-- True when both equal OR both NULL