AND & OR Operators

Logical Operators

SQL provides three main logical operators: AND, OR, and NOT. These operators are used to combine multiple conditions in a WHERE clause.

The AND Operator

The AND operator returns TRUE only if all conditions are true.

SELECT * FROM EMPLOYEE
WHERE DEPARTMENT = 'IT' AND SALARY > 5000000;

The query above only returns employees who work in the IT department and have a salary above 5,000,000.

The OR Operator

The OR operator returns TRUE if at least one condition is true.

SELECT * FROM EMPLOYEE
WHERE DEPARTMENT = 'IT' OR DEPARTMENT = 'HR';

This query returns all employees from the IT or HR department.

The NOT Operator

The NOT operator inverts the result of a condition. A condition that was TRUE becomes FALSE, and vice versa.

SELECT * FROM EMPLOYEE
WHERE NOT DEPARTMENT = 'FINANCE';

This query returns all employees who are not from the FINANCE department.

Combining AND & OR

When combining AND and OR, use parentheses to control the order of evaluation.

SELECT * FROM EMPLOYEE
WHERE (DEPARTMENT = 'IT' OR DEPARTMENT = 'HR')
  AND SALARY > 4000000;

Without parentheses, the AND operator has higher precedence than OR, so the results may differ.

Operator Precedence

The order of evaluation in SQL:

1. NOT (highest precedence)
2. AND
3. OR (lowest precedence)

SELECT * FROM EMPLOYEE
WHERE SALARY > 5000000
  AND DEPARTMENT = 'IT'
  OR DEPARTMENT = 'HR';

The query above is evaluated as: (SALARY > 5000000 AND DEPARTMENT = 'IT') OR DEPARTMENT = 'HR'. In other words, all HR employees will appear even if their salary is below 5,000,000.

Condition Combination Examples

Suppose the EMPLOYEE table has the following structure:

-- IT employees with a salary above 5 million
SELECT * FROM EMPLOYEE
WHERE DEPARTMENT = 'IT' AND SALARY > 5000000;

-- Result: Mike (ID 1)

-- IT employees or those with a salary above 5 million
SELECT * FROM EMPLOYEE
WHERE DEPARTMENT = 'IT' OR SALARY > 5000000;

-- Result: Mike, Anna, Dana

-- Active employees in IT or HR
SELECT * FROM EMPLOYEE
WHERE STATUS = 'Active'
  AND (DEPARTMENT = 'IT' OR DEPARTMENT = 'HR');

-- Result: Mike, John, Anna

Summary

• AND requires all conditions to be met
• OR requires at least one condition to be met
• NOT inverts the result of a condition
• Use parentheses when combining AND and OR

Understanding logical operators is very important because they will be used together with the UPDATE statement to update data precisely. Learn more in the next chapter.