Oracle EXISTS and NOT EXISTS Correlated Subqueries

The Oracle EXISTS condition tests whether a subquery returns at least one row. It is useful when a business question concerns the presence or absence of a relationship: Does this department contain an employee? Has this customer placed an order? Is there no matching detail row? The outer query returns its own columns; the subquery supplies a true-or-false existence test.

This purpose differs from that of IN. An IN condition tests whether an expression equals a member of a list or subquery result. For some positive membership questions, IN and a correlated EXISTS query produce the same result. That equivalence does not mean that one syntax is always faster. Oracle AI Database 26ai can transform eligible subqueries, and the optimizer chooses access paths and join methods from the statement, object definitions, statistics, and data distribution.

EXISTS tests whether qualifying rows are present

A common correlated EXISTS pattern has this form:

SELECT o.col1,
       o.col2
FROM   outer_table o
WHERE  EXISTS (
         SELECT 1
         FROM   inner_table i
         WHERE  i.key_value = o.key_value
       );

The statement can be read as a four-part process:

1. The outer query considers a row from outer_table.
2. The subquery searches for an inner_table row that satisfies its predicates.
3. The predicate i.key_value = o.key_value correlates the inner search with the current logical outer row.
4. If at least one qualifying inner row exists, the condition is true and the outer row remains in the result.

The expression following the inner SELECT does not provide a value to the outer query. If the same inner rows qualify, SELECT 1, SELECT NULL, and SELECT i.key_value have the same existence truth. SELECT 1 is a widely used convention because it makes the intent easy to recognize. It does not, by itself, guarantee fewer reads or a particular execution plan.

A subquery used with EXISTS does not have to be correlated, but the meaning changes when it is not. An uncorrelated existence test returns the same true-or-false answer for every outer row. If its subquery returns anything, every outer row that satisfies the other predicates passes; if it returns nothing, none of those rows pass. This behavior can be intentional for a database-wide prerequisite, but it often signals that a relationship predicate was accidentally omitted. Use table aliases consistently and identify the correlation predicate before running the query.

Find departments that contain employees

The following query answers the question, “Which departments have at least one 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;

The departments table supplies the result columns. The correlated subquery tests the relationship through e.department_id = d.department_id. A department appears once when at least one employee matches. Ten matching employees do not cause the department row to be returned ten times because the query tests existence rather than joining employee rows into the displayed result.

The same positive relationship can be expressed as a membership test:

SELECT d.department_id,
       d.department_name
FROM   departments d
WHERE  d.department_id IN (
         SELECT e.department_id
         FROM   employees e
       )
ORDER  BY d.department_id;

For this relationship, both statements return departments whose identifier occurs among the employee department identifiers. Duplicate identifiers returned by the subquery do not duplicate the outer department row. The IN form emphasizes set membership, while the EXISTS form emphasizes the presence of a related row. Choose the form that states the business rule clearly, then inspect a current plan when performance needs to be measured.

Find departments that contain no employees

NOT EXISTS reverses the existence test. This query returns departments for which no employee has the same department identifier:

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;

For each logical department row, the correlated subquery asks whether a matching employee exists. NOT EXISTS is true only when that search produces no row. This is often the clearest way to express an absence rule, such as customers without orders, products without inventory records, or parents without children.

Why NOT IN requires special attention to nulls

A superficially similar query uses NOT IN:

SELECT d.department_id,
       d.department_name
FROM   departments d
WHERE  d.department_id NOT IN (
         SELECT e.department_id
         FROM   employees e
       )
ORDER  BY d.department_id;

The two negative forms are not automatically interchangeable. NOT IN means that the outer value must differ from every value returned by the subquery. If the subquery produces a null, SQL cannot determine whether the outer value differs from that unknown value. The condition can become unknown for the candidate rows and return no departments. This behavior follows SQL's three-valued logic; it is not an optimizer error.

With the correlated equality used by NOT EXISTS, an inner null department identifier does not equal a non-null outer identifier, so that inner row does not prevent other outer departments from qualifying. An outer row whose own identifier is null also finds no equality match and can satisfy NOT EXISTS. If the business rule excludes outer rows with missing identifiers, state that requirement explicitly:

SELECT d.department_id,
       d.department_name
FROM   departments d
WHERE  d.department_id IS NOT NULL
AND    NOT EXISTS (
         SELECT 1
         FROM   employees e
         WHERE  e.department_id = d.department_id
       )
ORDER  BY d.department_id;

Do not summarize this distinction by saying that NOT EXISTS “handles nulls automatically.” Examine which columns can be null and decide what a null means in the business rule.

Choose the condition that matches the question

