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:
Explanation: Our employees table has been created as shown in image.
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.