mysql 交集,并集(union),差集

 

 有两张表a和b.

并集:UNION ALL

SELECT name,age FROM test_a
UNION ALL
SELECT name,age FROM test_b

 可以发现只是单纯的把两个表数据合并在一起了,再用union试试

并集:UNION 

SELECT name,age FROM test_a
UNION 
SELECT name,age FROM test_b;

可以明显看到去重了,  union会自动去重

交集:INNER JOIN

SELECT a.name,a.age FROM test_a a INNER JOIN test_b b ON a.name=b.name AND a.age=b.age;

等价于下面

SELECT a.name,a.age FROM test_a a INNER JOIN test_b b USING(name,age);

 查询差集:在a表不在b表

SELECT a.name, a.age 
FROM
  test_a a 
  LEFT JOIN test_b b 
    ON a.name = b.name 
    AND a.age = b.age 
WHERE b.id IS NULL;