An EXISTS select list cannot return inner-table values for display. When the result needs an employee name, order date, or other inner column, use a join, scalar subquery, or another form designed to return that value. Use EXISTS when the inner table's role is to determine whether the outer row qualifies.

How Oracle may transform existence tests

A semijoin returns an outer row when at least one inner match exists. It does not return the inner columns and does not multiply the outer row when several inner rows match. An antijoin returns an outer row when no corresponding inner row exists. These logical ideas closely match many EXISTS and NOT EXISTS questions.

Oracle 26ai may unnest an eligible EXISTS or IN subquery and optimize it as a semijoin. Eligible negative forms may be optimized as antijoins. The transformed statement may use nested loops, hashing, merging, indexes, or scans. Query shape and restrictions can prevent a particular transformation, so a semijoin or antijoin operation is possible rather than guaranteed.

This is why an execution plan copied from an older database should not be presented as the plan every visitor will receive. Estimated rows, bytes, cost, elapsed time, access paths, and index names depend on a specific schema and statistics set. Logical correctness comes first. For a performance investigation, collect evidence from the current statement and environment rather than inferring the physical algorithm from the SQL text alone.

Rewrite IN as EXISTS when existence expresses the relationship

Suppose the requirement is to list employees assigned to a department located in New York. A membership form is:

SELECT e.emp_id,
       e.emp_name,
       e.salary
FROM   employees e
WHERE  e.dept_id IN (
         SELECT d.dept_id
         FROM   departments d
         WHERE  d.location = 'New York'
       )
ORDER  BY e.emp_id;

The equivalent existence-oriented form correlates each employee with a qualifying department:

SELECT e.emp_id,
       e.emp_name,
       e.salary
FROM   employees e
WHERE  EXISTS (
         SELECT 1
         FROM   departments d
         WHERE  d.dept_id = e.dept_id
         AND    d.location = 'New York'
       )
ORDER  BY e.emp_id;

The predicate d.dept_id = e.dept_id is essential. It connects the inner department to the current outer employee. If that predicate were omitted, the subquery would merely ask whether any New York department exists. The answer would then be the same for every employee: either all outer employees would pass the existence test or none would pass it.

This rewrite is useful for learning how correlation changes the question, but it is not an automatic tuning prescription. Both forms are readable, and Oracle may transform them into equivalent plan shapes. Do not select one merely because the inner result is assumed to be small or large.

Return titles written by authors with multiple books

The BOOKSHELF_AUTHOR example combines grouping with an existence test. Begin by finding authors represented by more than one row:

SELECT ba.author_name,
       COUNT(*) AS title_count
FROM   bookshelf_author ba
GROUP  BY ba.author_name
HAVING COUNT(*) > 1
ORDER  BY ba.author_name;

The lesson's source data produces these author counts:

Adding title directly to this grouping changes the question. If (author_name, title) uniquely identifies a row, every group contains one row and the HAVING condition finds nothing:

SELECT ba.author_name,
       ba.title,
       COUNT(*) AS row_count
FROM   bookshelf_author ba
GROUP  BY ba.author_name,
          ba.title
HAVING COUNT(*) > 1;

To display every title for an author who has multiple bookshelf rows, let the outer query return the author and title. Use the correlated subquery only to test whether the current author's group has more than one row:

SELECT ba.author_name,
       ba.title
FROM   bookshelf_author ba
WHERE  EXISTS (
         SELECT 1
         FROM   bookshelf_author peer
         WHERE  peer.author_name = ba.author_name
         GROUP  BY peer.author_name
         HAVING COUNT(*) > 1
       )
ORDER  BY ba.author_name,
          ba.title;

The correlation predicate ties each inner group to the author in the current outer row. The subquery does not return a title. It answers whether the author has more than one qualifying row, after which the outer query returns that author's individual titles. The source packet does not supply the title values, so they should be obtained by running the query rather than invented in the lesson.

EXISTS and NOT EXISTS summary

• EXISTS is true when its subquery returns at least one row.
• NOT EXISTS is true when the correlated subquery returns no row.
• The subquery select list does not pass a value to the outer query; SELECT 1 is a convention.
• A correlation predicate connects the inner search to the current logical outer row.
• Positive IN and EXISTS forms can express the same relationship, but they emphasize different ways of stating it.
• NOT IN is null-sensitive, so analyze nullable expressions before treating it as equivalent to NOT EXISTS.
• Oracle may transform eligible queries into semijoin or antijoin plans; no particular physical join method is guaranteed.
• Choose correct, clear SQL first and use current execution evidence for tuning decisions.

Practice interpreting correlated subqueries

Apply the correlation rules from this lesson in the Interpret Correlated Subqueries practice activity.

In the next lesson, you will examine the unusual but useful case of placing a subquery in the FROM clause.