经典sql题（十）查找共同好友或相互关注 一

SQL 的 JOIN

• JOIN 类型：这题我们使用的是 LEFT JOIN，也称为左外连接。它用于从左侧表（tl）中返回所有行，并且在右侧表（t2）中查找匹配的行。如果没有匹配，右侧表的结果将为 NULL。
• INNER JOIN vs LEFT JOIN：INNER JOIN 仅返回两个表中匹配的行，而 LEFT JOIN 保留左侧表的所有行。
• 在使用 LEFT JOIN 时，如果在右侧没有找到匹配的行，结果将返回 NULL。在这种情况下，通常需要通过条件判断来处理这些 NULL 值，比如通过 IF 或 CASE 来判断双向关系是否存在。

示例数据

假设我们的 fans 表有以下数据：

from_user	to_user
1	2
2	1
1	3
3	1
4	5
5	4
2	3

思路：提取用户之间的关系

我们希望通过查询找出每个用户之间的关系，并确定他们是否为好友关系。

查询：

SELECT 
    tl.from_user,
    tl.to_user,
    IF(t2.from_user IS NOT NULL, 1, 0) AS is_friend
FROM 
    fans tl
LEFT JOIN 
    fans t2 ON tl.from_user = t2.to_user AND tl.to_user = t2.from_user;

结果（表 table1）：

from_user	to_user	is_friend
1	2	1
2	1	1
1	3	0
3	1	0
4	5	1
5	4	1
2	3	0

第一步：分析好友关系

在这个查询中，我们通过左连接（LEFT JOIN）将 fans 表与自身连接，以检查每个用户和他们的朋友之间的关系。如果 t2.from_user 不为 NULL，则表示 from_user 和 to_user 之间存在双向关系，我们将其标记为好友（is_friend = 1），否则标记为非好友（is_friend = 0）。

关键步骤：

1. 左连接：将 tl 和 t2 通过用户的 from_user 与 to_user 进行连接，以便在 t2 中找到反向的好友关系。

   • tl 表是 fans 的一个别名，表示“用户关注的用户”。
   • t2 表是 fans 的另一个别名，表示“关注此用户的用户”。

2. 条件判断：使用 IF 语句来确定是否存在双向关系，并相应地标记 is_friend。

第二步：中间表结构

在执行查询时，形成了两个视图：

表 tl（当前用户关注的列表）：

from_user	to_user
1	2
2	1
1	3
3	1
4	5
5	4
2	3

表 t2（关注当前用户的列表）：

from_user	to_user
2	1
1	2
3	1
1	3
5	4
4	5
3	2

第三步：连接步骤

1. 原始表（fans 表）

这是我们在执行连接前的原始数据：

from_user	to_user
1	2
2	1
1	3
3	1
4	5
5	4
2	3

2. 连接条件

我们要执行的连接是左连接（LEFT JOIN），连接条件是：

• tl.from_user = t2.to_user
• tl.to_user = t2.from_user

这意味着我们要找出每个用户是否关注了对方，以判断双向关系。

3. 连接后的表

在连接之前，我们将原始表复制一份，称为 tl，并将其与自身进行连接，称为 t2。连接后的表如下：

连接规则示意

从 tl 表	from_user	to_user	连接条件	从 t2 表	t2.from_user	t2.to_user
第一行	1	2	1 = 2 (from_user) AND 2 = 1 (to_user)	第一行	2	1
第二行	2	1	2 = 1 (from_user) AND 1 = 2 (to_user)	第二行	1	2
第三行	1	3	1 = 3 (from_user) AND 3 = 1 (to_user)	无匹配	NULL	NULL
第四行	3	1	3 = 1 (from_user) AND 1 = 3 (to_user)	无匹配	NULL	NULL
第五行	4	5	4 = 5 (from_user) AND 5 = 4 (to_user)	第一行	5	4
第六行	5	4	5 = 4 (from_user) AND 4 = 5 (to_user)	第二行	4	5
第七行	2	3	2 = 3 (from_user) AND 3 = 2 (to_user)	无匹配	NULL	NULL

4. 连接后的结果表

连接后结果表如下：

tl.from_user	tl.to_user	t2.from_user	t2.to_user	is_friend
1	2	2	1	1
2	1	1	2	1
1	3	NULL	NULL	0
3	1	NULL	NULL	0
4	5	5	4	1
5	4	4	5	1
2	3	NULL	NULL	0

解析

• 在执行 LEFT JOIN 之前，原始表的行数是保持不变的。
• 通过连接条件，我们可以判断出哪些用户之间存在双向关系。
• 余下的行在连接后保持在结果中，尽管部分行的连接结果为 NULL，但它们在最终结果中依然存在。

最终查询：

SELECT 
    tl.from_user,
    tl.to_user,
    IF(t2.from_user IS NOT NULL, 1, 0) AS is_friend
FROM 
    fans tl
LEFT JOIN 
    fans t2 ON tl.from_user = t2.to_user AND tl.to_user = t2.from_user;

最终结果（表 table1）：

from_user	to_user	is_friend
1	2	1
2	1	1
1	3	0
3	1	0
4	5	1
5	4	1
2	3	0

最终结果分析

根据上述查询结果，我们可以分析出每对用户之间的好友关系：

• 用户 1 和 用户 2 是好友，is_friend 值为 1。
• 用户 1 和 用户 3 不是好友，is_friend 值为 0。
• 用户 4 和 用户 5 是好友，is_friend 值为 1。
• 用户 2 和 用户 3 不是好友，is_friend 值为 0。