SQL — Where

The WHERE clause

WHERE filters rows based on a condition. Only rows that evaluate to TRUE are included:

SELECT * FROM students WHERE age > 20;

Comparison operators

SELECT * FROM students WHERE age = 20;       -- equal
SELECT * FROM students WHERE age != 20;      -- not equal
SELECT * FROM students WHERE age <> 20;      -- not equal (alternative)
SELECT * FROM students WHERE age > 20;       -- greater than
SELECT * FROM students WHERE age < 20;       -- less than
SELECT * FROM students WHERE age >= 20;      -- greater than or equal
SELECT * FROM students WHERE age <= 20;      -- less than or equal

AND — combining conditions

SELECT * FROM students
WHERE age >= 18 AND grade = 'A';

SELECT * FROM students
WHERE age > 18 AND age < 25 AND grade IN ('A', 'B');

All conditions must be TRUE for the row to be included.

OR — either condition

SELECT * FROM students
WHERE grade = 'A' OR grade = 'B';

SELECT * FROM students
WHERE age < 18 OR age > 65;

At least one condition must be TRUE.

NOT — reversing conditions

SELECT * FROM students WHERE NOT grade = 'A';
SELECT * FROM students WHERE NOT age > 20;
SELECT * FROM students WHERE grade NOT IN ('A', 'B');

Operator precedence

AND has higher precedence than OR:

-- This means: (age > 18) AND (grade = 'A' OR grade = 'B')
SELECT * FROM students
WHERE age > 18 AND grade = 'A' OR grade = 'B';

-- Use parentheses for clarity
SELECT * FROM students
WHERE age > 18 AND (grade = 'A' OR grade = 'B');

Always use parentheses when combining AND and OR. It prevents ambiguity.

BETWEEN — range filter

SELECT * FROM students
WHERE age BETWEEN 18 AND 25;

-- Equivalent to:
SELECT * FROM students
WHERE age >= 18 AND age <= 25;

BETWEEN is inclusive — both endpoints are included.

IN — matching a list

SELECT * FROM students
WHERE grade IN ('A', 'B', 'C');

-- Equivalent to:
SELECT * FROM students
WHERE grade = 'A' OR grade = 'B' OR grade = 'C';

-- NOT IN
SELECT * FROM students
WHERE grade NOT IN ('F', 'D');

IN is cleaner than multiple OR conditions.

LIKE — pattern matching

-- Starts with 'A'
SELECT * FROM students WHERE name LIKE 'A%';

-- Ends with 'son'
SELECT * FROM students WHERE name LIKE '%son';

-- Contains 'an'
SELECT * FROM students WHERE name LIKE '%an%';

-- Exactly 5 characters
SELECT * FROM students WHERE name LIKE '_____';

Wildcards:

Pattern	Meaning
%	Any number of characters (including none)
_	Exactly one character

IS NULL — checking for missing values

SELECT * FROM students WHERE email IS NULL;
SELECT * FROM students WHERE email IS NOT NULL;

NULL = NULL returns NULL (not TRUE). Always use IS NULL or IS NOT NULL.

EXISTS — subquery check

SELECT * FROM students s
WHERE EXISTS (
    SELECT 1 FROM enrollments e
    WHERE e.student_id = s.id
);

EXISTS returns TRUE if the subquery returns any rows. It's often faster than IN for large datasets.

ANY and ALL

-- Any student older than the youngest student in grade 'A'
SELECT * FROM students
WHERE age > ANY (SELECT age FROM students WHERE grade = 'A');

-- All students older than every student in grade 'A'
SELECT * FROM students
WHERE age > ALL (SELECT age FROM students WHERE grade = 'A');

CASE — conditional logic in WHERE

SELECT name, age,
    CASE
        WHEN age < 13 THEN 'Child'
        WHEN age < 18 THEN 'Teenager'
        WHEN age < 65 THEN 'Adult'
        ELSE 'Senior'
    END AS age_group
FROM students;

CASE evaluates conditions in order and returns the first match.

Performance tips

-- SARGable — uses index efficiently
SELECT * FROM students WHERE age > 20;

-- Non-SARGable — can't use index
SELECT * FROM students WHERE age + 1 > 20;

-- Avoid functions on indexed columns in WHERE
SELECT * FROM students WHERE YEAR(enrollment_date) = 2024;
-- Better: use range
SELECT * FROM students
WHERE enrollment_date >= '2024-01-01'
  AND enrollment_date < '2025-01-01';

Common mistakes

-- WRONG: NULL comparison
SELECT * FROM students WHERE age = NULL;  -- returns nothing!

-- CORRECT
SELECT * FROM students WHERE age IS NULL;

-- WRONG: using OR when IN is better
SELECT * FROM students
WHERE grade = 'A' OR grade = 'B' OR grade = 'C';

-- BETTER
SELECT * FROM students WHERE grade IN ('A', 'B', 'C');

Mini Practice

1. Find all students whose age is between 20 and 30
2. Find all students whose name starts with 'A' and ends with 'a'
3. Find all students with a NULL email address
4. Find students in grades A or B who are older than 20
5. Use a CASE expression to categorize students by age group

Next: inserting, updating, and deleting data →