当前位置：首页 > news >正文

【Oracle数据库指南】第04篇：Oracle多表查询与连接操作——JOIN的全面解析

news 2026/5/11 0:23:06

上一篇【第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_id=d.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_id=d.department_idANDd.location_id=l.location_idANDe.job_id=j.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_id=m.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_id=d.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_id=d.department_id;-- INNER可以省略，直接写JOIN也表示内连接-- 多表内连接SELECTe.employee_id,e.last_name,e.salary,d.department_name,l.city,c.country_nameFROMemployees eJOINdepartments dONe.department_id=d.department_idJOINlocations lONd.location_id=l.location_idJOINcountries cONl.country_id=c.country_idWHEREe.salary>10000ORDERBYe.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_id=d.department_idORDERBYe.employee_id;-- 找出没有分配部门的员工SELECTe.employee_id,e.first_name,e.last_nameFROMemployees eLEFTJOINdepartments dONe.department_id=d.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_id=d.department_idGROUPBYd.department_id,d.department_nameORDERBYemp_countDESC;-- 找出没有员工的空部门SELECTd.department_id,d.department_nameFROMemployees eRIGHTJOINdepartments dONe.department_id=d.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_id=d.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_id=m.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_id=m.employee_idWHEREe.salary>m.salary;

四、高级连接技巧

4.1 多条件连接

-- ON子句可以包含多个条件SELECTe.employee_id,e.last_name,d.department_name,d.location_idFROMemployees eJOINdepartments dONe.department_id=d.department_idANDd.location_id=1700-- 附加筛选条件（注意与WHERE的区别）ORDERBYe.employee_id;-- ON和WHERE的区别：-- ON中的非连接条件会影响连接方式（外连接时尤为重要）-- WHERE在连接之后过滤-- 对比：以下两个查询结果不同！-- 写法1：条件在ON中SELECTe.last_name,d.department_nameFROMemployees eLEFTJOINdepartments dONe.department_id=d.department_idANDd.location_id=1700;-- 不满足的部门行显示NULL（保留员工行）-- 写法2：条件在WHERE中SELECTe.last_name,d.department_nameFROMemployees eLEFTJOINdepartments dONe.department_id=d.department_idWHEREd.location_id=1700;-- 直接过滤，不匹配的行被删除（变成内连接效果）

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.salary<5000THEN'E级'WHENe.salary<8000THEN'D级'WHENe.salary<12000THEN'C级'WHENe.salary<17000THEN'B级'ELSE'A级'ENDAS"薪资等级"FROMemployees eJOINjobs jONe.job_id=j.job_idJOINdepartments dONe.department_id=d.department_idJOINlocations lONd.location_id=l.location_idJOINcountries cONl.country_id=c.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_id=l.country_idLEFTJOINdepartments dONl.location_id=d.location_idLEFTJOINemployees eONd.department_id=e.department_idGROUPBYc.country_nameHAVINGCOUNT(e.employee_id)>0ORDERBYCOUNT(e.employee_id)DESC;

五、层级查询（CONNECT BY）

Oracle提供了强大的层级查询语法，专门用于处理树形结构数据。

-- 查询组织结构树（从CEO开始，展示全部层级）SELECTLEVELASdept_level,LPAD(' ',(LEVEL-1)*3)||employee_idAS"层级缩进",employee_id,first_name||' '||last_nameASfull_name,manager_id,salaryFROMemployeesSTARTWITHmanager_idISNULL-- 从根节点开始（没有上级的人是CEO）CONNECTBYPRIOR employee_id=manager_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_id=manager_id;-- 只查询某人下属的所有员工（包括间接下属）SELECTemployee_id,last_name,salary,LEVELFROMemployeesSTARTWITHemployee_id=101-- 从员工101开始CONNECTBYPRIOR employee_id=manager_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_id=m.employee_idSTARTWITHe.manager_idISNULLCONNECTBYPRIOR e.employee_id=e.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_id=j.job_idLEFTJOINdepartments dONeh.department_id=d.department_idLEFTJOINlocations lONd.location_id=l.location_idORDERBYeh.employee_id;

七、常见问题

Q1：JOIN时出现重复行怎么办？

-- 多对多关系或连接条件不唯一时可能出现重复行-- 解决方案1：检查连接条件是否正确-- 解决方案2：使用DISTINCT去重（注意性能影响）-- 解决方案3：使用GROUP BY汇总-- 示例：员工-角色多对多关系SELECTDISTINCTe.employee_id,e.last_nameFROMemployees eJOINemployee_roles erONe.employee_id=er.employee_idWHEREer.role_name='ADMIN';

Q2：NULL值在连接中的处理

-- 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_id=0)dONNVL(e.department_id,0)=d.department_id;-- 示例，实际很少这样用