（课堂笔记）Oracle 表关联：连接类型、数据发散、自关联、同环比计算
本文系统梳理了Oracle表关联的核心知识，包括：
连接类型：详细解析INNER JOIN(只返回匹配记录)、LEFT JOIN(保留左表全部)、RIGHT JOIN(保留右表全部)和FULL JOIN(保留两表全部)四种连接方式的特点和使用场景。
数据发散问题：说明一对多或多对多关系时可能产生的笛卡尔积现象，以及如何通过正确设置连接条件避免数据膨胀。
自关联技术：演示如何使用表别名实现同一张表的自我连接，典型应用于查询层级关系(如员工-领导关系)。
实用计算：提供同环比增长率的SQL实现方法，展示如何通过表连接计算业务指标。
优化建议：给出连接查询的性能优化技巧和常见陷阱的规避方法，包括NULL值处理、连接顺序选择等最佳实践。
课堂笔记
根据你提供的课堂笔记，以下是对Oracle 表关联的详细总结与梳理，涵盖连接类型、数据发散、自关联、同环比计算等核心内容。
一、连接类型详解
1. INNER JOIN（内连接）
返回：两表匹配的记录
特点：不匹配的数据不显示
sql
SELECT * FROM 中国移动 M INNER JOIN 中国联通 N ON M.姓名 = N.姓名; -- 结果：只显示张三、李四（两表都有的）
2. LEFT JOIN（左连接）
返回：左表全部 + 右表匹配部分
特点：左表数据不丢失，右表无匹配则为 NULL
sql
SELECT * FROM 中国移动 M LEFT JOIN 中国联通 N ON M.姓名 = N.姓名; -- 结果：张三、李四、王五（王五在联通表无对应）
3. RIGHT JOIN（右连接）
返回：右表全部 + 左表匹配部分
特点：右表数据不丢失，左表无匹配则为 NULL
sql
SELECT * FROM 中国移动 M RIGHT JOIN 中国联通 N ON M.姓名 = N.姓名; -- 结果：张三、李四、熊大（熊大在移动表无对应）
4. FULL JOIN（全外连接）
返回：两表全部数据
特点：双方数据都不丢失
sql
SELECT * FROM 中国移动 M FULL JOIN 中国联通 N ON M.姓名 = N.姓名; -- 结果：张三、李四、王五、熊大（全部保留）
✅ 全连接注意事项：
sql
-- 正确写法：使用 NVL 处理可能为 NULL 的字段 SELECT NVL(M.姓名, N.姓名) AS 姓名, -- 关键：连接字段要用 NVL NVL(M.话费, 0) + NVL(N.话费, 0) AS 总话费 FROM 中国移动 M FULL JOIN 中国联通 N ON M.姓名 = N.姓名;
二、数据发散（笛卡尔积）
产生原因
当连接条件满足一对多或多对多关系时，会产生数据发散。
典型示例
sql
-- 1. 无条件连接 = 笛卡尔积 SELECT * FROM EMP E CROSS JOIN DEPT D; -- 14行 × 4行 = 56行 -- 2. 等价写法 SELECT * FROM EMP E, DEPT D; -- 老式写法 SELECT * FROM EMP E LEFT JOIN DEPT D ON 1=1; -- 恒真条件
📊 行数推算规则
场景1：基于ID范围
A表：ID 1~100（100行）
B表：ID 61~120（60行）
交集：61~100（40行）
| 连接类型 | 返回行数 |
|---|---|
| INNER JOIN | 40 行（交集） |
| LEFT JOIN | 100 行（左表全部） |
| RIGHT JOIN | 60 行（右表全部） |
| FULL JOIN | 120 行（并集） |
场景2：基于表记录数
A表：10行
B表：5行
| 连接类型 | 最多行数 | 最少行数 |
|---|---|---|
| INNER JOIN | 50（笛卡尔积） | 0（无匹配） |
| LEFT JOIN | 50（笛卡尔积） | 10（无匹配） |
| RIGHT JOIN | 50（笛卡尔积） | 5（无匹配） |
| FULL JOIN | 50（笛卡尔积） | 10（无匹配） |
💡口诀：
最少行数：INNER可为0，LEFT不少于左表，RIGHT不少于右表，FULL不少于大表
最多行数：都是笛卡尔积（M × N）
三、自关联（Self Join）
定义
同一张表自己与自己连接，需要使用不同的别名区分不同角色。
典型场景：上下级关系
sql
-- 示例：员工及其领导信息 SELECT E.ENAME AS 员工姓名, E.SAL AS 员工薪资, F.ENAME AS 领导姓名, F.SAL AS 领导薪资 FROM EMP E -- 员工表（角色：员工） LEFT JOIN EMP F -- 领导表（角色：领导） ON E.MGR = F.EMPNO; -- 员工的领导编号 = 领导的员工编号
复杂示例：带部门信息
sql
SELECT E.ENAME AS 员工姓名, E.SAL AS 员工薪资, D.DNAME AS 员工部门, F.ENAME AS 领导姓名, F.SAL AS 领导薪资, K.DNAME AS 领导部门 FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO LEFT JOIN EMP F ON E.MGR = F.EMPNO LEFT JOIN DEPT K ON F.DEPTNO = K.DEPTNO;
四、实际应用：同环比计算
场景：计算每月环比增长率
公式：(本期 - 上期) / 上期 = 本期/上期 - 1
sql
-- 创建销售表 CREATE TABLE T_SALES( MONTH NUMBER, AMOUNT NUMBER ); INSERT INTO T_SALES VALUES (4,4432); INSERT INTO T_SALES VALUES (5,434); INSERT INTO T_SALES VALUES (6,232); INSERT INTO T_SALES VALUES (7,46554); INSERT INTO T_SALES VALUES (8,76); INSERT INTO T_SALES VALUES (9,454); COMMIT; -- 计算环比增长率 SELECT M.MONTH AS 月份, M.AMOUNT AS 本月销售额, N.AMOUNT AS 上月销售额, ROUND((M.AMOUNT / N.AMOUNT - 1) * 100, 2) || '%' AS 环比增长率 FROM T_SALES M -- 本月数据 LEFT JOIN T_SALES N ON M.MONTH - 1 = N.MONTH; -- 上月数据
扩展：同比计算（去年同期）
sql
-- 假设有年份字段 LEFT JOIN T_SALES N ON M.YEAR - 1 = N.YEAR AND M.MONTH = N.MONTH
五、重点总结
✅ 连接类型选择指南
| 需求 | 推荐连接 |
|---|---|
| 只要匹配的数据 | INNER JOIN |
| 左表数据全要 | LEFT JOIN |
| 右表数据全要 | RIGHT JOIN |
| 两表数据全要 | FULL JOIN |
| 避免数据发散 | 确保连接条件能唯一匹配 |
✅ 常见陷阱
忘记处理 NULL：全连接中使用
NVL处理可能为 NULL 的字段数据发散：连接前确认关联字段是否唯一
自关联忘记别名：必须用不同别名区分同一张表
连接顺序影响性能：小表驱动大表会更高效
✅ 调试技巧
sql
-- 1. 先查两表的唯一性 SELECT 关联字段, COUNT(*) FROM 表名 GROUP BY 关联字段 HAVING COUNT(*) > 1; -- 2. 用小数据集验证连接结果 SELECT * FROM 表名 WHERE ROWNUM <= 10; -- 3. 逐步添加连接条件 -- 先做单表查询，确认数据正确，再加 JOIN
六、练习答案参考
验证行数极值
sql
-- 1. INNER JOIN 无匹配（最少0行） SELECT * FROM TABLEA A INNER JOIN TABLEB B ON 1=2; -- 0行 -- 2. LEFT JOIN 无匹配（最少10行） SELECT * FROM TABLEA A LEFT JOIN TABLEB B ON 1=2; -- 10行（A表全保留） -- 3. RIGHT JOIN 全匹配（最多5×10=50行） SELECT * FROM TABLEA A RIGHT JOIN TABLEB B ON 1=1; -- 50行 -- 4. FULL JOIN 基于ID（返回并集） SELECT * FROM TABLEA A FULL JOIN TABLEB B ON A.ID = B.ID; -- 10行（1-5匹配，6-10仅A有）