MySQL：两表取交集、并集、差集

MySQL：两表取交集、并集、差集

1.t1和t2的并集，使用UNION (ALL)，注意两表的字段要一致

返回所有（两表数据有重复也都返回）

SELECT id,name FROM t1
UNIONALLSELECT id,name FROM t2

返回去重后的（两表数据有重复时只返回一条）

SELECT id,name FROM t1
UNIONSELECT id,name FROM t2

2.查询t1和t2的交集

SELECT*FROM t1
INNERJOIN t2 ON t1.id=t2.id

3.查询t1和t2的差集

t1结果集>t2结果集

SELECT t1.id AS t1_id,t2.id AS t2_id FROM t1  
LEFTJOIN t2 ON t1.id=t2.id
WHERE t2.id ISNULL;

t1结果集<t2结果集

SELECT t1.id AS t1_id,t2.id AS t2_id FROM(SELECT id FROM`data_make_certificate_apply`WHERE`deleted`=0GROUPBY batch_no
) t1  
RIGHTJOIN(SELECT id FROM`data_make_certificate_apply`WHERE`deleted`=0GROUPBY batch_no,competition_item,saiqu
) t2 
ON t1.id=t2.id
WHERE t1.id ISNULL;