High-value EXISTS, IN, NOT EXISTS, and LEFT JOIN IS NULL patterns for membership and missing-row queries.
Return left-side rows when a related match exists.
Return customers who have at least one order without duplicating customer rows.
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);`EXISTS` is often clearer and more efficient than joining and deduplicating.
Another semi-join style when selecting from a single column set.
SELECT customer_id, name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
);`IN` can be concise, but `EXISTS` is often preferred for correlated logic and large subqueries.
Return left-side rows with no related match.
Find customers who have never ordered.
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);`NOT EXISTS` is usually the safest anti-join pattern.
Find unmatched rows by outer join plus null check.
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;Common and readable, though `NOT EXISTS` is often more robust when multiple conditions are involved.
A single `NULL` in the subquery can change results in surprising ways.
Prefer:
WHERE NOT EXISTS (...)
Use caution:
WHERE id NOT IN (SELECT some_nullable_column FROM t)`NOT IN` can yield no rows if the subquery returns `NULL`. `NOT EXISTS` avoids that pitfall.