MySQL：两表取交集、并集、差集_mysql 两个表的差集

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

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

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

SELECT id,name FROM t1
UNION ALL
SELECT id,name FROM t2
1
2
3

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

SELECT id,name FROM t1
UNION
SELECT id,name FROM t2
1
2
3

2.查询t1和t2的交集

SELECT * FROM t1
INNER JOIN t2 ON t1.id=t2.id
1
2

3.查询t1和t2的差集

t1结果集>t2结果集

SELECT t1.id AS t1_id,t2.id AS t2_id FROM t1  
LEFT JOIN t2 ON t1.id=t2.id
WHERE t2.id IS NULL;
1
2
3

t1结果集<t2结果集

SELECT t1.id AS t1_id,t2.id AS t2_id FROM 
(
    SELECT id FROM `data_make_certificate_apply` WHERE `deleted`=0 GROUP BY batch_no
) t1  
RIGHT JOIN
(
    SELECT id FROM `data_make_certificate_apply` WHERE `deleted`=0 GROUP BY batch_no,competition_item,saiqu
) t2 
ON t1.id=t2.id
WHERE t1.id IS NULL;
1
2
3
4
5
6
7
8
9
10