There's a family of questions that needs no value at all: just a yes or a no. Has this customer ever bought? Does this product have any review? Does this order contain any cosmetics item? It doesn't matter how many, or which, or how much they add up to: what matters is whether at least one exists.

EXISTS is the operator that answers exactly that, and it's probably the subquery you'll write most often in your professional life. Its negation, NOT EXISTS, solves the other half —customers who haven't bought, products nobody has sold— and it does so with a property none of the alternatives has: it's immune to null values. In this lesson you'll see both, you'll compare them with NOT IN, with 03-03's anti-join and with 03-07's EXCEPT, and you'll close with the classic problem of relational division.

1. EXISTS: a predicate, not a value
2. Why it makes no difference what you put in the inner SELECT
3. Four GreenStore cases with EXISTS
4. NOT EXISTS: the data set's four gaps
5. NOT EXISTS against NOT IN: the difference that matters
6. The four ways of answering "what doesn't match"
7. Short-circuiting: EXISTS against COUNT(*) > 0
8. Relational division: the double negation
9. Common Mistakes and Tips
10. Exercises
11. Conclusion

EXISTS (subquery) is a predicate: it returns TRUE or FALSE, never NULL. Its rule is radically simple:

EXISTS is TRUE the moment the subquery produces its first row. If it produces none, it's FALSE.

(First 4 of 12 rows: the 12 customers who have bought at some point.)

Three properties set it apart from everything you've seen: it returns TRUE/FALSE and never NULL, so it's immune to 04-03's three-valued logic; it doesn't look at the contents of the subquery, whatever columns it returns; and it stops at the first row, it doesn't count or sum or sort.

And a fourth, decisive one: in practice EXISTS is always correlated. An uncorrelated EXISTS —WHERE EXISTS (SELECT 1 FROM orders)— is TRUE or FALSE for every row alike, so it returns either the whole table or no rows at all. It isn't an error, but it isn't any use either. The condition o.customer_id = c.id is what does the work: it turns "are there orders?" into "are there orders for this customer?".

These four forms are exactly equivalent and produce the same execution plan:

The last one is the proof: 1/0 is a division by zero and it raises no error, because PostgreSQL never evaluates the column list of an EXISTS. It only checks whether the query produces rows. The projection is discarded before being computed.

Hence the SELECT 1 idiom, the one you'll see in 90 % of professional code: it's the shortest way of saying "I'm not interested in the contents". SELECT * is just as correct and some style guides prefer it because it stresses the same thing. Pick one and be consistent; in this course we use SELECT 1.

What does matter inside the EXISTS is the WHERE: the correlation. An EXISTS whose WHERE doesn't mention the outer row is almost certainly badly framed.

Products with at least one review. The EXISTS doesn't say how many or with what rating: only that there is one.

(First 5 of 9 rows; the other four are products 12, 15, 16 and 18.) 9 of the 20 products. Compare it with an INNER JOIN on reviews: that would have returned 12 rows, one per review, with the oil, the rice and the cream repeated. EXISTS never multiplies rows, and that's its most practical advantage over the JOIN.

Orders containing some natural cosmetics product. Here the subquery carries its own JOIN:

6 orders out of 20. Order 9 contains two cosmetics items (shampoo and lip balm) and appears only once: with a JOIN 7 rows would have come out and a DISTINCT would have been needed. Employees who have handled at least one order, same pattern:

3 of the 8 employees, exactly the ones 01-06 announced.

Dialect note: in PostgreSQL EXISTS returns a genuine boolean, so you can use it as a column: SELECT c.id, EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) AS has_bought FROM customers c returns true/false for all 15 customers. SQL Server and Oracle don't allow it and force you to wrap it in a CASE WHEN EXISTS (...) THEN 1 ELSE 0 END. MySQL 8 and SQLite do accept it, returning 1 or 0.

NOT EXISTS is the literal negation: TRUE when the subquery produces no rows at all. It's the natural tool for every question with "without", "never" or "none", and it answers GreenStore's four deliberate gaps with the same template.

And the same template, changing only the inner table, gives the products never sold:

With NOT EXISTS (SELECT 1 FROM reviews AS r WHERE r.product_id = p.id) over products you get the 11 products with no review, and with NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.employee_id = e.id) over employees, the 5 employees with no orders (Rosa, Andrés, Beatriz, Irene and Daniel).

Four business questions, a single template. That uniformity is the main reason to prefer NOT EXISTS: there's no need to decide which column to check with IS NULL, nor to worry about nulls, nor to remember which table goes on the left.

Now the module's central demonstration. The same question, two forms, two different results.

