联接中的负条件 [英] Negative condition in Join

问题描述

是否有更好的方法(性能或语法)编写以下mysql查询:

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

目的是查找尚未被推送通知的那些user_id特定的notification_id

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

推荐答案

您可以尝试以下方法，它与@Abhik的第一个答案相同，但有更多条件.

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.

这篇关于联接中的负条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！