从 column.id 不在另一个表的列 1 或列 2 中的表中选择 *？PostgreSQL

Question

我有一个用户表：

id |   username   |          email          
----+--------------+-------------------------
6 | grahamsutt12 | [e-mail]
7 | jsmith       | [e-mail]
8 | dierobe      | [e-mail]
9 | mike12       | [e-mail]

我有一张友谊表格：

id | user_id | friend_id |  status  
----+---------+-----------+----------
22 |       6 |         7 | approved
23 |       6 |         8 | pending

我正在尝试创建一个"朋友建议器"，它建议请求朋友的人与你不是朋友。想法是：

• 当前用户使用存储在会话哈希中的用户ID登录
• 会话哈希中的用户ID应用于查找当前用户是而不是好友的所有其他用户

逻辑：

• 如果当前用户ID在user_id列的Friendships表中，则排除选择相应的friend_id
• 或者，如果当前用户ID在friend_id列的Friendships表中，则排除选择相应的user_id

我尝试过的一些查询：

SELECT *
FROM users
WHERE users.id
NOT IN (SELECT friendships.user_id FROM friendships)
INTERSECT
SELECT *
FROM users
WHERE users.id
NOT IN (SELECT friendships.friend_id FROM friendships);

这个只是返回任何不在友谊表中的用户，这是不实用的，因为mike12无法获得任何朋友建议。此外，我认为在一个非常大的表中，搜索如此大的用户记录需要更长的时间，而不仅仅是在友谊表中查找当前用户的ID。

---

SELECT *
FROM users
WHERE NOT EXISTS (SELECT * FROM friendships WHERE friendships.user_id = 6
OR friendships.friend_id = 6);

这一次没有任何回报，老实说，我真的不知道为什么。我在他们的6中只是为了测试查询。

---

SELECT *
FROM users
WHERE 6 NOT IN (SELECT friendships.user_id FROM friendships)
INTERSECT
SELECT *
FROM users
WHERE 6 NOT IN (SELECT friendships.friend_id FROM friendships);

这一点在逻辑上对我来说似乎最有意义(至少在我的脑海中)，但也没有产生任何结果。我原以为这个会至少返回ID为9的用户mike12，但没有。

我知道这里最大的问题是我的逻辑似乎有点偏离，我似乎无法用SQL的术语来写出来，因为我对SQL还比较陌生。

我们非常感谢任何人对此事的了解。非常感谢。

Answer 1

您要查找的是关系中的记录，而不是在其他关系或查询结果集中表示的记录。这在SQL中通常效率低下。下面的操作很好，但当表增长到数百万条记录时，性能不是很好，您可能不得不修改查询。friendship.user_id和friendship.friend_id上的单独索引以及user.id上的索引将加快查询速度。

诀窍是从friendship表(子查询)中构建当前用户及其所有朋友的id列表，然后在users表中查找具有不同id的记录(主查询)：

SELECT * 
FROM users
WHERE id NOT IN (
SELECT 6
UNION
SELECT user_id
FROM friendships
WHERE friend_id = 6
UNION
SELECT friend_id
FROM friendships
WHERE user_id = 6)

您可以用当前用户id的参数将此查询包装在SQL函数中，这样您就可以为任何用户id运行它。