AND, OR, NOT in MySQL

In MySQL, AND, OR, and NOT are logical operators used in WHERE conditions to filter query results based on multiple conditions.

1. AND Operator

• The AND operator is used to filter records where both conditions must be true.
• If one condition fails, the entire condition fails.

Syntax

SELECT column_name FROM table_name WHERE condition1 AND condition2;

Example

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

✅ Returns employees who are in the IT department AND have a salary greater than 50,000.
❌ If an employee has a salary > 50,000 but is in HR, they won’t be included.

2. OR Operator

• The OR operator filters records where at least one condition must be true.
• If one condition is true, the row is included.

Syntax

SELECT column_name FROM table_name WHERE condition1 OR condition2;

Example

SELECT * FROM employees WHERE department = 'IT' OR salary > 50000;

✅ Returns employees who are either in the IT department OR have a salary greater than 50,000 (or both).
❌ Only employees who don’t match both conditions will be excluded.

3. NOT Operator

• The NOT operator negates a condition (it reverses the result).
• It selects rows where the condition is false.

Syntax

SELECT column_name FROM table_name WHERE NOT condition;

Example

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

✅ Returns all employees who are NOT in the HR department.
❌ HR employees will be excluded.

4. Combining AND, OR, NOT

• You can combine these operators to create complex queries.
• Use parentheses () to control precedence.

Example 1: Using AND & OR

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

✅ Includes employees who:

• Work in IT AND (either earn more than 50,000 OR have more than 5 years of experience).

Example 2: Using NOT with AND

SELECT * FROM employees WHERE NOT (department = 'HR' AND salary < 40000);

✅ Includes all employees except:

• Those in HR AND earning less than 40,000.

Operator Precedence (Order of Execution)

1. NOT (evaluated first)
2. AND (evaluated second)
3. OR (evaluated last)

💡 Tip: Always use parentheses () to ensure the correct logic.

Summary Table