Filtering Data with WHERE Clause
Now that you've learned the basic structure of SQL queries, let's dive into one of the most powerful features: filtering data with the WHERE clause. We'll use the University tables to move from simply viewing raw records to answering specific questions like “Who enrolled in 2024?” or “Which courses carry four credits?”
Learning Goals
- Understand the purpose and syntax of the WHERE clause
- Learn to use comparison operators for filtering
- Master pattern matching with LIKE and wildcards
- Combine multiple conditions with AND, OR, and NOT
- Handle NULL values properly in filters
Understanding the WHERE Clause
The WHERE clause allows you to filter records in your SELECT statements, returning only the rows that meet specific conditions. Think of it as asking a precise question rather than getting all the information at once.
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Comparison Operators
SQL provides various operators to compare values in your WHERE conditions:
-- Equal to
SELECT course_id, course_code, dept_id
FROM course
WHERE dept_id = 1;
-- Greater than
SELECT course_code, course_title, credits
FROM course
WHERE credits > 3;
-- Less than or equal to
SELECT student_id, first_name, enrollment_year
FROM student
WHERE enrollment_year <= 2023;
-- Not equal to (two ways)
SELECT dept_id, dept_name
FROM department
WHERE dept_name <> 'Mathematics';
SELECT dept_id, dept_name
FROM department
WHERE dept_name != 'Mathematics';
When comparing strings, most databases are case-sensitive by default. 'Sales' and 'sales' would be considered different values unless your database is configured otherwise.
Pattern Matching with LIKE
The LIKE operator lets you search for patterns in text data using wildcards:
%- Matches any sequence of characters (zero or more)_- Matches exactly one character
-- Find student first names starting with 'A'
SELECT student_id, first_name, last_name
FROM student
WHERE first_name LIKE 'A%';
-- Find student emails ending with '@students.local'
SELECT student_id, email
FROM student
WHERE email LIKE '%@students.local';
-- Find 5-character course codes that start with 'CS'
SELECT course_code, course_title
FROM course
WHERE course_code LIKE 'CS___';
-- Find instructor last names containing 'an'
SELECT instructor_id, first_name, last_name
FROM instructor
WHERE last_name LIKE '%an%';
Combining Conditions
You can combine multiple conditions using AND, OR, and NOT:
-- AND: Both conditions must be true
SELECT course_code, course_title, credits
FROM course
WHERE dept_id = 1 AND credits >= 4;
-- OR: At least one condition must be true
SELECT student_id, first_name, last_name, dept_id
FROM student
WHERE dept_id = 1 OR dept_id = 2;
-- NOT: Exclude matching records
SELECT instructor_id, first_name, last_name, dept_id
FROM instructor
WHERE NOT dept_id = 1;
-- Complex combinations with parentheses
SELECT e.enroll_id,
s.first_name || ' ' || s.last_name AS student_name,
c.course_code,
e.semester,
e.grade
FROM enrollment AS e
JOIN student AS s ON s.student_id = e.student_id
JOIN course AS c ON c.course_id = e.course_id
WHERE (e.semester = 'Spring 2025' OR e.semester = 'Fall 2024')
AND c.dept_id = 1;
Handling NULL Values
NULL represents missing or unknown data, and it requires special handling:
-- Find students who haven't declared a department yet
SELECT student_id, first_name, last_name
FROM student
WHERE dept_id IS NULL;
-- Find students who have declared a department
SELECT student_id, first_name, last_name
FROM student
WHERE dept_id IS NOT NULL;
-- This WON'T work as expected!
SELECT student_id, first_name, last_name
FROM student
WHERE dept_id = NULL; -- Always returns no results
Never use = NULL or != NULL in your conditions. Always use IS NULL or IS NOT NULL instead. NULL represents an unknown value, so any comparison with NULL (even NULL = NULL) returns NULL, which is treated as false.
BETWEEN and IN Operators
These operators provide convenient ways to specify ranges and multiple values:
-- BETWEEN: Inclusive range
SELECT course_code, course_title, credits
FROM course
WHERE credits BETWEEN 3 AND 4;
-- IN: Match any value in a list
SELECT dept_id, dept_name
FROM department
WHERE dept_name IN ('Computer Science', 'Mathematics', 'Business Administration');
-- NOT IN: Exclude values in a list
SELECT course_code, course_title, instructor_id
FROM course
WHERE instructor_id NOT IN (1, 2);
Common Pitfalls
- Case sensitivity: String comparisons are often case-sensitive
- NULL handling: Forgetting to use IS NULL/IS NOT NULL
- Date formatting: Using incorrect date formats in comparisons
- Operator precedence: AND has higher precedence than OR, use parentheses to clarify
- Floating point precision: Exact equality comparisons with decimal numbers can be unreliable
-- This might not do what you expect!
SELECT course_code, credits, dept_id
FROM course
WHERE dept_id = 1 OR dept_id = 2 AND credits = 4;
-- Use parentheses to make it clear
SELECT course_code, credits, dept_id
FROM course
WHERE (dept_id = 1 OR dept_id = 2) AND credits = 4;
Summary
The WHERE clause is your primary tool for filtering data in SQL. You now know how to:
- Use comparison operators (
=,>,<,<>) on columns likecredits,dept_id, orenrollment_year - Search for patterns with LIKE and wildcards (
%,_) - Combine conditions with AND, OR, and NOT
- Handle NULL values properly with IS NULL/IS NOT NULL
- Use BETWEEN for ranges and IN for multiple values
Mastering these techniques will make your queries much more powerful and precise.
Quiz
Show quiz
- Which operator would you use to find courses whose credit value is between 3 and 4?
- How do you find students whose email addresses end with
@students.local? - What's wrong with this query:
SELECT * FROM student WHERE dept_id = NULL? - How do you find courses that belong to department 1 or 2 and also carry 4 credits?
- Which wildcard character matches exactly one character in a LIKE pattern?
Answers
BETWEEN 3 AND 4WHERE email LIKE '%@students.local'- Should use
IS NULLinstead of= NULL WHERE (dept_id = 1 OR dept_id = 2) AND credits = 4- The underscore
_character