Example of Correlated Subquery [Oracle]

Give an example of a correlated subquery using Oracle SQL.

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           |
+-----------+------+--------+--------------+

Example of a Correlated Subquery

SELECT PRODUCT_NAME
FROM PRODUCT P
WHERE LAST_UPDATE_DATE > 
  (SELECT MAX(LOG_DATETIME)
  FROM PET_CARE_LOG PCL
  WHERE P.PRODUCT_ID=PCL.PRODUCT_ID)

Example of correlated subquery with individual components explained.

Use an alias for your table name so that you can easily refer to the table in the subquery. Here, the letter P is the alias for the PRODUCT table.
In this example, the greater than symbol (>) is the operator that compares the outer query data (LAST_UPDATE_DATE) with the subquery data (MAX(LOG_DATETIME)). You can also use other operators such as equal, IN, LIKE, or NOT IN.
The subquery uses the PET_CARE_LOG table and assigns PCL as the alias for this table. This makes it more convenient to write the WHERE clause.
Here is the comparison that makes this a correlated subquery. The data FROM the outer query is used as criteria for the subquery. In this example, the criteria restricts the rows in the subquery to those rows with a PRODUCT_ID that matches the PRODUCT_ID of the current row of the outer query.