Lesson 05 ~25 min read

Filtering and Sorting Results

Retrieving all rows from a large table is almost never what you want. WHERE, comparison operators, and ORDER BY give you precise control over which rows you get and in what order they appear.

The WHERE Clause

WHERE filters rows — only rows where the condition evaluates to TRUE are included in the result. Without WHERE, SELECT returns every row in the table. With WHERE, you get only the rows that match.

WHERE comes after FROM and before ORDER BY or LIMIT.

sql

-- Only active students
SELECT full_name, email
FROM students
WHERE is_active = TRUE;

-- Students enrolled in 2025
SELECT full_name, enrolled_on
FROM students
WHERE enrolled_on >= '2025-01-01';

Comparison Operators

WHERE conditions use comparison operators to test values. These work on numbers, text, and dates.

=

Equal to. WHERE country = 'Malawi'. Note: SQL uses single = for comparison, not == like Python or JavaScript.

!= or <>

Not equal to. WHERE status != 'inactive'. Both forms work; != is more widely used.

> < >= <=

Greater than, less than, greater or equal, less or equal. Work on numbers and dates. WHERE age >= 18.

BETWEEN

Tests if a value falls within a range (inclusive). WHERE age BETWEEN 18 AND 25 — equivalent to age >= 18 AND age <= 25.

IN

Tests if a value matches any in a list. WHERE city IN ('Blantyre', 'Lilongwe', 'Mzuzu'). Cleaner than multiple OR conditions.

LIKE

Pattern matching for text. % matches any sequence of characters. WHERE name LIKE 'P%' matches names starting with P. WHERE email LIKE '%@gmail.com' matches Gmail addresses.

IS NULL

Tests for NULL (missing) values. Never use = NULL — it won't work. Always use IS NULL or IS NOT NULL.

sql — operators in practice

-- Students from specific cities
SELECT full_name, city FROM students
WHERE city IN ('Blantyre', 'Lilongwe');

-- Names starting with 'A'
SELECT full_name FROM students
WHERE full_name LIKE 'A%';

-- Students enrolled within a date range
SELECT full_name, enrolled_on FROM students
WHERE enrolled_on BETWEEN '2025-01-01' AND '2025-06-30';

-- Students with no email on file
SELECT full_name FROM students
WHERE email IS NULL;

AND, OR, NOT

Combine multiple conditions using logical operators. AND requires both conditions to be true. OR requires at least one. NOT inverts a condition. Use parentheses to control evaluation order.

sql

-- AND: both must be true
SELECT full_name FROM students
WHERE is_active = TRUE
  AND enrolled_on >= '2025-01-01';

-- OR: either can be true
SELECT full_name, city FROM students
WHERE city = 'Blantyre'
   OR city = 'Lilongwe';

-- NOT: invert the condition
SELECT full_name FROM students
WHERE NOT is_active = TRUE;

-- Parentheses control precedence (AND binds tighter than OR)
SELECT full_name FROM students
WHERE (city = 'Blantyre' OR city = 'Lilongwe')
  AND is_active = TRUE;

ORDER BY — Sorting Results

ORDER BY sorts the result set by one or more columns. ASC (ascending) is the default. DESC (descending) reverses the order.

sql

-- Alphabetical by name (A → Z)
SELECT full_name, enrolled_on
FROM students
ORDER BY full_name ASC;

-- Most recent enrolments first
SELECT full_name, enrolled_on
FROM students
ORDER BY enrolled_on DESC;

-- Multiple sort columns: city A→Z, then name A→Z within each city
SELECT full_name, city, enrolled_on
FROM students
ORDER BY city ASC, full_name ASC;

-- Putting it all together
SELECT full_name, city, enrolled_on
FROM students
WHERE is_active = TRUE
ORDER BY enrolled_on DESC
LIMIT 10;

// Knowledge Check

You want to find all students whose name starts with the letter "B". Which WHERE clause is correct?