Avoid the NULL trap in NOT IN subqueries
A cleanup query looks for products whose IDs are not in an exclusion table. One NULL in the exclusion result can make the NOT IN condition evaluate unexpectedly, leaving the cleanup with no candidates.
Prefer an explicit anti-existence condition
SELECT p.id
FROM products AS p
WHERE NOT EXISTS (
SELECT 1 FROM exclusions AS e
WHERE e.product_id = p.id
);
This asks whether a matching exclusion row exists for each product. Include the required tenant scope in a real application. The sample is a SELECT so candidates can be inspected before any destructive operation.
Fix the data contract too
If an exclusion must always refer to a product, use an appropriate NOT NULL column and foreign key. Query design and schema constraints should reinforce the same meaning. A query workaround alone leaves malformed exclusion rows in the system.
There are cases where filtering NULL out of a NOT IN subquery is valid, but make the null policy explicit and test it. Avoid assuming a list built from application data contains only valid IDs.
Test an empty exclusion table, one matching ID, a nonmatching ID, and a NULL row if the schema currently permits it. For a deletion workflow, first compare the selected IDs with the expected fixture and then test the guarded mutation separately. A zero-row result should not automatically be treated as evidence that the underlying data is clean.