MySQL AND OR and NOT Operators

MySQL Tutorial

🔀 MySQL AND, OR, and NOT Operators

In MySQL, logical operators AND, OR, and NOT are used in the WHERE clause to combine, broaden, or negate conditions.

👉 They are fundamental for filtering data and are very common in exams and interviews.

1️⃣ What are Logical Operators?

Logical operators:

  • Combine multiple conditions

  • Return rows based on TRUE / FALSE logic

  • Commonly used with WHERE, HAVING, JOIN

2️⃣ AND Operator ⭐

🔹 Meaning

Returns rows only if all conditions are TRUE.

Syntax


Example


✔ Employees who are in IT and earn more than 50,000

3️⃣ OR Operator ⭐

🔹 Meaning

Returns rows if any one condition is TRUE.

Syntax


Example


✔ Employees from HR or IT

4️⃣ NOT Operator ⭐

🔹 Meaning

Reverses the result of a condition.

Syntax


Example


✔ All employees except IT

5️⃣ Combining AND, OR, NOT ⭐ (Very Important)


📌 Use parentheses () to control logic and avoid mistakes.

6️⃣ Operator Precedence ⭐

MySQL evaluates logical operators in this order:

  1. NOT

  2. AND

  3. OR

❌ Risky:

WHERE a = 1 OR b = 2 AND c = 3;

✔ Safe:

WHERE a = 1 OR (b = 2 AND c = 3);

7️⃣ AND / OR with BETWEEN, IN, LIKE



8️⃣ AND / OR with NOT IN


9️⃣ AND / OR with NULL Handling

⚠️ NULL needs special care:


📌 = NULL ❌

📌 IS NULL ✔

🔟 AND vs OR vs NOT – Comparison ⭐

Operator Meaning Result
AND All conditions true Narrow result
OR Any condition true Broad result
NOT Reverse condition Exclusion

1️⃣1️⃣ Common Mistakes ❌

❌ Forgetting parentheses
❌ Confusing AND / OR logic
❌ Using = NULL instead of IS NULL
❌ Writing complex logic without clarity

📌 Interview Questions & MCQs (Very Important)

Q1. AND operator returns rows when:

A) Any condition is true
B) All conditions are true
C) No condition is true
D) Only first condition is true

✅ Answer: B

Q2. OR operator returns rows when:

A) All conditions are true
B) Any condition is true
C) Condition is false
D) No condition is true

✅ Answer: B

Q3. What does NOT operator do?

A) Joins conditions
B) Filters rows
C) Reverses condition
D) Sorts data

✅ Answer: C

Q4. Which operator has highest precedence?

A) AND
B) OR
C) NOT

✅ Answer: C

Q5. Which is correct?

WHERE NOT city = 'Delhi'

A) Correct
B) Incorrect

✅ Answer: A

Q6. Best practice for complex conditions?

A) Avoid AND/OR
B) Use parentheses
C) Use only OR
D) Use only AND

✅ Answer: B

🔥 Real-Life Use Cases

✔ Filter employees by multiple criteria
✔ Search features (name OR email)
✔ Excluding unwanted records
✔ Complex report conditions

✅ Summary

  • AND → all conditions must be true

  • OR → any condition can be true

  • NOT → reverses condition

  • Use parentheses for clarity

  • Operator precedence matters

  • Very important for SQL exams & interviews

You may also like...