SQL Server: Combining the AND and OR Conditions
This SQL Server tutorial explains how to use the AND condition and the OR condition together in a SQL Server (Transact-SQL) query with syntax and examples.
When combining these conditions, it is important to use parentheses so that the database knows what order to evaluate each condition. (Just like when you were learning the order of operations in Math class!)
The syntax for the AND condition and the OR condition together in SQL Server (Transact-SQL) is:
WHERE condition1 AND condition2 ... OR condition_n;
Parameters or Arguments
- condition1, condition2, … condition_n
- The conditions that are evaluated to determine if the records will be selected.
- The SQL Server AND & OR conditions allow you to test multiple conditions.
- Don’t forget the order of operation parentheses!
Example – With SELECT Statement
Let’s look at an example that combines the AND and OR conditions in a SELECT statement.
SELECT * FROM employees WHERE (last_name = 'Anderson' AND first_name = 'Sarah') OR (employee_id = 75);
This AND & OR example would return all employees whose last_name is ‘Anderson’ and first_name is ‘Sarah, as well as all employees whose employee_id is 75. The parentheses determine the order that the AND and OR conditions are evaluated. Just like you learned in the order of operations in Math class!
The next example takes a look at a more complex statement.
SELECT employee_id, last_name, first_name FROM employees WHERE (last_name = 'Smith') OR (last_name = 'Anderson AND first_name = 'Sarah') OR (employee_id > 1000 and state = 'California');
This AND & OR example would return all employee_id, last_name, and first_name values where the last_name is ‘Smith’ OR the last_name is ‘Anderson’ and the first_name is ‘Sarah’ OR the employee_id is greater than 1000 and the state is ‘California’.
Example – With INSERT Statement
This next AND & OR example demonstrates how the AND condition and OR condition can be combined in the INSERT statement.
INSERT INTO contacts (last_name, first_name) SELECT last_name, first_name FROM employees WHERE (last_name = 'Johnson' OR 'last_name = 'Anderson') AND employee_id > 54;
This SQL Server AND and OR example would insert into the contacts table, all last_name and first_name values from the employees table whose last_name is either ‘Johnson’ or ‘Anderson’ and where the employee_id is greater than 54.
Example – With UPDATE Statement
This AND & OR example shows how the AND and OR conditions can be used in the UPDATE statement.
UPDATE employees SET last_name = 'TBD' WHERE employee_id <= 2000 AND (state = 'California' OR state = 'Arizona');
This SQL Server AND & OR condition example would update all last_name values in the employees table to ‘TBD’ where the employee_id is less than or equal to 2000 and resides in either the state of ‘California’ or ‘Arizona’.
Example – With DELETE Statement
Finally, this last AND & OR example demonstrates how the AND and OR conditions can be used in the DELETE statement.
DELETE FROM employees WHERE state = 'California' AND (last_name = 'Johnson' OR first_name = 'Joe');
This SQL Server AND and OR condition example would delete all records from the employees table where the state is ‘California’ and either the last_name is ‘Johnson’ or the first_name is ‘Joe’.