SQL: AND & OR Conditions
This SQL tutorial explains how to use the AND condition and the OR condition together in a single query with syntax and examples.
When combining these conditions, it is important to use brackets 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 is:
WHERE condition1 AND condition2 ... OR condition_n;
Parameters or Arguments
condition1, condition2, condition_n are the conditions that are evaluated to determine if the records will be selected.
- The SQL AND & OR conditions allows you to test multiple conditions.
- Don't forget the order of operation brackets!
Example - With SELECT Statement
Let's look at an example that combines the AND condition and OR condition in a SELECT query.
SELECT * FROM suppliers 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 brackets 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 supplier_id FROM suppliers 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 (supplier_id, supplier_name) SELECT account_no, customer_name FROM customers 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.
UPDATE suppliers SET supplier_name = 'HP' WHERE supplier_name = 'IBM' AND state = 'California';
This SQL AND & OR condition example would update all supplier_name values in the suppliers table to HP where the supplier_name was IBM and resides in the state of California.
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.