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

2 tables:

items(id, ...)

users(id, item_id, ...)

How do you delete all records in items that are not referenced 开发者_运维问答from users?

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)

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)

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