mysql - 当连接表中不存在值时

Question

我使用此查询来查找 users 的电子邮件地址是否不存在于 emails_sent 表中。

SELECT users.email,users.registration_date 
FROM users 
LEFT JOIN emails_sent
ON emails_sent.email=users.email
WHERE emails_sent.email IS NULL
ORDER BY registration_date DESC
LIMIT 0,50

相反，我想返回users.email，其中没有emails_sent.type='welcome'

的记录

我尝试了以下方法，但没有返回结果

SELECT users.email,users.registration_date 
FROM users 
LEFT JOIN emails_sent
ON emails_sent.email=users.email
WHERE emails_sent.type!='welcome'
ORDER BY registration_date DESC
LIMIT 0,50

emails_sent表如下

id  int(11) unsigned    NO  PRI NULL
email   varchar(200)    YES     NULL
type    varchar(200)    YES     NULL
timestamp   int(200)    YES     NULL

Answer 1

只需在 LEFT-JOIN 部分包含“AND”条件，而不是 WHERE

SELECT 
      users.email,
      users.registration_date 
   FROM 
      users 
         LEFT JOIN emails_sent
            ON users.email = emails_sent.email
           AND emails_sent.type = 'welcome'
   where
       emails_sent.email IS NULL
   ORDER BY 
      registration_date DESC
   LIMIT 
      0,50

因此，您的加入既取决于匹配用户的标准，又取决于“欢迎”电子邮件消息。 Ø