如何在MySQL查询中排除该组？

Question

表1：用户

| profile_id | name    |
------------------------
| 1          | Joe     |
| 2          | Jane    |
| 3          | Jill    |
| 4          | Jeffery |

表2：用户、角色和团队的对应关系

| team_id | profile_id | role   |
---------------------------------
| 1       | 1          | coach  |
| 1       | 2          | player |
| 2       | 4          | coach  |
| 2       | 1          | player |

场景是Jill正在建立一个团队，限制条件是你不能成为多个团队的成员。因此，我正在尝试构建一个查询，以查找那些有资格加入Jill团队的人。

我的第一次尝试是：

SELECT `users`.`profile_id`
FROM `users` LEFT JOIN `user_role_to_team_lookup` AS `utr` USING(`profile_id`)
WHERE `utr`.`role` != 'player' OR `utr`.`role` IS NULL

问题在于，由于Joe是一名教练，他符合条件~尽管他已经是一名球员。

如何正确地排除已经是球员的用户？

- Shad

Answer 1

我会写出不使用大多数人使用的子查询的代码：

SELECT u.profile_id
FROM users AS u 
LEFT OUTER JOIN user_role_to_team_lookup AS utr 
  ON u.profile_id = utr.profile_id AND utr.role = 'player'
WHERE utr.profile_id IS NULL

换句话说，寻找一个已经是玩家的用户。那些不是玩家的用户在外连接中将没有匹配行，因此utr的任何列都将为NULL。

但最好将条件放在连接的ON子句中。

- Bill Karwin

Answer 2

SELECT u.profile_id
    FROM users u
    WHERE NOT EXISTS(SELECT 1
                         FROM user_role_to_team_lookup urtl
                         WHERE urtl.profile_id = u.profile_id
                             AND urtl.role = 'player')

- Joe Stefanelli

---

谢谢，通过研究您的答案，我了解到为什么“EXISTS”方法比其他样式的“NOT IN”子查询更有效。 - Shad

Answer 3

你可以尝试这样做：

SELECT profile_id FROM users
WHERE profile_id NOT IN (SELECT DISTINCT profile_id FROM utr WHERE role = 'player');

- Karlson

Answer 4

SELECT profile_id
FROM users
WHERE profile_id NOT IN (
    SELECT profile_id
    FROM user_role_to_team_lookup
    WHERE role = 'player');

- Patrickdev