Membership, ranges and text patterns make common filters shorter and easier to read. This lesson focuses on the details that often cause interview bugs: inclusive range endpoints, wildcard meaning, date boundaries and NULL in NOT IN.
Key Takeaways at a Glance:
- •IN (val1, val2) tests set membership; if the set contains NULL and no match is found, NOT IN yields UNKNOWN (the NOT IN trap!).
- •BETWEEN low AND high is inclusive on both boundaries; avoid BETWEEN for timestamp ranges with fractional seconds.
- •LIKE uses '%' to match zero or more characters and '_' to match exactly one character.
- •Leading wildcards (e.g. LIKE '%smith') cannot use standard B-Tree index scans and cause full table scans.
In Simple Words (The Mental Model)
When you don't know the exact string, or you need to check multiple values or ranges, SQL provides pattern operators. IN replaces multiple OR conditions. BETWEEN checks if a value falls between two boundaries. LIKE performs wildcard searches like finding all names starting with 'Al'.
Real-World Analogy
Think of looking through a phone contacts list. Searching for 'Al%' is like flipping immediately to the 'Al' section in an indexed phonebook (fast index seek). Searching for '%son' is like having to read every single name in the phonebook from page 1 to the end to see if the name ends in 'son' (slow table scan).