oracle中union和union all的区别和使用

步骤二：通过实验来看看，首先创建数据drop table student2;create table student2(     id int primary key,     name varchar2(50) not null,     age number not null);insert into student2 values(1,'张三',23);insert into student2 values(2,'李四',21);insert into student2 values(3,'王二',20);insert into student2 values(4,'郭经',20);insert into student2 values(5,'王伟',23);insert into student2 values(6,'Frado',24);insert into student2 values(7,'伍梅',23);insert into student2 values(8,'张伦',23);insert into student2 values(9,'郭飞',21);insert into student2 values(10,'刘鹏飞',22); commit;

步骤三：区别1-- unionselect * from student2 where id < 4unionselect * from student2 where id > 2 and id < 6看到结果中ID=3的只有一条-- union all select * from student2 where id < 4union allselect * from student2 where id > 2 and id < 6结果中ID=3的结果有两个union和union all的区别在于union all取结果的交集之后不会进行去重

步骤四：区别二-- union allselect * from student2 where id > 2 and id < 6union allselect * from student2 where id < 4-- unionselect * from student2 where id > 2 and id < 6unionselect * from student2 where id < 4从结果中可以看到union还对获取的结果进行排序操作

步骤五：总结：union all只是合并查询结果，并不会进行去重和排序操作，在没有去重的前提下，使用union all的执行效率要比union高