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.

What are Logical Operators?

Logical operators:

  • Combine multiple conditions

  • Return rows based on TRUE / FALSE logic

  • Commonly used with WHERE, HAVING, JOIN

AND Operator

 Meaning

Returns rows only if all conditions are TRUE.

Syntax


Example

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

OR Operator

 Meaning

Returns rows if any one condition is TRUE.

Syntax


Example

  •  Employees from HR or IT

 NOT Operator

 Meaning

Reverses the result of a condition.

Syntax


Example

  •  All employees except IT

Combining AND, OR, NOT (Very Important)

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

Operator Precedence

MySQL evaluates logical operators in this order:

  1. NOT

  2. AND

  3. OR

 Risky:

 Safe:

AND / OR with BETWEEN, IN, LIKE

 AND / OR with NOT IN

AND_OR with NULL Handling

 NULL needs special care:

  •  = NULL
  • IS NULL

AND vs OR vs NOT – Comparison

OperatorMeaningResult
ANDAll conditions trueNarrow result
ORAny condition trueBroad result
NOTReverse conditionExclusion

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...