> ## 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 AND, OR and NOT > How to combine conditions in a MySQL WHERE clause with AND, OR and NOT, which one binds tighter, and the 2 mistakes that quietly return wrong rows. All examples on this page work on VillageSQL. Install Now → One condition answers one question. `AND`, `OR` and `NOT` join conditions together so that a single `WHERE` can ask a compound question. Open the client with `mysql -u root -p sakila` before you start. ## How AND, OR and NOT work ```sql theme={null} SELECT COLUMN_LIST FROM TABLE_NAME WHERE FIRST_CONDITION AND SECOND_CONDITION; ``` Replace `COLUMN_LIST` with the columns you want, `TABLE_NAME` with the table, and each of `FIRST_CONDITION` and `SECOND_CONDITION` with a test of its own. `AND` keeps a row only when both conditions are true. `OR` keeps a row when at least one is true. `NOT` reverses the condition that follows it. They do not all bind equally. Of the 3, `NOT` binds tightest, then `AND`, then `OR`. The server therefore reads `a OR b AND c` as `a OR (b AND c)`, which is rarely what someone writing it meant. Parentheses override that, and they cost nothing. All 3 bind more loosely than a comparison, which is why `NOT rating = 'G'` below reverses the whole comparison rather than just the column. ## Examples ### 1) Both conditions ```sql theme={null} SELECT title, rating, length FROM film WHERE rating = 'G' AND length > 180 ORDER BY title; ``` ```text theme={null} +-----------------+--------+--------+ | title | rating | length | +-----------------+--------+--------+ | BAKED CLEOPATRA | G | 182 | | CATCH AMISTAD | G | 183 | | CONTROL ANTHEM | G | 185 | | DARN FORRESTER | G | 185 | | INTRIGUE WORST | G | 181 | | LAWLESS VISION | G | 181 | | MOONWALKER FOOL | G | 184 | | MUSCLE BRIGHT | G | 185 | | YOUNG LANGUAGE | G | 183 | +-----------------+--------+--------+ 9 rows in set (0.00 sec) ``` 9 films are rated `G` and run over 180 minutes. ### 2) Either condition ```sql theme={null} SELECT title, rating FROM film WHERE rating = 'G' OR rating = 'PG' ORDER BY title LIMIT 5; ``` ```text theme={null} +------------------+--------+ | title | rating | +------------------+--------+ | ACADEMY DINOSAUR | PG | | ACE GOLDFINGER | G | | AFFAIR PREJUDICE | G | | AFRICAN EGG | G | | AGENT TRUMAN | PG | +------------------+--------+ 5 rows in set (0.00 sec) ``` Testing one column against a list of values is common enough to have its own operator. [IN](/docs/tutorial/in) writes the same filter in one clause. ### 3) Reverse a condition ```sql theme={null} SELECT title, rating FROM film WHERE NOT 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) ``` `NOT rating = 'G'` and `rating <> 'G'` select the same rows. Prefer `<>` for a single comparison and keep `NOT` for reversing something larger. ## AND binds tighter than OR This looks like "rated G or PG, and over 180 minutes". ```sql theme={null} SELECT title, rating, length FROM film WHERE rating = 'G' OR rating = 'PG' AND length > 180 ORDER BY title; ``` ```text theme={null} +---------------------------+--------+--------+ | title | rating | length | +---------------------------+--------+--------+ | ACE GOLDFINGER | G | 48 | | AFFAIR PREJUDICE | G | 117 | | AFRICAN EGG | G | 130 | ... +---------------------------+--------+--------+ 182 rows in set (0.00 sec) ``` 182 rows, and the first one runs 48 minutes. `AND` bound to the pair next to it, so the server answered "rated G, or else rated PG and over 180 minutes". Every `G` film qualified on the first branch alone, whatever its length. Parentheses say what was meant. ```sql theme={null} SELECT title, rating, length FROM film WHERE (rating = 'G' OR rating = 'PG') AND length > 180 ORDER BY title; ``` ```text theme={null} +-----------------+--------+--------+ | title | rating | length | +-----------------+--------+--------+ | BAKED CLEOPATRA | G | 182 | | CATCH AMISTAD | G | 183 | | CONTROL ANTHEM | G | 185 | | DARN FORRESTER | G | 185 | | INTRIGUE WORST | G | 181 | | LAWLESS VISION | G | 181 | | MONSOON CAUSE | PG | 182 | | MOONWALKER FOOL | G | 184 | | MUSCLE BRIGHT | G | 185 | | RECORDS ZORRO | PG | 182 | | STAR OPERATION | PG | 181 | | WORST BANGER | PG | 185 | | YOUNG LANGUAGE | G | 183 | +-----------------+--------+--------+ 13 rows in set (0.01 sec) ``` 13 rows. Neither query is invalid, and nothing warns you, so the only defense is to bracket every mixture of `AND` and `OR` as you write it. ## OR does not take a bare second value `rating = 'G' OR 'PG'` reads in English as "rated G or PG". To the server it is 2 separate conditions, and the second one is the string `'PG'` standing alone. ```sql theme={null} SELECT title, rating FROM film WHERE rating = 'G' OR 'PG' ORDER BY title LIMIT 5; ``` ```text theme={null} +-------------------+--------+ | title | rating | +-------------------+--------+ | ACE GOLDFINGER | G | | AFFAIR PREJUDICE | G | | AFRICAN EGG | G | | ALAMO VIDEOTAPE | G | | AMISTAD MIDSUMMER | G | +-------------------+--------+ 5 rows in set, 1 warning (0.00 sec) ``` Only `G` films came back, and the footer reports a warning. Run `SHOW WARNINGS` in the same session and the server explains itself: `Warning 1292 Truncated incorrect DOUBLE value: 'PG'`. A condition has to be true or false, so the server converted `'PG'` to a number. `'PG'` starts with no digit, which converts to 0, which is false. The whole second branch was therefore dead. Name the column on both sides, or use [IN](/docs/tutorial/in). ## NOT reverses only what comes next ```sql theme={null} SELECT title, rating, length FROM film WHERE NOT rating = 'G' AND length > 180 ORDER BY title; ``` ```text theme={null} +--------------------+--------+--------+ | title | rating | length | +--------------------+--------+--------+ | ANALYZE HOOSIERS | R | 181 | | CHICAGO NORTH | PG-13 | 185 | | CONSPIRACY SPIRIT | PG-13 | 184 | ... +--------------------+--------+--------+ 30 rows in set (0.00 sec) ``` `NOT` took the comparison beside it, so this is "not rated G, and over 180 minutes". To reverse the whole pair, bracket it. ```sql theme={null} SELECT title, rating, length FROM film WHERE NOT (rating = 'G' AND length > 180) ORDER BY title LIMIT 5; ``` ```text theme={null} +------------------+--------+--------+ | title | rating | length | +------------------+--------+--------+ | ACADEMY DINOSAUR | PG | 86 | | ACE GOLDFINGER | G | 48 | | ADAPTATION HOLES | NC-17 | 50 | | AFFAIR PREJUDICE | G | 117 | | AFRICAN EGG | G | 130 | +------------------+--------+--------+ 5 rows in set (0.00 sec) ``` `ACE GOLDFINGER` is rated `G` and runs 48 minutes. It fails the bracketed pair, so reversing the pair keeps it. ## Summary * Use `AND` when every condition must hold, and `OR` when any one will do. * Bracket any `WHERE` that mixes `AND` and `OR`, because `AND` binds tighter. * Expect `NOT` to reverse only the condition beside it. * Repeat the column on both sides of `OR`, because a bare value converts to a number. * Watch the warning count in the footer, then run `SHOW WARNINGS`. ## See also * [WHERE](/docs/tutorial/where) — the clause these operators build up * [IN](/docs/tutorial/in) — a tidier way to test one column against several values * [Operators](/docs/tutorial/operators) — the comparisons being combined