Filtering Data with WHERE

Okay, so you’ve mastered SELECT and you can pull data from tables. That’s great - but here’s the thing: most of the time, you don’t want all the data. You want the right data. That’s where WHERE comes in, and it’s going to change everything about how you think about databases.

Think of WHERE as your database’s search function. Instead of getting everything and then filtering through it yourself, you tell the database exactly what you’re looking for, and it does the hard work for you.

Basic WHERE Syntax

Every WHERE clause follows the same pattern:

1SELECT [columns]
2FROM [table]
3WHERE [condition];

The condition is where the magic happens. You’re telling the database: “Only show me rows where this thing is true.”

Here’s a simple example:

1SELECT first_name, last_name, salary
2FROM employees
3WHERE salary > 60000;

This says: “Show me the names and salaries of employees who make more than $60,000.” The database looks at every row, checks if the salary is greater than 60,000, and only includes rows that pass the test.

Comparison Operators: Your New Best Friends

SQL gives you six basic ways to compare values:

Equal to (=)

1SELECT * FROM employees WHERE department = 'IT';

Find everyone in the IT department. Notice the single quotes around ‘IT’ - that’s how you tell SQL it’s text, not a column name.

Greater than (>)

1SELECT * FROM employees WHERE salary > 70000;

Find everyone making more than $70,000.

Less than (<)

1SELECT * FROM employees WHERE hire_date < '2020-01-01';

Find everyone hired before 2020.

Greater than or equal to (>=)

1SELECT * FROM employees WHERE age >= 25;

Find everyone 25 or older.

Less than or equal to (<=)

1SELECT * FROM employees WHERE salary <= 50000;

Find everyone making $50,000 or less.

Not equal to (<>)

1SELECT * FROM employees WHERE department <> 'Sales';

Find everyone who’s NOT in Sales. Some databases also let you use != instead of <>, but <> is the official SQL standard.

Logical Operators: Combining Conditions

Here’s where things get really powerful. You can combine multiple conditions using logical operators:

AND: Both conditions must be true

1SELECT * FROM employees 
2WHERE department = 'IT' AND salary > 60000;

Find IT employees who make more than $60,000. Both conditions have to be true for a row to be included.

OR: Either condition can be true

1SELECT * FROM employees 
2WHERE department = 'IT' OR department = 'Engineering';

Find employees in either IT or Engineering.

NOT: Flip the condition

1SELECT * FROM employees 
2WHERE NOT department = 'Sales';

Find everyone who’s NOT in Sales. This does the same thing as department <> 'Sales' but reads more naturally.

Combining Multiple Conditions

You can chain together as many conditions as you need:

1SELECT first_name, last_name, department, salary
2FROM employees
3WHERE (department = 'IT' OR department = 'Engineering')
4  AND salary > 70000
5  AND hire_date >= '2021-01-01';

This finds employees who:

• Work in IT OR Engineering, AND
• Make more than $70,000, AND
• Were hired in 2021 or later

Notice the parentheses around the OR condition - that’s important! Without them, SQL might interpret your logic differently than you intended.

Working with NULL Values

Here’s something that trips up even experienced developers: NULL values are special in SQL. NULL means “unknown” or “missing data,” and it behaves differently than you might expect.

You can’t use = with NULL:

1-- This WON'T work:
2SELECT * FROM employees WHERE middle_name = NULL;
3
4-- This WILL work:
5SELECT * FROM employees WHERE middle_name IS NULL;

To find rows with data:

1SELECT * FROM employees WHERE middle_name IS NOT NULL;

NULL in comparisons:

1-- If salary is NULL, this won't match even if you think it should:
2SELECT * FROM employees WHERE salary > 0;
3
4-- To include NULLs in your logic:
5SELECT * FROM employees 
6WHERE salary > 0 OR salary IS NULL;

Think of NULL as “I don’t know.” When SQL asks “Is unknown greater than 0?” the answer isn’t yes or no - it’s “I don’t know,” which SQL treats as false.

Pattern Matching with LIKE

Sometimes you don’t want exact matches - you want to find data that follows a pattern. That’s where LIKE comes in:

The % wildcard matches any number of characters:

1SELECT * FROM employees WHERE first_name LIKE 'J%';

Find everyone whose name starts with ‘J’: John, Jane, Jennifer, etc.

1SELECT * FROM employees WHERE email LIKE '%@gmail.com';

Find everyone with a Gmail address.

1SELECT * FROM employees WHERE last_name LIKE '%son%';

Find anyone with ‘son’ anywhere in their last name: Johnson, Anderson, Paulson, etc.

The _ wildcard matches exactly one character:

1SELECT * FROM employees WHERE phone LIKE '555-___-1234';

Find phone numbers that start with 555-, end with 1234, and have any three digits in the middle.

Real-World Examples

Let’s put this all together with some practical examples:

Find all senior developers:

1SELECT first_name, last_name, salary
2FROM employees
3WHERE department = 'Engineering' 
4  AND salary >= 80000
5  AND title LIKE '%Senior%';

Find employees hired in the last year who need attention:

1SELECT first_name, last_name, hire_date, performance_score
2FROM employees
3WHERE hire_date >= '2023-01-01'
4  AND (performance_score < 3 OR performance_score IS NULL);

Find potential email issues:

1SELECT first_name, last_name, email
2FROM employees
3WHERE email NOT LIKE '%@%' 
4   OR email IS NULL;

Common Beginner Mistakes

Forgetting quotes around text values:

1-- Wrong:
2WHERE department = IT
3
4-- Right:
5WHERE department = 'IT'

Using = instead of IS for NULL:

1-- Wrong:
2WHERE middle_name = NULL
3
4-- Right:
5WHERE middle_name IS NULL

Forgetting parentheses with mixed AND/OR:

1-- This might not do what you expect:
2WHERE department = 'IT' OR department = 'Sales' AND salary > 50000
3
4-- This is clearer:
5WHERE (department = 'IT' OR department = 'Sales') AND salary > 50000

Case sensitivity with LIKE: In some databases, LIKE is case-sensitive. ‘John’ won’t match ‘john’. Check your database’s documentation or use functions like UPPER() or LOWER() to handle this.

Practice Exercises

Try these queries if you have access to a practice database:

1. Find all employees in a specific department
2. Find employees hired after a certain date
3. Find employees whose names start with a specific letter
4. Find employees making between $50,000 and $80,000 (hint: use AND with two conditions)
5. Find employees with missing email addresses

What You’ve Learned

You’ve just mastered the art of asking databases precise questions. WHERE clauses turn your broad SELECT statements into surgical instruments that find exactly the data you need.

Here’s what’s powerful about this: every WHERE condition you write makes your queries faster and more useful. Instead of grabbing thousands of rows and filtering them in your application, you’re letting the database - which is really good at this stuff - do the work for you.

Next up, you’ll learn about sorting and organizing your results, but honestly? You’ve already learned the most important part. Being able to filter data with precision is what separates people who use databases from people who understand them.

The beautiful thing is that these same patterns work in virtually every database system in the world. Master WHERE clauses, and you’ve unlocked a skill that’ll serve you for decades.