sql集合.联合查询

1.集合

集合：每一个sql查询的结果就是一个集合 select * from emp;

并集：取两个集合中所有元素

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

union：取两个集合的并集，删除重复元素

--查询员工工资大于1000的员工信息

select * from emp where sal>1000;

--查询员工工资小于3000 的员工信息

select * from emp where sal<3000;

--取并集，工资大于1000 的员工和工资小于3000 的员工都要

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

union all 比 union 执行效率高union 需要合并之后再去掉重复元素

交集

交集：取两个元素的重叠部分

intersect 表示交集关键字

select * from emp where sal>1000
intersect
select * from emp where sal<3000;
相当于
select * from emp where sal>1000 and sal<3000;

差集

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

关键词：minus

select * from emp where sal>1000
minus 
select * from emp where sal<3000;
--所有工资大于1000的员工信息减去工资为1000-3000 的员工信息，也就是表示
查询工资大于3000的员工信息

2.联合查询

联合查询包括  交叉连接(笛卡尔积连接)，内连接，自连接，外连接（全连接，左外连接，右外连接），自然连接

(1).交叉连接

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

--将emp表跟dept表连接

select e.*,d.* from emp e cross join dept d;
或
select e.*,d.* from emp e,dept d;

(2）.内连接

内连接：把两个表中符合条件的数据连接为一条数据，如果哪个表中存在不符合连接条件的数据，那么这些数据就会被过滤掉（不显示）

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

inner join:中的inner可以省略不写

--查询员工信息及他的部门信息

二种方式
select e.*,d.* from emp e join dept d on e.deptno=d.deptno;
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,empe2 where e1.mgr=e2.empno;

(4)不等连接

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

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;

(5)全连接

全连接：他会查出两个表中的所有数据

1.如果两个表中的数据都符合连接条件，那么他会和内连接一样，将符合条件的数据连接为一条

2.如果第一张表中的一条记录，在第二张表中没有找到和他对应的记录，那么它会显示第一张表的这条记录，同事对应的第二张表数据的位置会显示为空

3.同理，如果第二张表的一条记录在第一张表中没有找到符合连接条件的数据，那么同样会显示，在显示第一张表数据的位置显示为空

语法：

select 列名,列名.....列名 from 表 A full outer join 表 B on 连接条件;

full outer join :中的outer 可以省略

--查询所有部门和员工信息

select e.*,d.* from emp e full outer join dept d on e.deptno=d.deptno;

(6)左外连接

查出左表（left outer join 关键字前面的表）的所有数据，根据连接条件去右表中找出对应的数据

如果找到就显示出来，如果找不到就显示空

语法：select 列名，列名，....from 表A left  join表 B on 连接条件

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 e.deptno(+)=d.deptno;

--右链接

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

（7）右外连接

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

语法：

select 列名,列名,列名,...from 左表 right outer join 右表 on 连接条件

select d.*,e.* from dept d right join emp e on d.deptno=e.deptno;

（8）自然连接

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

语法：

select col_name,col_name,.. from table_a natural join table_b

   select col_name,...  from table_a natural join table_b   using(关系列)

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

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