Lesson 2: Filter rows with WHERE
WHERE evaluates each row before grouping. Use parameters for user-provided values in applications; never construct SQL by concatenating untrusted input.
SELECT order_id, total
FROM orders
WHERE total >= 100
AND order_date >= DATE '2025-01-01'
ORDER BY total DESC, order_id;Filtering operators
12 examples shown
| Predicate | Meaning | Example |
|---|---|---|
| = and <> | Equality and inequality. | status = 'paid' |
| AND / OR / NOT | Combine or negate Boolean conditions; use parentheses for clarity. | (status='paid' OR status='shipped') AND total>0 |
| IS NULL | Test missing values; NULL is not compared with equals. | shipped_at IS NULL |
| IS NOT NULL | Keep rows where a value exists. | customer_id IS NOT NULL |
| IN | Match one of a finite set. | status IN ('paid','shipped') |
| BETWEEN | Inclusive lower and upper bounds. | total BETWEEN 50 AND 100 |
| LIKE | Pattern matching; percent means any sequence, underscore one character. | email LIKE '%@example.com' |
| ILIKE | PostgreSQL case-insensitive LIKE. | name ILIKE 'ada%' |
| SIMILAR TO | PostgreSQL pattern matching with SQL regular-expression-like syntax. | code SIMILAR TO '(A|B)%' |
| IS DISTINCT FROM | Null-safe equality comparison. | old_value IS DISTINCT FROM new_value |
| EXISTS | True when a correlated subquery returns a row. | WHERE EXISTS (SELECT 1 FROM items i WHERE i.order_id=o.order_id) |
| ORDER BY / LIMIT | Sort and page deterministically using a unique tie-breaker. | ORDER BY created_at DESC, id DESC LIMIT 20 |
NULL and date-range rules
SQL uses three-valued logic: a comparison with NULL is unknown, not true or false. Use IS NULL or IS DISTINCT FROM. For timestamps, prefer half-open ranges such as created_at >= start_date AND created_at < next_date so all times on the final day are included safely.
Progress stays in this browser.