13、SQL--SET运算符（含习题）

前情概要：

 

 

 

 

一、UNION操作符：

 

 

 

 

1、创建两个表employees01和employees02，二者中有相同的数据（80号部门）。

（1）、表employees01：

create table employees01

as

select * from employees

where department_id in (70,80)

 

（2）、表employees02：

create table employees02

as

select * from employees

where department_id in (80,90)

 

2、UNION（把employees01和employees02中重复的80号部门只算了一次）：

select employee_id,department_id

from employees01

union

select employee_id,department_id

from employees02

 

3、UNION ALL：

（1）、正确的写法：

select employee_id,department_id

from employees01

union all

select employee_id,department_id

from employees02

附：union上下表中两个select后的列项数和列的数据类型都要对应相同。

 

（2）、错误的写法：

select employee_id,department_id,last_name

from employees01

union all

select employee_id,department_id

from employees02

ORA-01789: 查询块具有不正确的结果列数

 

4、如果两个表用了set（UNION ALL）操作，如果列有别名，以最上边表的别名为别名。

 

5、两个表之后，按照employee_id降序排序：

select employee_id emp00,department_id dept00

from employees01

union

select employee_id,department_id dept01

from employees02

order by emp00 desc

  --默认是asc（升序排序）

 

 

二、INTERSECT（交集）、MINUS（差集）：

 

1、（intersect）两个表的交集，并按employee_id降序输出

select employee_id emp00,department_id dept00

from employees01

intersect

select employee_id,department_id dept01

from employees02

order by emp00 desc

 

2、

select employee_id emp00,department_id dept00

from employees01

minus

select employee_id,department_id dept01

from employees02

order by emp00 desc

 

 

3、按照第几列的升序或降序排：

（1）、按照第1列的降序排列输出：

select employee_id emp00,department_id dept00  --第1列  第2列

from employees01

intersect

select employee_id,department_id dept01

from employees02

order by 1 desc

 

（2）、按照第2列的降序排列输出：

select employee_id emp00,department_id dept00  --第1列  第2列

from employees01

union

select employee_id,department_id dept01

from employees02

order by 2 desc

 

 

4、万一SET运算符上、下数据个数或数据类型不匹配，需要匹配各SELECT语句：

 

 

（1）、

select employee_id emp00,department_id,to_char(null)

from employees01

union

select to_number(null),department_id,department_name

from departments

附：因为是两个不同的表，employees表中的to_char( )对应表departments中的department_name；departments表中的to_number对应employees表中的employee_id。

 

 

5、练习：（column a_dummy noprint与sql语句中的a_dummy列）

 

（1）、输出样式：

I want to

Study at

www.atguigu.com

select 'study at' as "MyDream"

from dual

union

select 'I want to'

from dual

union

select 'www.atguigu.com'

from dual

 

（2）、改进：

select 'study at' as "MyDream",2  --2代表列号

from dual

union

select 'I want to',1  --1代表列号

from dual

union

select 'www.atguigu.com',3

from dual

order by 2

 

结果：

MyDream                  2

--------------- ----------

I want to                1

study at                 2

www.atguigu.com          3

 

（3）、不显示后面的1、2、3（序列号）：

①、SQL> column a_dummy noprint;  --不让名为“a_dummy”的列打印。

②、再sql语句中定义名为“a_dummy”的列

select 'study at' as "MyDream",2 a_dummy

from dual

union

select 'I want to',1

from dual

union

select 'www.atguigu.com',3

from dual

order by 2

 

 

 

三、测验题一：

 

1、查询部门的部门号，其中不包括job_id是”ST_CLERK”的部门号

（1）、/*  子查询

select department_id

from departments

where department_id not in (

                           select distinct department_id

                           from employees

                           where job_id = 'ST_CLERK'

                           )

*/

 

（2）、

select department_id

from departments

minus

select department_id

from employees

where job_id = 'ST_CLERK'  --抠掉ST_CLERK的

 

2、查询10，50，20号部门的job_id，department_id并且department_id按10，50，20的顺序排列

1）、column a_dummy noprint;

 

2) 、

SELECT job_id,department_id,1 a_dummy

from employees

where department_id = 10

union

SELECT job_id,department_id,2

from employees

where department_id = 50

union

SELECT job_id,department_id,3

from employees

where department_id = 20

order by 3 asc  --按照第三列（自定义的序号）的从小到大的顺序排序并输出。

 

3、查询所有员工的last_name ,department_id 和department_name

select last_name,department_id,to_char(null)

from employees

union

select to_char(null),department_id,department_name

from departments