This SQL tutorial explains how to use the AND condition and the OR condition together in a single query with syntax and examples.
The SQL AND condition and OR condition can be combined to test for multiple conditions in a SELECT, INSERT, UPDATE, or DELETE statement.
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 SQL AND condition and OR condition together is:
Parameters or Arguments
- condition1, condition2, ... condition_n
- The conditions that are evaluated to determine if the records will be selected.
- The SQL 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 condition and OR condition in a SELECT query.
WHERE (city = 'New York' AND name = 'IBM')
OR (ranking >= 10);
This SQL SELECT example would return all suppliers that reside in New York whose name is IBM and all suppliers whose ranking is greater than or equal to 10. 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.
WHERE (name = 'IBM')
OR (name = 'Hewlett Packard' AND city = 'Atlantic City')
OR (name = 'Gateway' AND status = 'Active' AND city = 'Burma');
This SQL SELECT statement would return all supplier_id values where the supplier's name is IBM or the name is Hewlett Packard and the city is Atlantic City or the name is Gateway, the status is Active, and the city is Burma.
Example - With INSERT Statement
This next example demonstrates how the SQL AND condition and SQL OR condition can be combined in the INSERT statement.
INSERT INTO suppliers
SELECT account_no, customer_name
WHERE (customer_name = 'IBM' OR customer_name = 'Apple')
AND employees > 15;
This SQL AND and OR condition example would insert into the suppliers table, all account_no and customer_name records from the customers table whose customer_name is either IBM or Apple and where the employees is greater than 15.
Example - With UPDATE Statement
This example shows how the AND and OR conditions can be used in the UPDATE statement.
SET supplier_name = 'HP'
WHERE supplier_name = 'IBM'
OR (state = 'California' AND supplier_id = 50);
This SQL AND & OR condition example would update all supplier_name values in the suppliers table to HP where the supplier_name was IBM or the state is California and the supplier_id is equal to 50.
Example - With DELETE Statement
Finally, this last AND & OR condition example demonstrates how the AND and OR condition can be used in the DELETE statement.
DELETE FROM suppliers
WHERE city = 'New York'
AND (product = 'PC computers' OR supplier_name = 'Dell');
This SQL AND and OR condition example would delete all suppliers from the suppliers table whose city was New York and either the product was PC computers or the supplier name was Dell.