Filtering Data with WHERE

Now that you can retrieve data with SELECT, it's time to learn how to filter it! The WHERE clause is like a search filter on a shopping website—it helps you find exactly what you're looking for without sifting through everything. This is one of the most powerful tools in SQL.

📚 What You'll Learn

• How WHERE filters rows based on conditions
• Using comparison operators (=, <>, <, >, <=, >=)
• Filtering text values vs. numbers
• Combining conditions with AND (all must be true)
• Using OR for alternative conditions (any can be true)
• Using IN to match multiple values efficiently
• Writing range filters with BETWEEN
• Understanding NULL values (why they're tricky!)
• Common mistakes and how to avoid them
• Hands-on practice exercises

1. What Does WHERE Do?

The WHERE clause filters rows based on conditions you specify. Without WHERE, SELECT returns every row in the table. With WHERE, you get only the rows that match your criteria.

Basic Syntax

SELECT column1, column2
FROM table_name
WHERE condition;

The condition is what you're checking for. Let's explore different types of conditions!

2. Comparison Operators

Comparison operators let you compare values. Here are the six essential operators:

Example: Finding Students with High GPAs

SELECT name, gpa
FROM students
WHERE gpa >= 3.7;

This query says: "Show me the name and GPA of students whose GPA is 3.7 or higher."

Result:

Notice Bob Smith (GPA 3.6) is not included because 3.6 is less than 3.7.

3. Filtering Text Values

When filtering text (strings), you must use single quotes around the value:

SELECT name, major
FROM students
WHERE major = 'Computer Science';

Text is Case-Sensitive (Usually)

In most databases, text comparisons are case-sensitive:

-- Finds exact match
WHERE major = 'Biology'

-- Won't match
WHERE major = 'biology'

4. Using AND for Multiple Conditions

AND means both conditions must be true. Think of it as narrowing your search:

SELECT name, major, gpa
FROM students
WHERE major = 'Computer Science' AND gpa >= 3.5;

This query says: "Show me Computer Science majors who ALSO have a GPA of 3.5 or higher."

How AND Works (Logic Table)

Chaining Multiple AND Conditions

You can chain as many AND conditions as you need:

SELECT name, major, gpa, year
FROM students
WHERE major = 'Computer Science' 
  AND gpa >= 3.5 
  AND year = 2024;

"Show me 2024 Computer Science majors with GPA ≥ 3.5." All three conditions must be true!

5. Using OR for Alternative Conditions

OR means at least one condition must be true. Think of it as widening your search:

SELECT name, major
FROM students
WHERE major = 'Computer Science' OR major = 'Mathematics';

This query says: "Show me students who are EITHER Computer Science OR Mathematics majors."

How OR Works (Logic Table)

Combining AND with OR (Be Careful!)

When mixing AND and OR, use parentheses to be clear about order:

-- Find CS or Math majors who also have GPA >= 3.5
SELECT name, major, gpa
FROM students
WHERE (major = 'Computer Science' OR major = 'Mathematics') 
  AND gpa >= 3.5;

Without parentheses, the database might interpret it differently! The parentheses say: "First check if they're CS or Math, THEN check the GPA."

6. Using IN for Lists of Values

Instead of writing multiple OR conditions, use IN with a list:

The Long Way (using OR):

SELECT name, major
FROM students
WHERE major = 'Computer Science' 
   OR major = 'Mathematics' 
   OR major = 'Physics' 
   OR major = 'Engineering';

The Short Way (using IN):

SELECT name, major
FROM students
WHERE major IN ('Computer Science', 'Mathematics', 'Physics', 'Engineering');

Both do the same thing, but IN is cleaner and easier to read! Think of IN as "check if the value is in this list."

NOT IN (Exclusion)

Use NOT IN to exclude values from the list:

SELECT name, major
FROM students
WHERE major NOT IN ('Art History', 'Music', 'Theatre');

"Show me students who are NOT majoring in Art History, Music, or Theatre."

7. Writing Range Filters with BETWEEN

BETWEEN is perfect for checking if a value falls within a range (inclusive):

The Long Way:

SELECT name, gpa
FROM students
WHERE gpa >= 3.0 AND gpa <= 3.7;

The Short Way (using BETWEEN):

SELECT name, gpa
FROM students
WHERE gpa BETWEEN 3.0 AND 3.7;

Both include the endpoints (3.0 and 3.7). BETWEEN is inclusive!

BETWEEN Works with Dates Too!

SELECT name, enrollment_date
FROM students
WHERE enrollment_date BETWEEN '2023-01-01' AND '2023-12-31';

"Show me students who enrolled anytime in 2023."

8. Understanding NULL Values (The Tricky Part)

NULL means "no value" or "unknown"—not zero, not an empty string, but literally nothing. NULL is special and behaves differently!

This Does NOT Work:

-- ❌ WRONG - this won't find NULL values!
SELECT name, email
FROM students
WHERE email = NULL;

This DOES Work:

-- ✅ CORRECT - use IS NULL
SELECT name, email
FROM students
WHERE email IS NULL;

Similarly, to find rows that are NOT NULL:

SELECT name, email
FROM students
WHERE email IS NOT NULL;

9. Common Beginner Mistakes

Mistake #1: Using = Instead of IS NULL

-- ❌ WRONG
WHERE email = NULL

-- ✅ CORRECT
WHERE email IS NULL

Mistake #2: Forgetting Quotes Around Text

-- ❌ WRONG
WHERE major = Biology

-- ✅ CORRECT
WHERE major = 'Biology'

Mistake #3: Using Quotes Around Numbers

-- ❌ WRONG (usually works but bad practice)
WHERE gpa = '3.8'

-- ✅ CORRECT
WHERE gpa = 3.8

Mistake #4: Incorrect BETWEEN Order

-- ❌ WRONG (returns nothing!)
WHERE gpa BETWEEN 3.7 AND 3.0

-- ✅ CORRECT (lower value first)
WHERE gpa BETWEEN 3.0 AND 3.7

Mistake #5: Forgetting Parentheses with AND/OR

-- ❌ AMBIGUOUS (might not work as expected)
WHERE major = 'CS' OR major = 'Math' AND gpa >= 3.5

-- ✅ CLEAR (parentheses show intent)
WHERE (major = 'CS' OR major = 'Math') AND gpa >= 3.5

10. 🎯 Hands-On Practice Exercises

SELECT *
FROM students
WHERE gpa > 3.8;

SELECT name, email
FROM students
WHERE major = 'Computer Science';

SELECT name, major, gpa
FROM students
WHERE major = 'Biology' AND gpa >= 3.5;

SELECT name, major
FROM students
WHERE major = 'Mathematics' OR major = 'Physics';

SELECT name, major
FROM students
WHERE major IN ('Mathematics', 'Physics');

SELECT name, gpa
FROM students
WHERE gpa BETWEEN 3.0 AND 3.5;

SELECT name, major, gpa
FROM students
WHERE (major = 'Computer Science' OR major = 'Engineering')
  AND gpa > 3.6;

SELECT name, student_id
FROM students
WHERE email IS NULL;

📝 Key Takeaways

• WHERE filters rows based on conditions you specify
• Use comparison operators: =, <>, <, >, <=, >=
• Text values need single quotes: 'Computer Science'
• Numbers don't use quotes: gpa = 3.8
• AND requires all conditions to be true (narrows results)
• OR requires at least one condition to be true (widens results)
• IN is cleaner than multiple OR conditions
• BETWEEN checks inclusive ranges (lower value first!)
• Use IS NULL and IS NOT NULL for missing values
• Use parentheses when mixing AND/OR to be explicit