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:
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:
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:
DELETE notes_user.* FROM notes_users LEFT JOIN notes ON notes_users.note_id = notes.id WHERE notes.id IS NULL
it works :)
DELETE note_users FROM notes_users LEFT JOIN notes ON notes_users.note_id = notes.id WHERE notes.id IS NULL