8.3 Correlated Subqueries & EXISTS Logic

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

8.3 Correlated Subqueries & EXISTS Logic

Key Takeaways Explain This Topic AI

A correlated subquery references one or more columns from the outer query, executing once for every candidate row evaluated by the outer query.

The EXISTS operator tests for the existence of rows in the subquery, returning TRUE as soon as the first matching row is found (short-circuit boolean evaluation).

NOT EXISTS evaluates to TRUE if the subquery returns zero rows; it is inherently NULL-safe because it verifies row existence rather than value comparison.

The projection list in an EXISTS subquery is completely ignored by Oracle; SELECT 1, SELECT *, or SELECT 'X' are functionally identical and incur zero performance difference.

Correlated subqueries are widely used in DML statements, such as updating column values based on child/lookup tables or deleting records based on parent-child existence.

Last updated: August 2026

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

Characteristic EXISTS / NOT EXISTS IN / NOT IN
Evaluation Type Boolean existence test (row cardinality) Value set membership comparison
Correlation Almost always correlated Can be uncorrelated or correlated

Optimizer Execution Stops at first match (short-circuit) Must evaluate full distinct value set (unless transformed)

Handling of NULLs NULL-safe : Returns TRUE/FALSE cleanly DANGER : NOT IN with NULL returns empty result set!

SELECT List Requirement Column list ignored ( SELECT 1 standard) Must project exactly one matching column
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

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.

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.

Updating without EXISTS Protection: Running a correlated UPDATE without a WHERE EXISTS guard can inadvertently overwrite valid data with NULL s for parent rows lacking matching child records.