使用的兩個(gè)表都非常簡(jiǎn)單，只為演示可以實(shí)現(xiàn)該功能。

table_a 和 table_b的結(jié)構(gòu)如下：

image

image

用Oracle實(shí)現(xiàn)兩個(gè)a、b兩個(gè)表的交集

SELECT a_id as c_id,a_name as c_name from table_a  
INTERSECT  
SELECT b_id as c_id,b_name as c_name from table_b  

用Mysql實(shí)現(xiàn)

SELECT c_id,c_name from (  
    SELECT a_id as c_id,a_name as c_name from table_a  
    UNION ALL  
    SELECT b_id as c_id,b_name as c_name from table_b  
)a   
GROUP BY c_id,c_name  
 HAVING count(*)>1  

輸出結(jié)果如下：

image.png

用Oracle實(shí)現(xiàn)兩個(gè)a、b兩個(gè)表的差集

SELECT a_id as c_id,a_name as c_name from table_a  
MINUS  
SELECT b_id as c_id,b_name as c_name from table_b  

用Mysql實(shí)現(xiàn)：

SELECT a.a_id,a.a_name FROM table_a a  
LEFT JOIN table_b b   
ON a.a_id = b.b_id  
WHERE a.a_name != b.b_name;  

輸出結(jié)果如下：

image.png