上一篇【第03篇】Oracle SQL分组统计与排序——GROUP BY、HAVING与ORDER BY深度解析下一篇【第05篇】Oracle子查询与集合操作——嵌套查询与结果合并全解析摘要本文全面讲解Oracle SQL中的多表连接操作包括内连接、外连接、自连接、交叉连接等各种JOIN类型以及Oracle特有的传统连接语法与ANSI标准JOIN语法的对比。通过员工与部门信息关联、组织层级查询等实战案例帮助读者掌握多表查询的核心技能为复杂业务报表的开发奠定基础。一、引言真实业务系统中数据往往分散在多个关联表中。以HR系统为例员工表EMPLOYEES存储个人信息部门表DEPARTMENTS存储部门名称地点表LOCATIONS存储地址工种表JOBS存储职位描述。单表查询无法满足查询每个员工所在部门及所在城市这样的需求这就需要多表连接JOIN。本文使用Oracle标准的HR示例数据库进行讲解涉及以下主要表EMPLOYEES (employee_id, first_name, last_name, salary, department_id, manager_id, job_id) DEPARTMENTS (department_id, department_name, manager_id, location_id) LOCATIONS (location_id, street_address, city, state_province, country_id) JOBS (job_id, job_title, min_salary, max_salary)二、传统连接语法Oracle特有在ANSI JOIN语法普及之前Oracle使用WHERE子句实现表连接。理解传统语法有助于阅读遗留代码。2.1 等值连接Equijoin-- 传统语法在WHERE中指定连接条件SELECTe.employee_id,e.first_name,e.last_name,d.department_nameFROMemployees e,departments dWHEREe.department_idd.department_id;-- 多表等值连接SELECTe.employee_id,e.first_name|| ||e.last_nameASfull_name,d.department_name,l.city,j.job_titleFROMemployees e,departments d,locations l,jobs jWHEREe.department_idd.department_idANDd.location_idl.location_idANDe.job_idj.job_idORDERBYe.employee_id;2.2 非等值连接Non-Equijoin-- 根据薪资范围匹配薪资等级表-- 假设存在JOB_GRADES表(grade_level, lowest_sal, highest_sal)SELECTe.employee_id,e.last_name,e.salary,jg.grade_levelASsalary_gradeFROMemployees e,job_grades jgWHEREe.salaryBETWEENjg.lowest_salANDjg.highest_salORDERBYjg.grade_level,e.salary;2.3 自连接Self Join自连接是表与自身的连接常用于处理层级数据如员工与其上级的关系。-- 查询每个员工及其直属上级的姓名SELECTe.employee_idAS员工ID,e.first_name|| ||e.last_nameAS员工姓名,e.salaryAS员工薪资,m.employee_idAS上级ID,m.first_name|| ||m.last_nameAS上级姓名,m.salaryAS上级薪资FROMemployees e,employees m-- 同一张表起不同别名WHEREe.manager_idm.employee_idORDERBYm.employee_id,e.employee_id;2.4 Oracle外连接传统语法()符号-- 左外连接包含左表所有行右表不匹配时显示NULL-- 在连接条件的右侧加()SELECTe.employee_id,e.last_name,d.department_id,d.department_nameFROMemployees e,departments dWHEREe.department_idd.department_id()-- ()在右侧 左外连接ORDERBYe.employee_id;-- 右外连接包含右表所有行SELECTe.employee_id,e.last_name,d.department_id,d.department_nameFROMemployees e,departments dWHEREe.department_id()d.department_id-- ()在左侧 右外连接ORDERBYd.department_id;-- 注意传统语法不支持完全外连接需要用UNION模拟三、ANSI标准JOIN语法推荐使用Oracle 9i起支持ANSI/ISO SQL标准的JOIN语法可读性更好是现代Oracle开发的推荐写法。3.1 内连接INNER JOIN内连接只返回两表中满足连接条件的匹配行。-- 基本内连接SELECTe.employee_id,e.first_name|| ||e.last_nameASfull_name,d.department_nameFROMemployees eINNERJOINdepartments dONe.department_idd.department_id;-- INNER可以省略直接写JOIN也表示内连接-- 多表内连接SELECTe.employee_id,e.last_name,e.salary,d.department_name,l.city,c.country_nameFROMemployees eJOINdepartments dONe.department_idd.department_idJOINlocations lONd.location_idl.location_idJOINcountries cONl.country_idc.country_idWHEREe.salary10000ORDERBYe.salaryDESC;-- NATURAL JOIN自动匹配两表中同名列-- 注意需谨慎使用同名列的数据类型必须一致SELECTemployee_id,department_id,department_nameFROMemployeesNATURALJOINdepartments;-- USING子句指定特定的同名列进行连接SELECTe.employee_id,department_id,d.department_nameFROMemployees eJOINdepartments dUSING(department_id)-- 使用USING时连接列不能加表名前缀ORDERBYe.employee_id;3.2 左外连接LEFT OUTER JOIN返回左表所有行右表不匹配时用NULL填充。-- 查询所有员工及其部门名称包括未分配部门的员工SELECTe.employee_id,e.first_name,e.last_name,NVL(d.department_name,未分配部门)ASdept_nameFROMemployees eLEFTOUTERJOINdepartments dONe.department_idd.department_idORDERBYe.employee_id;-- 找出没有分配部门的员工SELECTe.employee_id,e.first_name,e.last_nameFROMemployees eLEFTJOINdepartments dONe.department_idd.department_idWHEREd.department_idISNULL;-- 右表为NULL表示无匹配行3.3 右外连接RIGHT OUTER JOIN返回右表所有行左表不匹配时用NULL填充。-- 查询所有部门及其员工数量包括没有员工的空部门SELECTd.department_id,d.department_name,COUNT(e.employee_id)ASemp_count-- 用员工表的PK统计避免计入NULLFROMemployees eRIGHTOUTERJOINdepartments dONe.department_idd.department_idGROUPBYd.department_id,d.department_nameORDERBYemp_countDESC;-- 找出没有员工的空部门SELECTd.department_id,d.department_nameFROMemployees eRIGHTJOINdepartments dONe.department_idd.department_idWHEREe.employee_idISNULL;3.4 全外连接FULL OUTER JOIN返回左表和右表的所有行任一侧不匹配时用NULL填充。-- 全外连接找出所有员工和所有部门的完整关联情况SELECTe.employee_id,e.last_name,NVL(TO_CHAR(e.department_id),无部门)ASemp_dept,d.department_id,d.department_nameFROMemployees eFULLOUTERJOINdepartments dONe.department_idd.department_idORDERBYd.department_id NULLSLAST,e.employee_id;3.5 交叉连接CROSS JOIN返回两表的笛卡尔积每行与对方每行组合结果行数 左表行数 × 右表行数。-- CROSS JOIN生成笛卡尔积-- 场景生成所有可能的部门-职位组合SELECTd.department_name,j.job_titleFROMdepartments dCROSSJOINjobs jORDERBYd.department_name,j.job_title;-- 结果行数 27个部门 × 19个职位 513行-- 等价的传统语法WHERE条件中不加连接条件即为笛卡尔积SELECTd.department_name,j.job_titleFROMdepartments d,jobs jORDERBYd.department_name,j.job_title;3.6 自连接ANSI语法-- 使用ANSI语法实现自连接查询员工与上级SELECTe.employee_idASemp_id,e.last_nameASemp_name,e.salaryASemp_salary,m.employee_idASmgr_id,m.last_nameASmgr_name,m.salaryASmgr_salary,-- 计算薪资差距e.salary-m.salaryASsalary_diffFROMemployees eLEFTJOINemployees mONe.manager_idm.employee_idORDERBYm.employee_id NULLSFIRST,e.salary;-- 找出薪资高于直属上级的员工不正常情况SELECTe.employee_id,e.last_name||薪资: ||e.salary||ASemployee_info,m.last_name||薪资: ||m.salary||ASmanager_infoFROMemployees eJOINemployees mONe.manager_idm.employee_idWHEREe.salarym.salary;四、高级连接技巧4.1 多条件连接-- ON子句可以包含多个条件SELECTe.employee_id,e.last_name,d.department_name,d.location_idFROMemployees eJOINdepartments dONe.department_idd.department_idANDd.location_id1700-- 附加筛选条件注意与WHERE的区别ORDERBYe.employee_id;-- ON和WHERE的区别-- ON中的非连接条件会影响连接方式外连接时尤为重要-- WHERE在连接之后过滤-- 对比以下两个查询结果不同-- 写法1条件在ON中SELECTe.last_name,d.department_nameFROMemployees eLEFTJOINdepartments dONe.department_idd.department_idANDd.location_id1700;-- 不满足的部门行显示NULL保留员工行-- 写法2条件在WHERE中SELECTe.last_name,d.department_nameFROMemployees eLEFTJOINdepartments dONe.department_idd.department_idWHEREd.location_id1700;-- 直接过滤不匹配的行被删除变成内连接效果4.2 三表及以上连接-- 查询员工的完整信息姓名、部门、城市、国家、薪资等级SELECTe.employee_idAS工号,e.first_name|| ||e.last_nameAS姓名,j.job_titleAS职位,d.department_nameAS部门,l.cityAS城市,c.country_nameAS国家,TO_CHAR(e.salary,L99,999)AS月薪,CASEWHENe.salary5000THENE级WHENe.salary8000THEND级WHENe.salary12000THENC级WHENe.salary17000THENB级ELSEA级ENDAS薪资等级FROMemployees eJOINjobs jONe.job_idj.job_idJOINdepartments dONe.department_idd.department_idJOINlocations lONd.location_idl.location_idJOINcountries cONl.country_idc.country_idORDERBYd.department_name,e.salaryDESC;4.3 连接与分组的结合-- 统计各国家的员工数量和平均薪资SELECTc.country_nameAS国家,COUNT(DISTINCTd.department_id)AS部门数,COUNT(e.employee_id)AS员工总数,TO_CHAR(ROUND(AVG(e.salary),2),99,999.99)AS平均薪资,TO_CHAR(SUM(e.salary),9,999,999)AS薪资总计FROMcountries cLEFTJOINlocations lONc.country_idl.country_idLEFTJOINdepartments dONl.location_idd.location_idLEFTJOINemployees eONd.department_ide.department_idGROUPBYc.country_nameHAVINGCOUNT(e.employee_id)0ORDERBYCOUNT(e.employee_id)DESC;五、层级查询CONNECT BYOracle提供了强大的层级查询语法专门用于处理树形结构数据。-- 查询组织结构树从CEO开始展示全部层级SELECTLEVELASdept_level,LPAD( ,(LEVEL-1)*3)||employee_idAS层级缩进,employee_id,first_name|| ||last_nameASfull_name,manager_id,salaryFROMemployeesSTARTWITHmanager_idISNULL-- 从根节点开始没有上级的人是CEOCONNECTBYPRIOR employee_idmanager_id-- 向下遍历当前行的employee_id 下一行的manager_idORDERSIBLINGSBYsalaryDESC;-- 同级按薪资降序-- SYS_CONNECT_BY_PATH显示从根节点到当前节点的路径SELECTemployee_id,last_name,SYS_CONNECT_BY_PATH(last_name,/)ASorg_path,LEVELASorg_levelFROMemployeesSTARTWITHmanager_idISNULLCONNECTBYPRIOR employee_idmanager_id;-- 只查询某人下属的所有员工包括间接下属SELECTemployee_id,last_name,salary,LEVELFROMemployeesSTARTWITHemployee_id101-- 从员工101开始CONNECTBYPRIOR employee_idmanager_idORDERBYLEVEL,salaryDESC;六、综合实战案例案例员工组织架构报表-- 生成完整的员工组织架构报表-- 包含员工信息、所属部门、上级姓名、工作地点WITHemp_hierarchyAS(SELECTe.employee_id,e.first_name|| ||e.last_nameASfull_name,e.job_id,e.salary,e.department_id,e.manager_id,m.first_name|| ||m.last_nameASmanager_name,LEVELASorg_level,SYS_CONNECT_BY_PATH(e.last_name, - )ASreporting_chainFROMemployees eLEFTJOINemployees mONe.manager_idm.employee_idSTARTWITHe.manager_idISNULLCONNECTBYPRIOR e.employee_ide.manager_id)SELECTeh.employee_idAS工号,LPAD( ,(eh.org_level-1)*2)||eh.full_nameAS姓名层级缩进,j.job_titleAS职位,d.department_nameAS所属部门,NVL(eh.manager_name,无上级)AS直属上级,l.cityAS工作城市,TO_CHAR(eh.salary,L99,999)AS月薪,eh.org_levelAS层级FROMemp_hierarchy ehJOINjobs jONeh.job_idj.job_idLEFTJOINdepartments dONeh.department_idd.department_idLEFTJOINlocations lONd.location_idl.location_idORDERBYeh.employee_id;七、常见问题Q1JOIN时出现重复行怎么办-- 多对多关系或连接条件不唯一时可能出现重复行-- 解决方案1检查连接条件是否正确-- 解决方案2使用DISTINCT去重注意性能影响-- 解决方案3使用GROUP BY汇总-- 示例员工-角色多对多关系SELECTDISTINCTe.employee_id,e.last_nameFROMemployees eJOINemployee_roles erONe.employee_ider.employee_idWHEREer.role_nameADMIN;Q2NULL值在连接中的处理-- NULL NULL 结果是FALSE或UNKNOWN所以含NULL的连接条件不会匹配-- 查询manager_id为NULL的员工SELECTemployee_id,last_nameFROMemployeesWHEREmanager_idISNULL;-- 如果要连接包含NULL的列需要用NVL或COALESCE转换SELECTe.employee_id,e.last_name,NVL(e.department_id,0)ASdept_idFROMemployees eJOIN(SELECTdepartment_idFROMdepartmentsWHEREdepartment_id0)dONNVL(e.department_id,0)d.department_id;-- 示例实际很少这样用Q3传统语法与ANSI语法的性能差异两者在Oracle中的执行计划通常是相同的Oracle优化器会将它们转化为相同的内部执行步骤。优先使用ANSI语法因为可读性更好且是SQL标准。八、最佳实践始终使用表别名多表查询时所有列前加表别名避免歧义提升可读性优先ANSI语法新代码使用JOIN…ON语法避免传统的WHERE逗号连接注意外连接的方向仔细确认哪个表需要保留所有行连接列要有索引JOIN的ON条件列上应有索引否则大表连接会很慢避免笛卡尔积确保每对表之间都有明确的连接条件层级查询谨慎使用CONNECT BY在大数据量时性能较差可考虑应用层实现九、总结本文全面讲解了Oracle多表连接的技术体系传统语法WHERE条件连接、()外连接符号ANSI语法INNER/LEFT/RIGHT/FULL OUTER/CROSS JOIN自连接同表不同别名处理层级数据层级查询CONNECT BY/START WITH处理树形结构高级技巧多表连接、连接与分组结合下一篇将讲解子查询与集合操作UNION/INTERSECT/MINUS完成SQL查询技巧的系统性梳理。上一篇【第03篇】Oracle SQL分组统计与排序——GROUP BY、HAVING与ORDER BY深度解析下一篇【第05篇】Oracle子查询与集合操作——嵌套查询与结果合并全解析参考资料《Oracle 11g数据库管理员指南》— 刘宪军著Oracle官方文档SQL Language Reference - JOINOracle官方文档SQL Language Reference - Hierarchical Queries