Rewrite SQL query to remove duplicate SELECTs in [WHERE xxx IN] condition

I need to execute following query:

DELETE FROM notification 
WHERE account_id IN ( SELECT id FROM missing ) 
   OR receiver_id IN ( SELECT id FROM missing ) 
   OR created_by_id IN ( SELECT id FROM missing ) 
RETURNING id 

What is bothering me – is that it has to select same values 3 times. I am sure that there is a better, proper way of doing it. Could you please suggest how this query might be rewritten?

Answer

You can use an EXISTS condition with an IN:

delete from notification n
where exists (select *
              from missing m 
              where m.id in (n.account_id, n.receiver_id, n.created_by_id))
returning id;

Which is more or less the same as:

delete from notification n
using missing m 
where m.id in (n.account_id, n.receiver_id, n.created_by_id)
returning n.id;

---

However, the majority of the time will be spent by the actual DELETE part, rather than by finding the rows. So unless missing is really huge or a really complicated subquery, I doubt you will see a big performance difference.

---

After a few simple tests (250000 rows in notifications, 10000 rows in missing) it seems that the original version is way faster than the EXISTS or USING alternative.