显示SQL语句的补集结果

Question

我有一个类似推特的网站，用户可以关注其他用户，我需要向他们展示关注新人的建议。

TABLE USERS

user id_user
A       1
B       2
C       3
D       4
E       5
F       6

TABLE COMMUNITY

id_follower id_followed
3               4
3               5
3               6

3 (C)关注了4、5、6（D、E、F）。

我收到了显示被关注用户4、5、6（D、E、F）的声明：

SELECT user,id_followed
  FROM users,community
    WHERE users.id_user=community.id_followed AND community.id_follower=3
        GROUP BY user

我该如何显示未被C（3）关注的用户，换句话说是1、2（A、B）?

我需要使用EXCEPT吗？还是LEFT OUTER JOIN？

SELECT id_followed
   FROM community
    EXCEPT
      (
        SELECT user,id_followed
           FROM users,community
                WHERE users.id_user=community.id_followed AND community.id_follower=3
        GROUP BY user
     )

    RETRIEVE ERROR.

- masterhoo

Answer 1

你可以使用NOT EXISTS。

SELECT *
   FROM users u
WHERE NOT EXISTS
      (
        SELECT *
           FROM community c
                WHERE c.id_follower=3
            AND ( u.id_user = c.id_followed or u.id_user = c.id_follower)
     )

sql fiddle

- Serkan Arslan

---

我尝试了所有的答案，我认为它们都是正确的，但是只有你的完美地起作用了！谢谢 ;) - masterhoo

Answer 2

简单的方法是使用减号运算符。它看起来应该是这样的：

SELECT id_user
 FROM users
MINUS
SELECT id_followed
 FROM community
 WHERE community.id_follower=3

- Egret

Answer 3

你想要做的可以通过使用“not in”子句来实现 尝试以下查询以实现你的目标

SELECT * 
FROM 
   users
WHERE 
   id_user not in 
        (SELECT 
            id_followed 
         FROM 
            community 
        WHERE 
            id_follower = 3)
AND 
    id_user <> 3

- Khan M