Correlated subqueries (EXISTS)

Concepts covered: Existence Checks

EXISTS takes a subquery and asks a single yes-or-no question about it: does this subquery return at least 1 row? The answer is TRUE as soon as a row turns up and FALSE when none do. It never reads the values in those rows, so putting SELECT 1 inside it gives the same result as SELECT * or SELECT order_id. Writing SELECT 1 is the convention because it tells the next reader that only existence matters.

The form you will reach for most is correlated: the subquery mentions a column from the outer query, so it's asked again for every outer row with that row's values filled in. For store 1 it reads as orders whose store_id is 1, and for store 2 as orders whose store_id is 2. Each outer row gets its own answer, and that answer decides whether the row stays. This section and the next 2 use the 5 stores and 5 orders below.

One existence check per store

Press play to watch the check run once per store. For Maple Mugs the subquery finds orders 101 and 102, yet Maple Mugs lands in the result a single time. EXISTS keeps or drops the outer row and adds nothing to it, so a store with 40 matching orders still produces 1 row.

SELECT
  s.store_id,
  s.store_name
FROM stores s
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.store_id = s.store_id
)

Maple Mugs and North Knits survive, and so does Tidal Tees. Order 105 carries a NULL store_id, and o.store_id = s.store_id is never TRUE when one side is NULL, so that order matches no store and changes nothing here. Keep order 105 in mind: in the NOT IN section later in this lesson, the same row empties an entire result.

Narrow what counts as a match

Every condition you add to the subquery's WHERE narrows what counts as a related row. Adding AND o.status = 'paid' turns the question into: does this store have a paid order? Tidal Tees drops out, because its only order, 104, was refunded. Maple Mugs stays: order 102 was refunded, but order 101 is paid, and 1 paid row is enough.

SELECT
  s.store_id,
  s.store_name
FROM stores s
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.store_id = s.store_id
    AND o.status = 'paid'
)

The link can also combine several columns and comparisons. On a schema with launch dates, o.store_id = s.store_id AND o.created_at >= s.launched_at asks whether a store took an order after it launched. IN compares the outer column to the subquery's output by equality only, so a >= has to sit inside the subquery's own WHERE. EXISTS keeps both the equality and the >= in one WHERE that reads both tables.

EXISTS next to IN

EXISTS (correlated subquery)

• The link lives inside the subquery's WHERE
• Can test several columns and conditions at once
• Reads no values, only whether a row came back
• Its NOT form stays correct when NULLs appear

IN (subquery)

• The subquery returns 1 column of values
• Compares a single outer column against that list
• Reads every value the subquery returns
• Its NOT form breaks when the list holds a NULL

A teammate deletes the store link from the subquery. Which stores does this query return?

SELECT
  s.store_name
FROM stores s
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.status = 'paid'
)

• Maple Mugs and North Knits only
• Maple Mugs, North Knits and Tidal Tees
• All 5 stores, Fern Studio and Pixel Prints included
• None, because EXISTS needs a correlated column

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.