CODEWITHSUNDEEP
mysql
Varun Jain
Varun Jain

Reputation: 1911

Negative condition in Join

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

Upvotes: 2

Views: 1512

Answers (3)

bitkot
bitkot

Reputation: 4504

You can try the following, it is same as @Abhik's first answer but with one more condition.

SELECT DISTINCT un.user_id        -- This will give you unique users
FROM user_notifications un
LEFT JOIN
  user_push_notifications upn
ON
 upn.user_id = un.user_id
AND upn.notification_id = "xyz"   -- This will match with un by user_id for a specific notifioncation id.
WHERE un.notification_id = "xyz"  -- This will get only the specific notifications.
AND upn.notification_id IS null;  -- This will make sure that all the user_ids are filtered which exist in upn with specific notification id.

Upvotes: 1

Bellash
Bellash

Reputation: 8184

You can left join them like this

    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'

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

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);

Upvotes: 1

Related Questions