You want customers with no recorded order. The customer table contains IDs 11, 12 and 13. The orders table contains customer ID 11 and one row whose customer ID is NULL. A query using NOT IN returns no customers. Where did 12 and 13 go?
They did not disappear from the table. The condition failed to become true. PostgreSQL’s documentation on subquery expressions explains that a nonmatching value compared with a NOT IN result containing NULL produces an unknown result, rather than true. WHERE retains true conditions, so those rows are omitted.
Ask whether a matching row exists
For this original practice example, assume customer IDs are non-null unique keys. The following query asks a more direct question for each customer:
SELECT c.id
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
);For customer 11, a matching order exists, so NOT EXISTS is false. For 12 and 13, the subquery finds no matching row, so both are returned. The order with an unknown customer ID does not establish a match to either one.
That last point matters. This result says “no order matched using this key.” It does not prove that the person has never ordered. An unassigned order is a data-quality question the query has not answered. Keep a separate count of orders with missing customer IDs rather than quietly treating them as irrelevant.
Test the boundary cases
In a disposable practice database, try four small cases: no orders at all; one matching order; two orders for the same customer; and an order with a missing key. Write the expected customer IDs before running each query. Duplicate matching orders should not duplicate the customer in this NOT EXISTS result.
Also decide whether the question is “ever ordered” or “ordered during a particular period.” A date condition belongs inside the matching test when you are looking for customers without an order in that period. Moving the condition without thinking can change the question.
Do not repair the result by guessing
Removing NULL rows from a NOT IN subquery can be appropriate when that matches the intended meaning, but it should be a deliberate choice. Neither spelling is a substitute for understanding nullable keys and the matching rule.
Before using the output for a mailing list or report, inspect a few returned IDs against their source records. A technically correct anti-join can still use an incomplete order table. The useful habit is to validate both the SQL logic and the business statement you attach to its result.
AI-generated editorial illustration; not a documentary photograph.