Lesson 7 — Filtering Rows

WHERE

WHERE filters which rows appear in your results. It supports comparison, range, pattern, list, and null tests — mastering all of them gives you precise control over your data.

SQL — Basic WHERE 
-- Basic comparisons:
SELECT full_name, mark FROM learners WHERE mark >= 50;
SELECT full_name, city FROM learners WHERE city = 'Johannesburg';
SELECT full_name, mark FROM learners WHERE mark <> 50;  -- not equal (<> or !=)
SELECT full_name, age  FROM learners WHERE age < 25;

-- Multiple conditions with AND / OR:
SELECT full_name, mark
FROM   learners
WHERE  mark >= 50 AND city = 'Johannesburg';

SELECT full_name, city
FROM   learners
WHERE  city = 'Cape Town' OR city = 'Durban';

-- NOT:
SELECT full_name FROM learners WHERE NOT city = 'Johannesburg';
SQL — BETWEEN and IN 
-- BETWEEN — inclusive range (same as >= AND <=):
SELECT full_name, mark
FROM   learners
WHERE  mark BETWEEN 60 AND 80;
-- Equivalent to: WHERE mark >= 60 AND mark <= 80

-- BETWEEN with dates:
SELECT full_name, enrolment_date
FROM   learners
WHERE  enrolment_date BETWEEN '2024-01-01' AND '2024-12-31';

-- IN — match any value in a list:
SELECT full_name, course
FROM   learners
WHERE  course IN ('Java', 'Python', 'C#');

-- NOT IN:
SELECT full_name, course
FROM   learners
WHERE  course NOT IN ('SQL', 'Undecided');
SQL — LIKE patterns 
-- LIKE — pattern matching:
-- % matches zero or more characters
-- _ matches exactly one character

SELECT full_name FROM learners
WHERE  full_name LIKE 'T%';         -- starts with T

SELECT full_name FROM learners
WHERE  full_name LIKE '%Mokoena';   -- ends with Mokoena

SELECT full_name FROM learners
WHERE  full_name LIKE '%an%';       -- contains 'an' anywhere

SELECT email FROM learners
WHERE  email LIKE '%@gmail.com';    -- Gmail addresses

SELECT phone FROM learners
WHERE  phone LIKE '082_______';     -- 082 followed by exactly 7 digits

-- Case sensitivity:
-- MySQL LIKE is case-insensitive by default for latin charset
-- PostgreSQL LIKE is case-sensitive; use ILIKE for case-insensitive
SQL — NULL checks 
-- NULL checks:
SELECT full_name FROM learners WHERE mark IS NULL;     -- not yet marked
SELECT full_name FROM learners WHERE mark IS NOT NULL; -- has a mark

-- NEVER use = NULL or != NULL — they never return rows:
-- WHERE mark = NULL   -- always returns 0 rows (NULL is not equal to anything)
-- WHERE mark != NULL  -- always returns 0 rows

-- COALESCE — use a default when NULL:
SELECT full_name,
       COALESCE(mark, 0) AS mark_or_zero
FROM   learners;

Practice Task

Your Turn

Write queries: (1) learners with mark between 70 and 90; (2) learners from Johannesburg or Pretoria; (3) learners enrolled in Java OR Python but NOT SQL; (4) learners whose name starts with 'S'; (5) learners who have no city on record (IS NULL); (6) learners aged 18-25 with a mark above 60 and from Gauteng.

Common Mistakes

  • WHERE mark = NULL returns nothing — always use IS NULL.
  • LIKE '%text%' cannot use an index efficiently — slow on large tables.
  • NOT IN with a subquery that can return NULL — the result is always empty. Use NOT EXISTS instead.
  • AND binds tighter than OR — use parentheses: WHERE (city='JHB' OR city='CPT') AND mark >= 50 not WHERE city='JHB' OR city='CPT' AND mark >= 50.

Professional Tip

BETWEEN is inclusive on both ends. BETWEEN 60 AND 80 includes both 60 and 80. If you want to exclude an endpoint, use explicit >= and < comparisons.

Mini Quiz

How do you correctly check for NULL in a WHERE clause?