Why Filtering Matters
In the real world, you rarely want all the data. You want specific rows that match certain conditions. Maybe you need all customers from Austin, or all products priced between $50 and $200. That's where filtering comes in.
In this lesson, you'll master five powerful filtering techniques: IN (match a list), BETWEEN (match a range), LIKE (pattern matching), IS NULL (find empty values), and how to combine them all. By the end, you'll write precise queries that slice data exactly the way analysts need it.
The IN Operator
The IN operator checks if a value matches any item in a list. Instead of writing multiple OR conditions, IN makes your code cleaner and faster.
Q1: Find Customers in Specific Cities (IN)
Suppose you only want to analyze customers from three cities: Austin, Seattle, and Portland.
──────────────────────────────────────
Jake Martin | Seattle
Mason Lee | Seattle
Charlotte Anderson | Seattle
Lucas White | Austin
Alexander Harris | Austin
(48 rows)
What it does: Returns customers whose city is Austin OR Seattle OR Portland. This is equivalent to:
WHERE city = 'Austin' OR city = 'Seattle' OR city = 'Portland' — verbose and error-prone.
IN is much cleaner, faster, and easier to maintain.
Q2: NOT IN — Exclude a List (NOT IN)
Now let's find customers NOT in those three cities:
──────────────────────────────────────
Tyler White | Denver
Sneha Reddy | Miami
Isabella Thomas | Chicago
Tyler Lee | Miami
Arjun Miller | Denver
(70 rows)
What it does: Returns all customers except those in Austin or Seattle. Perfect for exclusion lists.
Pro Tip: IN can also check numbers. Example: WHERE product_id IN (101, 105, 202) finds products with those IDs.
BETWEEN for Ranges
BETWEEN finds values within a range (inclusive on both ends). It works with dates, numbers, and text. Perfect for "all sales in Q4" or "products between $50 and $200."
Q3: Find Customers Joined in Second Half of 2024 (BETWEEN dates)
Let's find all customers who joined between June and December 2024:
──────────────────────────────────────
Tyler White | 2024-12-29
Isabella Thomas | 2024-10-06
Lucas White | 2024-10-06
Mason Wilson | 2024-11-24
Vikram Verma | 2024-08-09
(37 rows)
What it does: Returns customers who joined on or after June 1, 2024 AND on or before December 31, 2024. BETWEEN is inclusive on both ends.
Q4: Find Products in a Price Range (BETWEEN numbers)
Find all products priced between $50 and $200:
──────────────────────────────────────
Pull-Up Bar | 53.91
Dumbbell 5kg Pair | 55.43
Art Supply Kit | 59.51
Bluetooth Speaker | 61.07
Winter Gloves | 64.82
(34 rows)
What it does: Returns all products with price >= 50 AND price <= 200, sorted by price. BETWEEN is useful for bucketing and range queries.
Remember: BETWEEN includes both endpoints. To exclude the endpoints, use WHERE price > 50 AND price < 200 instead.
Pattern Matching with LIKE
LIKE finds text that matches a pattern. Use wildcards % (any number of characters) and _ (exactly one character) to search flexibly.
Q5: Find Products Containing "Wireless" (LIKE %)
Find all products with "Wireless" anywhere in the name:
──────────────────────────
Wireless Headphones
Mouse Wireless
(2 rows)
What it does: % matches zero or more characters. name LIKE '%Wireless%' finds "Wireless" anywhere in the name.
LIKE 'Wireless%'— Starts with "Wireless"LIKE '%Wireless'— Ends with "Wireless"LIKE '%Wireless%'— Contains "Wireless" anywhere (what we used)
Q6: Find Exactly 5-Character Names (LIKE _)
Find products with exactly 5 characters in the name:
──────────────────────────
(0 rows)
What it does: _ matches exactly one character. Five underscores match exactly 5 characters. No products in ShopStream have exactly 5-character names.
Q7: Case-Insensitive Search (ILIKE) — PostgreSQL
PostgreSQL has ILIKE, which is case-insensitive (find "Bluetooth", "bluetooth", "BLUETOOTH" — they're all the same):
──────────────────────────
Bluetooth Speaker
(1 row)
What it does: ILIKE ignores case. '%bluetooth%' (lowercase) matches "Bluetooth Speaker" (mixed case). Extremely useful for user searches where people type anything.
LIKE vs ILIKE: LIKE is case-sensitive (SQL standard). ILIKE is PostgreSQL-specific and case-insensitive. For other databases (MySQL, SQL Server), use LOWER() instead: WHERE LOWER(name) LIKE '%bluetooth%'.
Handling NULLs
NULL is not a value — it's the absence of a value. You can't use = NULL or != NULL. Instead, use IS NULL and IS NOT NULL.
Q8: Find Customers with Missing Email (IS NULL)
Find all customers who haven't provided an email:
──────────────────────────
(0 rows)
What it does: Returns rows where the email column is empty (NULL). All ShopStream customers have emails, so this returns 0 rows.
Q9: Find Customers with City (IS NOT NULL)
Find customers who have a city on file:
──────────────────────────────────────
Jake Martin | Seattle
Tyler White | Denver
Sneha Reddy | Miami
Isabella Thomas | Chicago
Mason Lee | Seattle
(100 rows)
What it does: Returns all customers with a non-empty city. All 100 ShopStream customers have cities.
Common Mistake: Writing WHERE email = NULL or WHERE email != NULL returns 0 rows because NULL doesn't equal anything (not even itself). Always use IS NULL or IS NOT NULL.
Combining Filters
Real queries combine multiple filters with AND and OR. Mix IN, BETWEEN, LIKE, and NULL checks to build powerful, precise queries.
Q10: Complex Filter (All Techniques Combined)
Find electronics and books priced $20-$150 with a rating of 4.0 or higher:
─────────────────────────────────────────────────────
Bluetooth Speaker | 61.07 | Electronics
Atomic Habits | 42.52 | Books
Deep Work | 31.37 | Books
Python Crash Course | 29.74 | Books
(4 rows)
What it does:
category IN ('Electronics', 'Books')— Only these two categories.price BETWEEN 20 AND 150— Price in range.avg_rating IS NOT NULL— Product has a rating (not missing data).avg_rating >= 4.0— Rating is 4.0 or higher.ORDER BY price DESC— Most expensive first.
Q11: NOT LIKE — Exclude a Pattern (NOT LIKE)
Find all products that do NOT contain "Cable" in the name:
──────────────────────────────────────
Wireless Headphones
Bluetooth Speaker
USB-C Hub
Laptop Stand
Phone Charger 65W
(70 rows)
What it does: Returns all 70 products that don't have "Cable" anywhere in their name. Useful for filtering out unwanted product types.
Tip: Use parentheses when combining AND/OR to be explicit: WHERE (category = 'A' OR category = 'B') AND price > 50 is clearer than without parentheses.
Challenge
Write a query to find all high-value customers:
- Customers from Denver, Miami, or Chicago (use IN)
- Who joined between January 1, 2024 and June 30, 2024 (use BETWEEN)
- Whose name contains "a" or "A" (use ILIKE or LIKE)
Return their name, city, and joined_date, ordered by joined_date.
Here's the structure to follow:
Test Your Knowledge
Filtering unlocked. Real analysis starts now.
Join the Best AI Program: The AI-Era Data Engineer — 5 months, fully online, with placement assistance. Week 1 is free.
Explore The AI-Era Data Engineer →