NOT EXISTS

Find rows that fail a condition.

NOT EXISTS checks that the subquery returns no rows. It replaces an anti-join.

SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

This query returns customers without orders.

name is selected from customers c. WHERE keeps only the rows where NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = … holds.

NOT EXISTS — details

With NOT EXISTS you find 'missing' records. It is an alternative to LEFT JOIN ... IS NULL.

For example, WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) finds customers who never ordered.

Find customers without orders using NOT EXISTS.

Show example solution

SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Add NOT

SELECT name FROM customers c
WHERE ____ EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Show answer

SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Next lesson: EXISTS vs IN