Oracle集合查询详解加练习题_oracle 集合

#集合查询
概念：将不同的数据集合（SQL查询语句）按照集合的规则，拼接一个临时的，新的数据集合（表）

1.集合：并集、交集、差集
并集 union all
语法：select column1,COLUMN2…from table_name
UNION ALL
select column1,COLUMN2…from table_name
UNION ALL
select column1,COLUMN2…from table_name

UNION ALL 查询结果为合并结果，不去重，不排序
union 语法：
select column1,COLUMN2…from table_name UNION
union查询结果为合并结果，并去重，并默认根据第一行正序排序
2.交集
INTERSECT
语法：select column1,COLUMN2…from table_name
INTERSECT
select column1,COLUMN2…from table_name
INTERSECT
select column1,COLUMN2…from table_name
intrsect 查询结果为：获取两个结果中的相同部分，并去重，并默认第一行正序排序
3.差集
MINUS
语法：select column1,COLUMN2…from table_name
minus
select column1,COLUMN2…from table_name
minus
minusselect column1,COLUMN2…from table_name

minus 查询结果为：第一个结果减去第二个中相同部分，只获取结果中独有的数据，并去重，并默认根据第一列正序排序

—查询工资等于1250和等于3000的员工的编号，姓名，工资
select empno,ename,sal from emp where sal=1250 or sal=3000;
select empno,ename,sal from emp where sal in(1250,3000);
select empno,ename,sal from emp where sal= any(1250,3000);

select empno,ename,sal from emp where sal = 1250
union ALL
SELECT empno,ename,sal from emp where sal = 3000;

select empno,ename,sal from emp where sal = 1250
union
SELECT empno,ename,sal from emp where sal = 3000;

—查询工资大于1600的员工信息和工资小于3000的员工信息的同样部分
select * from emp where sal>1600 and sal<3000;

SELECT * from emp where sal>1600
INTERSECT
SELECT * from emp where sal<3000;

SELECT job from emp where sal>1600
INTERSECT
SELECT job from emp where sal<3000;

SELECT empno,job from emp where sal>1600
INTERSECT
SELECT empno,job from emp where sal<3000;

—查询工资大于1300的员工和工资小于3000的员工中查询小于1300的独有的信息

select * from EMP where sal>1300
MINUS
SELECT * from EMP where sal<3000;

select * from EMP where sal<3000
MINUS
select * from EMP where sal>1300

----查询集合结果的显示
select empno 员工编号,ename,job from emp where sal>1000
UNION
select empno,enme,job from emp where sal<1600;

—如果查询列数不一致——————不可以，结果列数不一致，报错
select empno ,ename,job，sal from emp where sal>1000
UNION
select empno,enme,job from emp where sal<1600;

—数据类型不一致，报错
select empno ,ename,job,sal from emp where sal>1000
intersect
select ename,empno,job,sal from emp where sal<3000;

select empno ,ename,job,sal from emp where sal>1000
MINUS
select sal,job,empno,empno from emp where sal<3000;
注意：
查询结果列名为第一个查询结果列名为准
2.查询结果列数必须一致
3.查询结果顺序要一直，数据类型要一致

重点：
1.UNION 和 union ALL union :排序去重，union all ：不排序 不去重
2.union/intersect/minus 均会按照第一个列升序去排序，去重
3.minus 用于查询集合中的独有数据
4.minus签后集合的顺序不同影响返回结果
5.集合操作时候注意：集合的列的数据类型和个数要一致

附加：
查询10号部门独有的工作和查询20号部门独有的工作并吧这些合并查询

#练习题
1.查询部门编号是10,20的员工信息
select * from EMP where deptno=10 or deptno=20;
select * from EMP where deptno in(10,20);
select * from emp where deptno=any(10,20);

select * from EMP where deptno=10
union all
select * from EMP where deptno=20;

2.查询员工信息，职位是CLERK或者SALESMAN或者ANALYST
select * from emp where job=‘CLERK’
UNION
SELECT *from EMP where job=‘SALESMAN’
union
SELECT *from EMP where job=‘ANALYST’
3.查询薪资是800 950 的员工信息
select * from emp where sal=800
union ALL
select * from emp where sal=950
4.查询上级编号7902、7698的员工信息
select * from EMP where empno=7902
union all
select * from EMP where empno=7698
5.员工工资不是 800和1600的员工信息
select * from emp where sal!=800
INTERSECT
select * from emp where sal!=1600

6.查询员工薪资为1000到3000的员工信息
select * from EMP where sal>1000
INTERSECT
select * from emp where sal<3000;

7.查询员工编号在7500到7700之间的员工
select * from emp where empno>7500
INTERSECT
select * from emp where empno<7700;

8.查询姓名是以A开头的、薪资在1000到1500之间的员工
select * from emp where ename like ‘A%’
INTERSECT
select * from EMP where sal>1000
INTERSECT
select * from emp where sal<1500;

9.查询薪资在1000-3000之间，并且部门编号是10或者20
select * from EMP where sal>1000
INTERSECT
select * from emp where sal<3000
INTERSECT
select * from EMP where deptno=10
UNION all
select * from EMP where deptno=20;

10.查询（部门编号是20并且工作岗位是CLERK的员工） ,或者薪资大于3000的员工
select * from EMP where deptno =20
INTERSECT
select * from EMP where job=‘CLERK’
union all
select * from emp where sal>3000

11.查询没有员工的部门编号
SELECT * from dept where deptno not in (select DISTINCT deptno from emp)

12.查询30号部门中有，10部门没有的岗位名称
select DISTINCT job from EMP where deptno=30
MINUS
select DISTINCT job from EMP where deptno=10

13查询10号部门独有的工作和查询20号部门独有的工作并把这些合并查询

select DISTINCT JOB from emp where deptno=10
MINUS
select DISTINCT JOB from emp where deptno=20
MINUS
select DISTINCT JOB from emp where deptno=30
union

select DISTINCT JOB from emp where deptno=20
MINUS
select DISTINCT JOB from emp where deptno=10
MINUS
select DISTINCT JOB from emp where deptno=30