The first returns zero rows and the second five. The cause, which you already diagnosed in 07-01, is the 10 web-channel orders with employee_id set to NULL: the list is {4, 5, 6, NULL} and 1 <> NULL is UNKNOWN, so NOT IN can't be TRUE for any row. NOT EXISTS doesn't suffer the same fate because it doesn't compare the outer value against a list: it asks whether the subquery produces rows. For employee 1, SELECT 1 FROM orders WHERE employee_id = 1 produces none —the rows with a null employee_id don't satisfy that equality either and are discarded like any other that doesn't match— and NOT EXISTS is TRUE. Three-valued logic acts inside the subquery, where it only decides which rows come back, and it never escapes into the predicate.

The rule, in one sentence: NOT EXISTS is safe with nulls and NOT IN isn't. If you pick NOT EXISTS out of habit, you'll never have to wonder whether the subquery's column is nullable.

In 03-07 it was announced that the four forms would be compared in module 7. Here they are, solving the same question —customers who have never bought— and all returning the same 3 rows (13, 14 and 15):

The course's recommendation: use NOT EXISTS. It's safe, universal, it leaves all the outer table's columns available and it reads just like the business question. The anti-join is equally idiomatic and sometimes more natural if you were already joining those tables. EXCEPT, only when you're comparing sets of the same shape and don't need more columns. And NOT IN, only if you can guarantee the subquery's column is NOT NULL — and even then, you gain nothing. This table is closed off and extended with the performance criterion in 07-05.

It's tempting to write "is there any?" as "is the count greater than zero?". It works and gives the same result —12 rows both— but not the same work:

EXISTS short-circuits: as soon as it finds a row, it stops looking; COUNT can't, because to know how many there are it has to see them all. With 20 orders the difference is unmeasurable; with real tables it separates an instant query from a full scan. And there's a third argument, the most important day to day: EXISTS says what you mean. The same goes for the negation: COUNT(*) = 0 is NOT EXISTS written in a more expensive way.

We arrive at the classic problem. The question looks innocent:

Which customers have bought products from every category?

And it can't be answered with a plain EXISTS, because EXISTS talks about "some", not about "all". The trick is to reformulate the sentence until the "all" becomes two chained "nones":

That reformulation —which in logic is ∀x P(x) ≡ ¬∃x ¬P(x)— translates literally into SQL:

No customer at all, and the result isn't disappointing but diagnostic: Supplements has never sold anything (€0.00 in the course's figures), so nobody can have bought from all six categories. The query is correct; what's wrong is the question. Reframed over the four main categories —Food, Natural cosmetics, Sustainable home and Drinks— it's enough to narrow the middle subquery:

A single customer. Lucía is the only one who has bought from the four main categories, and she's managed it with three very different orders: number 1 (oil, rice and chamomile tea), number 5 (detergent, scrubber and bags) and number 15 (honey, shampoo and scrubber). It's exactly the profile a marketing team would want to identify: the customer who has explored the entire catalogue.

Read it from the inside outwards, in three sentences:

And a warning: there are two correlations in play. The inner one uses c.id (two levels outwards) and cat.id (one). If you forget one, the query is still valid and returns nonsense. Test the inner subquery with fixed values (c.id = 1, cat.id = 3) before assembling the three layers.

Note: there's a more readable alternative with GROUP BY and HAVING COUNT(DISTINCT p.category_id) = 4, shorter and often faster. The double negation deserves to be understood because it's the only one that works when the reference set isn't a simple count —"all the active categories", "all the products in a catalogue that changes"— and because it's the idiom you'll recognise when reading other people's code.

• Writing an EXISTS with no correlation. WHERE EXISTS (SELECT 1 FROM orders) is TRUE for every row and returns the whole table: the condition linking to the outer row is the essential part.
• Believing that SELECT * inside an EXISTS is slower. It isn't: the column list is never evaluated, and SELECT 1 and SELECT * produce the same plan.
• Using NOT IN over a nullable column. Zero rows, with no warning at all; NOT EXISTS never has that problem. And writing COUNT(*) > 0 counts every row to answer something the first row settles.
• Putting an ORDER BY or a LIMIT inside an EXISTS. They don't change the result and they add work. And confusing NOT EXISTS with EXISTS (... WHERE NOT ...). "Has no delivered order" is NOT EXISTS (... status = 'delivered'); "has some non-delivered order" is EXISTS (... status <> 'delivered'). They're different questions.
• Tip: translate the question word by word. "Customers who have bought" → EXISTS. "Customers who have never bought" → NOT EXISTS. "Customers who have bought from all" → NOT EXISTS ( ... NOT EXISTS ( ... )). And there, be careful not to forget one of the two correlations: it raises no error and the result is rubbish.
• Tip: use EXISTS when the JOIN would force you into a DISTINCT. If you only want to know whether there's a relationship and you need no data from the other table, avoid the row multiplication at the root. And always test subqueries with fixed values —replace c.id with 1— before wiring up the correlation.

