mysql - 两个连接语句

Question

所以我有这个查询:

SELECT DISTINCT(b.friend_id) AS possible_id
FROM friend_friends a
    JOIN friends_friends b 
      ON b.user_id = a.friend_id
WHERE a.user_id = 1703122278

它检索特定用户的 friend 的 friend 。

我有另一个数据库，其中包含那些声称自己不是 friend 的人的数据。

下面是获取非好友列表的查询:

SELECT friend_id
FROM friends_not_friends
WHERE user_id = 1703122278
AND not_friends = 1

然后我从可能的 friend 列表中比较非 friend 列表，并从列表中删除非 friend 列表。

如何在不使用子查询而是使用联接的情况下合并这两个查询？

Answer 1

SELECT DISTINCT(b.friend_id) AS possible_id
FROM friend_friends a
  JOIN friends_friends b 
    ON b.user_id = a.friend_id
  LEFT JOIN friends_not_friends n
    ON b.friend_id = n.friend_id
      AND n.user_id = a.user_id
      AND n.not_friends = 1
WHERE a.user_id = 1703122278
  AND n.friend_id IS NULL

这显示了

(1st list of friends of friends of 1703122278)
MINUS
(2nd list of not friends of 1703122278)

我希望你不想要

list of friends of
   (friends of 1703122278
    minus
    (2nd list of not friends of 1703122278)
   )

---

只是胡闹，这里是使用 NOT IN 的查询。我个人觉得这些更清晰，但它们在速度方面可能效率较低。

-- Friends of friends of a user
SELECT DISTINCT(b.friend_id) AS possible_id 
FROM friend_friends b                      -- the b and a aliases
WHERE b.user_id IN                         -- can be removed 
  ( SELECT a.friend_id
    FROM friends_friends a
    WHERE a.user_id = 1703122278
  )
;

和被问到的:

-- Friends of friends of a user that  
-- are also not not_friends of the user:
SELECT DISTINCT(b.friend_id) AS possible_id  
FROM friend_friends b                      -- the b, a and n aliases
WHERE b.user_id IN                         -- can be removed too
  ( SELECT a.friend_id
    FROM friends_friends a
    WHERE a.user_id = 1703122278
  )
  AND b.friend_id NOT IN 
  ( SELECT n.friend_id
    FROM friends_not_friends n
    WHERE n.user_id = 1703122278
      AND n.not_friends = 1
  )
;