This Oracle tutorial explains how to use the AND condition and the OR condition together in an Oracle query with syntax and examples.
The Oracle AND condition and OR condition can be combined 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 AND condition and OR condition together in Oracle/PLSQL is:
Parameters or Arguments
condition1, condition2, ... condition_n
The conditions that are evaluated to determine if the records will be selected.
- The Oracle 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.
WHERE (state = 'California' AND supplier_name = 'IBM')
OR (supplier_id < 5000);
This AND & OR example would return all suppliers that reside in the state of California whose supplier_name is IBM and all suppliers whose supplier_id is less than 5000. 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 (supplier_name = 'IBM')
OR (supplier_name = 'Apple' AND state = 'Florida')
OR (supplier_name = 'Best Buy' AND status = 'Active' AND state = 'California');
This AND & OR example would return all supplier_id values where the supplier_name is IBM OR the supplier_name is Apple and the state is Florida OR the supplier_name is Best Buy, the status is Active 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 suppliers
SELECT account_no, customer_name
WHERE (customer_name = 'Apple' OR customer_name = 'Samsung')
AND customer_id > 20;
This Oracle AND and OR example would insert into the suppliers table, all account_no and customer_name records from the customers table whose customer_name is either Apple or Samsung and where the customer_id is greater than 20.
Example - With UPDATE Statement
This AND & OR example shows how the AND and OR conditions can be used in the UPDATE statement.
SET supplier_name = 'Samsung'
WHERE supplier_name = 'RIM'
AND (state = 'California' OR state = 'Florida');
This Oracle AND & OR condition example would update all supplier_name values in the suppliers table to Samsung where the supplier_name was RIM and resides in either the state of California or Florida.
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 suppliers
WHERE state = 'Florida'
AND (product = 'PC computers' OR supplier_name = 'Dell');
This Oracle AND and OR condition example would delete all records from the suppliers table whose state was Florida and either the product was PC computers or the supplier name was Dell.