DELETE with LEFT JOIN in MySQL
in MySQL using a LEFT JOIN is a simple way of find records that exist in one table but which have no corresponding records in another table. An example might be in a JOIN table that exists between two other tables e.g.
A user can have many notes and notes can themselves have many users - a HABTM relationship.
If you delete a note or a user you could end up with orphaned records in the notes_users table - to find these orpahned records you might use a query like this:
1 2 | SELECT notes_users.* FROM notes_users LEFT JOIN notes ON notes_users.note_id = notes.id WHERE notes.id IS NULL |
You would imagine that to delete all of these orphaned records you could simply use the following query:
1 2 | DELETE FROM notes_users LEFT JOIN notes ON notes_users.note_id = notes.id WHERE notes.id IS NULL |
However this won’t work - you actually need to indicate which table you want to delete the records from:
1 2 | DELETE notes_user.* FROM notes_users LEFT JOIN notes ON notes_users.note_id = notes.id WHERE notes.id IS NULL |
