Oracle中的并集、差集和交集的例句。
一、并集UNION (ALL)
1.UNION ALL不去重
A集合和B集合相加的数据集合，包括重复数据
WITH A AS (SELECT 1 ID,'赵大' NM FROM DUAL UNION ALL SELECT 2 ID,'钱二' NM FROM DUAL UNION ALL SELECT 3 ID,'孙三' NM FROM DUAL UNION ALL SELECT 4 ID,'李四' NM FROM DUAL)
,B AS (SELECT 1 ID,'赵大' NM FROM DUAL UNION ALL SELECT 3 ID,'孙三' NM FROM DUAL UNION ALL SELECT 5 ID,'周五' NM FROM DUAL UNION ALL SELECT 6 ID,'吴六' NM FROM DUAL)
SELECT ID,NM FROM A UNION ALL SELECT ID,NM FROM B;/*UNION ALL不去重*/
2.UNION去重
A集合和B集合相加的数据集合，排除重复数据
WITH A AS (SELECT 1 ID,'赵大' NM FROM DUAL UNION ALL SELECT 2 ID,'钱二' NM FROM DUAL UNION ALL SELECT 3 ID,'孙三' NM FROM DUAL UNION ALL SELECT 4 ID,'李四' NM FROM DUAL)
,B AS (SELECT 1 ID,'赵大' NM FROM DUAL UNION ALL SELECT 3 ID,'孙三' NM FROM DUAL UNION ALL SELECT 5 ID,'周五' NM FROM DUAL UNION ALL SELECT 6 ID,'吴六' NM FROM DUAL)
SELECT ID,NM FROM A UNION SELECT ID,NM FROM B;/*UNION去重*/
二、差集MINUS
A集合比B集合多出的数据集合，例句如下：
WITH A AS (SELECT 1 ID,'赵大' NM FROM DUAL UNION ALL SELECT 2 ID,'钱二' NM FROM DUAL UNION ALL SELECT 3 ID,'孙三' NM FROM DUAL UNION ALL SELECT 4 ID,'李四' NM FROM DUAL)
,B AS (SELECT 1 ID,'赵大' NM FROM DUAL UNION ALL SELECT 3 ID,'孙三' NM FROM DUAL UNION ALL SELECT 5 ID,'周五' NM FROM DUAL UNION ALL SELECT 6 ID,'吴六' NM FROM DUAL)
SELECT ID,NM FROM A MINUS SELECT ID,NM FROM B;
三、交集INTERSECT
A和B两个集合重复的数据集合，例句如下：
WITH A AS (SELECT 1 ID,'赵大' NM FROM DUAL UNION ALL SELECT 2 ID,'钱二' NM FROM DUAL UNION ALL SELECT 3 ID,'孙三' NM FROM DUAL UNION ALL SELECT 4 ID,'李四' NM FROM DUAL)
,B AS (SELECT 1 ID,'赵大' NM FROM DUAL UNION ALL SELECT 3 ID,'孙三' NM FROM DUAL UNION ALL SELECT 5 ID,'周五' NM FROM DUAL UNION ALL SELECT 6 ID,'吴六' NM FROM DUAL)
SELECT ID,NM FROM A INTERSECT SELECT ID,NM FROM B;
