The OR condition in Oracle SQL / PLSQL allows us to filter the records from the result of a query based on 2 or more than 2 conditions.
OR condition can be used with SELECT, INSERT, UPDATE and DELETE statements.
Syntax for the OR condition in Oracle SQL / PLSQL is:
SELECT column_name(s)
FROM table_name
WHERE condition1
OR condition2
OR condition3
.
.
OR conditionN;
Or
INSERT INTO table_name
VALUES(column_name1
,colum_name2
,column_name3
.
.
Column_nameN)
WHERE condition1
OR condition2
OR condition3
.
.
OR conditionN;
Or
UPDATE table_name
SET (column_name1 = value/expression
,column_name2 = value/expression
, column_name3 = value/expression
.
.
)
WHERE condition1
OR condition2
OR condition3
.
.
OR conditionN;
Or
DELETE FROM table_name
WHERE condition1
OR condition2
OR condition3
.
.
OR conditionN;
Let’s take an example for understanding:
Suppose have a table named ‘employee’ in the database as shown below.
| Employee_ID | Employee_Name | Salary | Department | Commission |
| 101 | Emp A | 10000 | Sales | 10 |
| 102 | Emp B | 20000 | IT | 20 |
| 103 | Emp C | 28000 | IT | 20 |
| 104 | Emp D | 30000 | Support | |
| 105 | Emp E | 32000 | Sales | 10 |
Scenario 1:
Suppose we want to see the records of employee having Salary more than ‘20000’ or working in the ‘Sales’ department then we can achieve the same using OR condition as follows:
SELECT * FROM employee WHERE department = 'Sales' OR salary > 20000;
The result of the above query will be:
| Employee_ID | Employee_Name | Salary | Department | Commission |
| 101 | Emp A | 10000 | Sales | 10 |
| 103 | Emp C | 28000 | IT | 20 |
| 104 | Emp D | 30000 | Support | |
| 105 | Emp E | 32000 | Sales | 10 |
Here we have retrieved the records pertaining to employee having Salary more than ‘20000’ or working in the ‘Sales’ department using OR condition along with WHERE clause.
Scenario 2:
We can use multiple OR conditions in a query.
Let’s assume that we have a new table namely ‘employee’ as shown below:
| Employee_ID | Employee_Name | Salary | Department | Commission |
| 101 | Emp A | 10000 | Sales | 10 |
| 102 | Emp B | 20000 | IT | 20 |
| 103 | Emp C | 28000 | IT | 20 |
| 104 | Emp D | 30000 | Support | |
| 105 | Emp E | 32000 | Sales | 10 |
Suppose we want to view all the records of employees who are having Salary less than ‘30000’, or working in ‘Sales’ department or having commission less than ’20’.
We can achieve the same as:
SELECT * FROM employee WHERE department = 'Sales' OR salary < 30000 OR commission < 20;
Once we have run the above code following will be the result:
| Employee_ID | Employee_Name | Salary | Department | Commission |
| 101 | Emp A | 10000 | Sales | 10 |
| 102 | Emp B | 20000 | IT | 20 |
| 103 | Emp C | 28000 | IT | 20 |
| 105 | Emp E | 32000 | Sales | 10 |
Here we have successfully retrieved the records pertaining to employees who are having Salary less than ‘30000’, or working in ‘Sales’ department or having commission less than ’20’ using OR clause in combination with WHERE clause.