Find me every customer with no order in the last thirty days, then tell me which ways of writing that get NULLs wrong.

Settle the requirement first

"No order in the last thirty days" has two readings and they differ by an entire population. A customer who has never ordered has no order in the last thirty days, so a literal reading includes them; a churn report usually wants exactly that, while a re-engagement campaign may want only customers who used to order and have stopped. Ask. Then write whichever you were asked for, because the two are one clause apart and the difference is invisible in the output unless somebody checks.

Everything below takes the literal reading: customers with no qualifying order, including those with none at all.

The version to write

SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
        SELECT 1
        FROM orders o
        WHERE o.customer_id = c.customer_id
          -- Column bare on the left, expression on the right, so an index
          -- on orders (customer_id, placed_at) stays usable.
          AND o.placed_at >= now() - interval '30 days'
      );

NOT EXISTS is the shape to reach for because it asks the only question that matters — does a matching row exist — and existence is not a value comparison, so nulls in the data cannot change the answer. It also states the intent plainly enough that a reviewer sees what it means without reconstructing it.

On a large orders table the composite index on (customer_id, placed_at) is what makes this cheap: the leading column matches the correlation and the trailing column bounds the range, so each probe touches only that customer's rows inside the window.

Why NOT IN returns nothing at all

The version most people write next looks equivalent and is not.

SELECT c.customer_id
FROM customers c
WHERE c.customer_id NOT IN (
        SELECT o.customer_id          -- one NULL in here and the whole result is empty
        FROM orders o
        WHERE o.placed_at >= now() - interval '30 days'
      );

x NOT IN (a, b, c) is defined as x <> a AND x <> b AND x <> c, and a comparison against null is not false but unknown. For a customer who genuinely does not appear in the list, the chain evaluates to true and true and unknown, which is unknown, and WHERE admits only rows that are true. The row is dropped. Every row is dropped, and the query returns an empty set from a table full of qualifying customers.

That the failure is total rather than partial cuts both ways. It is obvious in testing if a null is present then, and it is a time bomb if orders.customer_id is nullable and the first null arrives a year later, on the day an import writes an order with no customer attached. So the rule is stronger than "watch out for nulls": do not point NOT IN at a subquery unless the column is declared NOT NULL, and prefer NOT EXISTS even when it is.

A performance argument points the same way. Because those null semantics have to be preserved, an optimiser generally cannot rewrite NOT IN over a subquery into an anti-join the way it can for NOT EXISTS. In PostgreSQL you will typically see the subquery evaluated into a hashed structure and probed instead, which is a different plan shape and usually a worse one as the inner set grows.

The join condition is not a filter

The third shape is correct and fails in a quieter way.

SELECT c.customer_id
FROM customers c
LEFT JOIN orders o
       ON o.customer_id = c.customer_id
      -- Belongs in ON, not WHERE. Moved to WHERE it discards the
      -- null-extended rows and the outer join collapses to an inner join.
      AND o.placed_at >= now() - interval '30 days'
WHERE o.customer_id IS NULL;

A LEFT JOIN produces a row for every left-hand row, filling the right-hand columns with nulls where nothing matched, and the anti-join is then expressed by keeping only those null-extended rows. The order of operations is what people get wrong: the ON clause decides what counts as a match, and WHERE runs afterwards on the joined result. Put o.placed_at >= ... in WHERE and every null-extended row fails it, since null is not greater than anything, so exactly the rows you were trying to find are eliminated and the query returns the customers who did order recently — the precise opposite, with no error.

The second detail here is which column you test for null. It has to be one that cannot be null in a genuinely matched row, so the primary key or the join column. Test a nullable payload column such as o.discount_code and you will also collect customers who ordered recently without a discount, which is a wrong answer that looks plausible for months.

Given a choice, still write NOT EXISTS. The outer-join form has a legitimate use when you want columns from the right-hand table for the rows that did match, but as a pure anti-join it carries two ways to be silently wrong in exchange for nothing.

An anti-join in SQL is an existence test, and writing it as a value comparison is what lets three-valued logic quietly invert your result. NOT EXISTS says what you mean, survives nullable columns, and gives the planner the shape it can execute best.