UNION 由每个查询选择的所有不重复的行 并集不包含重复值,默认按第1个查询的第1列升序排列
UNION ALL 由每个查询选择的所有的行,包括所有重复的行 包括所有重复的行 完全并集包含重复值,不排序
INTERSECT 由每个查询选择的所有不重复的相交行 交集,不包含重复行,按第1个查询的第1列升序排列
MINUS 在第一个查询中,不在后面查询中的行 不包含重复行,按第1个查询的第1列升序排列
Union all 效率一般比union高。Union all内部不做排序工作,也不做剔除重复行工作,而union则做这个工作。
测试数据:
一。union和union all
1.UNION运算返回所有由任一查询选择的行。用UNION运算从多表返回所有行,但除去任何重复的行。
原则 :
a.被选择的列数和列的数据类型必须是与所有用在查询中的SELECT语句一致。列的名字不必相同。
b.联合运算在所有被选择的列上进行。
c.在做重复检查的时候不忽略空(NULL)值。(如果有重复空行,则保留一个空行).
d.IN运算有比UNION运算高的优先级。
e.在默认情况下,输出以SELECT子句的第一列的升序排序。
2.UNION ALL用全联合运算从多个查询中返回所有行。
原则:和联合不同,重复的行不被过滤,并且默认情况下输出不排序。
3.使用:
二。intersect交集操作
相交运算 用相交运算返回多个查询中所有的公共行。 无重复行。
原则
À 在查询中被 SELECT 语句选择的列数和数据类型必须与在查询中所使用的所有的 SELTCT 语句中的一样,但列的名字不必一样。
À 相交的表的倒序排序不改变结果。
À 相交不忽略null值。
使用:
三。minus差集操作
相减运算,求差集。无重复行。用相减运算返回由第一个查询返回的行,那些行不出现在第二个查询中 (第一个SELECT语句减第二个SELECT语句)。
原则
À 在查询中被SELECT语句选择的列数和数据类型必须与在查询中所使用的所有的SELTCT语句中的一样,但列的名字不必一样。
À 对于MINUS运算,在WHERE子句中所有的列都必须在SELECT子句中。
使用:
总结:
集合操作是对多个查询结果进行并集,差集,交集的操作,都不忽略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,如下: