The SQL WHERE Clause: Filtering Data with Every Operator

SELECT tells the database which columns you want. WHERE tells it which rows qualify. It goes right after FROM (and before GROUP BY, if you use one), and every example below runs against the Employees table in the SQL Playground.

Comparison operators

SELECT first_name, salary
FROM Employees
WHERE salary >= 100000;

Combining conditions: AND, OR, NOT

AND requires both sides to be true; OR requires at least one. When you mix them, use parentheses — AND is evaluated before OR, which trips people up:

-- Active employees who are either in Engineering (10) OR earn over 120k
SELECT first_name, dept_id, salary
FROM Employees
WHERE active = 1
  AND (dept_id = 10 OR salary > 120000);

Without the parentheses, active = 1 AND dept_id = 10 OR salary > 120000 would also return inactive employees who happen to earn over 120k — probably not what you meant.

Matching a list with IN

Instead of chaining several ORs, use IN to check against a list of values:

SELECT first_name, dept_id
FROM Employees
WHERE dept_id IN (10, 20, 30);

Add NOT to invert it: WHERE dept_id NOT IN (10, 20, 30).

A range with BETWEEN

BETWEEN is inclusive on both ends — the boundary values themselves are included in the result:

SELECT first_name, salary
FROM Employees
WHERE salary BETWEEN 70000 AND 100000;

Pattern matching with LIKE

LIKE matches text patterns using two wildcards: % (any number of characters, including zero) and _ (exactly one character).

WHERE email LIKE '%@example.com'   -- ends with this domain
WHERE first_name LIKE 'A%'         -- starts with A
WHERE first_name LIKE '%an%'       -- contains "an" anywhere

Whether LIKE cares about upper and lower case depends on the database. The playground on this site runs SQLite, where LIKE ignores case for the letters A to Z, so the "starts with A" pattern still works written in lower case:

SELECT first_name, last_name
FROM Employees
WHERE first_name LIKE 'a%';

The = operator is stricter: WHERE first_name = 'ananya' returns nothing here, because it compares the text exactly. PostgreSQL's LIKE is case-sensitive, while MySQL and SQL Server usually ignore case because of their default collations. If a search has to behave the same everywhere, lower-case both sides: LOWER(first_name) LIKE 'a%'.

Checking for missing values with IS NULL

This is the mistake almost every beginner makes at least once: you cannot test for NULL with = NULL. NULL means "unknown," and in SQL's three-valued logic, unknown compared to anything — even another NULL — is never true.

-- Wrong: this returns zero rows, even if manager_id has NULLs
SELECT * FROM Employees WHERE manager_id = NULL;

-- Correct
SELECT * FROM Employees WHERE manager_id IS NULL;
SELECT * FROM Employees WHERE manager_id IS NOT NULL;

Operator precedence: why AND beats OR

AND binds more tightly than OR, exactly the way multiplication binds more tightly than addition. That single rule quietly changes the meaning of a lot of queries. Compare these two — they differ only by a pair of brackets:

-- Reads as: dept 10, OR (dept 20 AND well paid)
SELECT first_name, last_name, dept_id, salary
FROM Employees
WHERE dept_id = 10 OR dept_id = 20 AND salary > 120000;

-- Reads as: (dept 10 or dept 20), AND well paid
SELECT first_name, last_name, dept_id, salary
FROM Employees
WHERE (dept_id = 10 OR dept_id = 20) AND salary > 120000;

The first returns six rows — all five employees in department 10 whatever they earn, plus the one well-paid employee in department 20. The second returns two. Neither is wrong as SQL; only one of them is the question you meant to ask. When a condition mixes AND with OR, add the brackets even where they are technically redundant.

The NOT IN and NULL trap

This one is genuinely surprising the first time. If the list you give to NOT IN contains a single NULL, the whole condition returns no rows at all — not an error, just an empty result. That is how PostgreSQL, MySQL, SQL Server and SQLite all behave, and because this site's playground runs SQLite, you can watch it happen. The question is "which employees manage nobody?", answered by excluding everyone who appears as someone's manager:

-- Looks right, returns nothing: the subquery's list contains NULL
SELECT first_name, last_name
FROM Employees
WHERE emp_id NOT IN (SELECT manager_id FROM Employees);

Five employees have no manager, so the subquery hands NOT IN a list with NULL in it, and not a single row survives. The reason follows from how NULL works. x NOT IN (1, 2, NULL) is evaluated as x <> 1 AND x <> 2 AND x <> NULL, and that last comparison can never be true — NULL means "unknown", and nothing is known to be unequal to an unknown value. One unknown in the chain makes the whole AND unknown, so the row is not returned. IN does not have this problem, only NOT IN. If the subquery feeding the list might produce NULLs, filter them out with WHERE column IS NOT NULL, or use NOT EXISTS instead. It only asks whether a matching row exists, so a comparison with NULL simply fails to match instead of making the whole condition unknown:

SELECT e.first_name, e.last_name
FROM Employees e
WHERE NOT EXISTS (
  SELECT 1 FROM Employees r WHERE r.manager_id = e.emp_id
)
ORDER BY e.emp_id;

Seven employees have nobody reporting to them. Adding WHERE manager_id IS NOT NULL inside the first query's subquery returns the same seven. The EXISTS vs IN guide compares the two approaches in more depth.

WHERE vs HAVING

WHERE filters individual rows before any grouping happens. If you need to filter based on an aggregate result (like "departments with more than 5 employees"), you need HAVING instead — see our dedicated guide on GROUP BY vs HAVING.

Practice this topic

Reading explains it; solving it is what makes it stick. These exercises use exactly what this page covered, and your answer is checked by running it against the same database:

• Find the expensive products Easy
• Active engineers only Easy
• Companies beginning with A Medium
• Who reports to nobody? Medium

See all 42 exercises →

Frequently asked questions

Related reading

• EXISTS vs IN in SQL: Which to Use and When
• SQL Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT and Keys
• The SQL SELECT Statement: A Beginner's Guide
• SQL JOINs Explained: INNER, LEFT, RIGHT & FULL
• SQL CASE WHEN Explained: Conditional Logic in Queries