In MySQL, AND, OR, and NOT are logical operators used to combine or negate conditions in SQL queries. They help refine the results returned by SELECT statements and other SQL commands by applying complex criteria.

AND Operator

The AND operator is used to combine multiple conditions in a WHERE clause. All conditions joined by AND must be true for a row to be included in the result set.

Syntax

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

Example

To find employees who work in the 'Sales' department and have a salary greater than 50,000:

SELECT name, department, salary
FROM employees
WHERE department = 'Sales' AND salary > 50000;

In this example, only employees who meet both conditions—working in 'Sales' and having a salary greater than 50,000—will be included in the results.

OR Operator

The OR operator is used to combine multiple conditions where at least one of the conditions must be true for a row to be included in the result set.

Syntax

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

Example

To find employees who work in either the 'Sales' department or the 'Marketing' department:

SELECT name, department
FROM employees
WHERE department = 'Sales' OR department = 'Marketing';

In this example, employees who work in either 'Sales' or 'Marketing' will be included in the results.

NOT Operator

The NOT operator is used to negate a condition. It returns rows where the condition is false.

Syntax

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

Example

To find employees who do not work in the 'Sales' department:

SELECT name, department
FROM employees
WHERE NOT department = 'Sales';

In this example, employees who work in any department except 'Sales' will be included in the results.

Combining Logical Operators

Logical operators can be combined to create more complex conditions. Use parentheses to control the order of evaluation.

Example

To find employees who work in the 'Sales' department with a salary greater than 50,000 or employees who work in the 'Marketing' department with a salary greater than 60,000:

SELECT name, department, salary
FROM employees
WHERE (department = 'Sales' AND salary > 50000)
   OR (department = 'Marketing' AND salary > 60000);

In this query:

• The condition (department = 'Sales' AND salary > 50000) is evaluated.
• The condition (department = 'Marketing' AND salary > 60000) is evaluated.
• Rows that satisfy either of these conditions are included in the results.

Best Practices

• Use Parentheses: When combining multiple logical operators, use parentheses to ensure correct order of evaluation.
• Optimize Conditions: Use indexes on columns used in WHERE conditions to improve query performance.
• Keep Queries Readable: Structure complex conditions in a clear and readable manner to make maintenance easier.