如何连接表以查找不存在的项目

Question

my question may seem strange. But I have a question, how should I do a JOIN on a table for the following purpose:

I have a table called categories.

I have another pivot table called categories_products

Regularly I do a left join to get the categories of one product.

Now, after several month, there are some categories deleted from categories table. I had forgot to put a trigger at that time (that delete all related categories_products records of the deleted cat). I want to delete any record (which now is considered to be unused) from categories_products where its categories item is deleted (does not exist). What query should I write? Nothing comes to my mind. Thanks

Answer 1

delete from categories_products cp 
where not exists (select 1 from categories c where c.id = cp.category_id);

Or

delete from categories_products cp 
where cp.category_id not in (select distinct c.id from categories c);

But EXISTS is usually faster.