（课堂笔记）Oracle 表关联：连接类型、数据发散、自关联、同环比计算

本文系统梳理了Oracle表关联的核心知识包括连接类型详细解析INNER JOIN(只返回匹配记录)、LEFT JOIN(保留左表全部)、RIGHT JOIN(保留右表全部)和FULL JOIN(保留两表全部)四种连接方式的特点和使用场景。数据发散问题说明一对多或多对多关系时可能产生的笛卡尔积现象以及如何通过正确设置连接条件避免数据膨胀。自关联技术演示如何使用表别名实现同一张表的自我连接典型应用于查询层级关系(如员工-领导关系)。实用计算提供同环比增长率的SQL实现方法展示如何通过表连接计算业务指标。优化建议给出连接查询的性能优化技巧和常见陷阱的规避方法包括NULL值处理、连接顺序选择等最佳实践。课堂笔记根据你提供的课堂笔记以下是对Oracle 表关联的详细总结与梳理涵盖连接类型、数据发散、自关联、同环比计算等核心内容。一、连接类型详解1. INNER JOIN内连接返回两表匹配的记录特点不匹配的数据不显示sqlSELECT * FROM 中国移动 M INNER JOIN 中国联通 N ON M.姓名 N.姓名; -- 结果只显示张三、李四两表都有的2. LEFT JOIN左连接返回左表全部 右表匹配部分特点左表数据不丢失右表无匹配则为 NULLsqlSELECT * FROM 中国移动 M LEFT JOIN 中国联通 N ON M.姓名 N.姓名; -- 结果张三、李四、王五王五在联通表无对应3. RIGHT JOIN右连接返回右表全部 左表匹配部分特点右表数据不丢失左表无匹配则为 NULLsqlSELECT * FROM 中国移动 M RIGHT JOIN 中国联通 N ON M.姓名 N.姓名; -- 结果张三、李四、熊大熊大在移动表无对应4. FULL JOIN全外连接返回两表全部数据特点双方数据都不丢失sqlSELECT * 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 11; -- 恒真条件 行数推算规则场景1基于ID范围A表ID 1~100100行B表ID 61~12060行交集61~10040行连接类型返回行数INNER JOIN40 行交集LEFT JOIN100 行左表全部RIGHT JOIN60 行右表全部FULL JOIN120 行并集场景2基于表记录数A表10行B表5行连接类型最多行数最少行数INNER JOIN50笛卡尔积0无匹配LEFT JOIN50笛卡尔积10无匹配RIGHT JOIN50笛卡尔积5无匹配FULL JOIN50笛卡尔积10无匹配口诀最少行数INNER可为0LEFT不少于左表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; -- 员工的领导编号 领导的员工编号复杂示例带部门信息sqlSELECT 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;四、实际应用同环比计算场景计算每月环比增长率公式(本期 - 上期) / 上期 本期/上期 - 1sql-- 创建销售表 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 12; -- 0行 -- 2. LEFT JOIN 无匹配最少10行 SELECT * FROM TABLEA A LEFT JOIN TABLEB B ON 12; -- 10行A表全保留 -- 3. RIGHT JOIN 全匹配最多5×1050行 SELECT * FROM TABLEA A RIGHT JOIN TABLEB B ON 11; -- 50行 -- 4. FULL JOIN 基于ID返回并集 SELECT * FROM TABLEA A FULL JOIN TABLEB B ON A.ID B.ID; -- 10行1-5匹配6-10仅A有