WHERE: filtering rows

Why filtering matters

Almost no real query wants every row. You want the orders from this month, the customers in one country, the products that are low in stock. The WHERE clause expresses that. It sits after FROM and holds a condition. MySQL tests the condition against each row and keeps the rows where it is true.

SELECT name, price FROM products WHERE price > 50;

+-----------------------------+--------+
| name                        | price  |
+-----------------------------+--------+
| Mechanical Keyboard         |  89.50 |
| Noise-Cancelling Headphones | 199.99 |
| Standing Desk Mat           |  55.00 |
+-----------------------------+--------+

Three products cost more than 50. Notice that the condition mentions price, a column that is also in the select list, but it does not have to be. You can filter on a column you do not display.

Comparison operators

A single = is a comparison here. If you come from a programming language, you may reach for ==, which MySQL does not support.

Filtering on text

Text values go in single quotes. Double quotes work in MySQL too, but single quotes are the SQL standard and avoid confusion with identifiers.

SELECT first_name, last_name, city FROM customers WHERE country = 'India';

+------------+-----------+-----------+
| first_name | last_name | city      |
+------------+-----------+-----------+
| Asha       | Verma     | Delhi     |
| Farid      | Khan      | Mumbai    |
| Kavya      | Nair      | Bengaluru |
+------------+-----------+-----------+

With MySQL's default collation, text comparisons ignore case and accents, so this finds the same customers:

SELECT first_name, country FROM customers WHERE country = 'india';

+------------+---------+
| first_name | country |
+------------+---------+
| Asha       | India   |
| Farid      | India   |
| Kavya      | India   |
+------------+---------+

If the text itself contains a single quote, double it: 'O''Brien'.

SELECT first_name, last_name FROM customers WHERE last_name = 'O''Brien';

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Liam       | O'Brien   |
+------------+-----------+

Filtering on numbers

Numbers are written without quotes. Combine them with the comparison operators:

SELECT name, stock FROM products WHERE stock <= 40;

+-----------------------------+-------+
| name                        | stock |
+-----------------------------+-------+
| Noise-Cancelling Headphones |    25 |
| Standing Desk Mat           |    40 |
| Building Blocks Set         |    35 |
| Puzzle 1000 Pieces          |     0 |
+-----------------------------+-------+

The product with zero stock appears here as well, because 0 is less than 40. Decimal values use a dot: price = 24.99.

Filtering on dates

Dates are written as text in YYYY-MM-DD form and compared like numbers. Earlier dates are smaller:

SELECT id, customer_id, order_date, status FROM orders WHERE order_date >= '2024-06-01';

+----+-------------+------------+-----------+
| id | customer_id | order_date | status    |
+----+-------------+------------+-----------+
| 16 |           4 | 2024-06-03 | delivered |
| 17 |           8 | 2024-06-10 | cancelled |
| 18 |          10 | 2024-06-25 | delivered |
| 19 |           6 | 2024-07-07 | pending   |
| 20 |           1 | 2024-07-19 | shipped   |
+----+-------------+------------+-----------+

SELECT id, order_date FROM orders WHERE order_date = '2024-02-14';

+----+------------+
| id | order_date |
+----+------------+
|  2 | 2024-02-14 |
+----+------------+

Always use this format. Writing '14/02/2024' does not do what you hope and may silently compare as something else.

Filtering by an expression

The condition can contain calculations and functions as well as plain columns:

SELECT name, price, stock, price * stock AS stock_value FROM products WHERE price * stock > 5000;

+---------------------+-------+-------+-------------+
| name                | price | stock | stock_value |
+---------------------+-------+-------+-------------+
| Mechanical Keyboard | 89.50 |    60 |     5370.00 |
| SQL Basics Handbook | 29.95 |   200 |     5990.00 |
+---------------------+-------+-------+-------------+

Remember that you cannot use the alias stock_value in WHERE in this way. The WHERE clause is processed before the select list, so the alias does not exist yet. Repeat the expression, as above, or use a subquery (a later lesson).

Where else you will use WHERE

WHERE is not only for SELECT. The same clause decides which rows UPDATE changes and which rows DELETE removes. That is why this lesson matters beyond reading: a wrong condition on a SELECT shows you the wrong rows, but a wrong condition on a DELETE destroys them. A good habit is to write the WHERE as a SELECT first, check the rows, and only then change the statement.

Common mistakes

• Forgetting quotes around text: WHERE country = India makes MySQL look for a column named India.
• Quoting numbers: stock = '10' often works because MySQL converts it, but it is sloppy and can slow queries down.
• Writing == out of habit from other languages. SQL uses a single =.
• Comparing dates in other formats than YYYY-MM-DD.
• Using a select-list alias in WHERE, which fails with "Unknown column".

Exercise

Write queries for the sample shop that return: (1) the name and price of products costing 20 or less, (2) the customers who signed up after 1 July 2023, and (3) the orders whose status is not delivered.

SELECT name, price FROM products WHERE price <= 20;
SELECT first_name, signup_date FROM customers WHERE signup_date > '2023-07-01';
SELECT id, status FROM orders WHERE status <> 'delivered';

+--------------------+-------+
| name               | price |
+--------------------+-------+
| Ceramic Mug Set    | 18.75 |
| Resistance Bands   | 14.99 |
| Water Bottle       | 12.00 |
| Puzzle 1000 Pieces | 19.99 |
+--------------------+-------+
+------------+-------------+
| first_name | signup_date |
+------------+-------------+
| Jack       | 2023-07-14  |
| Kavya      | 2023-08-30  |
| Liam       | 2023-09-12  |
+------------+-------------+
+----+-----------+
| id | status    |
+----+-----------+
|  4 | shipped   |
|  6 | cancelled |
|  8 | shipped   |
|  9 | pending   |
| 12 | shipped   |
| 14 | pending   |
| 17 | cancelled |
| 19 | pending   |
| 20 | shipped   |
+----+-----------+