I’m doing some maintenance work on a database application and I’ve discovered that, joy of joys, even though values from one table are being used in the style of foreign keys, there’s no foreign key constraints on the tables.
I’m trying to add FK constraints on these columns, but I’m finding that, because there’s already a whole load of bad data in the tables from previous errors which have been naively corrected, I need to find the rows which don’t match up to the other table and then delete them.
I’ve found some examples of this kind of query on the web, but they all seem to provide examples rather than explanations, and I don’t understand why they work.
Can someone explain to me how to construct a query which returns all the rows with no matches in another table, and what it’s doing, so that I can make these queries myself, rather than coming running to SO for every table in this mess that has no FK constraints?