Oracle minus求差用法学习

最近工作接触到oracle数据库，记录一下学习过程

MINUS

minus求差，A minus B，返回结果集A中存在但结果集B中不存在的值

要点：

• 结果集A与结果集B具有相同的列数
• 所查询的列具有相同的数据类型
• minus具有去重功能，相同的值仅保留一次

练习：

//建表

create table employee1(
e_code varchar(20),
e_name varchar(20),
en_date date,
primary key(e_code)
);

create table employee2(
e_code varchar(20),
e_name varchar(20),
en_date date,
primary key(e_code));

//插入数据，插入一些特殊值NULL

insert into employee1 values('001', 'James', to_date('12-19-2020', 'MM-DD-YYYY'));  
insert into employee1 values('002', 'Alex', to_date('12-19-2020', 'MM-DD-YYYY'));
insert into employee1 values('003', 'Nana', to_date('12-19-2020', 'MM-DD-YYYY'));
insert into employee1 values('004', 'Tim', to_date('12-19-2020', 'MM-DD-YYYY'));
insert into employee1 values('005', 'Joe', to_date('12-19-2020', 'MM-DD-YYYY'));
insert into employee1 values('006', 'John', to_date('12-19-2020', 'MM-DD-YYYY'));
insert into employee1 values('007', 'Chris', to_date('12-19-2020', 'MM-DD-YYYY'));
insert into employee1 values('008', 'Jimmy', to_date('12-19-2020', 'MM-DD-YYYY'));
insert into employee1 values('009', 'Marry', to_date('12-19-2020', 'MM-DD-YYYY'));
insert into employee1 values('010', 'Peter', to_date('12-19-2020', 'MM-DD-YYYY'));
insert into employee1 values('013', 'Helen', NULL);
insert into employee1 values('014', NULL, NULL);
insert into employee1 values('015', 'John', to_date('12-19-2020', 'MM-DD-YYYY'));

insert into employee2 values('003', 'Nana', to_date('12-19-2020', 'MM-DD-YYYY'));
insert into employee2 values('005', 'Joe', to_date('12-19-2020', 'MM-DD-YYYY'));
insert into employee2 values('011', 'Lora', to_date('01-09-2020', 'MM-DD-YYYY'));
insert into employee2 values('009', 'Marry', to_date('12-19-2020', 'MM-DD-YYYY'));
insert into employee2 values('012', 'Kiki', to_date('01-09-2021', 'MM-DD-YYYY'));
insert into employee2 values('014', NULL, NULL);

//查询employee1中存在employee2中不存在的值，NULL值照常显示

1）查询全部字段

select * from employee1 minus select * from employee2; 

​
//查询employee2中存在employee1中不存在的值

select * from employee2 minus select * from employee1; 

2) 去重功能，查询结果相同的仅保留一个

employee2中加条数据

insert into employee2 values('013', 'David', to_date('01-09-2021', 'MM-DD-YYYY'));

select en_date from employee1;

查询employee2中有的日期但是employee1中没有的，结果相同的仅保留一个

select en_date from employee2 minus select en_date from employee1;