WHERE narrows a result down to the rows that matter, and operators are the vocabulary you filter with. This lesson covers every operator you'll reach for regularly.
Filters rows using =, <>, >, <, >=, <=.
SELECT * FROM table_name
WHERE column operator value;SELECT * FROM Employee
WHERE salary > 50000;Common Mistake
Using = to compare against NULL instead of IS NULL — a NULL is never equal to anything, not even another NULL.
Combines multiple conditions in a WHERE clause.
WHERE condition1 AND condition2
WHERE condition1 OR condition2SELECT * FROM Employee
WHERE department = 'IT'
AND salary > 50000;Common Mistake
Missing parentheses when mixing AND with OR, silently changing the intended logic.
Checks whether a value falls within an inclusive range.
WHERE column BETWEEN low AND high;WHERE salary BETWEEN 50000 AND 70000;Common Mistake
Forgetting that BETWEEN is inclusive on both ends.
Matches text against a pattern.
WHERE column LIKE 'pattern';WHERE name LIKE 'J%';% matches any sequence of characters, _ matches exactly one.Common Mistake
Leading a pattern with % ('%text'), which usually forces a slow full table scan.
Matches a column against a list of possible values.
WHERE column IN (value1, value2, ...);WHERE department IN ('IT', 'HR');Common Mistake
Using IN with a very large list instead of joining against a table.
Compares a value against every value returned by a subquery.
WHERE column > ANY (subquery);
WHERE column > ALL (subquery);WHERE salary > ANY (
SELECT salary FROM Employee WHERE department = 'HR'
);Common Mistake
Confusing ANY with IN — ANY works with comparison operators, not just equality. See the Subqueries lesson for more.
Checks whether a subquery returns any rows at all.
WHERE EXISTS (subquery);WHERE EXISTS (
SELECT 1 FROM Department WHERE Department.id = Employee.department_id
);Common Mistake
Selecting specific columns inside EXISTS — the values returned are never actually used.
| Operator | What it checks |
|---|---|
| =, <>, >, <, >=, <= | Direct value comparison |
| AND / OR / NOT | Combining or inverting conditions |
| BETWEEN | A value within an inclusive range |
| LIKE | A text pattern match |
| IN | A value against a fixed list |
| ANY / ALL | A value against every result of a subquery |
| EXISTS | Whether a subquery returns any rows |
With filtering covered, the next lesson is a short one: sorting results with ORDER BY.