MySQL WHERE

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

WHERE keeps the rows that satisfy a condition and drops the rest. Every lesson so far returned whole tables. This is the clause that turns a table into an answer. Open the client with mysql -u root -p sakila before you start.

How WHERE works

SELECT COLUMN_LIST
FROM TABLE_NAME
WHERE SEARCH_CONDITION;

Replace COLUMN_LIST with the columns you want, TABLE_NAME with the table, and SEARCH_CONDITION with a test the server can apply to one row at a time. The server reads each row, evaluates the condition against that row’s values, and keeps the row when the result is true. A row that gives false, or gives NULL, is dropped. WHERE goes after FROM and before ORDER BY, and that is the order the result is defined in: filter, then sort what survived, then apply LIMIT. So a LIMIT 5 after a WHERE gives you 5 of the matching rows, not 5 rows of which some match. The server is free to reach that answer any way it likes, and EXPLAIN shows which way it picked.

Examples

1) Match one value

Sakila rates every film. This keeps the films rated G.

SELECT film_id, title, rating
FROM film
WHERE rating = 'G'
ORDER BY title
LIMIT 5;

+---------+-------------------+--------+
| film_id | title             | rating |
+---------+-------------------+--------+
|       2 | ACE GOLDFINGER    | G      |
|       4 | AFFAIR PREJUDICE  | G      |
|       5 | AFRICAN EGG       | G      |
|      11 | ALAMO VIDEOTAPE   | G      |
|      22 | AMISTAD MIDSUMMER | G      |
+---------+-------------------+--------+
5 rows in set (0.00 sec)

A string goes in single quotes. A number does not.

2) Compare a number

SELECT title, length
FROM film
WHERE length > 180
ORDER BY length DESC, title
LIMIT 5;

+----------------+--------+
| title          | length |
+----------------+--------+
| CHICAGO NORTH  |    185 |
| CONTROL ANTHEM |    185 |
| DARN FORRESTER |    185 |
| GANGS PRIDE    |    185 |
| HOME PITY      |    185 |
+----------------+--------+
5 rows in set (0.00 sec)

185 minutes is the longest film in the catalog and 10 films run that long, so the first 5 rows are all ties. Sorting by title as well breaks the tie, which is what makes the same 5 come back every time.

3) Filter on a column you did not select

The condition tests the row, not the result. rental_rate never appears in the output here, and the filter still works.

SELECT title
FROM film
WHERE rental_rate = 4.99
ORDER BY title
LIMIT 5;

+------------------+
| title            |
+------------------+
| ACE GOLDFINGER   |
| AIRPLANE SIERRA  |
| AIRPORT POLLOCK  |
| ALADDIN CALENDAR |
| ALI FOREVER      |
+------------------+
5 rows in set (0.00 sec)

4) Everything except one value

SELECT title, rating
FROM film
WHERE rating <> 'G'
ORDER BY title
LIMIT 5;

+------------------+--------+
| title            | rating |
+------------------+--------+
| ACADEMY DINOSAUR | PG     |
| ADAPTATION HOLES | NC-17  |
| AGENT TRUMAN     | PG     |
| AIRPLANE SIERRA  | PG-13  |
| AIRPORT POLLOCK  | R      |
+------------------+--------+
5 rows in set (0.00 sec)

<> is not equal. != means the same thing and either is fine.

WHERE cannot see a column alias

An alias names a column of the result. WHERE runs before the result exists, so naming an alias there fails.

SELECT title, rental_rate * 2 AS double_rate
FROM film
WHERE double_rate > 8
ORDER BY title;

ERROR 1054 (42S22): Unknown column 'double_rate' in 'where clause'

Write the expression again in the condition instead.

SELECT title, rental_rate * 2 AS double_rate
FROM film
WHERE rental_rate * 2 > 8
ORDER BY title
LIMIT 5;

+------------------+-------------+
| title            | double_rate |
+------------------+-------------+
| ACE GOLDFINGER   |        9.98 |
| AIRPLANE SIERRA  |        9.98 |
| AIRPORT POLLOCK  |        9.98 |
| ALADDIN CALENDAR |        9.98 |
| ALI FOREVER      |        9.98 |
+------------------+-------------+
5 rows in set (0.00 sec)

ORDER BY is the clause that can use an alias, because it runs after the result is built. The ORDER BY lesson shows that.

Nothing matching is not an error

SELECT title FROM film WHERE rating = 'X';

Empty set (0.00 sec)

Empty set means the query ran and no row satisfied the condition. The server prints it in place of a table, so there are no column headings either. It is an answer.

Summary

Use WHERE to keep only the rows that satisfy a condition.
Put a string in single quotes and leave a number bare.
Expect the server to filter first, then sort, then apply LIMIT.
Test any column of the table, whether or not you selected it.
Repeat the expression in WHERE rather than naming an alias, which gives ERROR 1054.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How WHERE works

Examples

1) Match one value

2) Compare a number

3) Filter on a column you did not select

4) Everything except one value

WHERE cannot see a column alias

Nothing matching is not an error

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How WHERE works

​Examples

​1) Match one value

​2) Compare a number

​3) Filter on a column you did not select

​4) Everything except one value

​WHERE cannot see a column alias

​Nothing matching is not an error

​Summary

​See also

How WHERE works

Examples

1) Match one value

2) Compare a number

3) Filter on a column you did not select

4) Everything except one value

WHERE cannot see a column alias

Nothing matching is not an error

Summary

See also