Oracle - UNION/UNION ALL 并集、INTERSECT 交集、MINUS 差集

测试表：RY

SELECT COUNT(*) FROM ry

 

UNION/UNION ALL 并集

SELECT mc FROM ry
UNION 
SELECT mc FROM ry

上面执行结果，数据条数是：80 条，原因：UNION 会过滤掉重复数据！

SELECT mc, COUNT(1) counts FROM ry GROUP BY mc HAVING COUNT(1) > 1

再对比下 UNION ALL，UNION ALL 不会过滤重复数据

SELECT r.mc FROM ry r
UNION ALL
SELECT r.mc FROM ry r

执行后数据条数是：162 条，说明没有将重复记录过滤掉。

 

SELECT rowid, r.mc FROM ry r WHERE rownum <= 10

SELECT rowid, r.mc FROM ry r WHERE rownum <= 5

 

INTERSECT 交集

SELECT rowid, r.mc FROM ry r WHERE rownum <= 10
INTERSECT  
SELECT rowid, r.mc FROM ry r WHERE rownum <= 5

 

MINUS 差集

SELECT rowid, r.mc FROM ry r WHERE rownum<= 10
MINUS  
SELECT rowid, r.mc FROM ry r WHERE rownum <= 5

SELECT * FROM A MINUS SELECT * FROM B
    A表中有，B表中没有
SELECT * FROM B MINUS SELECT * FROM A
    B表中有，A表中没有