E0019 not-in-with-nulls

NOT IN over a nullable subquery column.

What it reports

x NOT IN (SELECT c FROM t) where t.c is a nullable column. If the subquery returns a single NULL, x NOT IN (...) is NULL for every x that isn't in the list, so the query returns no rows at all. It works in testing and breaks the day a NULL arrives.

Example

SELECT id FROM orders WHERE id NOT IN (SELECT order_id FROM coupons);

warning[E0019]: NOT IN over the nullable column 'coupons.order_id' returns no rows if it has a NULL
  --> q.sql:1:47
    |
  1 | SELECT id FROM orders WHERE id NOT IN (SELECT order_id FROM coupons);
    |                                               ^^^^^^^^
    = help: Use NOT EXISTS, or add WHERE order_id IS NOT NULL to the subquery

Fixed:

SELECT id FROM orders o WHERE NOT EXISTS (SELECT 1 FROM coupons c WHERE c.order_id = o.id);

Notes

Only a column of a schema table is checked, and only when it is nullable and not part of the primary key. Nothing is reported when the subquery mentions the column anywhere else (a WHERE, JOIN ... ON, GROUP BY or HAVING condition on it may exclude NULLs) or joins with USING / NATURAL.

Configuring

# sqlsift.toml
[rules]
not-in-with-nulls = "error"   # or "off"

Or for a single line: -- sqlsift:disable E0019. The examples use the schema on the Rules page.