In MySQL, the AND, OR, and NOT operators are used to combine multiple conditions in the WHERE clause to create complex filtering criteria. Here's how each operator works:
1. AND Operator: The AND operator is used to combine two or more conditions, and it returns true only if all the conditions separated by AND are true. If any of the conditions is false, the entire expression evaluates to false.
SELECT * FROM table_name WHERE condition1 AND condition2;
Example:
SELECT * FROM employees WHERE age > 30 AND department = 'Sales';
This query will retrieve records of employees who are older than 30 years old and work in the Sales department.
2. OR Operator: The OR operator is used to combine two or more conditions, and it returns true if any of the conditions separated by OR is true. If all the conditions are false, the entire expression evaluates to false.
SELECT * FROM table_name WHERE condition1 OR condition2;
Example:
SELECT * FROM employees WHERE department = 'Sales' OR department = 'Marketing';
This query will retrieve records of employees who work in either the Sales department or the Marketing department.
3. NOT Operator: The NOT operator is used to negate a condition, and it returns true if the condition is false, and vice versa. It is often used with other operators to create negative conditions.
SELECT * FROM table_name WHERE NOT condition;
Example:
SELECT * FROM employees WHERE NOT department = 'IT';
This query will retrieve records of employees who do not work in the IT department.
These operators can be combined to create complex conditions in the WHERE clause to filter data based on specific criteria in MySQL queries.