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.

Tables

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

One Response to “DELETE with LEFT JOIN in MySQL”

  1. Romulo

    it works 🙂

    DELETE note_users FROM notes_users LEFT JOIN notes ON notes_users.note_id = notes.id WHERE notes.id IS NULL