The EXISTS condition in Oracle SQL / PLSQL will return any records whenever the “exists” condition is met.
EXISTS condition can be used with SELECT, INSERT, UPDATE and DELETE SQL statements.
Syntax for the EXISTS condition in Oracle SQL / PLSQL is:
SELECT column_name(s)
FROM table_name
WHERE EXISTS (subquery);
Or
INSERT INTO table_name
VALUES(column_name1
,column_name2
,column_name3
.
.
column_nameN)
WHERE EXISTS (subquery);
Or
UPDATE table_name
SET (column_name1 = value/expression
,column_name2 = value/expression
, column_name3 = value/expression
.
.
)
WHERE EXISTS (subquery);
Or
DELETE FROM table_name
WHERE EXISTS (subquery);
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 |
And suppose we also have a table named “comm” as shown below:
| Emp_ID | Commission_Percent |
| 101 | 10 |
| 102 | 20 |
| 103 | 20 |
Scenario 1:
Suppose we want to see the records of employee(s) from ‘employee’ table that also have records in ‘comm’ table.
The above can be achieved as:
SELECT *
FROM employee
WHERE EXISTS(SELECT *
FROM comm
WHERE employee.employee_id = comm.emp_id
);
The result of the above query will be:
| 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 |
Here we have retrieved the records pertaining to employee(s) from ‘employee’ table that also have records in ‘comm’ table using EXISTS condition along with WHERE clause.
Scenario 2:
EXISTS condition can also be used with NOT operator.
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 |
And suppose we also have a table named “comm” as shown below:
| Emp_ID | Commission_Percent |
| 101 | 10 |
| 102 | 20 |
| 103 | 20 |
Suppose we want to see the records of employee(s) from ‘employee’ table that do not have records in ‘comm’ table.
We can achieve the same as:
SELECT *
FROM employee
WHERE NOT EXISTS(SELECT *
FROM comm
WHERE employee.employee_id = comm.emp_id
);
Once we have run the above code following will be the result:
| Employee_ID | Employee_Name | Salary | Department | Commission |
| 104 | Emp D | 30000 | Support | |
| 105 | Emp E | 32000 | Sales | 10 |
Here we have successfully retrieved the records pertaining to employee(s) from ‘employee’ table that do not have records in ‘comm’ table using EXISTS condition with NOT operator in combination with WHERE clause.