> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # MySQL WHERE > How the MySQL WHERE clause keeps only the rows you want, which columns a condition can test, and why it cannot see a column alias from the same query. 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 ```sql theme={null} 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`. ```sql theme={null} SELECT film_id, title, rating FROM film WHERE rating = 'G' ORDER BY title LIMIT 5; ``` ```text theme={null} +---------+-------------------+--------+ | 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 ```sql theme={null} SELECT title, length FROM film WHERE length > 180 ORDER BY length DESC, title LIMIT 5; ``` ```text theme={null} +----------------+--------+ | 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. ```sql theme={null} SELECT title FROM film WHERE rental_rate = 4.99 ORDER BY title LIMIT 5; ``` ```text theme={null} +------------------+ | title | +------------------+ | ACE GOLDFINGER | | AIRPLANE SIERRA | | AIRPORT POLLOCK | | ALADDIN CALENDAR | | ALI FOREVER | +------------------+ 5 rows in set (0.00 sec) ``` ### 4) Everything except one value ```sql theme={null} SELECT title, rating FROM film WHERE rating <> 'G' ORDER BY title LIMIT 5; ``` ```text theme={null} +------------------+--------+ | 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. ```sql theme={null} SELECT title, rental_rate * 2 AS double_rate FROM film WHERE double_rate > 8 ORDER BY title; ``` ```text theme={null} ERROR 1054 (42S22): Unknown column 'double_rate' in 'where clause' ``` Write the expression again in the condition instead. ```sql theme={null} SELECT title, rental_rate * 2 AS double_rate FROM film WHERE rental_rate * 2 > 8 ORDER BY title LIMIT 5; ``` ```text theme={null} +------------------+-------------+ | 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](/docs/tutorial/order-by) lesson shows that. ## Nothing matching is not an error ```sql theme={null} SELECT title FROM film WHERE rating = 'X'; ``` ```text theme={null} 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`. ## See also * [SELECT](/docs/tutorial/select) — the statement WHERE attaches to * [Operators](/docs/tutorial/operators) — the comparisons a condition is built from * [AND, OR and NOT](/docs/tutorial/and-or-not) — combining several conditions * [ORDER BY](/docs/tutorial/order-by) — sorting what survives the filter