Why does NOT IN hide equipment awaiting inspection?

Prepare the diagnostic input

The decision is which equipment lacks any matched inspection. NOT IN compares against every subquery value; a NULL can make the nonmatch outcome unknown rather than true. A correlated NOT EXISTS asks whether a matching inspection actually exists and is unaffected by an unrelated missing ID.

Use a fresh SQLite test database for the complete setup below. Every record is fictional; quantities are units and input monetary columns ending in cents use integer currency cents. Calculated averages may include fractions of a cent. Input previews include every table in this small fixture.

CREATE TABLE equipment(id TEXT PRIMARY KEY);
CREATE TABLE inspections(id INTEGER PRIMARY KEY,equipment_id TEXT);
INSERT INTO equipment VALUES ('E1'),('E2');
INSERT INTO inspections VALUES (1,'E1'),(2,NULL);

Reproduce the misleading result and correct it

SELECT id FROM equipment WHERE id NOT IN (SELECT equipment_id FROM inspections) ORDER BY id;

SELECT e.id FROM equipment e WHERE NOT EXISTS
(SELECT 1 FROM inspections i WHERE i.equipment_id=e.id) ORDER BY e.id;

The misleading query returns no rows. E1 was inspected, while E2 has no match, so the correction returns E2. The NULL inspection remains a data-quality issue; replacing it with a made-up equipment ID would make neither record trustworthy.

Check the boundary before applying the fix

Remove the NULL row only in a fresh synthetic copy. The corrected queue remains E2, demonstrating that unrelated bad data did not determine equipment eligibility.

For the boundary test, recreate the complete setup in another fresh synthetic database, apply only the change below, then rerun the corrected query. Do not apply a test mutation to a live system.

DELETE FROM inspections WHERE equipment_id IS NULL;

Any matched inspection counts here, regardless of date or result. A compliance or recurring-inspection process needs approved interval and pass-status rules.

The displayed bad, corrected and boundary results were executed in SQLite and checked against separately authored expected rows. This demonstrates the stated fixture only; it does not test other database engines, production performance or your live schema.