查找不在组织中的用户

Question

给定以下数据：

users
  id    name
  ==    ====
  1     Alice
  2     Bob
  3     Carl

organizations
  id    name
  ==    ====
  1     Aaa
  2     Bbb

memberships
  id    organization_id   user_id
  ==    ===============   =======
  1     1                 1
  2     2                 1
  3     1                 2

我想查找不属于特定组织的用户 X：

• 根本没有成员记录的用户

  并且

• 有会员记录的用户，但不属于组织 X。

例如，我想要不在组织 2 中的用户。我期望：

users.id
========
2
3

尝试此连接不起作用：

SELECT *
FROM users left join memberships on memberships.user_id = users.id
where memberships.id is null or memberships.organization_id <> 1

它返回用户 1,2,3，因为 1 与第二个 where 条件匹配。

有没有有效的方法来做到这一点？

Answer 1

将您的 JOIN 限制为 2 的组织，然后测试 null 是执行您正在寻找的操作的一种方法，例如

SELECT * 
FROM   users 
       LEFT JOIN memberships 
         ON memberships.user_id = users.id 
            AND memberships.organization_id = 2 
WHERE  memberships.id IS NULL 

您也可以使用 NOT IN

SELECT * 
FROM   users 
WHERE id NOT IN (SELECT user_id from memberships where organization_id = 2 )

Answer 2

您还可以使用减集运算符：

从用户中选择“名称”
减
从用户中选择“名称” u 内部加入成员资格 m 上 u.id = m.user_id 内部加入组织 o 上 m.organization_id = o.id 和 o."name" = 'X'

Answer 3

select Users.*
from users 
inner join memberships 
on Users.id = memberships.id
left join organizations 
on memberships.organization_id = organizations.id 
where memberships.id is null  AND memberships.organization_id = 2 

Answer 4

这是使用存在子句的另一个选项：

 select * from users
 where not exists 
    (select memberships.id from memberships 
      inner join organizations on memberships.organization_id = organizations.id 
      and memberships.user_id = users.id
      where organizations.id = 2)

Answer 5

select u.*
from users u
left join memberships m
on u.id = m.user_id
left join organizations o
on m.organization_id = o.id
where m.organization_id not in (2)

编辑：修改为包括所有用户