mysql - 加入一个表并选择不存在的地方

Question

我有下表:

employees: A table with all my employees
teams: A table with teams created
team_employees: A table with a relation of a team with a list of employees.

所以，基本上我需要的是在一个团队中选择所有没有团队的员工。例如，如果您看到，在表 team_employees 中，id_team #2 没有 id_employee #2。我正在寻找的查询选择应该给我 ID employee #2(但只有 Team ID #2)

我已经尝试过像这样执行 LEFT JOIN 但没有成功，因为我认为它会在 team_id 字段中给我一个 NULL，但是没有:

SELECT te.id_team AS id_team, e.id, e.name
FROM employees e
LEFT JOIN team_employees te
ON te.id_employee = e.id
WHERE id_team = 2
GROUP BY e.id

感谢您的帮助!

Answer 1

使用这种形式的左连接

SELECT te.id_team AS id_team, e.id, e.name
FROM employees e
LEFT JOIN team_employees te ON te.id_employee = e.id AND te.id_team = 2
WHERE te.id_team IS NULL

有时称为“左排除连接”。您需要做的是将 e.id_team = 2 放入连接条件中，而不是 where 子句中。然后用where子句定位所有没有匹配到team表的行。

备选方案:

SELECT e.*
FROM employees e
WHERE NOT EXISTS (
     select null 
     from team_employees te
     where te.id_employee = e.id AND te.id_team = 2
     )