MYSQL Tutorial

MySQL Logical Operators – Combine Conditions Like a Pro

Logical operators help you combine multiple conditions in a WHERE clause to filter data precisely. MySQL supports several logical operators:

🔹 Common Logical Operators

• AND – True if both conditions are true
• OR – True if any one condition is true
• NOT – Reverses the truth value of a condition
• XOR – True if only one condition is true (exclusive OR)

🔹 Example Table: employees

| employee_id | first_name | last_name | department | salary | hire_date  |
|-------------|------------|-----------|------------|--------|------------|
| 1           | John       | Doe       | Sales      | 50000  | 2020-01-10 |
| 2           | Jane       | Smith     | Marketing  | 60000  | 2019-05-15 |
| 3           | Mike       | Johnson   | IT         | 70000  | 2021-03-01 |
| 4           | Emma       | Brown     | Marketing  | 55000  | 2018-07-22 |
  

🔹 Using AND

Get employees from Marketing with salary above 55,000:

SELECT * FROM employees 
WHERE department = 'Marketing' AND salary > 55000;
  

🔹 Using OR

Get employees in Sales or IT department:

SELECT * FROM employees 
WHERE department = 'Sales' OR department = 'IT';
  

🔹 Using NOT

Get employees not in the Marketing department:

SELECT * FROM employees 
WHERE NOT department = 'Marketing';
  

🔹 Using XOR

Get employees who are either in Marketing or earn more than 60,000, but not both:

SELECT * FROM employees 
WHERE (department = 'Marketing') XOR (salary > 60000);
  

🔹 Operator Precedence

When combining multiple logical operators, remember the order of evaluation:

1. NOT has the highest precedence
2. AND is evaluated next
3. OR has the lowest precedence

Use parentheses () to control evaluation order:

SELECT * FROM employees 
WHERE department = 'Marketing' AND (salary > 50000 OR hire_date > '2020-01-01');
  

🔹 Interactive Challenge

Write a query to find employees who are either in Sales and earn less than 55,000, or are hired after 2019-01-01.

SELECT * FROM employees 
WHERE (department = 'Sales' AND salary < 55000) OR hire_date > '2019-01-01';
    

🔹 Summary

• AND requires both conditions true.
• OR requires one or more conditions true.
• NOT negates a condition.
• XOR is true when exactly one condition is true.
• Use parentheses () to group conditions and clarify logic.