AND, OR, NOT, IN and BETWEEN: MySQL

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonCombine several conditions correctly, use parentheses to control precedence, and write shorter conditions with IN and BETWEEN.

More than one condition

One condition is rarely enough. You might want products in the Electronics category that cost less than 50, or customers from India or the UK. SQL combines conditions with three logical operators.

Operator	The row passes when
`AND`	both conditions are true
`OR`	at least one condition is true
`NOT`	the condition is false

AND: all conditions must hold

sql

SELECT name, category_id, price FROM products WHERE category_id = 1 AND price < 50;

result

+----------------+-------------+-------+
| name           | category_id | price |
+----------------+-------------+-------+
| Wireless Mouse |           1 | 24.99 |
| USB-C Hub      |           1 | 39.00 |
+----------------+-------------+-------+

Both tests apply to every row, so a row has to be in category 1 and cheaper than 50. Each extra AND makes the result smaller, because it adds a requirement.

OR: any condition is enough

sql

SELECT first_name, country FROM customers WHERE country = 'India' OR country = 'UK';

result

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

Each extra OR makes the result larger, because it adds another way for a row to qualify.

Precedence: AND before OR

When a query mixes AND and OR, MySQL evaluates AND first, exactly like multiplication comes before addition. This surprises many beginners.

Suppose you want books or home products that cost more than 40. This attempt gets it wrong:

sql

SELECT name, category_id, price FROM products WHERE category_id = 2 OR category_id = 3 AND price > 40;

result

+-----------------------------+-------------+-------+
| name                        | category_id | price |
+-----------------------------+-------------+-------+
| SQL Basics Handbook         |           2 | 29.95 |
| Web Design Patterns         |           2 | 34.50 |
| Data Structures Illustrated |           2 | 45.00 |
| Standing Desk Mat           |           3 | 55.00 |
+-----------------------------+-------------+-------+

MySQL reads it as "category 2, or (category 3 and price over 40)". Every book is returned, including the cheap ones. Add parentheses to say what you actually mean:

sql

SELECT name, category_id, price FROM products WHERE (category_id = 2 OR category_id = 3) AND price > 40;

result

+-----------------------------+-------------+-------+
| name                        | category_id | price |
+-----------------------------+-------------+-------+
| Data Structures Illustrated |           2 | 45.00 |
| Standing Desk Mat           |           3 | 55.00 |
+-----------------------------+-------------+-------+

Make it a habit to use parentheses whenever AND and OR appear together. They cost nothing and make the query readable to the next person, who may be you in a month.

NOT

NOT reverses a condition. It reads naturally with other operators:

sql

SELECT id, status FROM orders WHERE NOT status = 'delivered';

result

+----+-----------+
| id | status    |
+----+-----------+
|  4 | shipped   |
|  6 | cancelled |
|  8 | shipped   |
|  9 | pending   |
| 12 | shipped   |
| 14 | pending   |
| 17 | cancelled |
| 19 | pending   |
| 20 | shipped   |
+----+-----------+

That is the same as status <> 'delivered'. NOT is most useful in front of IN, BETWEEN, LIKE and EXISTS, as you will see.

IN: matching a list

Writing country = 'India' OR country = 'UK' OR country = 'France' is tedious. IN does the same in one go:

sql

SELECT first_name, country FROM customers WHERE country IN ('India', 'UK', 'France');

result

+------------+---------+
| first_name | country |
+------------+---------+
| Asha       | India   |
| Ben        | UK      |
| Chloe      | France  |
| Farid      | India   |
| Kavya      | India   |
+------------+---------+

sql

SELECT id, status FROM orders WHERE status NOT IN ('pending', 'cancelled');

result

+----+-----------+
| id | status    |
+----+-----------+
|  1 | delivered |
|  2 | delivered |
|  3 | delivered |
|  4 | shipped   |
|  5 | delivered |
|  7 | delivered |
|  8 | shipped   |
| 10 | delivered |
| 11 | delivered |
| 12 | shipped   |
| 13 | delivered |
| 15 | delivered |
| 16 | delivered |
| 18 | delivered |
| 20 | shipped   |
+----+-----------+

IN also accepts a subquery instead of a fixed list, which you will use later: WHERE customer_id IN (SELECT ...).

BETWEEN: a range

BETWEEN low AND high checks that a value lies in a range, including both ends:

sql

SELECT name, price FROM products WHERE price BETWEEN 20 AND 40;

result

+---------------------+-------+
| name                | price |
+---------------------+-------+
| Wireless Mouse      | 24.99 |
| USB-C Hub           | 39.00 |
| SQL Basics Handbook | 29.95 |
| Web Design Patterns | 34.50 |
| Desk Lamp           | 32.00 |
| Yoga Mat            | 27.50 |
+---------------------+-------+

It works for dates too, and it is a tidy way to select a month. Because dates without a time are treated as midnight, this is safe for a DATE column:

sql

SELECT id, order_date FROM orders WHERE order_date BETWEEN '2024-03-01' AND '2024-03-31';

result

+----+------------+
| id | order_date |
+----+------------+
|  6 | 2024-03-01 |
|  7 | 2024-03-09 |
|  8 | 2024-03-22 |
|  9 | 2024-03-30 |
+----+------------+

Take care with DATETIME and TIMESTAMP columns, which store a time of day. A range ending '2024-03-31' means midnight at the start of the 31st, so orders later that day are missed. For those columns use >= the start and < the day after the end.

Common mistakes

Mixing AND and OR without parentheses, then getting more rows than expected.
Writing category_id = 2 OR 3. This does not mean "2 or 3". Write category_id IN (2, 3).
Assuming BETWEEN excludes its ends. It includes them.
Writing the range backwards: BETWEEN 40 AND 20 matches nothing, because no number is both at least 40 and at most 20.
Using NOT IN on a list that can contain NULL. The result may be empty. The NULL lesson explains why.

Exercise

Write queries that return: (1) customers from India who signed up after 1 March 2023, (2) products in the Sports category (id 4) or the Toys category (id 5) priced under 20, with parentheses, and (3) orders placed in May 2024 that are not pending.

Show one possible answer

sql

SELECT first_name, signup_date FROM customers WHERE country = 'India' AND signup_date > '2023-03-01';
SELECT name, category_id, price FROM products WHERE category_id IN (4, 5) AND price < 20;
SELECT id, order_date, status FROM orders WHERE order_date BETWEEN '2024-05-01' AND '2024-05-31' AND status <> 'pending';

result

+------------+-------------+
| first_name | signup_date |
+------------+-------------+
| Farid      | 2023-04-18  |
| Kavya      | 2023-08-30  |
+------------+-------------+
+--------------------+-------------+-------+
| name               | category_id | price |
+--------------------+-------------+-------+
| Resistance Bands   |           4 | 14.99 |
| Water Bottle       |           4 | 12.00 |
| Puzzle 1000 Pieces |           5 | 19.99 |
+--------------------+-------------+-------+
+----+------------+-----------+
| id | order_date | status    |
+----+------------+-----------+
| 13 | 2024-05-02 | delivered |
| 15 | 2024-05-21 | delivered |
+----+------------+-----------+

Key points

AND needs both conditions true; OR needs at least one; NOT reverses a condition.
AND is evaluated before OR. Use parentheses whenever you mix them so the meaning is obvious.
IN (a, b, c) is a short way to write several OR comparisons on one column.
BETWEEN low AND high is inclusive of both ends.

More than one condition

AND: all conditions must hold

OR: any condition is enough

Precedence: AND before OR

NOT

IN: matching a list

BETWEEN: a range

Common mistakes

Exercise

Key points

Check yourself