AND, OR & NOT

Real questions rarely have just one condition. "Accessories under ₹2,000." "Customers from Haryana or Punjab." "Every order that is not delivered and was placed after March." To ask these, you join conditions together with the logical operators AND, OR and NOT.

In this lesson you will use all three, learn the rule that decides which one MySQL checks first, and see how one missing pair of brackets can quietly give you the wrong answer.

AND: both conditions must be true

AND keeps a row only when the condition on its left and the condition on its right are both true.

SELECT product_name, category, price
FROM products
WHERE category = 'Accessories' AND price < 2000;

product_name   | category    | price
---------------+-------------+------
Wireless Mouse | Accessories | 799
USB-C Hub      | Accessories | 1899
Laptop Stand   | Accessories | 1299
(3 rows)

The store has six accessories, but only three of them cost less than ₹2,000. Each extra AND makes the filter stricter, so the result can only get smaller or stay the same.

You can chain as many conditions as you need. This query finds delivered orders placed in March 2025:

SELECT order_id, order_date, status
FROM orders
WHERE status = 'delivered'
  AND order_date >= '2025-03-01'
  AND order_date < '2025-04-01';

order_id | order_date | status
---------+------------+----------
107      | 2025-03-09 | delivered
109      | 2025-03-22 | delivered
(2 rows)

Putting each condition on its own line makes a long WHERE clause much easier to read. Using < '2025-04-01' for the end date includes every day of March, however many days the month has.

OR: at least one condition must be true

OR keeps a row when either condition is true, or both. Each extra OR makes the filter looser, so the result can only grow.

SELECT first_name, city, state
FROM customers
WHERE state = 'Haryana' OR state = 'Punjab';

first_name | city     | state
-----------+----------+--------
Aarav      | Rohtak   | Haryana
Kabir      | Ludhiana | Punjab
Vikram     | Rohtak   | Haryana
Isha       | Gurugram | Haryana
(4 rows)

Three customers are from Haryana and one is from Punjab. When you test one column against a list of values like this, IN & BETWEEN gives you a shorter way to write it.

NOT: reverse a condition

NOT turns true into false and false into true. Put it in front of a condition to get every row that does not match.

SELECT product_name, category
FROM products
WHERE NOT category = 'Accessories';

product_name                | category
----------------------------+---------
27-inch Monitor             | Displays
Noise-Cancelling Headphones | Audio
Bluetooth Speaker           | Audio
1TB External SSD            | Storage
64GB Pen Drive              | Storage
Python Basics (Book)        | Books
(6 rows)

For a simple comparison, category <> 'Accessories' does the same job. NOT is more useful in front of other operators you will meet soon: NOT LIKE, NOT IN, NOT BETWEEN and IS NOT NULL.

The truth rules

Here is what each operator gives for every combination of true (T) and false (F):

There is a third possibility in SQL, "unknown", which appears when a value is NULL. You will see how it behaves in NULL Values.

AND is checked before OR

When a condition mixes AND and OR, MySQL does all the AND parts first, just as maths does multiplication before addition. This is called operator precedence, and it causes one of the most common bugs in SQL.

A customer asks for "accessories or audio products under ₹3,000". The obvious query is wrong:

SELECT product_name, category, price
FROM products
WHERE category = 'Accessories' OR category = 'Audio' AND price < 3000;

product_name        | category    | price
--------------------+-------------+------
Wireless Mouse      | Accessories | 799
Mechanical Keyboard | Accessories | 3499
USB-C Hub           | Accessories | 1899
Laptop Stand        | Accessories | 1299
Bluetooth Speaker   | Audio       | 2499
HD Webcam           | Accessories | 2199
Graphics Tablet     | Accessories | 4599
(7 rows)

The Mechanical Keyboard costs ₹3,499 and the Graphics Tablet ₹4,599, yet both appear. MySQL read the condition as "Accessories (at any price), or (Audio under ₹3,000)". Brackets fix it by forcing the OR to be worked out first:

SELECT product_name, category, price
FROM products
WHERE (category = 'Accessories' OR category = 'Audio') AND price < 3000;

product_name      | category    | price
------------------+-------------+------
Wireless Mouse    | Accessories | 799
USB-C Hub         | Accessories | 1899
Laptop Stand      | Accessories | 1299
Bluetooth Speaker | Audio       | 2499
HD Webcam         | Accessories | 2199
(5 rows)

Now every row is under ₹3,000. The order is NOT first, then AND, then OR. You do not need to memorise it: whenever a condition contains both AND and OR, add brackets so that anyone reading it, including you next month, sees the meaning at once.

Common mistakes

• Leaving out brackets when mixing AND and OR. As shown above, the query runs without any error but returns extra rows. Always bracket the OR part.
• Shortening the second condition. WHERE city = 'Rohtak' OR 'Jaipur' does not mean "Rohtak or Jaipur". The second part is just the text 'Jaipur', which is treated as false, so you get only the Rohtak rows. Write the full condition: city = 'Rohtak' OR city = 'Jaipur'.
• Using AND when you mean OR. WHERE city = 'Rohtak' AND city = 'Jaipur' returns nothing, because one customer cannot live in two cities at once.

Key points

• AND needs both conditions to be true; it narrows the result.
• OR needs at least one condition to be true; it widens the result.
• NOT reverses a condition and is often used as NOT IN, NOT LIKE and IS NOT NULL.
• MySQL evaluates NOT, then AND, then OR.
• Put brackets around the OR part whenever you mix AND and OR.

SELECT first_name, department, salary
FROM employees
WHERE (department = 'Sales' OR department = 'Support')
  AND salary >= 45000;