sql - SQL Query 连接到第二个表中看似循环不存在的匹配项

Question

我正在尝试编写一个查询，该查询将输出用户不属于角色的所有情况。

我试图将此查询限制为尽可能简单（无循环等）。

我的示例中的表结构如下：

• 表Team：

  ID | UserID | RoleID
  ---+--------+-------
  1  | 0      | 0
  2  | 0      | 1
  3  | 0      | 2
  4  | 1      | 0
  5  | 1      | 2
  

• 表Roles：

  RoleID | Name
  -------+-----
  0      | AAA
  1      | BBB
  2      | CCC
  3      | DDD
  4      | EEE
  

期望的输出：

UserID | RoleID
-------+-------
0      | 3
0      | 4
1      | 1
1      | 3
1      | 4

我目前正在使用的代码是：

SELECT *
FROM Team t
RIGHT OUTER JOIN Roles r ON t.RoleID = r.RoleID
LEFT JOIN Roles r1 ON t.RoleID = r1.RoleID
WHERE r.RoleID IS NULL

标签： sqlleft-joinouter-join

Answer 1

您可以CROSS JOIN团队和角色生成所有可能的组合，然后使用LEFT JOIN反模式找出哪些组合不存在：

SELECT DISTINCT t.UserID, r.RoleID
FROM Team t
CROSS JOIN Roles r
LEFT JOIN Team t1 ON t1.RoleID = r.RoleID AND t1.userID = t.UserID
WHERE t1.UserID IS NULL
ORDER BY t.UserID, r.RoleID

DB Fiddle上的这个演示与您的示例数据返回：

| UserID | RoleID |
| ------ | ------ |
| 0      | 3      |
| 0      | 4      |
| 1      | 1      |
| 1      | 3      |
| 1      | 4      |