Oracle AND and OR combination of conditions.

Preamble

Oracle AND and OR conditions can be combined in SELECT, INSERT, UPDATE or DELETE operators.

When combining these conditions, it is important to use parentheses so that the database knows how to evaluate each condition.

Syntax of AND and OR conditions in Oracle/PLSQL

WHERE condition1
AND condition2
…
OR condition_n;

Parameters and arguments of the condition

• condition1, condition2, … condition_n – The conditions that define the selected records.

Note:
Oracle AND and OR conditions allow checking several conditions.
Do not forget about the order of operations with brackets!

Example with the SELECT operator

Let’s consider an example that combines AND and OR conditions in a SELECT operator.
For example:

SELECT *
FROM suppliers
WHERE (state = 'Nevada' AND supplier_name = 'INTEL')
OR (supplier_id < 5000);

This example AND and OR would return all suppliers that are in state ‘Nevada’ whose supplier_name is ‘INTEL’ and all suppliers whose supplier_id is less than 5000. The brackets define the order in which the conditions AND and OR will be evaluated.

The following example will consider a more complex sentence.
For example:

SELECT supplier_id
FROM suppliers
WHERE (supplier_name = 'INTEL')
OR (supplier_name = 'Apple' AND state = 'Florida')
OR (supplier_name = 'Best Buy' AND status = 'Active' AND state = 'California');

This example AND and OR will return all values of supplier_id where supplier_name is ‘INTEL’ OR supplier_name is ‘Apple’ AND state ‘Florida’ OR supplier_name is ‘Best Buy’ AND status is ‘Active’ AND state ‘California’.

Example of an INSERT operator

The following example shows how AND and OR conditions can be combined in an INSERT operator.
For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, customer_name
FROM customers
WHERE (customer_name = 'Nike' OR customer_name = 'Adidas')
AND customer_id > 25;

This example can insert into the customers table, all account_no and customer_name entries from the customers table whose customer_name is ‘Nike’ OR ‘Adidas’ and where customer_id is over 25.

Example with UPDATE operator

This example shows how AND and OR conditions can be used in UPDATE.
For example:

UPDATE suppliers
SET supplier_name = 'Nokia'
WHERE supplier_name = 'Fly'
AND (state = 'California' OR state = 'Florida');

This example of Oracle’s AND and OR conditions will update all values of the supplier_name field in the suppliers table to ‘Nokia’, where supplier_name was ‘Fly’ and the location that is in one of two states ‘California’ OR ‘Florida’.

Example with DELETE operator

Finally, the example of AND and OR which shows how AND and OR conditions can be used in DELETE.
For example:

DELETE FROM suppliers
WHERE state = 'Nevada'
AND (product = 'laptop' OR supplier_name = 'Asus');

This example of Oracle’s AND and OR conditions will remove all entries from the suppliers table that were in state ‘Nevada’ and had product with ‘laptop’ or supplier_name with ‘Asus’.

Oracle Tutorial: Logical Operator; AND OR NOT Operators

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.