sql - 显示来自语句 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.

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