Oracle / PLSQL: OR Condition

This Oracle tutorial explains how to use the Oracle OR condition with syntax and examples.

Description

The Oracle OR condition is used to test multiple conditions where records are returned when any one of the conditions are met. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the OR condition in Oracle/PLSQL is:

WHERE condition1
OR condition2
...
OR condition_n;

Parameters or Arguments

condition1, condition2, condition_n

Any of the conditions can be met for the records to be selected.

Note

• The Oracle OR condition allows you to test 2 or more conditions.
• The Oracle OR condition requires that any of the conditions (ie: condition1, condition2, condition_n) be must be met for the record to be included in the result set.

Example - With SELECT Statement

The first Oracle OR condition example that we'll take a look at involves an Oracle SELECT statement with 2 conditions:

SELECT *
FROM customers
WHERE state = 'California'
OR available_credit > 500;

This Oracle OR condition example would return all customers that reside in either the state of California or have available_credit greater than 500. Because the * is used in the SELECT statement, all fields from the customers table would appear in the result set.

Example - With SELECT Statement (3 conditions)

The next Oracle OR example looks at an Oracle SELECT statement with 3 conditions. If any of these conditions is met, the record will be included in the result set.

SELECT supplier_id
FROM suppliers
WHERE supplier_name = 'CISCO'
OR city = 'New York'
OR offices > 5;

This Oracle OR condition example would return all supplier_id values where the supplier's name is either CISCO, city is New York, or offices is greater than 5.

Example - With INSERT Statement

The Oracle OR condition can be used in the Oracle INSERT statement.

For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city = 'New York'
OR city = 'Newark';

This Oracle OR example would insert into the suppliers table, all account_no and name records from the customers table that reside in either New York or Newark.

Example - With UPDATE Statement

The Oracle OR condition can be used in the Oracle UPDATE statement.

For example:

UPDATE suppliers
SET supplier_name = 'Apple'
WHERE supplier_name = 'RIM'
OR available_products < 10;

This Oracle OR condition example would update all supplier_name values in the suppliers table to Apple where the supplier_name was RIM or its availabe_products was less than 10.

Example - With DELETE Statement

The Oracle OR condition can be used in the Oracle DELETE statement.

For example:

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

This Oracle OR condition example would delete all suppliers from the suppliers table whose supplier_name was HP or its employees was greater than or equal to 60.