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 行记录)