SQL：SELF JOIN（自连接）与CROSS JOIN（交叉连接）

目录

SELF JOIN（自连接）

CROSS JOIN（交叉连接 / 笛卡尔积） 

 示例：

SELF JOIN 

 CROSS JOIN

注意事项 ⚠️

如果没有 DATEDIFF() 函数怎么办？

🔍 SELF JOIN vs CROSS JOIN 对比总结

---

SELF JOIN（自连接）

📌1. SELF JOIN 是什么？

SELF JOIN（自连接） 是一种让同一张表自己连接自己的一种 JOIN 操作。

我们可以把同一张表“复制”两份，然后像普通 JOIN 一样连接。

📌 2. SELF JOIN 的语法：

SELECT ...
FROM 表名 AS A
JOIN 表名 AS B
  ON A.某列 = B.某列
WHERE ...

这里的 A 和 B 都是同一张表的“别名”，用于区分连接的两份数据。

📌3. SELF JOIN 的典型使用场景：

• 比较不同行之间的关系，比如“比昨天高”

• 计算相邻记录之间的差值

• 在同一个表中查找有逻辑关系的记录，比如“员工和上级在一个表中”

CROSS JOIN（交叉连接 / 笛卡尔积） 

📌 1. CROSS JOIN 是什么？

CROSS JOIN（交叉连接） 是一种连接方式，它会将两个表的所有记录两两组合，形成笛卡尔积。

例如：

表 A 有 2 行，表 B 有 3 行，
那么 A CROSS JOIN B 会得到 2×3=6 行记录。

 📌 2. CROSS JOIN 的语法：

SELECT ...
FROM 表1
CROSS JOIN 表2

 也可以写成不带 ON 的普通 JOIN，效果一样：

SELECT ...
FROM 表1, 表2

 注意：没有连接条件！ 全部组合！

📌 3. CROSS JOIN 的使用场景：

• 枚举所有可能的组合（如所有商品和所有顾客）

• 一般会配合 WHERE 条件进行过滤，否则生成的结果会很大

---

示例：

表： Weather 

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+

 id 是该表具有唯一值的列。

没有具有相同 recordDate 的不同行。

该表包含特定日期的温度信息

编写解决方案，找出与之前（昨天的）日期相比温度更高的所有日期的 id 。

 （来源：Leecode）

SELF JOIN 

SELECT w1.id
FROM Weather w1
JOIN Weather w2
  ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
WHERE w1.temperature > w2.temperature;

• Weather w1 JOIN Weather w2：自己连接自己，w1 是今天，w2 是昨天

• DATEDIFF(w1.recordDate, w2.recordDate) = 1：表示 w1 是 w2 的第二天

• w1.temperature > w2.temperature：当天温度高于昨天

 CROSS JOIN

SELECT w1.id
FROM Weather w1
CROSS JOIN Weather w2
WHERE DATEDIFF(w1.recordDate, w2.recordDate) = 1
  AND w1.temperature > w2.temperature;

• CROSS JOIN：生成所有日期组合

• WHERE DATEDIFF(...) = 1：筛选出“今天-昨天=1天”的组合

• w1.temperature > w2.temperature：筛选出当天温度更高的组合

注意事项 ⚠️

在这道题中，Weather 表要自连接一次，用两个别名：w1, w2：

SELECT w1.id
FROM Weather w1
JOIN Weather w2

这时，id 这个字段在两个表里都有（虽然它们是同一张表，但被赋予不同别名后 SQL 会认为是两个“独立源”）。

✅ 正确做法：

你应该明确指定字段来自哪个表别名，比如：

SELECT w1.id

只要你 JOIN 了多个表，尤其是同一张表的自连接，就必须写清楚字段来自哪个表别名，否则就会 column is ambiguous（列不明确） 报错！ 

如果没有 DATEDIFF() 函数怎么办？

可以用日期减一的方式：

SELECT w1.id
FROM Weather w1
JOIN Weather w2
  ON w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY)
WHERE w1.temperature > w2.temperature;

⚠️ 注意性能：

CROSS JOIN 会先生成 全部组合，再用 WHERE 过滤，效率远低于 JOIN ... ON 的方式。

所以在实际中不推荐用 CROSS JOIN 写这个题，主要用于教学理解。

🔍 SELF JOIN vs CROSS JOIN 对比总结

比较项	SELF JOIN	CROSS JOIN
含义	表与自身连接，按条件匹配不同记录	两表的所有记录两两组合
结果行数	根据 JOIN 条件过滤后生成的匹配行	m × n 行（m,n 分别为两表的行数）
使用场景	自我比较、查找相关记录（如“比昨天温度高”）	枚举所有组合，如“所有商品和所有客户”
性能	较高（条件过滤优先）	较低（生成全部组合再过滤）