本文在于弄懂join、inner join;left join、left outer join; right join、right outer join;full join、full outer join;scoss join在oracle中的区别。
1.选择oracle中自带的表,部门表:dept,员工表:emp
dept表的查询如下:
select * from dept;
emp表的查询如下:
select * from emp;
2.内连接: join 等价于inner join。在两张表进行查询时,只保留两张表中条件完全匹配的结果集。即满足on条件的记录才会返回。
--dept与emp的内连接,join 等价与 inner join, 下面两条语句查询结果相同 相当于交集
select * from dept inner join emp on dept.deptno=emp.deptno;
select * from dept join emp on dept.deptno=emp.deptno;
3. 左连接:left join 等价于left outer join,。外部连接两个表中的记录,并包含左表中的全部记录。如果左表的某记录在右表中没有匹配记录,则在相关联的结果集中右表的所有选择列表列均为空值。即使左表不符合ON条件,左表中的记录也全部显示出来,且结果集中该类记录的右表字段为null,即以左表为参考点。
--dept与emp的左连接
select * from dept left join emp on dept.deptno = emp.deptno;
select * from dept left outer join emp on dept.deptno = emp.deptno;
select * from dept , emp where dept.deptno = emp.deptno(+);
4. 右连接:right join 等价于right outer jooin。外部连接两个表中的记录,并包含右表中的全部记录。如果右表的某记录在左表中没有匹配记录,则在相关联的结果集中左表的所有选择列表列均为空值。即使右表不符合ON条件,右表中的记录也全部显示出来,且结果集中该类记录的左表字段为null,即以右表为参考点。由此看出,左连接与右连接是反过来的。
--dept与emp的右连接
select * from dept right join emp on dept.deptno = emp.deptno;
select * from dept right outer join emp on dept.deptno = emp.deptno;
select * from dept, emp where dept.deptno(+) = emp.deptno;
5.全连接: full join 等价于 full outer join。完整外部联接返回左表和右表中的所有行。就是LEFT JOIN和RIGHT JOIN和合并,左右两表的数据都全部显示。
-- 全连接 并集
select* from dept full join emp on dept.deptno = emp.deptno;
select * from dept full outer join emp on dept.deptno = emp.deptno;
6.笛卡尔积 :cross join 例如:A={a,b},B={0,1,2},则:AxB = { (a,0), (a,1), (a,2), (b,0), (b,1), (b,2) }
select * from dept cross join emp ;
因此,如dept表(4行)与emp(14行)表进行笛卡尔积,最终的结果为4x14=56行。
7. union 与 union all的区别:
如果我们需要将两个select语句的结果作为一个整体显示出来,我们就需要用到union或者union all关键字。union(或称为联合)的作用是将多个结果合并在一起显示出来。
union可以对字段名不同但数据类型相同的结果集进行合并;
union和union all,区别在于union从结果集中删除重复的行。如果使用union all 将包含所有行并且将不删除重复的行。
union,对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序;
union all,对两个结果集进行并集操作,包括重复行,不进行排序;
另外:
intersect,对两个结果集进行交集操作,不包括重复行,同时进行默认规则的排序;
minus,对两个结果集进行差操作,不包括重复行,同时进行默认规则的排序。
语句为:
select * from dept union select * from dept_p1;
select * from dept union all select * from dept_p1;
select * from dept intersect select * from dept_p1;
select * from dept minus select * from dept_p1;
附:这是一张图,能形象的表示各个连接在集合中的关系可视化: