Checking for matches with EXISTS

Ask "is there at least one?" or "are there none?" without duplicating rows, and dodge the NOT IN trap.

Many questions are yes-or-no per row. Does this account have any contacts? Does this rep own any deals? EXISTS answers exactly that, and it never multiplies rows the way a join can.

EXISTS: is there at least one?

Which accounts have at least one contact?

SELECT a.name
FROM accounts AS a
WHERE EXISTS (SELECT 1
              FROM contacts AS c
              WHERE c.account_id = a.account_id);

That returns 12 of the 14 accounts. For each account, the database runs the inner query and asks one thing: did it find any row at all? It stops at the first match.

Two things to notice:

• SELECT 1 is a convention. EXISTS only cares whether a row came back, not what’s in it. SELECT * or SELECT 1 work the same.
• c.account_id = a.account_id is the link. It ties the inner query to the current account, the same as the correlated subqueries in lesson 23. Without it, the inner query finds contacts for any account and every row passes.

Why not just join?

SELECT a.name
FROM accounts AS a
JOIN contacts AS c ON c.account_id = a.account_id;    -- 31 rows

The join returns one row per contact, so Northwind Traders shows up four times. You’d need DISTINCT to fix it, and lesson 7 warned about covering up duplicates that way. EXISTS returns each account once, because it only asks the question.

NOT EXISTS: are there none?

Which reps don’t own any deals?

SELECT u.name
FROM users AS u
WHERE NOT EXISTS (SELECT 1
                  FROM opportunities AS o
                  WHERE o.owner_id = u.user_id);     -- Erin Walsh

Erin is a new rep with no deals yet. This is called an anti-join: keep the rows that have no match. Lesson 15’s LEFT JOIN ... WHERE o.opportunity_id IS NULL gets the same answer. NOT EXISTS states the question more directly.

The NOT IN trap

Here’s the same question with NOT IN, which looks like it should work:

SELECT name
FROM users
WHERE user_id NOT IN (SELECT owner_id FROM opportunities);    -- no rows at all

It returns nothing. Not even Erin.

The list from the subquery includes NULL, because 9 deals have no owner. NOT IN asks “is Erin’s ID different from every value in the list?”, and “is 14 different from NULL?” is unknown, not TRUE (lesson 5). One unknown is enough to fail the whole check, so every row drops out. No error, just an empty result that looks believable.

Use NOT EXISTS for “has none” questions. IN (without NOT) doesn’t have this problem, because a NULL in the list just never matches.

Extra conditions go inside

To ask “is there at least one open deal?”, put the extra filter in the inner query:

SELECT COUNT(*)
FROM accounts AS a
WHERE NOT EXISTS (SELECT 1
                  FROM opportunities AS o
                  WHERE o.account_id = a.account_id
                    AND o.closed_at IS NULL);       -- 5 accounts with no open deals

The inner WHERE describes the rows you’re looking for. The outer WHERE only sees yes or no.

Common mistakes

Dialect notes

EXISTS and NOT EXISTS work the same in SQL Server, PostgreSQL, MySQL and SQLite, and so does the NOT IN trap. On older MySQL versions, NOT EXISTS and NOT IN could be slow on big tables, and a LEFT JOIN ... IS NULL was the usual workaround.

Snowflake notes

Snowflake supports EXISTS and NOT EXISTS in WHERE, and has the same NOT IN behavior with NULLs. Like other correlated subqueries (lesson 23), some complex forms fail with an “unsupported subquery type” error, and a LEFT JOIN ... IS NULL is the fallback.

More in the field guide: EXISTS and Anti-join (NOT EXISTS). Exercise I3 adds a date condition to the Try it below.

Try it

Which accounts have at least one contact but no open deals? Those are customers sales could call about new business. Return the account name, A to Z.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson