Oracle OR condition

The Oracle OR condition (also called the OR statement) is used to check several conditions in which records are returned when a particular condition is true. It can be used in SELECT, INSERT, UPDATE or DELETE.

Syntax for OR condition in Oracle/PLSQL

WHERE condition1
OR condition2
…
OR condition_n;

Parameters and arguments of the condition

• condition1, condition2, condition_n – All conditions that must be met for the selected records.

condition1 OR condition2 logic:

Note:

• Oracle condition OR allows checking 2 or more conditions.
• The Oracle condition OR requirement, so that all conditions (condition1, condition2, condition_n) must be met for the entries that are included in the resulting set.

Example with the SELECT operator

The first example of the Oracle OR condition, which we will consider, is included in the SELECT operator with 2 conditions:

SELECT *
FROM customers
WHERE state = 'Nevada'
OR available_credit > 700;

This example Oracle OR conditions will return all customers who are either in state ‘Nevada’ or available_credit above 700. Since SELECT uses *, all fields from the customers table will fall into the result set.

Example of a SELECT operator (with 3 conditions)

The following example of the Oracle OR condition is a SELECT operator with 3 conditions. If any of these conditions are not met, the record will be included in the result set.

SELECT supplier_id
FROM suppliers
WHERE supplier_name = 'INTEL'
OR city = 'Boston'
OR offices > 7;

This example Oracle condition OR will return all supplier_id values where supplier_name is either ‘INTEL’ or city ‘Boston’ or offices is greater than 7.

Example with the INSERT operator

Oracle condition OR can be used in INSERT operator.
For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city = 'Las Vegas'
OR city = 'Denver';

This Oracle example OR condition will insert into the suppliers table, all account_no records, name from the customers table that are in the city ‘Las Vegas’ or ‘Denver’.

Example with UPDATE operator

Oracle condition OR can be used in UPDATE operator.
For example:

UPDATE suppliers
SET supplier_name = 'Nike'
WHERE supplier_name = 'Adidas'
OR available_products < 9;

This example Oracle condition OR will update all values in the supplier_name field of the supplier table in ‘Nike’ where supplier_name was ‘Adidas’ or its availabe_products was less than 9.

Example with DELETE

Oracle condition OR can be used in DELETE operator.
For example:

DELETE FROM suppliers
WHERE supplier_name = 'Epson'
OR employees >= 60;

In this Oracle example, the OR conditions will remove all entries from the suppliers table whose supplier_name was ‘Epson’ or their employees were greater than or equal to 60.

PL/SQL tutorial: WHILE Loop in Oracle Database