PL/SQL EXISTS Operator

The EXISTS operator in PL/SQL is a powerful tool used to check the existence of records in a subquery. Unlike traditional comparison operators that evaluate data values, EXISTS focuses on whether a set of conditions returns any rows. It is commonly used to determine the presence or absence of records that satisfy specific conditions.

If a subquery returns one or more rows, the EXISTS operator evaluates to TRUE; otherwise, it returns FALSE for other conditions. In this article, we will explain the PL/SQL EXISTS operator in detail with syntax, examples, and outputs to help us understand its usage and performance benefits.

What is the PL/SQL EXISTS Operator?

The primary purpose of the EXISTS operator is to determine whether a subquery returns any rows. Rather than fetching the actual data, EXISTS simply checks whether rows meeting certain conditions are present. If at least one row is returned, EXISTS evaluates to TRUE; if no rows are returned, it evaluates to FALSE.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE EXISTS (subquery);

key terms

• column_name(s): The columns we want to retrieve from table_name.
  
• table_name: The main table from which we want to select the data.
  
• subquery: The nested query that is checks for presence of the rows.

Examples of PL/SQL EXISTS Operator

Here,we will explore how to use the EXISTS operator with practical examples. By applying this operator to real-world scenarios, we can see how it helps to check for the existence of certain conditions in subqueries. Each example will demonstrate a different way to use EXISTS in querying data from a relational database.

Employees Table

We will first create an employees table with sample data. This table includes employee details such as their ID, name, salary, and manager ID. This setup allows us to simulate various scenarios, such as identifying managers or comparing employee salaries with their managers.

Query:

CREATE TABLE employees (
    emp_id     NUMBER(10) PRIMARY KEY,   -- Unique employee ID
    emp_name   VARCHAR2(20),            -- Employee name
    manager_id NUMBER(10),               -- Manager ID (references another employee)
    salary     NUMBER(10, 2),            -- Employee salary with two decimal places
    CONSTRAINT fk_manager
        FOREIGN KEY (manager_id)         -- Foreign key constraint for self-referencing manager ID
        REFERENCES employees(emp_id)     -- References the emp_id of another employee
);


-- Insert sample employees
INSERT INTO employees (emp_id, emp_name, manager_id, salary)
VALUES (1, 'John Smith', NULL, 12000);  -- Top-level manager (e.g., CEO)

INSERT INTO employees (emp_id, emp_name, manager_id, salary)
VALUES (2, 'Alice Johnson', 1, 9000);  -- Alice is managed by John

INSERT INTO employees (emp_id, emp_name, manager_id, salary)
VALUES (3, 'Mark Williams', 1, 8500);  -- Mark is also managed by John

INSERT INTO employees (emp_id, emp_name, manager_id, salary)
VALUES (4, 'Emily Davis', 2, 6000);    -- Emily is managed by Alice

INSERT INTO employees (emp_id, emp_name, manager_id, salary)
VALUES (5, 'David Harris', 2, 6500);   -- David is managed by Alice

INSERT INTO employees (emp_id, emp_name, manager_id, salary)
VALUES (6, 'Sarah Wilson', 3, 7500);   -- Sarah is managed by Mark

INSERT INTO employees (emp_id, emp_name, manager_id, salary)
VALUES (7, 'James Brown', 3, 7000);    -- James is managed by Mark

-- Check the table
SELECT * FROM employees;

Output

Example 1: Find Employees Who Are Managers

Let us consider the above table, we want to retrieve names of the employees who are managers that means who are referenced as the manager_id by other employees in same table.

Query:

SELECT emp_name
FROM employees e1
WHERE EXISTS (SELECT 1
              FROM employees e2
              WHERE e2.manager_id = e1.emp_id);

Output

Explanation:

• The main query retrieves the emp_name from the employees table (aliased as e1).
  
• The subquery checks if the emp_id from e1 is referenced as a manager_id in any row of the employees table (aliased as e2).
  
• The EXISTS operator evaluates to TRUE if at least one employee in e2 has the current employee from e1 as their manager.
  
• The result includes John Smith, Alice Johnson, and Mark Williams, who are referenced as managers by other employees.

Example 2: Find Employees Earning More Than Their Managers:

In this example, we want to find employees who earn more than their managers using the EXISTS operator. We will query the employees table to compare each employee’s salary with their manager’s salary. If an employee's salary exceeds that of their manager, their name will be included in the result.

Query:

SELECT emp_name
FROM employees e1
WHERE EXISTS (SELECT 1
              FROM employees e2
              WHERE e1.manager_id = e2.emp_id
              AND e1.salary > e2.salary);

Output

no rows selected

Explanation:

• The main query selects emp_name from the employees table (e1).
  
• The subquery checks if an employee's manager_id in e1 matches the emp_id of another employee in e2, meaning the employee reports to that manager.
  
• It also checks if the employee's salary (e1.salary) is greater than their manager's salary (e2.salary).
  
• The EXISTS operator evaluates to TRUE if an employee earns more than their manager, but since no such employees exist in this case, no rows are returned.

Conclusion

In conclusion, the EXISTS operator in the PL/SQL is the versatile and the efficient tool for performing the conditional checks based on presence of the related rows in the subqueries. If is focuses more on whether subquery returns one or more rows, without the comparing specific values.

By Using the EXISTS, we can simplify the queries and optimize the performance, especially when dealing with the large datasets or the complex relationships between the tables. Therefore, understanding and using EXISTS operator effectively can enhance our query-writing skills and overall database performance.