Semi-Joins and Anti-Joins

High-value EXISTS, IN, NOT EXISTS, and LEFT JOIN IS NULL patterns for membership and missing-row queries.

View

Export

Copy the compact sheet, download it, or print it.

Download

`D` dense toggle · `C` copy all

## Semi-joins

Use EXISTS for semi-join behavior

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

# Return customers who have at least one order without duplicating customer rows.

Use IN for simple membership checks

SELECT customer_id, name
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
);

# Another semi-join style when selecting from a single column set.

## Anti-joins

Use NOT EXISTS for anti-join behavior

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

# Find customers who have never ordered.

LEFT JOIN ... IS NULL anti-join

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;

# Find unmatched rows by outer join plus null check.

Be careful with NOT IN and NULLs

Prefer:
WHERE NOT EXISTS (...)

Use caution:
WHERE id NOT IN (SELECT some_nullable_column FROM t)

# A single `NULL` in the subquery can change results in surprising ways.

More in SQL Joins

No recommendations yet.