mysql - Negative condition in Join

Question

Is there a better way (performance or syntax) to write the following mysql query:

Select un.user_id 
from user_notifications un
where un.notification_id  = 'xxxxyyyyyzzzz' 
and un.user_id not in (Select user_id from user_push_notifications upn 
where  upn.notification_id = 'xxxxyyyyyzzzz') ; 

The purpose is to find those user_id which have not been pushed a notification for a certain notification_id

Answer 1

You have many ways using left join with is null or not exists

Select 
un.user_id 
from user_notifications un
left join user_push_notifications upn 
on upn. user_id = un.user_id  and un.notification_id  = 'xxxxyyyyyzzzz' 
where upn. user_id is null

Select 
un.user_id 
from user_notifications un
where 
un.notification_id  = 'xxxxyyyyyzzzz' 
and not exists
(
 select 1 from user_push_notifications upn 
 where 
 un.user_id = upn.user_id 
 and upn.notification_id = 'xxxxyyyyyzzzz'
)

To boost the performance , you may need to add index if its not added yet

alter table user_notifications add index user_notifi_idx(user_id,notification_id);
alter table user_push_notifications add index user_notifp_idx(user_id,notification_id);