Combining the AND and OR Conditions

This Oracle tutorial explains how to use the AND condition and the OR condition together in an Oracle query with syntax and examples.

Description

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!)

Syntax

The syntax for the AND condition and OR condition together in Oracle/PLSQL 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.

Note

• 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.

For example:

SELECT *

FROM suppliers

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.

For example:

SELECT supplier_id

FROM suppliers

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.

For example:

INSERT INTO suppliers

(supplier_id, supplier_name)

SELECT account_no, customer_name

FROM customers

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.

For example:

UPDATE suppliers

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.

For example:

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.