DataslopeFiltering Rows with WHERE
How to keep only the rows you care about using WHERE, comparison operators, and combinations with AND, OR, and NOT.
SELECT chooses columns. WHERE chooses rows. Together they let
you cut a table down to exactly the slice you want — the most
common thing you will ever do in SQL.
The idea: a test applied to every row
A WHERE clause is a condition — a true/false test. The
database checks it against every row and keeps only the rows where
the test comes out true.
A first filter
Only the two engineers survive the filter. Note = tests equality
(SQL uses a single equals sign for "is equal to"), and the text goes
in single quotes.
The comparison operators
You can compare with more than just equality:
| Operator | Means | Example |
|---|---|---|
= | equal to | dept = 'Sales' |
<> or != | not equal to | dept <> 'Sales' |
< > | less / greater than | salary > 90000 |
<= >= | at most / at least | salary >= 80000 |
BETWEEN a AND b | within a range (inclusive) | salary BETWEEN 70000 AND 90000 |
IN (...) | matches any in a list | dept IN ('Sales', 'Marketing') |
LIKE | text pattern match | name LIKE 'A%' |
Matching text patterns with LIKE
LIKE filters text by pattern. Two wildcards do the work:
%matches any number of characters (including none)._matches exactly one character.
So 'A%' means "starts with A," '%son' means "ends with son," and
'%ar%' means "contains ar."
Combining conditions: AND, OR, NOT
Real questions often have several parts. Combine conditions with
AND (both must be true), OR (at least one true), and NOT
(flips a condition):
Mixing AND and OR? Use parentheses
When a condition mixes AND and OR, wrap the OR part in
parentheses to make your intent unambiguous, e.g.
WHERE dept = 'Sales' AND (salary > 75000 OR name LIKE 'C%').
AND binds more tightly than OR, and being explicit prevents
surprising results.
How WHERE fits with SELECT
A useful way to picture a query: the database first figures out
which rows pass WHERE, then picks the SELECT columns from those
surviving rows.
We will refine this picture in the Composing Queries section, but "filter rows, then choose columns" is an excellent mental model to carry for now.
Check your understanding
What is the role of a WHERE clause?
It chooses which columns appear in the result.
It keeps only the rows for which its condition is true.
It sorts the rows into order.
It permanently deletes rows that don't match.
Which condition keeps employees whose salary is at least 80000?
WHERE salary > 80000
WHERE salary >= 80000
WHERE salary < 80000
WHERE salary = 80000
In a LIKE pattern, what does the % wildcard match?
Exactly one character.
Any number of characters, including none.
Only digits.
The literal percent sign only.
Which combination keeps rows where the department is Engineering and the salary is above 100000?
WHERE dept = 'Engineering' OR salary > 100000
WHERE dept = 'Engineering' AND salary > 100000
WHERE dept = 'Engineering' NOT salary > 100000
WHERE dept = 'Engineering', salary > 100000
Practice challenge
The books table has a year column. Return the title and year of every book published before the year 2000.
SELECT Basics
Choosing columns, reading every row, and understanding that a query returns a brand-new result table without touching the original data.
Sorting and Limiting
Putting results in a deliberate order with ORDER BY, and grabbing just the top rows with LIMIT — the tools behind every "top 10" list.