Home » Oracle SQL » Correlated Subquery in Oracle

Correlated Subquery in Oracle

In a correlated subquery, the inner subquery refers to an object from outer query.

Before we understand the concept behind correlated subquery, lets revisit the concept of subquery by seeing this example:

SELECT *
FROM employees
WHERE salary > (SELECT salary
FROM employees
WHERE first_name = 'Den');

This query returns a list of employees who are having salary greater than ‘Den’. The inner subquery gives us the salary of employee named

In this example, the nested subquery is complete in itself and gets executed first before outer query. In case of correlated subquery, the nested query references to the outer query and may need to evaluated once for each row selected by outer query.

Let’s understand this through the following examples:

Oracle Correlated Subquery Example

We will be using Employees table from HR schema for this example:

1. Simple Correlated Subquery Example

Suppose, we want to find a list of all employees who are earning more than the average salary of employees in their department, then we use the following query:

SELECT first_name, salary, department_id
FROM employees e
WHERE salary >
 (SELECT AVG(salary)
 FROM employees
WHERE department_id =
e.department_id)
ORDER BY salary

Result:

The query returns all the employees who are earning more than the average salary in their department.

In this case, the nested subquery refers to the department_id from outer query for returning a result on a row by row basis. Then the outer query evaluates each row to check whether the employee is earning more than the average salary in his/her department or not. If a row satisfies the condition, then it gets returned in the final output.

Let’s see one more query of this type to get a hang of this concept:

The following query returns a list of all employees who are earning more than the minimum salary offered in their department:

SELECT first_name, salary, department_id
FROM employees e
WHERE salary >
 (SELECT MIN(salary)
 FROM employees
WHERE department_id =
e.department_id)
ORDER BY salary

Result:

As Oracle needs to constantly move back and forth between the inner and outer query on a row by row basis for evaluating the results, Correlated Subquery can be slow in execution.

2. Correlated Subquery in SELECT clause Example

Suppose, we want to find salary and average salary in an employee ‘s department side by side, then we we use this query:

SELECT
    employee_id,
    first_name,
    last_name,
    salary,
    ROUND(
        (   SELECT
                AVG( salary )
            FROM
                employees
            WHERE
                department_id = e.department_id
        ),
        3
    ) avg_salary
FROM
    employees p
ORDER BY
    salary;

Result:

Correlated Subquery in SELECT Clause Example

The department ID from nested subquery is referencing to department_id in outer query and it returns employee’s salary and average salary in their department side by side.

3. Correlated Subquery with EXISTS Operator example

The EXISTS operator checks whether any row is found. The nested subquery is used an operand for EXISTS operator. If nested subquery returns some rows, EXISTS operator returns TRUE if it doesn’t then it returns FALSE.

Let’s see one example:

The following correlated subquery returns all departments which have at least one employee working in them.

SELECT d.department_id, d.department_name
FROM departments d
WHERE EXISTS (SELECT *
 FROM employees e
 WHERE d.department_id =
e.department_id)
ORDER BY d.department_id

Result:

Correlated SUBQUERY with EXISTS Operator

The query returns all departments where at least one employee is working.

Now let’s see a similar example of NOT EXISTS subquery:

Using NOT EXISTS operator gives us all the departments in which there is no one working.

SELECT d.department_id, d.department_name
FROM departments d
WHERE NOT EXISTS (SELECT *
 FROM employees e
WHERE d.department_id =
e.department_id)
ORDER BY d.department_id

Result: