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:
Employee Name | Salary | Department ID |
Anne | 10000 | 90 |
David | 9000 | 80 |
John | 8000 | 70 |
George | 7000 | 70 |
Roger | 5000 | 70 |
Sean | 4000 | 40 |
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%'