NOT EXISTS for missing rows

Concepts covered: Existence Checks

NOT EXISTS flips the answer: it is TRUE when the subquery returns 0 rows for the current outer row. That makes it the standard tool for orphan hunting, finding rows on one side with no partner on the other. Stores with no orders, users who never logged in, invoices with no payment and products nobody reviewed all share this query shape.

Watch a missing partner keep a row

The animation runs this shape on a support question: which customers have no open ticket? Acme and Initech each have an open ticket, so their subqueries return a row and NOT EXISTS is FALSE. Globex has only a closed ticket, which the status condition inside the subquery filters out, so its subquery comes back empty. Umbrella has no tickets at all. Globex and Umbrella stay.

SELECT c.customer_id, c.company
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM support_tickets t
  WHERE t.customer_id = c.customer_id
    AND t.status = 'open'
)

Where the condition sits changes the question

The status check belongs inside the subquery. There it redefines which partner rows count, so a store whose only order was refunded has no paid partner and stays. A condition in the outer WHERE filters the stores themselves, and stores has no status column to test. Each of the 3 tabs below asks a different question of the same 2 tables, which this section aliases as st and od.

No orders

Stores with no order of any kind. Fern Studio and Pixel Prints come back.

SELECT
  st.store_id,
  st.store_name
FROM stores st
WHERE NOT EXISTS (
  SELECT 1
  FROM orders od
  WHERE od.store_id = st.store_id
)

No paid

Stores with no paid order. Tidal Tees joins them, since its only order was refunded.

SELECT
  st.store_id,
  st.store_name
FROM stores st
WHERE NOT EXISTS (
  SELECT 1
  FROM orders od
  WHERE od.store_id = st.store_id
    AND od.status = 'paid'
)

Paid only

Stores with a paid order and no refunded one. Only North Knits passes both checks.

SELECT
  st.store_id,
  st.store_name
FROM stores st
WHERE EXISTS (
  SELECT 1
  FROM orders od
  WHERE od.store_id = st.store_id
    AND od.status = 'paid'
)
  AND NOT EXISTS (
    SELECT 1
    FROM orders od
    WHERE od.store_id = st.store_id
      AND od.status = 'refunded'
  )

The third tab shows why these filters compose well: each EXISTS or NOT EXISTS is an independent yes-or-no check on the same outer row, so you combine them with AND and OR, grouped by parentheses, the way you would any other condition. Maple Mugs fails there because refunded order 102 makes its NOT EXISTS FALSE.

Support wants every customer who has never had a ticket closed. Using the customers and support_tickets tables from the animation, put the tiles in order. Two tiles are not needed.

NOT EXISTS next to EXCEPT

You already know a set-based way to find missing ids: SELECT store_id FROM stores EXCEPT SELECT store_id FROM orders returns 2 and 4. It answers the same question, with 3 differences that matter in practice.

NOT EXISTS

• Returns whole outer rows, every column intact
• Keeps duplicate outer rows as they are
• The partner test can add any extra condition, such as a status or a date range

EXCEPT

• Returns only the columns both queries select
• Removes duplicates from what it returns
• Compares entire rows for equality and nothing else

To get store names out of EXCEPT you would feed its ids back into another query. NOT EXISTS returns the store rows directly, which is why it is the default for orphan checks in pipelines, such as these:

• Orders whose store_id points at no store: a broken foreign key
• Customers with a signup row but no profile row: a half-finished pipeline run
• Shipments with no matching order: rows loaded out of sequence
• Rows in yesterday's snapshot missing from today's: silent deletions

A typo compares the order to itself. Which stores does this return?

SELECT
  st.store_name
FROM stores st
WHERE NOT EXISTS (
  SELECT 1
  FROM orders od
  WHERE od.store_id = od.store_id
)

• No stores, because every subquery finds rows
• Fern Studio and Pixel Prints, as before
• All 5 stores, since the link is gone
• Tidal Tees only, since its order was refunded

About this lesson

This section is part of the Filtering: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.