MINUS(差集)、INTERSECT(交集)和UNION (ALL)(并集)

最新推荐文章于 2022-07-07 20:21:41 发布

lceBear

最新推荐文章于 2022-07-07 20:21:41 发布

阅读量237

点赞数

原文链接：http://www.cnblogs.com/veygoux/p/4181503.html

版权

MINUS(差集)

代码：

WITH A AS (
SELECT '1' AS num FROM dual UNION
SELECT '2' AS num FROM dual UNION
SELECT '3' AS num FROM dual UNION
SELECT '4' AS num FROM dual UNION
SELECT '5' AS num FROM dual UNION
SELECT '6' AS num FROM dual UNION
SELECT '7' AS num FROM dual UNION
SELECT '8' AS num FROM dual UNION
SELECT '9' AS num FROM dual ), B AS (
SELECT '6' AS num FROM dual UNION
SELECT '7' AS num FROM dual UNION
SELECT '8' AS num FROM dual UNION
SELECT '9' AS num FROM dual UNION
SELECT '10' AS num FROM dual UNION
SELECT '11' AS num FROM dual UNION
SELECT '12' AS num FROM dual UNION
SELECT '13' AS num FROM dual UNION
SELECT '14' AS num FROM dual UNION
SELECT '15' AS num FROM dual UNION
SELECT '16' AS num FROM dual)
SELECT num FROM A
MINUS
SELECT num FROM B;

运行结果如下图所示：

INTERSECT(交集)

代码：

WITH A AS (
SELECT '1' AS num FROM dual UNION
SELECT '2' AS num FROM dual UNION
SELECT '3' AS num FROM dual UNION
SELECT '4' AS num FROM dual UNION
SELECT '5' AS num FROM dual UNION
SELECT '6' AS num FROM dual UNION
SELECT '7' AS num FROM dual UNION
SELECT '8' AS num FROM dual UNION
SELECT '9' AS num FROM dual ), B AS (
SELECT '6' AS num FROM dual UNION
SELECT '7' AS num FROM dual UNION
SELECT '8' AS num FROM dual UNION
SELECT '9' AS num FROM dual UNION
SELECT '10' AS num FROM dual UNION
SELECT '11' AS num FROM dual UNION
SELECT '12' AS num FROM dual UNION
SELECT '13' AS num FROM dual UNION
SELECT '14' AS num FROM dual UNION
SELECT '15' AS num FROM dual UNION
SELECT '16' AS num FROM dual)
SELECT num FROM A
INTERSECT
SELECT num FROM B;

运行结果如下图所示：

UNION (ALL)(并集)------UNION

代码：

WITH A AS (
SELECT '1' AS num FROM dual UNION
SELECT '2' AS num FROM dual UNION
SELECT '3' AS num FROM dual UNION
SELECT '4' AS num FROM dual UNION
SELECT '5' AS num FROM dual UNION
SELECT '6' AS num FROM dual UNION
SELECT '7' AS num FROM dual UNION
SELECT '8' AS num FROM dual UNION
SELECT '9' AS num FROM dual ), B AS (
SELECT '6' AS num FROM dual UNION
SELECT '7' AS num FROM dual UNION
SELECT '8' AS num FROM dual UNION
SELECT '9' AS num FROM dual UNION
SELECT '10' AS num FROM dual UNION
SELECT '11' AS num FROM dual UNION
SELECT '12' AS num FROM dual UNION
SELECT '13' AS num FROM dual UNION
SELECT '14' AS num FROM dual UNION
SELECT '15' AS num FROM dual UNION
SELECT '16' AS num FROM dual)
SELECT num FROM A
UNION
SELECT num FROM B;

运行结果如下图所示：

UNION (ALL)(并集)------UNION ALL

代码：

WITH A AS (
SELECT '1' AS num FROM dual UNION
SELECT '2' AS num FROM dual UNION
SELECT '3' AS num FROM dual UNION
SELECT '4' AS num FROM dual UNION
SELECT '5' AS num FROM dual UNION
SELECT '6' AS num FROM dual UNION
SELECT '7' AS num FROM dual UNION
SELECT '8' AS num FROM dual UNION
SELECT '9' AS num FROM dual ), B AS (
SELECT '6' AS num FROM dual UNION
SELECT '7' AS num FROM dual UNION
SELECT '8' AS num FROM dual UNION
SELECT '9' AS num FROM dual UNION
SELECT '10' AS num FROM dual UNION
SELECT '11' AS num FROM dual UNION
SELECT '12' AS num FROM dual UNION
SELECT '13' AS num FROM dual UNION
SELECT '14' AS num FROM dual UNION
SELECT '15' AS num FROM dual UNION
SELECT '16' AS num FROM dual)
SELECT num FROM A
UNION ALL
SELECT num FROM B;

运行结果如下图所示：

PS：
应用两个集合MINUS，INTERSECT，UNION(ALL)时，是有严格要求的
№_01: 两个集合的字段必须明确(用*就不行会产生报错)；
№_02: 字段类型和顺序相同(名称可以不同)。如:集合A的字段1是NUMBER型，字段2是VARCHAR型，那么集合B的字段1也必须是NUMBER型，字段2也必须是VARCHAR型；
№_03: 不能排序。如果要对结果排序，可以在集合运算后，外面再套一个查询，然后排序。

转载于:https://www.cnblogs.com/veygoux/p/4181503.html