SQL · Chapter 27 of 42

SQL EXISTS

`EXISTS (subquery)` returns TRUE if the subquery returns at least one row. Perfect for existence checks.

Often faster than IN, and safer with NULLs.

vs IN

IN materialises the whole list. EXISTS stops at the first match. On big subquery results, EXISTS is usually faster.

Example 1 (sql)

SELECT name FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Output

users with at least one order

The subquery selects `1` because we only care that SOMETHING exists.

Example 2 (sql)

SELECT name FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Output

users with zero orders

NOT EXISTS = anti-join, NULL-safe.

💡 Note: `SELECT 1` and `SELECT *` are equivalent inside EXISTS — the database ignores the column list.