NOT IN and one NULL

Julia has the same trap and handles it differently. 1 in [2, missing] returns missing, which is your NOT IN case exactly. The same three-valued rules apply throughout. missing == 1 is missing, true | missing is true and false & missing is false, matching SQL's OR and AND.

The difference is at the point where SQL's WHERE would quietly drop the row. if missing throws a TypeError, and so does filter with a predicate that returns missing. The trap can't spring silently. You choose coalesce, skipmissing or isequal, and the choice is in the code.

Kotlin's nullable types make you answer this before anything compiles, so my first question is what absence means here. A NULL in that list could be an unfinished row or a blocked account that lost its id. NOT EXISTS quietly treats it as blocking nobody. Option three makes the schema say so instead.

The plans differ too. PostgreSQL turns the NOT EXISTS form into an Anti Join. The NOT IN form stays a filter over a SubPlan, hashed if the subquery fits in work_mem, because the NULL rule stops the planner from treating the two as the same query. So option two is also the one the planner handles better.

Fair, though I'd pick it on a ten-row table too. On ten rows the plan hardly matters and the NULL still bites.

The third repair has one thing going for it that the others don't. ALTER TABLE blocked ALTER COLUMN id SET NOT NULL fails while a NULL row exists, so the question about what that row means has to come first. Mine had no account behind it at all and blocked nobody in particular. Deleted it, the constraint went on, and NOT IN returns the expected rows again.

Two notes for anyone repeating this.

1. SET NOT NULL scans the whole table to check existing rows, unless a valid CHECK constraint already proves there are no NULLs.
2. Keep NOT EXISTS in the query anyway. The constraint protects this column, and the query may outlive the schema it was written against.

There is a fourth spelling people write without thinking of it as a repair, the outer join that keeps only the rows with no partner.

SELECT o.id
FROM orders o
LEFT JOIN blocked b ON b.id = o.customer_id
WHERE b.id IS NULL;

On your five rows it returns orders 1 and 3, the same answer as NOT EXISTS. The NULL in blocked matches nothing, so it never pairs with an order. The IS NULL test has to be on a column that cannot be NULL in a matched row, and b.id qualifies because the join condition only holds when it is not NULL. In PostgreSQL the planner usually turns this and NOT EXISTS into the same Anti Join, so the choice comes down to which one the next reader understands.

Not the same once blocked has duplicates. Two rows for customer 20 and the join hands you that order twice.

It would for the matched rows, but the WHERE throws every matched row away. An order that survives had no partner at all, so it appears once however many duplicates sit in blocked.

INSERT INTO blocked VALUES (20);
SELECT o.id FROM orders o
LEFT JOIN blocked b ON b.id = o.customer_id
WHERE b.id IS NULL;
-- 1
-- 3

Duplicates matter for the positive join, the one that lists orders that are blocked. That one needs EXISTS or a DISTINCT.

Right, I had the positive join in my head. Scratch that one.

The R port gets this wrong in the other direction, and I like that even less, because it looks right.

1 %in% c(2, NA)
#> [1] FALSE
NA %in% c(2, NA)
#> [1] TRUE

%in% is match(), and match() treats NA as a value like any other. So filter(orders, !customer_id %in% blocked$id) keeps customers 10 and 30 and never asks what the NA in blocked meant. The rows match NOT EXISTS, and nobody had to think to get there. SQL's zero rows at least made someone go and look.

The repairs also differ on the other side of the comparison, and the five rows never reach it. Add an order whose customer_id is NULL.

INSERT INTO orders VALUES (4, NULL);

SELECT o.id FROM orders o
WHERE o.customer_id NOT IN (
  SELECT id FROM blocked WHERE id IS NOT NULL);
-- 1, 3

SELECT o.id FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM blocked b WHERE b.id = o.customer_id);
-- 1, 3, 4

With the filtered subquery, NULL NOT IN (20) is unknown and order 4 is dropped. NOT EXISTS finds no partner for it and keeps it. A constraint on blocked.id changes nothing here, because the NULL sits in orders. So repairs one and three agree with each other and disagree with repair two. SQLite printed what the comments show, and the PostgreSQL docs state the same rule for NOT IN.

One corner narrows the gap. NULL NOT IN an empty subquery is true, so while blocked is empty both forms keep order 4.

Keeping order 4 is not obviously the right answer either. An order with no customer was never checked against the block list at all, and NOT EXISTS lets it through the same way R lets an NA through !x %in% y. Count those orders before choosing a form. If there are none, declare the column NOT NULL and stop worrying about it. If there are some, somebody has to say whether an unknown customer may pass, and neither query should be the one saying it.

Note one deserves a narrower reading, going by the ALTER TABLE page. The scan is skipped only for a valid CHECK constraint that proves no NULL can exist. A check added with NOT VALID proves nothing yet, so the scan still happens. The documented order is three statements.

ALTER TABLE blocked ADD CONSTRAINT blocked_id_nn
  CHECK (id IS NOT NULL) NOT VALID;
ALTER TABLE blocked VALIDATE CONSTRAINT blocked_id_nn;
ALTER TABLE blocked ALTER COLUMN id SET NOT NULL;

VALIDATE CONSTRAINT still reads every row, but it holds only a SHARE UPDATE EXCLUSIVE lock, which does not block concurrent inserts and updates. The final statement then skips its scan. For a block list of a few hundred rows none of this matters and the single statement is fine. Everything above is PostgreSQL behaviour and says nothing about other engines.

Before the constraint goes on, find every statement that writes to blocked. Whatever inserted that NULL will keep trying afterwards, and then it fails at run time instead of quietly adding an unknown.

The same trap reaches the query through parameters, where nobody sees a subquery at all. A client that builds its block list in application code and sends WHERE customer_id <> ALL($1) gets the NOT IN behaviour exactly. The PostgreSQL docs say that if the array holds a null and no comparison comes out false, ALL yields null. A TypeScript signature of number[] for that list says nothing about the JSON it was parsed from, so a stray null passes the type and lands in the array. I'd filter it where the list is built, and keep the SQL in its NOT EXISTS form wherever there is a table to join against.

Catching it at the build step is right, though I'd rather stop there than filter. A null in a block list is a sign the list came out of its source wrong, and dropping it quietly leaves a list that looks finished and isn't. So the order I use is collect the ids, check that the collection is complete and every entry is an integer, and only then hand the array to the query. If the check fails, nothing runs and the error names the bad entry. It's one more step before the query, and it keeps the question mulchpile raised, what the missing value meant, in front of a person instead of inside a filter.