高级sql学习——集合操作！！！

UNION           由每个查询选择的所有不重复的行                        并集不包含重复值，默认按第1个查询的第1列升序排列
UNION ALL    由每个查询选择的所有的行，包括所有重复的行      包括所有重复的行 完全并集包含重复值，不排序
INTERSECT    由每个查询选择的所有不重复的相交行                  交集，不包含重复行，按第1个查询的第1列升序排列
MINUS           在第一个查询中，不在后面查询中的行                  不包含重复行，按第1个查询的第1列升序排列

Union all 效率一般比union高。Union all内部不做排序工作，也不做剔除重复行工作，而union则做这个工作。

 

测试数据：

drop table T1;create table T1( V1 VARCHAR2(50));insert into T1 values('1');insert into T1 values('2');insert into T1 values('4');insert into T1 values('3');commit;drop table T2;create table T2( V2 VARCHAR2(50));insert into T2 values('2');insert into T2 values('3');insert into T2 values('5');insert into T2 values('4');commit;

 

select * from t1;V1--------------------------------------------------1243select * from t2;V2--------------------------------------------------2354

 

一。union和union all

1.UNION运算返回所有由任一查询选择的行。用UNION运算从多表返回所有行，但除去任何重复的行。

原则 :

a.被选择的列数和列的数据类型必须是与所有用在查询中的SELECT语句一致。列的名字不必相同。

b.联合运算在所有被选择的列上进行。

c.在做重复检查的时候不忽略空(NULL)值。(如果有重复空行，则保留一个空行).

d.IN运算有比UNION运算高的优先级。

e.在默认情况下，输出以SELECT子句的第一列的升序排序。

2.UNION ALL用全联合运算从多个查询中返回所有行。
原则:和联合不同，重复的行不被过滤，并且默认情况下输出不排序。

3.使用：

select * from t1 union select * from t2;V1--------------------------------------------------12345

 

select * from t1 union all select * from t2;V1--------------------------------------------------12432354

 

二。intersect交集操作

相交运算 用相交运算返回多个查询中所有的公共行。 无重复行。
原则
À 在查询中被 SELECT 语句选择的列数和数据类型必须与在查询中所使用的所有的 SELTCT 语句中的一样，但列的名字不必一样。
À 相交的表的倒序排序不改变结果。
À 相交不忽略null值。
使用：

select * from t1 intersect select * from t2;V1--------------------------------------------------234

 

三。minus差集操作

相减运算，求差集。无重复行。用相减运算返回由第一个查询返回的行，那些行不出现在第二个查询中 (第一个SELECT语句减第二个SELECT语句)。
原则
À 在查询中被SELECT语句选择的列数和数据类型必须与在查询中所使用的所有的SELTCT语句中的一样，但列的名字不必一样。
À 对于MINUS运算，在WHERE子句中所有的列都必须在SELECT子句中。
使用：

select * from t1 minus select * from t2;V1--------------------------------------------------1

 

总结：
集合操作是对多个查询结果进行并集，差集，交集的操作，都不忽略null行, 除了union all 默认不做排序和剔除重复行的操作外，union,minus,intersect都默认按第1个查询结果的第1列进行升序排列，并且不包含重复行。对集合运算的结果要排序，只能在最后一个select 后面排序，是对整个集合操作进行排序，而不是对最后一个select 进行排序，排序选择的列必须是第1 个select 中出现的列，如果有别名，则使用别名，不能选用没有查询出来的列(这和普通的order by不一样，普通的order by可以选用不在select中的列
排序)，当然可以按位置指定order by，比如order by 1 desc,就是按第1列降序。在业务要求允许的情况下，能使用union all 就不要使用union，因为unionall不做排序剔重操作，使用union all效率方面要好点。

 

四。集合操作实例

需求：有A表和B表如下

 

A表
--------------------------------
com_name com_id
大连公司 005
上海公司 008
北京公司 007

 

B表
---------------------------------
staff_no com_id
8732 005
8832 005
3243 007
2342 008
2211 005

 

查询每个公司的员工个数，结果如下
大连公司 3
上海公司 1
北京公司 1
员工总数 5

 

分析：对于总数只需求count即可，对于每个公司员工数目统计，需要连接查询，并且按照com_name分组，然后求count，如下：
select a.com_name, count(b.com_id) from a, b where a.com_id = b.com_id group by a.com_nameunion all --这里可以使用union all，和union效果一样，所有优选选择union allselect '员工总数', count(b.staff_no) from b;