Customer support wants an opinions campaign aimed at customers who have bought at some point but have never written a review. Write the query with EXISTS and NOT EXISTS in the same WHERE clause, showing id, full name, country and number of orders.

Then answer: how many customers are left out by each of the two conditions?

A colleague needs the orders that contain no food product at all and has written this:

1. Explain why it's wrong and what question it really answers.
2. Write it correctly with NOT EXISTS and give the number of rows.
3. Could you solve it with an anti-join? And with NOT IN? Justify whether they'd be safe.

Purchasing wants to know which suppliers have no unsold product: the ones whose references have all been sold at least once. Write it with a double NOT EXISTS. (Hint: same structure as relational division, but the reference set is the supplier's own products.)

Solution 1

3 customers. How the 15 break down:

All three are perfect targets: they've bought, they're satisfied or we don't know, and we've never asked them for their opinion. Notice that the two conditions are inseparable: with NOT EXISTS (reviews) alone, 6 customers would come out, including three who have bought nothing.

Solution 2

1. What's wrong. The query answers "orders containing some product that isn't food", which is almost the opposite. An order with oil (category 1) and kombucha (category 4) has a line satisfying category_id <> 1, so it shows up — even though it does carry food. The mistake is one of quantifier: a JOIN's WHERE filters lines, and the question is about orders. The DISTINCT disguises the symptom (the repeated rows) without touching the cause.

2. The correct version:

(First 5 of 10 rows; the others are orders 12, 13, 16, 18 and 19.) 10 orders out of 20 carry not a single food product. The original query returned 18 —every order with some line from another category— and eight of them do buy food. Notice the change of logic: the condition p.category_id = 1 is written in the positive inside the EXISTS, and the NOT supplies the negation. That's the pattern of every question with "none".

3. With an anti-join, yes: FROM orders o LEFT JOIN (order_lines ol JOIN products p ON p.id = ol.product_id AND p.category_id = 1) ON ol.order_id = o.id WHERE ol.id IS NULL works, but you have to carry the category condition into the ON (if it goes in the WHERE it degrades the LEFT to an INNER, 03-03), which makes it considerably more fragile to write. With NOT IN it would also work —o.id NOT IN (SELECT ol.order_id FROM order_lines ol JOIN products p ON ... WHERE p.category_id = 1)— and it would be safe because order_lines.order_id is NOT NULL. But you'd still be depending on a schema guarantee that could change tomorrow: NOT EXISTS depends on nothing.

Solution 3

3 suppliers out of 5. It squares with the three products never sold: number 19 (Deodorant) is from Maison Nature, and number 13 (Candles) and number 20 (Spirulina) are from EcoNordic Supplies. Those two are left out; the other three have placed their entire catalogue.

The structure is identical to section 8's with a single difference: the reference set is correlated (p.supplier_id = s.id) instead of being the whole categories table. It's the most useful variant of the pattern in practice, and its literal reading is "there is no product of theirs for which no sale exists".

EXISTS is the subquery you'll write the most:

• EXISTS is a predicate: it returns TRUE the moment the subquery produces one row, and never NULL. It doesn't look at the inner SELECT's columns —hence the SELECT 1 idiom, and even SELECT 1/0 works— and it short-circuits.
• In practice it's always correlated: the condition linking it to the outer row is what gives it meaning.
• It doesn't multiply rows. The 9 products with a review come out 9 times, not 12; the 6 orders with cosmetics come out 6, not 7. Where a JOIN would call for a DISTINCT, EXISTS doesn't need one.
• NOT EXISTS solves GreenStore's four gaps with a single template: 3 customers with no purchase, 3 products never sold, 11 products with no review and 5 employees with no orders. And above all: it's safe with nulls and NOT IN isn't — the same question gives 0 rows with NOT IN and 5 with NOT EXISTS, because of the 10 web orders with a null employee_id.
• You know the four ways of answering "what doesn't match" —NOT EXISTS, NOT IN, anti-join and EXCEPT— with their differences in nulls, available columns, duplicates and portability; the recommendation is NOT EXISTS. And you can translate an "all" with the double negation: no customer has bought from the six categories (Supplements never sold anything) and only Lucía has done so from the four main ones.

In the next lesson, subqueries in SELECT, FROM and WHERE, you'll stop looking at the what to look at the where: what changes depending on the clause you place the subquery in, why a derived table in the FROM needs an alias without exception, how you aggregate twice in a row to reach at last the €36.40 average order value from its source, and how two aggregates of different granularity are crossed so that the €727.95 of product and the €118.25 of shipping add up without inflating each other.