MySql - AND, OR, and NOT for complex conditions

Using AND, OR, and NOT for Complex Conditions IN MySQL

In MySQL, conditional logic plays a crucial role when querying data. The ability to use logical operators such as AND, OR, and NOT empowers developers to build powerful, complex filters for selecting or modifying rows in a database.

This tutorial covers the practical usage, syntax, and real-world examples of these operators. You will learn how to combine them, avoid logical mistakes, and improve query precision while querying or manipulating your data.

1. Introduction to Logical Operators

Logical operators are used in the WHERE clause to combine one or more conditions. The common logical operators supported by MySQL include:

• AND – Returns true if both conditions are true
• OR – Returns true if any one of the conditions is true
• NOT – Reverses the result of the condition

2. The AND Operator

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2;

Example

SELECT * FROM employees
WHERE department = 'Sales' AND salary > 40000;

This query returns all employees from the Sales department whose salary is greater than 40,000.

3. The OR Operator

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2;

Example

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

This query returns employees who belong to either the Sales or Marketing department.

4. The NOT Operator

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Example

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

This query retrieves all employees who are not in the IT department.

5. Combining AND, OR, and NOT

These operators can be combined to form more complex conditions. Use parentheses () to group conditions and control precedence.

Example

SELECT * FROM employees
WHERE department = 'Sales' AND (salary > 50000 OR experience_years > 5);

This query fetches employees from the Sales department who either earn more than 50,000 or have more than 5 years of experience.

6. Operator Precedence

MySQL evaluates conditions in the following order unless overridden by parentheses:

1. NOT
2. AND
3. OR

Without Parentheses Example

SELECT * FROM employees
WHERE NOT department = 'Finance' AND salary > 50000 OR experience_years > 10;

Due to operator precedence, this condition is interpreted as:

(NOT department = 'Finance') AND (salary > 50000) OR (experience_years > 10);

To change this logic, you should use parentheses explicitly.

With Parentheses

SELECT * FROM employees
WHERE NOT (department = 'Finance' AND salary > 50000) OR experience_years > 10;

7. Real-World Scenarios

Example 1: Customer Filtering

SELECT * FROM customers
WHERE city = 'New York' AND (age >= 30 OR membership = 'Premium');

Returns customers from New York who are either 30+ years old or have a premium membership.

Example 2: Filtering Orders

SELECT * FROM orders
WHERE NOT status = 'Cancelled' AND (total_amount > 500 OR priority = 'High');

Returns orders that are not cancelled and either have a high priority or total above 500.

8. DELETE with Logical Operators

DELETE FROM users
WHERE is_active = 0 AND (created_at < NOW() - INTERVAL 1 YEAR);

Deletes users who are inactive and were created more than a year ago.

9. UPDATE with Logical Conditions

UPDATE products
SET discount = 20
WHERE category = 'Electronics' OR price > 1000;

Applies a 20% discount to all electronics or products priced above 1000.

10. Combining Logical with IN, BETWEEN, and LIKE

IN with AND

SELECT * FROM employees
WHERE department IN ('IT', 'Finance') AND status = 'active';

BETWEEN with OR

SELECT * FROM products
WHERE price BETWEEN 500 AND 1000 OR category = 'Luxury';

LIKE with NOT

SELECT * FROM customers
WHERE NOT email LIKE '%@example.com';

Returns customers who do not have an email with @example.com domain.

11. Nested Conditions for Complex Queries

Use multiple levels of parentheses to build deeply nested logical queries.

SELECT * FROM employees
WHERE (department = 'HR' AND salary > 30000)
   OR (department = 'IT' AND (experience_years > 5 OR certification = 'Yes'));

12. Filtering NULL Values

SELECT * FROM products
WHERE price IS NULL OR quantity <= 0;

This query identifies products that either have no price or are out of stock.

13. Using Logical Operators in HAVING Clauses

Logical operators can also be used in HAVING to filter group results.

SELECT department, COUNT(*) AS total
FROM employees
GROUP BY department
HAVING total > 10 AND department != 'Intern';

14. Logical Operators in Stored Procedures

Example Procedure

DELIMITER //

CREATE PROCEDURE GetQualifiedEmployees()
BEGIN
  SELECT * FROM employees
  WHERE (department = 'Sales' AND salary > 50000)
     OR (department = 'IT' AND experience_years > 5);
END //

DELIMITER ;

15. Performance Considerations

• Use indexes on columns in logical conditions to speed up evaluation.
• Prefer simpler conditions when possible to avoid full table scans.
• Use EXPLAIN to analyze query performance.

Using EXPLAIN

EXPLAIN SELECT * FROM employees
WHERE department = 'Sales' AND salary > 50000;

16. Common Mistakes to Avoid

• Forgetting parentheses – leads to unexpected results due to precedence
• Using = NULL instead of IS NULL
• Overusing NOT, which may reduce performance
• Incorrect assumption of operator precedence

17. Best Practices

• Always test your WHERE clause with SELECT before applying DELETE or UPDATE
• Use parentheses liberally for readability and clarity
• Combine conditions logically rather than writing multiple queries
• Use meaningful field aliases when filtering joined tables

18. Case Study: Employee Bonus Eligibility

You want to select employees eligible for a bonus. Criteria:

• Must be in 'Sales' or 'Support'
• Must have worked for more than 3 years
• Salary must be under 70,000

SELECT * FROM employees
WHERE (department = 'Sales' OR department = 'Support')
  AND experience_years > 3
  AND salary < 70000;

19. Debugging Logical Condition Queries

If your query returns unexpected results, test each logical component separately:

SELECT * FROM employees WHERE department = 'Sales';
SELECT * FROM employees WHERE experience_years > 3;
SELECT * FROM employees WHERE salary < 70000;

Then combine them once verified individually.

MySQL's logical operators AND, OR, and NOT are fundamental tools for building complex and precise queries. They provide developers with flexibility in defining custom logic for filtering data. By mastering these operators, you can write more expressive queries, maintain clean and optimized SQL code, and avoid common logic pitfalls. Proper use of parentheses and understanding operator precedence ensures that your logic yields correct and expected results.