SQL: where & operators
This course covers how SQL filters rows with the WHERE clause in PostgreSQL, alongside the ActiveRecord code that generates each query. It starts with comparison operators and moves through AND, OR and NOT, operator precedence, LIKE and ILIKE pattern matching, IN and BETWEEN, and NULL handling with three-valued logic. Later lessons deal with timestamps and time zones in Rails, then with B-tree indexes, sargability, EXPLAIN ANALYZE, and expression and partial indexes. Parameterized queries and SQL injection come up throughout.
What you'll learn
- ✓Filtering rows with WHERE and the six comparison operators
- ✓Combining conditions with AND, OR and NOT, and using parentheses for precedence
- ✓Pattern matching with LIKE, ILIKE and safe parameterized search
- ✓Filtering lists and ranges with IN, BETWEEN and Ruby ranges
- ✓Testing for NULL with IS NULL, IS NOT NULL and COALESCE
- ✓Writing sargable conditions and reading plans with EXPLAIN ANALYZE
The WHERE clause
Imagine a nightclub with a thousand people inside. You don't want to talk to everyone: you want to find your friend who's wearing a red jacket. Instead of scanning every face, you tell the bouncer: "Only let through people in red jackets." The bouncer filters the crowd, and only matching people…
Read the full lesson free →Curriculum
7 lessonsThe WHERE clause
How WHERE evaluates a predicate per row, plus comparison operators, string quoting and the NULL comparison rule.
AND, OR & NOT
Combining conditions with AND, OR and NOT, why AND binds before OR, and the ActiveRecord equivalents.
LIKE & pattern matching
Matching strings with LIKE, ILIKE and wildcards, avoiding SQL injection, and why leading wildcards skip B-tree indexes.
IN, BETWEEN & range operators
Using IN for lists and subqueries, BETWEEN for inclusive ranges, Ruby ranges, and the NOT IN trap with NULLs.
NULL operators: IS NULL & IS NOT NULL
Three-valued logic, IS NULL and IS NOT NULL, COALESCE and NULLIF, and NULL behavior in aggregates and sorting.
Comparison with dates & times
How Rails stores timestamps without time zone, filtering with exclusive upper bounds, and avoiding timezone boundary bugs.
WHERE performance: indexes & sargability
How B-tree indexes serve WHERE, what makes a condition sargable, EXPLAIN ANALYZE, and expression and partial indexes.