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

@jQuery.PHP.Magento.com "Do you have indexing on primary" --- what does this even mean? — zerkms, Mar 26 '15 at 07:30
Show the `EXPLAIN` for the query and `SHOW CREATE TABLE` for both tables and let's see if it's already fine or not. — zerkms, Mar 26 '15 at 07:33
WHERE NOT EXISTS will return True even if there is one record in user_push_notifications? — Varun Jain, Mar 26 '15 at 07:35
You can remove the following condition as it is already in the subquery: where un.notification_id = 'xxxxyyyyyzzzz' — sayani, Mar 26 '15 at 07:37

score 1 · Answer 1 · answered Mar 26 '15 at 07:34

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

`user_notifi_idx(user_id,notification_id)` --- this index will not work for your both queries. The order of columns must be the other way around. — zerkms, Mar 26 '15 at 07:40

score 1 · Answer 2 · edited Apr 27 '19 at 19:49

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.

score 0 · Answer 3 · answered Mar 26 '15 at 07:45

0

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'

answered Mar 26 '15 at 07:45

Bellash

7,560
6
53
86

Negative condition in Join

3 Answers3

Linked

Related