SQLDailyDrills

EXISTS and NOT EXISTS

Test whether a subquery finds any row. EXISTS is true when it does; NOT EXISTS is true when it finds none.

Syntax

WHERE EXISTS (SELECT 1 FROM table WHERE …);
WHERE NOT EXISTS (SELECT 1 FROM table WHERE …);
  • SELECT 1 is enough: EXISTS only checks whether a row is returned.

Example

SELECT first_name FROM customers AS c WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id);

Customers who have placed at least one order.

Tables used: customers, orders. Try it in a lesson below.

Common mistakes

  • Use NOT EXISTS, not NOT IN, when the subquery can return NULL: a single NULL makes NOT IN return no rows.

Practise it