MySql 希望丢弃左连接的结果

Question

我得到了这个表:

用户

|id |Name  | country|
--------------
|1  | Ben   | Japan  |
|2  | Jo    | Japan  |
|3  | Sam   |   US   |

邮件

|id | subject|
--------------
|1  | Good   |
|2  | Bonus  |

Ref_Users_Mails

|user_id | mail_id|
-------------------
|   1    |    1   |
|   1    |    2   |
|   2    |    1   |
|   3    |    1   |

我想要以下结果:获取所有用户来自一组国家不是来自一组国家收到带有此 mailIds 的邮件并且没有收到其他mailId的邮件

例如:所有用户都来自日本而不是美国收到邮件 1，尚未收到 2。

==> 结果应该只是“Jo”，但我设法获得了“Jo”和“Ben”(因为“Ben”也收到了邮件 ID 1)

我如何过滤掉“Ben”，因为他也收到了 ID 2 的邮件？

这是我的查询:

   SELECT * FROM Users
   LEFT JOIN Ref_Users_Mails
   ON Users.id = Ref_Users_Mails.user_id
   WHERE ( trim(Users.country) IN ( 'Japan' ) ) 
   AND ( trim(Users.country) NOT IN ( 'US' ) ) 
   AND ( trim(Ref_Users_Mails.mail_id) NOT IN ( 2 ) ) 
   ORDER BY Users.user_id;

结果:

|id |Name  | country|
--------------
|1  | Ben   | Japan  |
|2  | Jo    | Japan  |

想要的结果:

|id |Name  | country|
--------------
|2  | Jo    | Japan  |

Answer 1

要从国家/地区=“日本”的用户获取行，这就足够了:

   SELECT u.id
        , u.name
        , u.country
     FROM Users u
    WHERE TRIM(u.country) IN ('Japan')  
    ORDER BY u.id

无需在 u.country 上添加另一个谓词。这不是必需的:

      AND TRIM(u.country) NOT IN ('US')

因为如果 WHERE 子句中的条件为 true，我们就保证为 true。

从该查询返回的集合中，我们只需要已收到邮件的用户 1。如果我们保证 (user_id,mail_id) 元组在 Ref_Users_Mails 中是唯一的>，我们可以使用连接操作来获得它。

     JOIN Ref_Users_Mails r
       ON r.user_id = u.id
      AND r.mail_id = 1

如果我们还想排除已收到邮件2的用户，我们可以使用反加入模式。

     LEFT
     JOIN Ref_Users_Mails q
       ON q.user_id = u.id
      AND q.mail_id = 2
    WHERE q.user_id IS NULL 

所以，把所有这些放在一起:

   SELECT u.id
        , u.name
        , u.country
     FROM Users u
     JOIN Ref_Users_Mails r
       ON r.user_id = u.id
      AND r.mail_id = 1
     LEFT
     JOIN Ref_Users_Mails q
       ON q.user_id = u.id
      AND q.mail_id = 2
    WHERE q.id IS NULL 
      AND TRIM(u.country) IN ('Japan')  
    ORDER BY u.id 

还有其他查询模式将返回等效结果。例如，我们可以使用 EXISTS 和 NOT EXISTS 谓词。

   SELECT u.id
        , u.name
        , u.country
     FROM Users u
    WHERE TRIM(u.country) IN ('Japan')
      AND EXISTS 
          ( SELECT 1 
              FROM Ref_Users_Mails r
             WHERE r.user_id = u.id
               AND r.mail_id = 1
          )
      AND NOT EXISTS
          ( SELECT 1
              FROM Ref_Users_Mails q
             WHERE q.user_id = u.id
               AND q.mail_id = 2
          )
    ORDER BY u.id

如果我们不能保证(user_id, mail_id)元组在Ref_Users_Mails中是唯一的，则此查询模式将起作用