sql的集合运算

使用sql进行集合运算

①交集
内连接

SELECT * FROM table_a
INNER JOIN table_b ON table_a.id = table_b.id;

②并集
全外连接

SELECT * FROM table_a
FULL OUTER JOIN table_b ON table_a.id = table_b.id;

有的DBMS不支持全外连接，比如MYSQL，可以使用union all来实现全外连接：

SELECT * FROM table_a
INNER JOIN table_b ON table_a.id = table_b.id
UNION ALL
SELECT * FROM table_a
LEFT OUTER JOIN table_b ON table_a.id = table_b.id
WHERE table_b.id IS NULL
UNION ALL
SELECT * FROM table_a
RIGHT OUTER JOIN table_b ON table_a.id = table_b.id
WHERE table_a.id IS NULL

③差集
左外连接、右外连接

SELECT * FROM table_a
RIGHT OUTER JOIN table_b ON table_a.id = table_b.id
WHERE table_a.id IS NULL;

④异或集
全外连接-内连接

SELECT * FROM table_a
FULL OUTER JOIN table_b ON table_a.id = table_b.id
where table_a.id IS NULL 
   OR table_b.id IS NULL;