mysql - 在没有 NOT IN 查询的情况下获取不同的记录

Question

我有如下所示的三个表(admin、domain、user)

• 'admin'(admin_id, email, domain_id, pass)
• '域'(domain_id, 名称)
• “用户”(user_id、电子邮件、domain_id、密码)

管理表:

+--------------------------------+         
| admin_id | email | domain_id  |          
+--------------------------------+         
| 1        | aaa   | 2034       |        
| 2        | bbb   | 3034       |
| 3        | ccc   | 2034       | 
+--------------------------------+

用户表

+--------------------------------+         
| user_id | email | domain_id  |          
+--------------------------------+         
| 11        | aaa   | 2034       |        
| 12        | bbb   | 3034       |
| 13        | ccc   | 2034       | 
| 15        | ddd   | 2034       | 
| 16        | eee   | 3034       |
+--------------------------------+

域表:

+-----------------------+         
| domain_id | name    |          
+-----------------------+         
| 2034      | aaa.com |        
| 3034      | bbb.com |
+-----------------------+

输出:

+------------+
|ddd@aaa.com |
|eee@bbb.com |
+------------+

所以我想要用户表中不存在于管理表中的记录。

有没有不用 NOT IN 查询的方法？

Answer 1

Is there any way to this without NOT IN query?

是的，使用用户与管理员的 LEFT JOIN。

如果user表和admin表中的id属性用于判断两条记录是否属于同一个用户，那么你的LEFT JOIN将在这个属性上，如下所示:

SELECT CONCAT(u.email, CONCAT('@', d.name))
FROM 
    user u
    JOIN domain d
    ON u.domain_id = d.domain_id
    LEFT JOIN admin a
    ON u.id = a.id
WHERE 
    a.id IS NULL

如果单独使用id属性还不够，还需要domain_id来判断两条记录属于同一个用户，那么

SELECT CONCAT(u.email, CONCAT('@', d.name))
FROM 
    user u
    JOIN domain d
    ON u.domain_id = d.domain_id
    LEFT JOIN admin a
    ON u.id = a.id AND u.domain_id = a.domain_id
WHERE 
    a.id IS NULL

编辑:

根据您对问题的更新，由于电子邮件和域由管理员和用户表共享，因此您应该在这两列上 LEFT JOIN 这些表:

SELECT CONCAT(u.email, CONCAT('@', d.name))
FROM 
    user u
    JOIN domain d
    ON u.domain_id = d.domain_id
    LEFT JOIN admin a
    ON u.email = a.email AND u.domain_id = a.domain_id
WHERE 
    a.email IS NULL