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 ;