The orphan hunt loses the orphan

TG-103 says its gear type is 99. gear_types has two rows, 1 for Trail gear and 2 for Water gear. A row whose foreign key points at nothing is an orphan, and an orphan in a file is easy to lose before anyone sees it.

Look for it with an INNER JOIN and it disappears. The join keeps only the staged rows that found a parent, so WHERE g.id IS NULL is asked of five rows that each have one. The query returns nothing, and nothing looks exactly like no orphans.

A LEFT JOIN from staging to the parent keeps all six staged rows. TG-103 comes back with NULL in every gear_types column, and the test is on the parent's key: WHERE g.id IS NULL. The ON clause converts the staged text, g.id = CAST(s.type_id AS INTEGER), and that CAST is safe only because every type_id in this file was checked first and is all digits.

SELECT s.serial, s.type_id, g.name AS gear_type
FROM stg_items s
LEFT JOIN gear_types g ON g.id = CAST(s.type_id AS INTEGER)
ORDER BY s.rowid;

Finding TG-103 is SQL. Deciding what happens to it is not. There are three honest answers: exclude the row and write down why; correct it, when the client confirms what the type should have been and sends a corrected file; or let it in with no type, if the business allows an item without one and the column is declared to allow NULL. Each needs a reason the client would sign.

The dishonest fourth answer is to invent a gear type 99, call it Unknown, and watch the error go away. The foreign key is satisfied, and every report now has a category nobody defined. The rehearsal excludes TG-103 with the reason 'Unknown gear type', and the reconciliation lists it by name.