Using Combining the AND and OR Conditions in Oracle

This article is written about how to use the AND circumstance and the OR situation together in an Oracle question with syntax and examples.

Description

The Oracle AND circumstance and OR circumstance can be blended in a SELECT, INSERT, UPDATE, or DELETE statement.

When combining these conditions, it is vital to use parentheses so that the database is aware of what order to evaluate every condition. (Just like when you had been mastering the order of operations in Math class!)

Syntax

The syntax for the AND circumstance and OR situation collectively in Oracle/PLSQL is:

WHERE condition1
AND condition2
...
OR condition_n;

Parameters or Arguments

condition1, condition2, … condition_n The stipulations that are evaluated to determine if the files will be selected.

Note

The Oracle AND & OR conditions allow you to check a couple of conditions. Don’t overlook the order of operation parentheses!

Example – With SELECT Statement

Let’s seem to be at an instance that combines the AND and OR prerequisites in a SELECT statement.

For example:

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

This AND & OR instance would return all suppliers that live in the nation 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 prerequisites are evaluated. Just like you realized in the order of operations in Math class!

The next example takes a appear at a greater complicated 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 country is Florida OR the supplier_name is Best Buy, the fame is Active and the kingdom is California.

Example – With INSERT Statement

This next AND & OR example demonstrates how the AND situation and OR situation 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 instance would insert into the suppliers table, all account_no and customer_name records from the clients table whose customer_name is both Apple or Samsung and where the customer_id is increased than 20.

Example – With UPDATE Statement

This AND & OR instance shows how the AND and OR prerequisites 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 instance would update all supplier_name values in the suppliers table to Samsung the place the supplier_name was RIM and resides in both the state of California or Florida.

Example – With DELETE Statement

Finally, this final AND & OR example demonstrates how the AND and OR prerequisites 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 circumstance example would delete all files from the suppliers desk whose nation was once Florida and either the product was once PC computer systems or the supplier title was once Dell.