How to Write Semi-Joins with EXISTS in Oracle

"Customers who bought a business ticket" and "airports with no routes" sound like join questions, but a plain join answers them badly: it repeats a customer once for every matching ticket, and it cannot list rows that have no match at all without extra work. A semi-join returns each row that has at least one match, once. An anti-join returns each row that has none. In Oracle SQL you write them with EXISTS and NOT EXISTS.

Code for This Guide

The main examples are in the examples/joins and examples/basic-elements folders of the Oracle Database 26ai code repository on GitHub, each with its output. They query NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

select ... from table1 t1
where  [not] exists (select null from table2 t2
                     where  t2.col = t1.col [and ...])

EXISTS is true when the subquery returns at least one row, and NOT EXISTS when it returns none. The select list of the subquery does not matter, since only the existence of rows is tested; select null, 1, or * as you prefer. The subquery is correlated: it refers to the outer row.

The Problem with a Plain Join

Joining customers to their business tickets returns one row per ticket, so a customer with five business tickets appears five times:

Example:

-- a plain join repeats each customer once per matching ticket
select count(*) as joined_rows, count(distinct c.customer_id) as customers
from   customers c
join   bookings b on b.customer_id = c.customer_id
join   tickets t on t.booking_id = b.booking_id
where  t.cabin = 'BUSINESS';

-- the same semi-join written with IN
select count(*) as business_customers
from   customers c
where  c.customer_id in (select b.customer_id
                         from   bookings b join tickets t on t.booking_id = b.booking_id
                         where  t.cabin = 'BUSINESS');

Output:

   JOINED_ROWS    CUSTOMERS
______________ ____________
           270           67

   BUSINESS_CUSTOMERS
_____________________
                   67

The join produces 270 rows for 67 customers, and DISTINCT would be needed to undo the duplication. The second query is a semi-join written with IN, which returns each customer once.

Semi-Joins and Anti-Joins with EXISTS

The first query counts the customers with at least one business ticket; the second counts the customers who never wrote a review.

Example:

-- semi-join: customers with at least one business ticket (each customer once)
select count(*) as business_customers
from   customers c
where  exists (select null from bookings b join tickets t on t.booking_id = b.booking_id
               where b.customer_id = c.customer_id and t.cabin = 'BUSINESS');

-- anti-join: customers who never wrote a review
select count(*) as silent_customers
from   customers c
where  not exists (select null from reviews r where r.customer_id = c.customer_id);

Output:

   BUSINESS_CUSTOMERS
_____________________
                   67

   SILENT_CUSTOMERS
___________________
                 58

The optimizer runs these as special joins that stop at the first match for each customer, so EXISTS does not read more than it needs. Listing the rows instead of counting them works the same way:

Example:

-- the first five customers who never wrote a review
select c.customer_id, c.last_name
from   customers c
where  not exists (select null from reviews r where r.customer_id = c.customer_id)
order  by c.customer_id
fetch  first 5 rows only;

Output:

   CUSTOMER_ID LAST_NAME
______________ ____________
             8 Laurent
             9 Ito
            10 Reddy
            11 Suzuki
            15 Aziz

Combine EXISTS and NOT EXISTS

Conditions can be combined. The first query finds the airport with no routes out; the second finds the countries that have customers but no airport.

Example:

select a.airport_code, a.city
from   airports a
where  not exists (select null from routes r where r.origin = a.airport_code);

select c.country_name
from   countries c
where  exists (select null from customers u where u.country_code = c.country_code)
and    not exists (select null from airports a where a.country_code = c.country_code);

Output:

AIRPORT_CODE    CITY
_______________ ____________
KTM             Kathmandu

COUNTRY_NAME
_______________
Ireland

EXISTS, IN, and NOT IN

For semi-joins, EXISTS and IN are interchangeable, and the optimizer usually produces the same plan for both. For anti-joins, prefer NOT EXISTS: NOT IN returns nothing at all as soon as the subquery returns a single NULL, a trap that is easy to miss when the column is nullable.

Related Guides

• How to Write Correlated Subqueries in Oracle SQL
• How to Use Subqueries in Oracle SQL
• Oracle SQL INNER JOIN: Complete Guide to Matching Records Between Tables

Conclusion

Use EXISTS for semi-joins, which return each row with at least one match exactly once, and NOT EXISTS for anti-joins, which return the rows with no match. Both are correlated subqueries that stop at the first match, both avoid the duplicates of a plain join, and NOT EXISTS avoids the NULL trap of NOT IN.