oracle多表查询员工和上级,oracle多表查询之经典面试题

一、笛卡尔积

概念

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尓积(Cartesian product)，又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。^1

简单点说就是：集合X的每个元素和集合B的每个元素进行两两组合，组合次数等于集合X元素数量 * 集合Y元素数量。

2. 从笛卡尔积中筛选出有效数据图解

select * from emp e, dept d where e.deptno = d.deptno;

复制代码

3. 一些注意事项

从上图可以看出，部门不相同的行都没有实际意义，单纯笛卡尔积的出现大多数情况没有意义。如果出现笛卡尔积或者小范围内出现，则说明sql语句很可能错了；

从上图可以看出，两张表先笛卡尔积在通过条件逐条排除，中间会产生很多无效数据，所以尽量在进行笛卡尔积前进行条件过滤；

二、四种连接方式简介^2

内连接(表A inner join 表B on 条件)

返回表A(左表)和表B(右表)相匹配的数据；

左外连接(表A left join 表B on 条件)

表A(左表)的记录全部表示出来,而表B(右表)只会显示符合条件的记录,表B(右表)记录不足的地方均为null；

右外连接(表A right join 表B on 条件)

和左外连接的结果刚好相反，是以表B(右表)为基础，显示表B(右表)的全部记录，在加上表A(左表)符合条件的记录，表A(左表)记录不足的地方均为null；

全连接(表A full join 表B on 条件)

返回表A(左表)和表B(右表)中的所有行；

三、案例详解

sql运行环境介绍

数据库：oracle数据库 10g

可视化开发工具：PL/SQL Developer

表格信息来源：oracle自带表emp表(员工表)和dept表(部门表)

员工表与部门表的关系：一对多的关系(一个员工属于一个部门，一个部门有多个员工)

两张表格内容

员工表：

部门表：

工资登记表：

案例

查询出每个员工的上级领导(查询内容：员工编号、员工姓名、员工部门编号、员工工资、领导编号、领导姓名、领导工资)

select

e1.empno 雇员编号, e1.ename 雇员姓名, e1.deptno 雇员部门编号, e1.sal 雇员月薪, e1.mgr 雇员领导编号, e2.ename 领导名称, e2.sal 领导工资

from

emp e1, emp e2

where

e1.mgr = e2.empno(+);

复制代码

查询结果: