Chapter 7: Filtering and Sorting Advanced
Now we’re going to level up our WHERE clause and make our SELECT queries super powerful!
In the last chapter we learned basic filtering. Today we’ll master:
- Combining conditions with AND, OR, NOT
- Removing duplicates with DISTINCT
- Advanced pattern matching with LIKE and wildcards (% and _)
- Handling missing values properly with IS NULL and IS NOT NULL
Let’s make sure we have the same sample data in the students table. Run this once to reset and populate it nicely (copy-paste all at once):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
TRUNCATE TABLE students; INSERT INTO students (full_name, phone, email, date_of_birth, gender, join_date, is_active, notes) VALUES ('Priya Sharma', '9876543210', 'priya.sharma@gmail.com', '2008-05-12', 'F', '2025-01-05', TRUE, 'Very regular'), ('Rahul Patel', '9123456789', 'rahul.p@gmail.com', '2007-11-20', 'M', '2025-01-10', TRUE, NULL), ('Sneha Joshi', '9988776655', 'sneha.joshi@yahoo.com', '2009-03-03', 'F', '2025-02-15', TRUE, 'Needs extra classes'), ('Aarav Khan', '9765432109', 'aarav.khan@outlook.com', '2006-08-15', 'M', '2025-03-01', TRUE, NULL), ('Kavya Iyer', '9898989898', 'kavya.iyer@gmail.com', '2010-04-22', 'F', '2025-03-10', TRUE, NULL), ('Rohan Mehta', '9012345678', 'rohan.mehta@live.com', '2005-12-01', 'M', '2024-12-20', FALSE, 'Left due to relocation'), ('Ananya Gupta', '9223344556', 'ananya.gupta@gmail.com', '2009-07-18', 'F', '2025-02-28', TRUE, NULL), ('Vikram Singh', NULL, 'vikram.singh@proton.me', '2007-09-09', 'M', '2025-01-25', TRUE, 'Waiting for phone number'), ('Meera Desai', '9876541234', 'meera.desai@hotmail.com', '2008-02-14', 'F', '2025-04-01', TRUE, NULL); |
Now let’s dive in!
1. Logical Operators: AND, OR, NOT
(How to combine multiple conditions)
| Operator | Meaning | When to use |
|---|---|---|
| AND | Both conditions must be true | Narrow down results (strict filter) |
| OR | At least one condition true | Broaden results (any of these) |
| NOT | Reverse the condition | Exclude something |
Important rule: Use parentheses () when mixing AND and OR — otherwise MySQL follows AND first, then OR (like multiplication before addition).
Examples – Try these!
A. Active female students who joined in 2025
|
0 1 2 3 4 5 6 7 8 9 10 |
SELECT full_name, gender, join_date, is_active FROM students WHERE gender = 'F' AND is_active = TRUE AND join_date >= '2025-01-01'; |
B. Students who are either male OR joined after February 2025
|
0 1 2 3 4 5 6 7 8 9 |
SELECT full_name, gender, join_date FROM students WHERE gender = 'M' OR join_date > '2025-02-28'; |
C. Active students who are NOT male (i.e., female or other)
|
0 1 2 3 4 5 6 7 8 9 |
SELECT full_name, gender, is_active FROM students WHERE is_active = TRUE AND NOT gender = 'M'; |
D. Careful with AND + OR – Use parentheses! Wrong (MySQL will read AND first):
|
0 1 2 3 4 5 6 7 8 9 |
SELECT full_name, gender, join_date FROM students WHERE gender = 'F' OR gender = 'M' AND join_date > '2025-01-01'; -- This shows ALL females + only males who joined after Jan 2025 |
Correct way:
|
0 1 2 3 4 5 6 7 8 9 |
SELECT full_name, gender, join_date FROM students WHERE (gender = 'F' OR gender = 'M') AND join_date > '2025-01-01'; |
2. DISTINCT Keyword – Remove Duplicate Rows
Syntax:
|
0 1 2 3 4 5 6 7 |
SELECT DISTINCT column1, column2, ... FROM table_name; |
Example – Get unique genders and join years
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Without DISTINCT (shows duplicates) SELECT gender FROM students; -- With DISTINCT SELECT DISTINCT gender FROM students; -- Result: only 'F' and 'M' (no repeats) |
Multiple columns:
|
0 1 2 3 4 5 6 7 8 9 |
SELECT DISTINCT YEAR(join_date) AS join_year, gender FROM students ORDER BY join_year; -- Shows unique combinations of year + gender |
3. Pattern Matching with LIKE and Wildcards (% and _)
LIKE is used for text pattern matching.
| Wildcard | Meaning | Example |
|---|---|---|
| % | Zero or more characters (anything) | ‘Priya%’ → starts with Priya |
| _ | Exactly one character | ‘P_iya’ → P + any char + iya |
Examples:
A. Names starting with ‘P’ or ‘R’
|
0 1 2 3 4 5 6 7 8 |
SELECT full_name FROM students WHERE full_name LIKE 'P%' OR full_name LIKE 'R%'; |
B. Names ending with ‘a’
|
0 1 2 3 4 5 6 7 8 |
SELECT full_name FROM students WHERE full_name LIKE '%a'; |
C. Names containing ‘Sh’ anywhere
|
0 1 2 3 4 5 6 7 8 |
SELECT full_name FROM students WHERE full_name LIKE '%Sh%'; |
D. Emails ending with @gmail.com
|
0 1 2 3 4 5 6 7 8 |
SELECT full_name, email FROM students WHERE email LIKE '%@gmail.com'; |
E. Phone numbers starting with 98 and exactly 10 digits (using _ )
|
0 1 2 3 4 5 6 7 8 |
SELECT full_name, phone FROM students WHERE phone LIKE '98________'; |
4. NULL Handling – IS NULL and IS NOT NULL
(NEVER use = NULL or != NULL – it won’t work!)
NULL means “value is missing / unknown”.
Correct way:
| What you want | Correct Syntax | Wrong (does NOT work) |
|---|---|---|
| Phone is missing | phone IS NULL | phone = NULL |
| Phone exists | phone IS NOT NULL | phone != NULL |
Examples:
A. Students who have NOT provided phone number
|
0 1 2 3 4 5 6 7 8 9 |
SELECT full_name, phone FROM students WHERE phone IS NULL; -- Shows Vikram Singh |
B. Students who HAVE provided phone number
|
0 1 2 3 4 5 6 7 8 |
SELECT full_name, phone FROM students WHERE phone IS NOT NULL; |
C. Students who have notes (notes is NOT NULL)
|
0 1 2 3 4 5 6 7 8 |
SELECT full_name, notes FROM students WHERE notes IS NOT NULL; |
That’s it for Chapter 7! 🎉 You can now write very smart, precise queries that answer almost any business question!
Homework for today (do it right now – super important!) Run these 4 queries and paste the output of each here:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
-- 1. Active students who are female OR joined before February 2025 SELECT full_name, gender, join_date, is_active FROM students WHERE is_active = TRUE AND (gender = 'F' OR join_date < '2025-02-01') ORDER BY join_date ASC; -- 2. Unique join years from the table SELECT DISTINCT YEAR(join_date) AS join_year FROM students ORDER BY join_year; -- 3. Students whose name contains 'a' or 'i' (anywhere in name) SELECT full_name FROM students WHERE full_name LIKE '%a%' OR full_name LIKE '%i%' ORDER BY full_name ASC; -- 4. Students who have NOT provided phone number and are active SELECT full_name, phone, is_active FROM students WHERE phone IS NULL AND is_active = TRUE; |
