🪆 Subqueriesintermediate

How to use EXISTS and NOT EXISTS in SQL

Filter rows based on whether a related record exists in another table.

EXISTS — rows with a matchAll databases

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

Returns users who have at least one order. The SELECT 1 is a convention — the value does not matter.

When to use: Checking for existence. Often faster than IN on large tables.

NOT EXISTS — rows with no matchAll databases

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

Returns users with NO orders at all. Safer than NOT IN when NULLs may be present.

When to use: Preferred over NOT IN when the subquery might return NULLs.

EXISTS with conditionsAll databases

SELECT u.name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
    AND o.total > 1000
    AND o.status = 'completed'
);

You can add any conditions inside the EXISTS subquery.

Common Mistakes

→NOT IN vs NOT EXISTS — NOT IN returns no rows if subquery has any NULL. NOT EXISTS is safer.