SQL Server AND & OR Operators

This SQL tutorial focuses on the SQL Server AND & OR logical operators, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts explaining how to use the WHERE clause in SQL Server. To read additional posts regarding this subject, please use the following links:

• Introduction – SQL Server WHERE clause
• Comparison operators
• IN operator
• BETWEEN operator
• LIKE operator
• IS NULL operator
• AND & OR operators

SQL Server AND & OR Operators

The purpose of these SQL Server operators is to allow filtering based on multiple conditions. Using these operators, you can combine the result of two (or more) conditions to produce a single result based on them, for example:

SQL Server AND Operator

AND requires all conditions to be true.

SELECT *
FROM employees
WHERE salary > 6000
AND
      Department_id = 70

AND indicates that all conditions must be met – the employees whose salary is higher than 6000 and whose department number is 70.

The employees who meet both these conditions are: John and George (2 employees in total).

SQL Server OR Operator

OR requires either condition to be true.

SELECT *
FROM employees
WHERE salary > 6000
OR
      Department_id = 70

OR indicates that at least one of the conditions must be met – the employees whose salary is higher than 6000 or whose department number is 70.

• For Sean – none of these conditions is met.
• For Roger, the department number is 70 – one condition is met.
• For John and George, salary is higher than 6000 and the department number is 70 – both conditions are met.
• For Anne and David, salary is higher than 6000 – one condition is met.

5 employees meet at least one of these conditions.

Note:

Note: the WHERE keyword appears only once. After the AND operator or the OR operator, specify the additional condition without specifying the WHERE keyword again.

SELECT *
FROM employees
WHERE salary > 6000
OR
WHERE Department_id = 70
-- (Error)

SELECT * 
FROM employees 
WHERE salary > 6000
OR 
      department_id = 70
-- Valid statement

Order of Precedence

The SQL Server AND operator takes precedence over the SQL Server OR operator (just like a multiplication operation takes precedence over an addition operation).

The following SQL Server SELECT statement is used for displaying the employees who:

(First condition) have the role ‘DBA’.

Or:

(Second condition) have the role ‘Manager’ and whose salary is higher than 8000.

SELECT *
FROM employees
WHERE    Job_Title = 'DBA'
OR
         Job_Title = 'Manager' AND Salary > 8000

Changing the Order of Precedence

Changing the order of priorities between the SQL Server OR and AND operators requires round brackets  (just like round brackets change the order of priorities between the mathematical addition and multiplication operations).

The following SQL Server SELECT statement is used for displaying the employees who:

(First condition) have the role ‘DBA’ or whose salary is higher than 8000.

And

(Second condition) have a last name that contains the letter “a”.

SELECT *
FROM employees
WHERE (Job_Title = 'DBA' OR Salary > 8000)
AND
       Last_name LIKE '%a%'