Oracle中复合查询(compound query)包括union,union all , minus,intersect,
union进行排序,默认以第一列升序排序,并且不包括重复记录。
union all则不会排序,并且不会进行distinct操作,所以速度较快。
以下两个查询的结果是相同的
select distinct * from (select id from a union all select id from b) order by 1;
select id from a union select id from b;
intersect选择两个查询的交集,调换两个查询顺序不影响查询结果;
minus选择第一个查询的结果,但不能包含第二个查询中的记录。
除了union all不会对结果进行排序和distinct,其他三个compound query都会对结果进行distinct和sort操作。
compound query中的子查询的列数和类型必须相同;
select 1,2 from dual union select 1, null from dual; --correct
select 1 first,2 second from dual union select 1 one, '' two from dual; --not correct
select 1 first,'2' second from dual union select 1 one, '' two from dual; --correct
order by clause can only appear at the very end of the statement.