MySQL AND, OR, NOT Operators

A WHERE clause with a single condition is useful, but real-world queries often need to check multiple conditions at once. MySQL provides three logical operators — AND, OR, and NOT — to combine conditions and build more precise filters.

---

Sample Table

id	name	city	age
1	Rahim Uddin	Dhaka	22
2	Sara Begum	Chittagong	20
3	Karim Ali	Sylhet	23
4	Nila Akter	Dhaka	21
5	Rafi Hossain	Rajshahi	24

---

AND Operator

AND returns rows where all conditions are true. If even one condition fails, the row is excluded.

-- Students from Dhaka who are older than 20
SELECT * FROM students
WHERE city = 'Dhaka' AND age > 20;

id	name	city	age
1	Rahim Uddin	Dhaka	22
4	Nila Akter	Dhaka	21

Both conditions must be satisfied: the student must be from Dhaka and must be older than 20.

---

OR Operator

OR returns rows where at least one condition is true. A row only needs to pass one of the conditions to be included.

-- Students from either Dhaka or Sylhet
SELECT * FROM students
WHERE city = 'Dhaka' OR city = 'Sylhet';

id	name	city	age
1	Rahim Uddin	Dhaka	22
3	Karim Ali	Sylhet	23
4	Nila Akter	Dhaka	21

---

NOT Operator

NOT reverses a condition — it returns rows where the condition is false.

-- All students who are NOT from Dhaka
SELECT * FROM students
WHERE NOT city = 'Dhaka';

id	name	city	age
2	Sara Begum	Chittagong	20
3	Karim Ali	Sylhet	23
5	Rafi Hossain	Rajshahi	24

---

Combining AND and OR

You can mix AND and OR in the same condition, but be careful — AND has higher precedence than OR. This means MySQL evaluates AND conditions first, before OR.

This query may not behave as expected without parentheses:

-- Ambiguous: AND is evaluated first
SELECT * FROM students
WHERE city = 'Dhaka' OR city = 'Sylhet' AND age > 22;

MySQL reads this as: city = 'Dhaka' OR (city = 'Sylhet' AND age > 22) — returning all Dhaka students regardless of age, plus only Sylhet students older than 22.

Use parentheses to make the logic explicit and avoid surprises:

-- Clear intent: students from Dhaka or Sylhet, who are also older than 22
SELECT * FROM students
WHERE (city = 'Dhaka' OR city = 'Sylhet') AND age > 22;

---

Multiple ANDs and ORs

You can chain as many logical operators as needed:

-- Students aged between 21 and 23, from Dhaka or Chittagong
SELECT * FROM students
WHERE (age >= 21 AND age <= 23)
  AND (city = 'Dhaka' OR city = 'Chittagong');

---

Key Points to Remember

• AND — all conditions must be true for the row to be included.
• OR — at least one condition must be true.
• NOT — reverses a condition; useful with IN, BETWEEN, and LIKE.
• AND has higher precedence than OR — MySQL evaluates AND first.
• Always use parentheses when mixing AND and OR to make your intent clear and avoid unexpected results.