MySQL AND, OR and NOT

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

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

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

SELECT title, rating, length
FROM film
WHERE rating = 'G' AND length > 180
ORDER BY title;

+-----------------+--------+--------+
| 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

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

+------------------+--------+
| 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 writes the same filter in one clause.

3) Reverse a condition

SELECT title, rating
FROM film
WHERE NOT 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)

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”.

SELECT title, rating, length
FROM film
WHERE rating = 'G' OR rating = 'PG' AND length > 180
ORDER BY title;

+---------------------------+--------+--------+
| 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.

SELECT title, rating, length
FROM film
WHERE (rating = 'G' OR rating = 'PG') AND length > 180
ORDER BY title;

+-----------------+--------+--------+
| 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.

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

+-------------------+--------+
| 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.

NOT reverses only what comes next

SELECT title, rating, length
FROM film
WHERE NOT rating = 'G' AND length > 180
ORDER BY title;

+--------------------+--------+--------+
| 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.

SELECT title, rating, length
FROM film
WHERE NOT (rating = 'G' AND length > 180)
ORDER BY title
LIMIT 5;

+------------------+--------+--------+
| 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.

Getting Started

Server Development

Using Extensions

Creating Extensions

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

How AND, OR and NOT work

Examples

1) Both conditions

2) Either condition

3) Reverse a condition

AND binds tighter than OR

OR does not take a bare second value

NOT reverses only what comes next

Summary

See also

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

​How AND, OR and NOT work

​Examples

​1) Both conditions

​2) Either condition

​3) Reverse a condition

​AND binds tighter than OR

​OR does not take a bare second value

​NOT reverses only what comes next

​Summary

​See also

How AND, OR and NOT work

Examples

1) Both conditions

2) Either condition

3) Reverse a condition

AND binds tighter than OR

OR does not take a bare second value

NOT reverses only what comes next

Summary

See also