We'll walk through the operators you actually need as a beginner—comparison, logical, range and list, NULL handling, and pattern matching—all in PostgreSQL, with queries I actually ran against a patients table.
Comparison Operators
Comparison operators are the ones you'll use every single day. They return a boolean for every row, and WHERE keeps only the rows where the result is true. The core set is =, <> (or !=), <, >, <=, and >=.
Here's a query for a patients table:
SELECT patient_id, patient_name, bill_amount FROM patients WHERE bill_amount > 10000; Simple enough. The trouble starts when you combine conditions.
Logical Operators: AND & OR
AND requires both sides to be true. OR requires at least one. NOT negates a condition. But they don't have equal priority—AND is evaluated before OR.
I wrote this query to find male patients over 40, or anyone with a huge bill:
SELECT * FROM patients WHERE gender = 'Male' AND age > 40 OR bill_amount > 150000; It works but only by luck. The database parsed it as (gender = 'Male' AND age > 40) OR (bill_amount > 150000), which is what I intended. But if I'd added another AND later, the grouping could have shifted silently.
The fix is boring and essential: use parentheses.
SELECT * FROM patients WHERE (gender = 'Male' AND age > 40) OR (bill_amount > 150000); Now the logic is unmistakable to the next person who reads it—including future you.
Range and List Filtering: BETWEEN and IN
BETWEEN is shorthand for a range check, and in PostgreSQL it's inclusive on both ends. a BETWEEN x AND y is exactly a >= x AND a <= y.
I used it to find patients with bills in a middle band:
SELECT * FROM patients WHERE bill_amount BETWEEN 10000 AND 50000; If a patient's bill is exactly 50000, they appear in the results. Same for exactly 10000. This tripped me up at first—I assumed BETWEEN might be exclusive on the upper end. It isn't. I verified with a row where bill_amount = 50000, and it came back.
IN is the cleaner way to check against a list:
SELECT * FROM patients WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu'); Logically, IN (a, b, c) is the same as = a OR = b OR = c. For short lists, readability is the main win. For very long lists, consider VALUES + JOIN instead.
NULL
NULL means "unknown," not "empty." So column = NULL doesn't return true—it returns NULL (which WHERE treats as not-true), and you get zero rows.
-- WRONG: returns nothing SELECT * FROM patients WHERE bill_amount = NULL; -- RIGHT: use IS NULL SELECT * FROM patients WHERE bill_amount IS NULL; If you need to treat NULL as a comparable value (for example, checking whether two columns differ even when one is NULL), PostgreSQL offers IS DISTINCT FROM and IS NOT DISTINCT FROM. For everyday filtering, IS NULL and IS NOT NULL are all you need.
LIKE vs. ILIKE: Case Sensitivity Matters
LIKE does pattern matching with two wildcards: % matches any number of characters, _ matches exactly one.
SELECT * FROM patients WHERE email LIKE '%@gmail.com'; This finds addresses ending in @gmail.com. It will not match @Gmail.com or @GMAIL.COM—LIKE is case-sensitive.
ILIKE is the case-insensitive version, and it's a PostgreSQL extension:
SELECT * FROM patients WHERE email ILIKE '%@gmail.com'; Now @gmail.com, @Gmail.com, and @GMAIL.COM all match.
A Real Business Query
A clinic wants to text young adult men who don't live in Nairobi, have no insurance, and have a recorded phone number:
-- Which male patients aged 18-35, NOT living in Nairobi, -- with no insurance and a recorded phone number, should be contacted? SELECT * FROM patients WHERE gender = 'Male' AND age BETWEEN 18 AND 35 AND city <> 'Nairobi' AND insurance = 'No' AND phone IS NOT NULL ORDER BY age, city; Each line uses a different operator, and together they express a rule that would be painful to describe in plain English.
Operator Precedence
When an expression mixes operators, SQL follows a fixed evaluation order. Simplified, from highest to lowest: arithmetic → comparison → NOT → AND → OR. When unsure, just add parentheses—it's the safest and most readable approach.
If you take one thing from this post, make it this: never write = NULL.
It doesn't show errors or warnings.
-- This will never match anything WHERE bill_amount = NULL; -- Always use this instead WHERE bill_amount IS NULL; The same principle applies to <> NULL and NOT IN with NULL in the list—NULL propagates through comparisons and can surprise you. When in doubt, handle NULL explicitly.
Operators are the grammar of SQL filtering. The small rules that aren't obvious from the syntax are what trip beginners up: AND before OR, BETWEEN is inclusive, NULL isn't equal to anything, and LIKE cares about case.
Don't try to memorize every operator at once. Start with =, >, <, AND, and OR, then move on to IN, BETWEEN, and LIKE. The rest can wait until you actually need them.
