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:

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:

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:

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.

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.