最近工作接触到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;