mysql - SQL识别两张表不同数据

Question

有两张1W行左右的表，需要查询有差异的行，现在的代码如下：

SELECT number, version
FROM
 (
   SELECT a.number, b.version
   FROM a
   UNION ALL
   SELECT b.number, b.version
   FROM b
)  tb
GROUP BY number, version
HAVING COUNT(*) = 1
ORDER BY number

但是问题来了，以上代码只能查询出不同的行，但是没法显示a表中有的b表中没有的,b表中有的a表中没有的，有没有办法可以在第3列标识出来？

Answer 1

按楼主意思，单表中number和version是不会重复的，两张表的number和version建一个复合索引，然后执行以下sql

SELECT a.number, a.version,'from_a'
FROM a
where not exists (
SELECT 1 FROM b where a.number=b.number and a.version=b.version)
union all
SELECT b.number, b.version,'from_b'
FROM b
where not exists (
SELECT 1 FROM a where a.number=b.number and a.version=b.version)
ORDER BY number;
或者
SELECT a.number, a.version,'from_a'
from a left join b on a.number=b.number and a.version=b.version
where b.id is null
union all
SELECT b.number, b.version,'from_b'
from a right join b on a.number=b.number and a.version=b.version
where a.id is null
ORDER BY number;
下面这个效率可能会差点

Answer 2

试试full join ... where a is null or b is null。比如用Postgres：

select
  case when a.n is null then b.n else a.n end as n,
  case when a.n is null then b.v else a.v end as v,
  case when a.n is null then 'b' else 'a' end as src
from
  (values(1, 2), (2, 3), (3, 4)) as a(n, v)
  full join
  (values(6, 7), (2, 3), (3, 9)) as b(n, v)
  using (n, v)
where a.n is null or b.n is null

结果：

 n | v | src
---+---+-----
 1 | 2 | a
 3 | 4 | a
 3 | 9 | b
 6 | 7 | b
(4 行记录)