MySQL and or NOT: Combine Conditions Correctly

One condition is rarely enough. AND, OR and NOT combine them.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    added_on DATE NOT NULL
);

INSERT INTO products (name, category, price, stock, added_on) VALUES
    ('Notebook',      'stationery',  80.00, 120, '2026-01-10'),
    ('Gel Pen',       'stationery',  15.50, 300, '2026-01-12'),
    ('Backpack',      'bags',       999.00,  25, '2026-02-01'),
    ('Marker Set',    'stationery', 145.00,   0, '2026-02-15'),
    ('Desk Lamp',     'home',       650.00,  40, '2026-03-03'),
    ('Water Bottle',  'home',       220.00,  75, '2026-03-20'),
    ('Laptop Sleeve', 'bags',       450.00,  18, '2026-04-02'),
    ('Sticky Notes',  'stationery',  35.00, 210, '2026-04-18');

AND: all conditions must be true

query.sql

SQL

SELECT name, category, price FROM products WHERE category = 'stationery' AND price < 100;

Output

OUTPUT

+--------------+------------+-------+
| name         | category   | price |
+--------------+------------+-------+
| Notebook     | stationery | 80.00 |
| Gel Pen      | stationery | 15.50 |
| Sticky Notes | stationery | 35.00 |
+--------------+------------+-------+
3 rows in set

OR: at least one must be true

query.sql

SQL

SELECT name, category FROM products WHERE category = 'bags' OR category = 'home' ORDER BY name;

Output

OUTPUT

+---------------+----------+
| name          | category |
+---------------+----------+
| Backpack      | bags     |
| Desk Lamp     | home     |
| Laptop Sleeve | bags     |
| Water Bottle  | home     |
+---------------+----------+
4 rows in set

NOT: reverses a condition

query.sql

SQL

SELECT name, category FROM products WHERE NOT category = 'stationery' ORDER BY name;

Output

OUTPUT

+---------------+----------+
| name          | category |
+---------------+----------+
| Backpack      | bags     |
| Desk Lamp     | home     |
| Laptop Sleeve | bags     |
| Water Bottle  | home     |
+---------------+----------+
4 rows in set

(category <> 'stationery' says the same thing and is easier to read.)

Precedence: AND comes before OR

When a condition has both AND and OR, MySQL applies AND first:

query.sql

SQL

SELECT name, category, price
FROM products
WHERE category = 'bags' OR category = 'home' AND price < 300
ORDER BY name;

Output

OUTPUT

+---------------+----------+--------+
| name          | category | price  |
+---------------+----------+--------+
| Backpack      | bags     | 999.00 |
| Laptop Sleeve | bags     | 450.00 |
| Water Bottle  | home     | 220.00 |
+---------------+----------+--------+
3 rows in set

MySQL read that as category = 'bags' OR (category = 'home' AND price < 300). So all bags are returned (even the 999 one), plus the home products under 300. If you meant “bags or home, and under 300”, add brackets:

query.sql

SQL

SELECT name, category, price
FROM products
WHERE (category = 'bags' OR category = 'home') AND price < 300
ORDER BY name;

Output

OUTPUT

+--------------+----------+--------+
| name         | category | price  |
+--------------+----------+--------+
| Water Bottle | home     | 220.00 |
+--------------+----------+--------+
1 row in set

Always use brackets when you mix AND and OR. Even if you know the rule, the next reader may not.

The truth tables, with NULL

SQL has three values: true, false and unknown (NULL).

a	b	a AND b	a OR b
true	true	true	true
true	false	false	true
true	NULL	NULL	true
false	NULL	false	NULL
NULL	NULL	NULL	NULL

NOT NULL is NULL. And WHERE keeps only true.

query.sql

SQL

SELECT (1 = 1) AND NULL AS true_and_null,
       (1 = 1) OR NULL  AS true_or_null,
       (1 = 2) AND NULL AS false_and_null,
       (1 = 2) OR NULL  AS false_or_null,
       NOT NULL         AS not_null;

Output

OUTPUT

+---------------+--------------+----------------+---------------+----------+
| true_and_null | true_or_null | false_and_null | false_or_null | not_null |
+---------------+--------------+----------------+---------------+----------+
|          NULL |            1 |              0 |          NULL |     NULL |
+---------------+--------------+----------------+---------------+----------+
1 row in set

XOR

MySQL has an exclusive-or operator: true when exactly one side is true. It is rarely needed and not standard SQL.

query.sql

SQL

SELECT name, price, stock FROM products WHERE (price > 200) XOR (stock > 50) ORDER BY name;

Output

OUTPUT

+---------------+--------+-------+
| name          | price  | stock |
+---------------+--------+-------+
| Backpack      | 999.00 |    25 |
| Desk Lamp     | 650.00 |    40 |
| Gel Pen       |  15.50 |   300 |
| Laptop Sleeve | 450.00 |    18 |
| Notebook      |  80.00 |   120 |
| Sticky Notes  |  35.00 |   210 |
+---------------+--------+-------+
6 rows in set

Simplifying conditions

Instead of	Write
`a = 1 OR a = 2 OR a = 3`	`a IN (1, 2, 3)`
`a >= 10 AND a <= 20`	`a BETWEEN 10 AND 20`
`NOT (a = 1)`	`a <> 1`
`NOT (a > 5)`	`a <= 5` (be careful with `NULL`)
`NOT (x AND y)`	`NOT x OR NOT y` (De Morgan’s law)

warning

The classic bug

WHERE category = 'bags' OR category = 'home' AND stock > 0 returns out-of-stock bags. The stock > 0 applies only to home. Brackets fix it.