Filtering with WHERE

SELECT without a filter returns every row in the table. WHERE is how you cut that down to just the rows that match a condition, same table as the last lesson, but now you can ask for just the Engineering employees, or just the ones earning over $90,000.

Learning Objectives

Filter rows with comparison operators (=, >, <, etc.).
Combine multiple conditions with AND, OR, and NOT.
Use BETWEEN, IN, and LIKE for common filtering patterns.

All Rows

Every row in the table

WHERE Condition

Checked one row at a time

Matching Rows

Only these are returned

Comparison Operators

Operator	Meaning	Example
=	equal to	department = 'Engineering'
<> or !=	not equal to	department <> 'Research'
> or <	greater than / less than	salary > 90000
>= or <=	greater-or-equal / less-or-equal	salary >= 90000

MySQL / PostgreSQL / SQLite / SQL Server

SELECT first_name, salary
FROM employees
WHERE salary > 90000;

-- Result:
-- first_name | salary
-- Ada        | 95000
-- Grace      | 98000

Combining Conditions

AND requires both sides to be true. OR requires at least one side to be true. NOT reverses a condition. When mixing AND and OR in one query, use parentheses to make the grouping explicit, don't rely on remembering which one SQL evaluates first.

MySQL / PostgreSQL / SQLite / SQL Server

SELECT first_name, department, salary
FROM employees
WHERE department = 'Engineering' AND salary > 95000;

-- Result:
-- first_name | department  | salary
-- Grace      | Engineering | 98000

BETWEEN, IN, and LIKE

In a LIKE pattern, % matches any number of characters (including none), and _ matches exactly one character. 'H%' matches any text starting with H, Hopper and Hamilton both match. PostgreSQL alone adds ILIKE as a case-insensitive version, LIKE itself is case-sensitive there by default.

Pattern	What it does	Example
BETWEEN a AND b	value falls in a range, inclusive	salary BETWEEN 85000 AND 95000
IN (a, b, c)	value matches any in a list	department IN ('Engineering', 'Research')
LIKE 'pattern'	text matching with wildcards	last_name LIKE 'H%'

MySQL / SQLite / SQL Server

SELECT first_name, last_name
FROM employees
WHERE last_name LIKE 'H%';

-- Result:
-- first_name | last_name
-- Grace      | Hopper
-- Margaret   | Hamilton

PostgreSQL (case-insensitive variant)

SELECT first_name, last_name
FROM employees
WHERE last_name ILIKE 'h%';

-- Same result as LIKE 'H%', ILIKE ignores case entirely

Frequently Asked Questions

Why doesn't WHERE salary = NULL ever return rows?

NULL means "unknown value," and in SQL, unknown compared to anything, including another NULL, is never true. To check for NULL you need WHERE salary IS NULL instead of =. This gets covered in full in the NULL lesson.

Is LIKE case-sensitive?

It depends on the database. PostgreSQL's LIKE is case-sensitive by default (it has a separate ILIKE for case-insensitive matching); MySQL and SQLite are case-insensitive by default for standard text columns. Always test this assumption against whichever database you're actually using.

Can I filter on a column that isn't in my SELECT list?

Yes. WHERE operates on the table's columns directly, independent of which columns you chose to display in SELECT.

Common Beginner Mistakes

Writing WHERE some_column = NULL expecting it to match empty values

It never matches anything. NULL means "unknown," and SQL treats unknown compared to anything, even another NULL, as not true. The correct form is WHERE some_column IS NULL.

Mixing AND and OR without parentheses and assuming left-to-right evaluation

SQL evaluates AND before OR, the same way multiplication is evaluated before addition in math. WHERE a OR b AND c means WHERE a OR (b AND c), not WHERE (a OR b) AND c. Always add explicit parentheses when a query mixes both.

Assuming LIKE is case-insensitive on every database

PostgreSQL's LIKE is case-sensitive by default, 'hopper' won't match 'Hopper' unless you switch to ILIKE. MySQL and SQLite are case-insensitive by default for standard text columns. A pattern that works while testing on one database can silently stop matching rows on another.

Using = for a list of acceptable values instead of IN

WHERE department = 'Engineering' OR department = 'Research' works, but it's repetitive and easy to mistype as the list grows. WHERE department IN ('Engineering', 'Research') says the same thing more clearly and scales to any number of values.

Best Practices

Use IS NULL / IS NOT NULL for NULL checks, never = NULL or <> NULL.
Add parentheses any time a condition mixes AND and OR, even when you're confident about the default evaluation order.
Reach for IN (...) instead of a chain of OR = comparisons once you're checking against more than two values.
Confirm whether LIKE is case-sensitive on your specific database before relying on it matching mixed-case data.

Interview Questions

Write a query that returns employees earning between 85000 and 95000, inclusive.

SELECT first_name, salary FROM employees WHERE salary BETWEEN 85000 AND 95000; — BETWEEN includes both boundary values, equivalent to salary >= 85000 AND salary <= 95000.

What's the difference between <> and != in SQL?

They mean exactly the same thing, not equal to. <> is the ANSI SQL standard form and works on every major database; != is a widely supported alias that most databases, including MySQL, PostgreSQL, and SQL Server, also accept.

Why is WHERE department = 'Engineering' AND salary > 95000 OR department = 'Research' probably a bug?

Without parentheses, AND binds tighter than OR, so this reads as (department = 'Engineering' AND salary > 95000) OR department = 'Research', returning every Research employee regardless of salary, likely not the intent. The fix is explicit parentheses around whichever grouping was actually meant.

How would you find employees whose last name starts with 'H' but isn't exactly 'Hopper'?

SELECT first_name, last_name FROM employees WHERE last_name LIKE 'H%' AND last_name <> 'Hopper'; — LIKE handles the pattern match, and <> excludes the one specific value.

Summary

This lesson covered filtering rows with comparison operators, combining conditions with AND, OR, and NOT, and the common filtering patterns BETWEEN, IN, and LIKE, including where LIKE's case sensitivity differs between databases.

What's Next?

The next lesson covers ORDER BY and LIMIT, controlling what order rows come back in and how many you get, the basis of pagination.