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.

Setup Environment

For the demonstration in the following sections, I will first create an employee table and insert some values in it. The following query first creates an employee table that contains information such as id, name, Oracle develops it, and manager_id. Later in the query I inserted some sample data in the table.

Query:

CREATE TABLE employees
( 
    employee_id number(10) NOT NULL,
    employee_name varchar2(50) NOT NULL,
    manager_id number(10)
);


INSERT INTO employees
(employee_id, employee_name, manager_id)
SELECT 1, 'Jack', 2 FROM DUAL
UNION ALL
SELECT 2, 'Jill', NULL FROM DUAL
UNION ALL
SELECT 3, 'Jim', NULL FROM DUAL
UNION ALL
SELECT 4, 'Bill', 3 FROM DUAL
UNION ALL
SELECT 5, 'Ben', NULL FROM DUAL
UNION ALL
SELECT 6, 'Alex', 2 FROM DUAL
UNION ALL
SELECT 7, 'Andrew', 5 FROM DUAL
UNION ALL
SELECT 8, 'Chris', 5 FROM DUAL;

Output:

Output

Explanation: Our employees table has been created as shown in image.

IN Condition

In PL/SQL, the IN condition is a logical condition used in queries to specify a range of values. It is commonly used in the WHERE clause of a SELECT, UPDATE, DELETE, or MERGE statement to filter rows based on a specific list of values. Using the IN condition can make queries more concise and readable, especially when dealing with multiple values or subqueries.

Syntax:

query...
EXISTS (subquery/value_list)
query...

Example 1

The following query will output all the employees which have an odd employee_id:

Query:

SELECT * FROM employees
WHERE employee_id IN 
(
    SELECT employee_id FROM employees
    WHERE MOD(employee_id,2)=1
);

Output:

Output

Example 2

Let’s fetch the information about all the employees whose name starts with A.

Query:

SELECT * FROM employees
WHERE employee_id IN 
(
    SELECT employee_id FROM employees
    WHERE employee_name LIKE 'A%'
);

Output:

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
WHERE employee_id IN 
(
    SELECT employee_id FROM employees
    WHERE manager_id IS NOT NULL
);

Output:

Output

Explanation: In the above query we have fetched the information related all the employee which have a manager assigned to them.

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:

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:

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 vs IN Operator

Let’s understood what IN and EXISTS condition are, let us now understand the differences between the two.

The following are some of the differences between EXISTS and IN conditions in PL/SQL:

EXISTS	IN
Used to check for existence	Used to check for membership
SQL engine stops as soon as one true value found	SQL engine checks all the values provided
EXISTS is faster when result of subquery is large	IN is faster if result of subquery is small
Can be used to compare NULL	Cannot be used to compare NULL

Technical Example

Let’s go through a technical example to solidify our understanding. We are going to make use of the above table and the records in them. Just for completion the following is the statement used to create the table and insert the records.

Query:

CREATE TABLE employees
( 
    employee_id number(10) NOT NULL,
    employee_name varchar2(50) NOT NULL,
    manager_id number(10)
);


INSERT INTO employees
(employee_id, employee_name, manager_id)
SELECT 1, 'Jack', 2 FROM DUAL
UNION ALL
SELECT 2, 'Jill', NULL FROM DUAL
UNION ALL
SELECT 3, 'Jim', NULL FROM DUAL
UNION ALL
SELECT 4, 'Bill', 3 FROM DUAL
UNION ALL
SELECT 5, 'Ben', NULL FROM DUAL
UNION ALL
SELECT 6, 'Alex', 2 FROM DUAL
UNION ALL
SELECT 7, 'Andrew', 5 FROM DUAL
UNION ALL
SELECT 8, 'Chris', 5 FROM DUAL;

In this example we are going to find out all the employees which are manager of any other employee.

Firstly, let use a simple query using IN to find the desired result. The following query selects all the employees which are managers using a subquery.

Query:

SELECT * FROM employees
WHERE employee_id IN 
(
    SELECT manager_id FROM employees
);

However, when we run the above query it doesn’t return any values. This would mean that there are no employees which are manager, which is not true as we understand by seeing the data. So something must be wrong.

If we run the inner query independently, we will find out that it returns the following data.

Query:

SELECT manager_id FROM employees;

Output:

Output

Explanation: As we can see it returns NULL values. Whenever there is NULL values in the inner query, IN fails as it doesn’t have the ability to compare to NULL value. Hence, it returns false for every record which results in no data being printed.

Now let’s modify the IN query to get the correct output. The following modified query only retrieves the manager_ids which are not NULL.

Query:

SELECT * FROM employees
WHERE employee_id IN 
(
    SELECT manager_id FROM employees 
    WHERE manager_id IS NOT NULL
);

Output:

Output

Explanation: As we can see in the above image, the query now works fine and returns the correct employee data.

Let us now try to check this using EXISTS. In the following query we select only those employees which are manager of some employee in the inner query and then output them.

Query:

SELECT * FROM employees e
WHERE EXISTS 
(
    SELECT 1 FROM employees m
    where m.manager_id=e.employee_id
);

Output:

Output

Explanation: As we can see from the above image, we didn’t need to specially handle NULL values in the case of NOT EXISTS statement.

Conclusion

In this article we went through IN and EXISTS operators and understood what they are actually. We also looked at the differences between them and we finally wrapped the article with a technical example giving a sense of how we can use the concepts we learned in this article in real-life. While both operators are essential for filtering data and differs in terms of performance and handling NULL values. EXISTS is more efficient when dealing with large result sets, whereas IN may be faster for smaller sets. Moreover, EXISTS can evaluate NULL values without requiring special handling, unlike IN, which may need explicit filtering to avoid unexpected results.