What you will be able to do
After this lesson you can ask for only the rows you care about: the people in one department, salaries above a limit, or staff hired after a date. WHERE is the clause that turns a table of everything into an answer to a specific question.
The basic form
Put WHERE after FROM, followed by a condition. The database tests the condition against every row and keeps the ones for which it is true:
SELECT first_name, last_name, job_title
FROM employees
WHERE department_id = 1;+------------+-----------+----------------------+
| first_name | last_name | job_title |
+------------+-----------+----------------------+
| Meera | Iyer | Engineering Director |
| Arjun | Rao | Senior Developer |
| Kavya | Nair | Senior Developer |
| Rohan | Das | Developer |
| Isha | Kapoor | Developer |
| Diya | Chopra | Junior Developer |
+------------+-----------+----------------------+Six employees belong to Engineering, which is department 1. The other fourteen rows were tested and dropped. The condition is department_id = 1: a column, a comparison operator and a value.
Comparison operators
| Operator | Meaning |
|---|---|
= |
equal to |
<> |
not equal to (!= also works on all four databases in this course) |
< and > |
less than, greater than |
<= and >= |
less than or equal, greater than or equal |
Note that the equality test uses one equals sign. There is no == in SQL. Numbers need no quotes:
SELECT first_name, salary
FROM employees
WHERE salary > 100000;+------------+--------+
| first_name | salary |
+------------+--------+
| Meera | 180000 |
| Arjun | 125000 |
| Kavya | 125000 |
| Vikram | 150000 |
| Dev | 110000 |
| Lata | 115000 |
+------------+--------+The rule > excludes the boundary value, so someone earning exactly 100000 would not appear. Use >= to include it.
Text
Text goes in single quotes, and the match must be exact, character for character apart from case rules, which differ by database (see below):
SELECT first_name, last_name, salary
FROM employees
WHERE job_title = 'Support Engineer';+------------+-----------+--------+
| first_name | last_name | salary |
+------------+-----------+--------+
| Karan | Mehta | 56000 |
| Sana | Qureshi | 56000 |
+------------+-----------+--------+You can also compare text for ordering. Letters sort alphabetically, so last_name < 'D' finds names that come before D:
SELECT first_name, last_name
FROM employees
WHERE last_name < 'D';+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Lata | Bose |
| Diya | Chopra |
+------------+-----------+Bose and Chopra sort before 'D'. Das does not: it starts with D but is longer, and a longer text sorts after its own prefix, so 'Das' is greater than 'D'.
Dates
Dates are written as text in the form YYYY-MM-DD, in single quotes. This format sorts correctly and is understood by MySQL, PostgreSQL, SQLite and SQL Server:
SELECT first_name, hire_date
FROM employees
WHERE hire_date >= '2023-01-01';+------------+------------+
| first_name | hire_date |
+------------+------------+
| Tara | 2023-04-03 |
| Sana | 2023-07-17 |
| Ravi | 2024-01-08 |
| Diya | 2024-06-10 |
| Aman | 2024-03-18 |
+------------+------------+Five people joined in 2023 or later. SQLite stores dates as plain text and compares them as text, which works because of the year-first layout. Other databases have real date types and compare them as dates. Avoid regional formats such as '01/02/2023', which are ambiguous and not portable.
Not equal
Use <> to exclude a value:
SELECT first_name, job_title
FROM employees
WHERE department_id <> 1 AND salary >= 110000;+------------+-------------------+
| first_name | job_title |
+------------+-------------------+
| Vikram | Sales Director |
| Dev | Marketing Manager |
| Lata | Finance Manager |
+------------+-------------------+The AND joining two conditions is the subject of the next lesson. Notice that only rows with both conditions true appear. The consultant, who has no department, is absent for a reason you will meet in the NULL lesson: department_id <> 1 is unknown, not true, when the department is missing.
Calculations in a condition
Either side of a comparison can be a calculation. Which projects would still have a budget above 150000 if it were cut in half?
SELECT name, budget
FROM projects
WHERE budget / 2 > 150000;+-----------------+--------+
| name | budget |
+-----------------+--------+
| Customer Portal | 400000 |
| Mobile App | 650000 |
+-----------------+--------+A condition can also compare two columns of the same row. You will use that when tables are joined, because then the two columns often come from different tables.
How WHERE decides
For each row, the condition evaluates to true, false or unknown. Only true keeps the row. Unknown appears when NULL is involved, and the result is the same as false for the purposes of WHERE. The NULL lesson covers this properly.
WHERE is evaluated before the select list, so it can use any column of the table, even one you do not show:
SELECT first_name
FROM employees
WHERE salary = 125000;+------------+
| first_name |
+------------+
| Arjun |
| Kavya |
+------------+The salary is tested, but only the name is returned.
Differences between databases
- Case in text comparisons. MySQL and SQL Server compare text without regard to case with their default settings, so
'developer'matches'Developer'. PostgreSQL is case sensitive. SQLite is case sensitive for=. This depends on collation settings, so test before you rely on it. - Not equal.
<>is standard and works everywhere.!=is accepted by all four. - Dates. The
YYYY-MM-DDform works on all four. Only SQLite stores dates as text.
See the MySQL WHERE lesson for more on that product.
Common mistakes
- Using
==instead of=. - Quoting numbers or forgetting to quote text.
WHERE job_title = Developerlooks for a column called Developer. - Double quotes for text. It sometimes works by accident on MySQL and SQLite and fails on PostgreSQL.
- Using an alias from the select list in
WHERE. It is not available there. - Expecting
<>to keep rows with NULL. Missing values drop out of both=and<>tests.
Exercise
Find the employees in department 2 (Sales) who earn less than 60000, showing first_name, job_title and salary.
Show one possible answer
SELECT first_name, job_title, salary
FROM employees
WHERE department_id = 2 AND salary < 60000;+------------+-----------------+--------+
| first_name | job_title | salary |
+------------+-----------------+--------+
| Tara | Sales Associate | 52000 |
| Aman | Sales Associate | 50000 |
+------------+-----------------+--------+Key points
- WHERE keeps only the rows for which its condition is true. It runs before the select list is built.
- The comparison operators are =, <>, <, >, <= and >=. Most databases also accept != for not equal.
- Text and date values are written in single quotes. Dates use the YYYY-MM-DD format.
- A single = tests equality in SQL. There is no == operator.
- Rows where the condition is false or unknown (NULL) are left out of the result.