SQL: Remove rows whose associations are broken (orphaned data)

Question

I have a table called "downloads" with two foreign key columns -- "user_id" and "item_id". I need to select all rows from that table and remove the rows where the User or the Item in question no longer exists. (Look up the User and if it's not found, delete the row in "downloads", then look up the Item and if it's not found, delete the row in "downloads").

It's 3.4 million rows, so all my scripted solutions have been taking 6+ hours. I'm hoping there's a faster, SQL-only way to do this?

Answer 1

use two anti joins and or them together:

delete from your_table
where user_id not in (select id from users_table)
or item_id not in (select id from items_table)

once that's done, consider adding two foreign keys, each with an on delete cascade clause. it'll do this for you automatically.