8.3 Correlated Subqueries & EXISTS Logic

8.3 Correlated Subqueries & EXISTS Logic

In standard (uncorrelated) subqueries, the inner query executes exactly once, independent of the outer query, and passes its static result to the parent statement. However, many business problems require evaluating subqueries dynamically against each row processed by the outer query—such as finding employees who earn more than the average salary of their own specific department.

In Oracle SQL, a Correlated Subquery is a nested query that references one or more columns from its parent query. Because of this cross-reference, the subquery cannot execute in isolation; it must execute repeatedly for each candidate row processed by the outer query.

---

Correlated Subquery Execution Mechanics

The execution lifecycle of a correlated subquery follows a row-by-row candidate evaluation pattern:

+-------------------------------------------------------------------------+
|                    CORRELATED SUBQUERY EXECUTION TRACE                  |
+-------------------------------------------------------------------------+
|                                                                         |
|  OUTER QUERY:                                                           |
|  SELECT employee_id, last_name, salary, department_id                   |
|  FROM employees outer_emp                                               |
|  WHERE salary > (                                                       |
|      SELECT AVG(salary)                                                 |
|      FROM employees inner_emp                                           |
|      WHERE inner_emp.department_id = outer_emp.department_id            |
|  );                                                                     |
|                                                                         |
|  LIFECYCLE PER CANDIDATE ROW:                                           |
|  1. Outer query retrieves candidate row 1: [Emp 100, King, Sal: 24000, Dept: 90]
|  2. Outer passes department_id = 90 into the inner subquery            |
|  3. Inner subquery computes AVG(salary) for Dept 90: (19333.33)         |
|  4. Outer compares King's salary: 24000 > 19333.33 (TRUE) -> Keep row!  |
|                                                                         |
|  5. Outer query retrieves candidate row 2: [Emp 103, Hunold, Sal: 9000, Dept: 60]
|  6. Outer passes department_id = 60 into the inner subquery            |
|  7. Inner subquery computes AVG(salary) for Dept 60: (5760.00)          |
|  8. Outer compares Hunold's salary: 9000 > 5760.00 (TRUE) -> Keep row!  |
|                                                                         |
|  9. Process repeats for EVERY candidate row in the outer table.         |
+-------------------------------------------------------------------------+

Correlation Identifier Syntax

To link the inner subquery with the outer query, you assign a table alias to the outer table and reference that alias inside the subquery's WHERE clause:

-- Find employees whose salary is above the average salary of their own job title
SELECT 
    e.employee_id,
    e.last_name,
    e.job_id,
    e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(sub_e.salary)
    FROM employees sub_e
    WHERE sub_e.job_id = e.job_id
)
ORDER BY e.job_id, e.salary DESC;

---

The EXISTS and NOT EXISTS Operators

The EXISTS operator is a specialized boolean operator designed exclusively for subqueries. It tests whether the subquery returns at least one row.

Boolean Mechanics & Short-Circuit Optimization

• EXISTS (subquery) evaluates to TRUE if the subquery returns 1 or more rows.
• EXISTS (subquery) evaluates to FALSE if the subquery returns 0 rows.
• NOT EXISTS (subquery) evaluates to TRUE if the subquery returns 0 rows, and FALSE if it returns 1 or more rows.

Performance Mechanism: Oracle utilizes short-circuit evaluation for EXISTS. The database engine stops scanning the inner table as soon as the first matching row is encountered. It does not waste I/O computing remaining matches or aggregating row counts.

+-------------------------------------------------------------------------+
|                        EXISTS EVALUATION LOGIC                          |
+-------------------------------------------------------------------------+
|                                                                         |
|  Outer Candidate Row: [Dept 10: Administration]                         |
|  Subquery: SELECT 1 FROM employees WHERE department_id = 10;            |
|  --> Oracle scans employees table... Match found! (Emp 200)             |
|  --> SCAN STOPS IMMEDIATELY. Subquery returns TRUE.                     |
|  --> Dept 10 is included in output.                                     |
|                                                                         |
|  Outer Candidate Row: [Dept 190: Contracting]                           |
|  Subquery: SELECT 1 FROM employees WHERE department_id = 190;           |
|  --> Oracle scans index/table... No matching rows exist.                |
|  --> Subquery returns FALSE.                                            |
|  --> Dept 190 is discarded from output.                                 |
+-------------------------------------------------------------------------+

The SELECT 1 / SELECT * Convention

Because EXISTS only checks for the cardinality (existence) of rows and never returns actual data values to the outer query, the columns specified in the subquery's SELECT list are completely ignored by the Oracle SQL optimizer.

All of the following forms are semantically and performantly identical:

-- All three are 100% equivalent in Oracle SQL:
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id)
WHERE EXISTS (SELECT * FROM employees e WHERE e.department_id = d.department_id)
WHERE EXISTS (SELECT 'X' FROM employees e WHERE e.department_id = d.department_id)
WHERE EXISTS (SELECT NULL FROM employees e WHERE e.department_id = d.department_id)

Finding Rows with Matching Children (EXISTS)

-- Retrieve departments that currently have at least one assigned employee
SELECT d.department_id, d.department_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
)
ORDER BY d.department_id;

Finding Rows with No Matching Children (NOT EXISTS)

-- Retrieve departments that currently have NO assigned employees
SELECT d.department_id, d.department_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
)
ORDER BY d.department_id;

---

Comparison: EXISTS vs. IN

---

Correlated Subqueries in DML Operations

Correlated subqueries are not limited to SELECT queries; they are vital for complex data manipulation in UPDATE and DELETE statements.

1. Correlated UPDATE Statements

You can update column values in a target table based on dynamic calculations from a related table:

-- Update department total payroll based on the sum of employee salaries
UPDATE departments d
SET d.total_payroll = (
    SELECT SUM(e.salary)
    FROM employees e
    WHERE e.department_id = d.department_id
)
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
);

Exam Watchout: Notice the WHERE EXISTS clause in the UPDATE statement above. If you omit the WHERE EXISTS filter, departments that have no employees will have their total_payroll set to NULL (because SUM(salary) on zero rows returns NULL).

2. Correlated DELETE Statements

You can delete parent rows conditionally based on the existence or absence of child records:

-- Delete any customer from CUSTOMERS who has never placed an order in ORDERS
DELETE FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

---

Common 1Z0-071 Correlated Subquery Traps

1. Omitting the Correlation Condition: If you forget to correlate the inner query to the outer alias (e.g., omitting WHERE inner.dept_id = outer.dept_id), the subquery becomes uncorrelated and computes the global value across all rows.
2. Unqualified Ambiguous Column Names: If an inner table and outer table share the same column name (e.g., DEPARTMENT_ID), failing to qualify the column inside the subquery defaults to the innermost scope (inner.department_id = inner.department_id), resulting in a tautology that matches every row.
3. Updating without EXISTS Protection: Running a correlated UPDATE without a WHERE EXISTS guard can inadvertently overwrite valid data with NULLs for parent rows lacking matching child records.