Efficient way to check if ID of foreign key is still referenced?

March 17, 2023

Context: I have this table called product and I am writing a script to clean up some old products, but have to follow rules, which can be deleted. One of the rules, is to only delete it if its not referenced by some other specific tables.

So currently I find all the products (using other simler rules) and then try to filter that IDs list on all referenced products like:

SELECT id FROM product
WHERE 
  id NOT IN (SELECT product_id FROM sale_line)
  AND id NOT IN (SELECT product_id FROM purchase_line)

On smaller number of products, it works. But in real scenario, there is over a million rows. And this query just takes forever.

Is there any more efficient way to determine if table row is referenced on some other specific tables, before trying to delete it?

>Solution :

Typically NOT EXISTS is faster than NOT IN:

SELECT p.id 
FROM product p
WHERE NOT EXISTS (select * from sale_line sl where sl.product_id = p.id)
  AND NOT EXISTS (select * from purchase_line pl where pl.product_id = p.id)