mysql - 选择 friend 的 friend sql 的性能(但不是我的 friend )

Question

我想得到我 friend 的 friend 不是我 friend 的 friend 。我有一张这样的 table :用户 friend (idUser，idUserFriend)

我在想这样的事情:

select distinct idUserFriend from userFriends where idUser in
(select idUserFriend from userFriends where idUser = 1)
and idUserFriend not in (select idUserFriend from userFriends where idUser = 1)

但我不确定它是这么慢还是可以通过其他方式更快。<​​/p>

对此有何看法？

Answer 1

SELECT DISTINCT
    two.idUserFriend
FROM userFriends one
JOIN userFriends two ON one.idUserFriend = two.idUser
WHERE NOT EXISTS (
        SELECT * 
        FROM userFriends nx
        WHERE nx.idUser = one.idUser
        AND nx.idUserFriend = two.idUserFriend
        )
AND one.idUser = 1
        ;

同样可以通过“NOT IN”构造来完成:

SELECT DISTINCT
    two.idUserFriend
FROM userFriends one
JOIN userFriends two ON one.idUserFriend = two.idUser
WHERE two.idUserFriend NOT IN (
        SELECT  nx.idUserFriend
        FROM userFriends nx
        WHERE nx.idUser = one.idUser
        )
AND one.idUser = 1
        ;

还有“EXCEPT”版本:

SELECT DISTINCT
    two.idUserFriend
FROM userFriends one
JOIN userFriends two ON one.idUserFriend = two.idUser
WHERE one.idUser = 1
EXCEPT (
        SELECT  nx.idUserFriend
        FROM userFriends nx
        WHERE nx.idUser = 1
        )
        ;