Oracle之多表查询

多表查询

1、集合

集合：每一个sql查询的结果都是一个集合，例(1,3,4,65,24,13,12)

• 并集：表示取两个集合中的所有元素
• union all：表示取两个集合的并集，不删除重复的元素
• union：表示取两个集合的并集，删除重复的元素

-- 查询员工工资大于1000的员工信息
select * from emp where sal>1000;

-- 查询员工工资小于3000的员工信息
select * from emp where sal<3000;

select * from emp where sal>1000 
union all
select * from emp where sal<3000;

select * from emp where sal>1000
union
select * from emp where sal<3000;

union、union all 执行效率高，union 执行效率低（union 是先进行union all 把两个集合合并，之后对合并后的数据进行排序，去掉重复元素）

• 交集：表示取两个集合重叠部分的元素
• intersect：表示交集

-- 查询工资在1000到3000之间的员工信息
select * from emp where sal>1000 and sal<3000;

select * from emp where sal>1000
intersect
select * from emp where sal<3000;

-- 列的不同会导致结果的不同
select sal from emp where sal>1000
intersect
select sal from emp where sal<3000

• 差集：第一个集合的所有元素减去两个集合重叠部分的元素
• minus：表示差集

select * from emp where sal>1000
minus
select * from emp where sal<3000;

select * from emp where sal>=3000;


select * from emp where sal<3000
minus
select * from emp where sal>1000;

select * from emp where sal<=1000;

注意：集合运算，union、union all、intersect、minus 关键字前后的连个 sql 表示的结果集（字段或列）要完全相同

select ename,job from emp
union all
select ename,job from emp;

2、联合查询

(7)select (8)distinct col_name,col_name,...
(1)from table1
(3)join table2
(2)on 连接条件
(4)where 条件表达式
(5)group by 分组字段
(6)having 条件表达式
(9)order by 排序字段
-- 查询语句运行顺序

• 交叉连接（笛卡尔连接）
• 内连接
• 自连接
• 外连接
  • 全连接
  • 左外连接
  • 右外连接
• 自然连接
• 不等连接

笛卡尔连接：笛卡尔连接是指在sql语句中没有写出表连接的条件或者表的连接条件不能约束两个表的连接，优化器把第一个表的每一条记录和第二个表的所有记录相连接。如果第一个表的记录数为m, 第二个表的记录数为n,则会产生m*n条记录数。

1.交叉连接（笛卡尔连接）

将会返回被连接的两个表的笛卡尔积。

语法：select 列名[,列名,...] from a表 cross join b表

2.内连接

是把两个表中符合条件的数据连接为一条数据，如果哪个表中存在不符合连接条件的数据，那么这些数据不会显示

语法：select 列名[,列名,...] from a表 [inner] join b表 on 连接条件

-- 查询员工信息及他的部门信息
select e.*,d.* from emp e inner join dept d on e.deptno=d.deptno;
select e.*,d.* from emp e join dept d on e.deptno=no;
select e.*,d.* from emp e,dept d where e.deptno=d.deptno;

3.自连接

自连接是一个特殊的内连接，内连接一般是两个表的连接，自连接将一个表和它自己进行连接

-- 查询员工信息，并显示它的上级姓名
select e1.*,e2.ename 上级姓名 from emp e1 join emp e2 on e1.mgr=e2.empno
select e1.*,e2.ename 上级姓名 from emp e1,emp e2 where e1.mgr=e2.empno;

4.全连接

它会查出两个表中的所有数据

• 如果两个表中的数据都符合连接条件，那么它会和内连接一样，将符合条件的数据连接为一条记录
• 如果第一表中的一条数据，在第二张表中没有找到和它对应的记录（第二张表没有和第一张表这条数据符合连接条件），那么它同样会显示第一张表的这条记录，同时它对应的第二张表数据的位置会显示为空
• 同理，如果第二张的一条记录，在第一张表中没有找到符合连接条件的数据，那么同样会显示，在显示第一张表数据的位置显示为空

语法：select 列名[,列名,...] from a表 full [outer] join b表 on 连接条件

-- 查询所有部门和员工信息
select e.*,d.* from emp e full outer join dept d on e.deptno=d.deptno;

5.左外连接

查出左表（left outer join 关键字左边的表）的所有数据，根据连接条件去右表中找对应的数据，如果找到就显示出来，如果找不到就显示空

语法：select 列名[,列名,...] from a表 left [outer] join b表 on 连接条件

-- 查询所有部门信息，以及各部门下的员工信息
select d.*, e.* from dept d left outer join emp e on d.deptno = e.deptno;
select d.*,e.* from dept d left join emp e on d.deptno=e.deptno;
select d.*,e.* from dept d,emp e where d.deptno=e.deptno(+);

注意：用（+）表示的左连接或者右连接，如果是左连接，那么符号加在右表的关系列上，如果是右连接，那么符号加在左表的关系列上，一般不建议这个使用

--左外连接
select d.*,e.* from dept d,emp e where d.deptno=e.deptno(+); 

--右外连接 
select d.*,e.* from dept d,emp e where d.deptno(+)=e.deptno;

6.右外连接

查出右表的所有数据，根据连接条件去左表查找对应的数据，如果找到就显示，如果找不到就显示为空

语法：select 列名[,列名,...] from a表 right [outer] join b表 on 连接条件

-- 查询员工信息，并显示它的部门信息
select d.*,e.* from dept d right outer join emp e on d.deptno=e.deptno;
select d.*,e.* from dept d,emp e where d.deptno(+) = e.deptno;

7.自然连接

自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件的结果

语法：select 列名[,列名,...] from a表 natural join b表，select 列名[,列名,...] from a表 join b表 using(关系列)

-- 自然连接
select * from emp natural join dept;

-- 自然连接using用来指定关系列
select * from emp join dept using(deptno); 

8.不等连接

连接条件是不等条件（大于、小于、不等于）

-- 查询员工信息，并显示员工的工资等级
select * from salgrade;

select e.*,s.grade,s.losal,s.hisal from emp e join salgrade s on e.sal between s.losal and s.hisal;