A correlated subquery is a subquery that relies on values from the outer query to execute. In Oracle SQL, a correlated subquery can be used to filter or manipulate data based on values in the outer query. Here's an example:
Consider two tables: employees and departments.
employees table:
+-----------+---------+----------+-------------+
| employee_id | name | salary | department_id |
+-----------+---------+----------+-------------+
| 1 | John | 5000 | 10 |
| 2 | Jane | 6000 | 10 |
| 3 | Mark | 5500 | 20 |
| 4 | Lucy | 4500 | 20 |
+-----------+---------+----------+-------------+
departments table:
+---------------+----------+
| department_id | name |
+---------------+----------+
| 10 | HR |
| 20 | IT |
+---------------+----------+
Now, let's say we want to find all employees who earn the maximum salary in their department. We can use a correlated subquery to achieve this:
SELECT e1.employee_id, e1.name, e1.salary, e1.department_id
FROM employees e1
WHERE e1.salary = (
SELECT MAX(e2.salary)
FROM employees e2
WHERE e1.department_id = e2.department_id
);
In this query, the outer query is iterating over each employee (aliased as e1), and the correlated subquery (aliased as e2) calculates the maximum salary for each department. The outer query then filters the results by matching the employee's salary with the maximum salary in their department. The output will be:
+-----------+------+--------+--------------+
| employee_id | name | salary | department_id |
+-----------+------+--------+--------------+
| 2 | Jane | 6000 | 10 |
| 3 | Mark | 5500 | 20 |
+-----------+------+--------+--------------+