🎯WHERE filtering
WHERE is relational selection
After FROM (and joins) build a working multiset of rows, WHERE keeps only those for which the predicate is TRUE. Comparisons involving NULL yield UNKNOWN, and WHERE drops UNKNOWN, this is the root of half the “my query returned nothing” mysteries.
📌Sargable predicates (indexes care)
Write predicates so the engine can match them to index columns without wrapping the column in a function.
WHERE plate_id = 42 is ideal for a B-tree on plate_id. WHERE LOWER(gene_symbol) = 'brca1' may defeat a plain index on gene_symbol unless you add a functional index or store a normalized column. When you must use functions, do it consciously and measure EXPLAIN.
Numeric and equality filters
SELECT well_position, od600
FROM plate_reads
WHERE plate_id = 42 AND od600 >= 0.8;| well_position | od600 |
|---|---|
| A01 | 0.91 |
| B03 | 0.88 |
| H12 | 0.81 |
Text + pattern (LIKE lesson goes deeper)
SELECT primer_name
FROM primers
WHERE primer_name LIKE 'FAM-%' AND length_bp >= 20;| primer_name |
|---|
| FAM-BRCA1-ex7 |
| FAM-TP53-ex5 |
| Anti-pattern | Why it bites | Fix |
|---|---|---|
| WHERE col = NULL | Always UNKNOWN, never matches | WHERE col IS NULL |
| WHERE FUNC(col) = … | Often non-sargable; hides index use | Rewrite to range on raw column or indexed expression |
| OR across unrelated columns | May block index-only paths | Sometimes split into UNION ALL branches with selective indexes each |
◻️Three-valued logic in WHERE
UNKNOWN rows are dropped, design tests with NULL fixtures.
| Predicate | Result when col IS NULL |
|---|---|
| col = 5 | UNKNOWN (filtered out) |
| col <> 5 | UNKNOWN (filtered out, not “true because unknown ≠ 5”) |
| col IS NULL | TRUE (keeps NULL rows) |
| col IS NOT NULL | FALSE for NULL rows |
That is why NOT IN (subquery) can erase rows when the subquery yields NULL, prefer NOT EXISTS for anti-joins unless you have proven NULL-free keys.
Remember
WHERE runs before SELECT aliases are visible in standard SQL, use a CTE when you need to filter on a computed expression by name.
TL;DR
WHERE filters rows from the FROM/JOIN result before grouping; only rows where the predicate is TRUE survive (UNKNOWN from NULL comparisons is dropped). Writesargable predicates (column compared to constant/range without wrapping the column in functions) so B-tree indexes apply. Complex OR predicates may need rewrite or UNION ALL branches to stay fast.