Deleting all records of a table that are not referenced from another table

Question

2 tables:
items(id, ...)
users(id, item_id, ...)

How do you delete all records in items that are not referenced from users?

Answer 1

Beware that NOT IN may be really slow. Sometimes - surpringly enough - its faster to do something like this:

DELETE FROM items WHERE id IN
(SELECT id FROM items EXCEPT SELECT item_id FROM users)

Answer 2

DELETE FROM items WHERE id NOT IN (SELECT item_id FROM users)

(uses a subquery to select all the item_ids from users and then deletes the records from items where id is not in the results of that subquery)