ORACLE 集合交集、并集、差集操作

交集

WITH V_List1 AS (
SELECT 1 AS NUM FROM DUAL UNION ALL
SELECT 3 AS NUM FROM DUAL UNION ALL
SELECT 5 AS NUM FROM DUAL UNION ALL
SELECT 7 AS NUM FROM DUAL UNION ALL
SELECT 9 AS NUM FROM DUAL
)
, V_List2 AS (
SELECT 2 AS NUM FROM DUAL UNION ALL
SELECT 3 AS NUM FROM DUAL UNION ALL
SELECT 4 AS NUM FROM DUAL UNION ALL
SELECT 6 AS NUM FROM DUAL UNION ALL
SELECT 9 AS NUM FROM DUAL
)
--  交集 - V_List1 & V_List2 都存在的数据： 3 / 9
SELECT * FROM V_List1 INTERSECT
SELECT * FROM V_List2 ;

差集

WITH V_List1 AS (
SELECT 1 AS NUM FROM DUAL UNION ALL
SELECT 3 AS NUM FROM DUAL UNION ALL
SELECT 5 AS NUM FROM DUAL UNION ALL
SELECT 7 AS NUM FROM DUAL UNION ALL
SELECT 9 AS NUM FROM DUAL
)
, V_List2 AS (
SELECT 2 AS NUM FROM DUAL UNION ALL
SELECT 3 AS NUM FROM DUAL UNION ALL
SELECT 4 AS NUM FROM DUAL UNION ALL
SELECT 6 AS NUM FROM DUAL UNION ALL
SELECT 9 AS NUM FROM DUAL
)
--  差集 - V_List1 有 V_List2 没有的数据： 1 / 5 / 7
SELECT * FROM V_List1 MINUS
SELECT * FROM V_List2 ;

并集：去重

WITH V_List1 AS (
SELECT 1 AS NUM FROM DUAL UNION ALL
SELECT 3 AS NUM FROM DUAL UNION ALL
SELECT 5 AS NUM FROM DUAL UNION ALL
SELECT 7 AS NUM FROM DUAL UNION ALL
SELECT 9 AS NUM FROM DUAL
)
, V_List2 AS (
SELECT 2 AS NUM FROM DUAL UNION ALL
SELECT 3 AS NUM FROM DUAL UNION ALL
SELECT 4 AS NUM FROM DUAL UNION ALL
SELECT 6 AS NUM FROM DUAL UNION ALL
SELECT 9 AS NUM FROM DUAL
)
--  并集[去重]:数据量大会影响性能
SELECT * FROM V_List1 UNION
SELECT * FROM V_List2 ;

并集：不去重

WITH V_List1 AS (
SELECT 1 AS NUM FROM DUAL UNION ALL
SELECT 3 AS NUM FROM DUAL UNION ALL
SELECT 5 AS NUM FROM DUAL UNION ALL
SELECT 7 AS NUM FROM DUAL UNION ALL
SELECT 9 AS NUM FROM DUAL
)
, V_List2 AS (
SELECT 2 AS NUM FROM DUAL UNION ALL
SELECT 3 AS NUM FROM DUAL UNION ALL
SELECT 4 AS NUM FROM DUAL UNION ALL
SELECT 6 AS NUM FROM DUAL UNION ALL
SELECT 9 AS NUM FROM DUAL
)
--  并集[不去重]
SELECT * FROM V_List1 UNION ALL
SELECT * FROM V_List2 ;