EXISTS Condition
In PL/SQL, the EXISTS condition is a Boolean condition that tests for the existence of rows in a subquery. It returns TRUE if the subquery returns at least one row, otherwise it returns FALSE. The EXISTS condition is commonly used with the WHERE clause to filter rows based on the result of a subquery. It’s useful for checking the existence of related records before performing certain operations such as INSERT, UPDATE, or DELETE.
Syntax:
query...
EXISTS (subquery)
query...
Example 1
The following query will output all the employees who are manager of any other employee.
Query:
SELECT * FROM employees e
WHERE EXISTS
(
SELECT 1 FROM employees m
where m.manager_id=e.employee_id
);
Output:
Explanation: In the above query we have fetched the information related all the employees who are manager of any other employee.
Example 2
The following query prints information about all the employees whose name starts with A.
Query:
SELECT * FROM employees e1
WHERE EXISTS
(
SELECT 1 FROM employees e2
WHERE e1.employee_id=e2.employee_id and e2.employee_name LIKE 'A%'
);
Output:
Explanation: In the above query we have fetched the information related all the employees whose name starts with A.
Example 3:
The following query finds out all the employee which have a manager assigned to them.
Query:
SELECT * FROM employees e1
WHERE EXISTS
(
SELECT 1 FROM employees e2
WHERE e1.employee_id=e2.employee_id and e2.manager_id IS NOT NULL
);
Explanation: In the above query we have fetched the information related all the employee which have a manager assigned to them.
Difference between EXISTS and IN in PL/SQL
PL/SQL is a procedural language designed to enable developers to combine the power of procedural language with Oracle SQL. Oracle develops and serves as one of the three key programming languages embedded in the Oracle database, alongside SQL and Java. PL/SQL includes procedural language elements such as conditions and loops and can handle exceptions (run-time errors). It also allows the declaration of constants and variables, procedures, functions, packages, types and variables of those types, and triggers. In this article, we are going to see the differences between EXISTS and IN in PL/SQL.