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

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)

Answer 2

delete from items
where id not in (select item_id from users)

Answer 3

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)