Last week we covered the core SQL keywords - SELECT, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. Students could retrieve data, summarise it, sort it.
But there was a problem. Every WHERE clause we wrote used exactly one condition. price > 100. stock_level < 30. Clean, simple, single.
Real questions are never that simple.
"Show me all Dairy products that cost less than KES 70." That's two conditions. "Show me every product name that starts with the letter M." That's a pattern, not a value. "Show me products whose price is somewhere between 60 and 200 shillings." That's a range.
This session was about teaching how to ask those questions - and the five families of operators that make them possible.
The Dataset: Mama Nia's Duka
Same table we've been using all month - duka.duka_products. Mama Nia's shop. Products, categories, suppliers, prices, stock levels. A dataset that feels like a real place, which is exactly why the queries feel like real questions.
Five operator categories on the board before any code:
| Category | Operators |
|---|---|
| Comparison |
=, !=, >, <, >=, <=
|
| Logical |
AND, OR, NOT
|
| Range |
BETWEEN, NOT BETWEEN
|
| Membership |
IN, NOT IN
|
| Search |
LIKE, ILIKE
|
"By the end of today, you will use all five in the same query. Let's go one at a time."
Section 1: Comparison Operators - The Foundation
These are the ones students already half-know from mathematics. The only one that catches people is != - not equal to. The rest feel immediately natural.
Three examples, three different operators:
-- Example 1: Products above 150 shillings
SELECT product_name, price
FROM duka.duka_products
WHERE price > 150
ORDER BY price;
-- Example 2: Low on stock - less than or equal to 30 units
SELECT product_name, stock_level
FROM duka.duka_products
WHERE stock_level <= 30
ORDER BY stock_level;
-- Exact match
SELECT product_name
FROM duka.duka_products
WHERE price = 65.00;
I told the class: "Comparison operators are the vocabulary of filtering. Everything else we learn today uses these as building blocks."
The one worth pausing on: >= and <=. In English, "products with stock level 30 or below" translates directly to stock_level <= 30. The boundary value - 30 - is included. Students who meet BETWEEN later in the session sometimes forget that < alone excludes the boundary. A small distinction with meaningful consequences in inventory and financial queries.
Section 2: Logical Operators - AND, OR, NOT
AND - Both Must Be True
-- Dairy products that cost more than 70 shillings
SELECT product_name, product_category, price
FROM duka.duka_products
WHERE product_category = 'Dairy' AND price > 70;
AND is the strict filter. Both conditions must be true for a row to pass. Think of it as two bouncers at the door - the row has to get past both.
OR - At Least One Must Be True
-- Products that are either Beverages OR Household
SELECT product_name, product_category
FROM duka.duka_products
WHERE product_category = 'Beverages' OR product_category = 'Household';
OR is more relaxed. One condition being true is enough. The row passes if it satisfies either condition - or both.
NOT - Negate Everything
-- Everything that is NOT in Grains & Cereals
SELECT product_name, product_category
FROM duka.duka_products
WHERE NOT product_category = 'Grains & Cereals'
ORDER BY product_category;
NOT flips the result. Sometimes cleaner than writing the negative condition directly, especially when the positive condition is complex.
The Trap Nobody Sees Coming: AND Before OR
This is the moment of Section 2, and I engineer it deliberately every cohort.
PostgreSQL evaluates AND before OR - the same way multiplication happens before addition in mathematics. You learned BODMAS in school. SQL has its own version, and most beginners don't know it.
Here's the trap. Mama Nia wants Dairy products where price is either below KES 70 OR above KES 85:
-- WRONG — no parentheses
SELECT product_name, product_category, price
FROM duka.duka_products
WHERE product_category = 'Dairy' AND price < 70 OR price > 85;
This looks right. It isn't. SQL reads it as:
-- What PostgreSQL actually runs:
WHERE (product_category = 'Dairy' AND price < 70) OR price > 85
The AND binds first. The result: every product above KES 85 comes through — regardless of category - because the OR on the right has no category condition attached to it.
The fix is one pair of brackets:
-- CORRECT - parentheses group the OR together
SELECT product_name, product_category, price
FROM duka.duka_products
WHERE product_category = 'Dairy' AND (price < 70 OR price > 85);
We ran both versions live and compared the row counts. Different numbers. Visibly, provably different. That proof lands better than any explanation.
I told the class: "Any query that mixes AND and OR - use brackets. Not because you are unsure of the precedence, but because the next person reading your query definitely isn't. Brackets are documentation."
Section 3: BETWEEN - Ranges Made Readable
Without BETWEEN, a range filter needs two conditions joined by AND:
-- The long way
SELECT product_name, price
FROM duka.duka_products
WHERE price >= 60 AND price <= 200
ORDER BY price;
With BETWEEN:
-- The short way
SELECT product_name, price
FROM duka.duka_products
WHERE price BETWEEN 60 AND 200
ORDER BY price;
Same result. The BETWEEN version reads more like English - "price between 60 and 200" — which is exactly what we mean.
BETWEEN also works in reverse:
-- Products with stock level in range 20–40
SELECT product_name, stock_level
FROM duka.duka_products
WHERE stock_level BETWEEN 20 AND 40
ORDER BY stock_level;
-- Flip it: products OUTSIDE the 20–40 range
SELECT product_name, stock_level
FROM duka.duka_products
WHERE stock_level NOT BETWEEN 20 AND 40
ORDER BY stock_level;
Critical detail I repeat twice: BETWEEN is inclusive. BETWEEN 60 AND 200 includes products priced exactly 60 and exactly 200. If you want to exclude the boundaries, use > 60 AND < 200 instead.
This matters most in financial and date-range queries. A billing report BETWEEN two dates either includes or excludes those dates - and the answer matters. Know your boundaries.
Section 4: IN and NOT IN - Membership Checks
Without IN, filtering for multiple specific values means chaining OR:
-- The long way
SELECT product_name, product_category
FROM duka.duka_products
WHERE product_category = 'Dairy' OR product_category = 'Household';
With IN:
-- The short way
SELECT product_name, product_category
FROM duka.duka_products
WHERE product_category IN('Dairy', 'Household');
Same result. One tenth of the typing. Add a third category - just add it to the list. No restructuring the query.
NOT IN gives you the inverse. Mama Nia wants to see all products except those from two specific suppliers:
-- Products NOT from these two suppliers
SELECT product_name, supplier
FROM duka.duka_products
WHERE supplier NOT IN('Kenya Grain Millers', 'Brookside Dairy');
IN also works on specific product names:
-- Check prices for specific products by name
SELECT product_name, price
FROM duka.duka_products
WHERE product_name IN('Sukari', 'Mkate', 'Maharagwe');
One honest warning about NOT IN: it behaves unexpectedly when any value in the list is NULL. If the column being checked has NULL values, NOT IN may return zero rows - silently, with no error. For this dataset all our values are populated, so we noted the warning and moved on. Worth knowing before you hit it in a real project.
Section 5: LIKE and ILIKE - Pattern Matching
Sometimes you don't know the exact value. You know it starts with something, or contains something, or follows a certain shape. That's what LIKE and ILIKE are for.
Two wildcard characters:
| Symbol | Meaning |
|---|---|
% |
Matches any number of characters (including zero) |
_ |
Matches exactly one character |
-- Products starting with 'M'
SELECT product_name
FROM duka.duka_products
WHERE product_name LIKE 'M%';
-- Products containing 'ai' anywhere in the name
SELECT product_name
FROM duka.duka_products
WHERE product_name LIKE '%ai%';
-- The _ wildcard - exactly one character before 'kate'
SELECT product_name
FROM duka.duka_products
WHERE product_name LIKE '_kate';
-- ILIKE - case-insensitive search
-- 'SUKARI' matches 'Sukari', 'SUKARI', 'sukari'
SELECT product_name
FROM duka.duka_products
WHERE product_name ILIKE 'SUKARI';
LIKE is case-sensitive - LIKE 'sukari' won't match 'Sukari'. ILIKE is PostgreSQL's case-insensitive version.
The three % placement patterns, left on the board for the rest of the session:
LIKE 'M%' → starts with M
LIKE '%a' → ends with a
LIKE '%ai%' → contains 'ai' anywhere
The _ underscore matches exactly one character. LIKE '_kate' matches 'Mkate' - one character then 'kate'. Useful for product codes with fixed-length formats.
Rule of thumb I gave the room: "If a human is typing the search term into any kind of input box, use ILIKE. They'll type 'SUKARI', 'sukari', 'Sukari' - and you want all of them to match."
All Five Together - The Session Closer
SELECT product_name, product_category, price, supplier, stock_level
FROM duka.duka_products
WHERE product_category IN('Dairy', 'Beverages') -- membership
AND price BETWEEN 50 AND 200 -- range
AND supplier ILIKE '%brook%' -- pattern
AND product_name NOT IN('Maziwa') -- exclusion
AND stock_level > 20 -- comparison
ORDER BY price;
The Cheat Sheet: When to Use What
| You want to… | Use |
|---|---|
| Match an exact value | = |
| Exclude a specific value | != |
| Both conditions required | AND |
| Either condition is enough | OR |
| Negate a condition | NOT |
| Mix AND and OR | Brackets — always |
| Number or date range | BETWEEN |
| Exclude a range | NOT BETWEEN |
| One of several specific values | IN |
| Exclude several specific values | NOT IN |
| Pattern match, case-sensitive | LIKE |
| Pattern match, case-insensitive | ILIKE |
Practice Problems
Easy:
-- 1. Show all products where stock_level is between 25 and 60
-- 2. Show products from 'Dairy' OR 'Beverages' category
-- 3. Show product names that start with the letter 'S'
Medium:
-- Mama Nia wants to review specific products:
-- Show 'Sukari', 'Mkate', and 'Maharagwe' only if price is above 50
-- Order by price descending
SELECT product_name, price
FROM duka.duka_products
WHERE product_name IN('Sukari', 'Mkate', 'Maharagwe')
AND price > 50
ORDER BY price DESC;
Challenge:
-- Restock priority report:
-- Products NOT supplied by 'Kenya Grain Millers' or 'Brookside Dairy'
-- AND whose name contains the letter 'a' anywhere (case-insensitive)
-- AND whose stock level is NOT BETWEEN 30 AND 80
-- Order by stock_level ascending (most urgent first)
-- Your query here...
What's Next: JOINs
Every query we have written so far has touched exactly one table. Next session, Mama Nia's duka gets a second table - and we start connecting them.
-- A preview
SELECT p.product_name, p.price, s.contact_person, s.phone
FROM duka_products p
JOIN suppliers s ON p.supplier_id = s.id
WHERE p.stock_level < 30;
One query. Two tables. The database starts feeling like a real system. See you then.
Try It Yourself
Focus your practice on the AND + OR parentheses trap - write a query that gives the wrong answer without brackets, confirm the wrong row count, then fix it. That one exercise teaches more about SQL logic than a dozen clean examples.
Top comments (0)