选择仅出现在两个表中的数据，最佳方法是什么？

Question

如果我有两个这样的表格：

CREATE TABLE #table1 (id INT, name VARCHAR(10))
INSERT INTO #table1 VALUES (1,'John')
INSERT INTO #table1 VALUES (2,'Alan')
INSERT INTO #table1 VALUES (3,'Dave')
INSERT INTO #table1 VALUES (4,'Fred')
CREATE TABLE #table2 (id INT, name VARCHAR(10))
INSERT INTO #table2 VALUES (1,'John')
INSERT INTO #table2 VALUES (3,'Dave')
INSERT INTO #table2 VALUES (5,'Steve')

“我想要查看仅出现在其中一个表格中的所有行，最好的方法是什么？”

“我所能想到的是要么：”

SELECT * from #table1 except SELECT * FROM #table2
UNION
SELECT * from #table2 except SELECT * FROM #table1

或者类似于以下内容：

SELECT id,MAX(name) as name  FROM
(
SELECT *,1 as count from #table1 UNION ALL
SELECT *,1 as count from #table2
) data 
group by id
HAVING SUM(count) =1

在这种情况下，它将返回Alan、Fred和Steve。

但是这些感觉非常笨重——有没有更有效的方法来处理这个问题？

- Dibstar

Answer 1

select coalesce(t1.id, t2.id)     id,
       coalesce(t1.name, t2.name) name
from   #table1 t1
       full outer join #table2 t2
         on t1.id = t2.id
where  t1.id is null
        or t2.id is null  

完全外连接（full outer join）保证了从连接两侧都有的记录，而不在两侧中的记录（即您要查找的记录）将在一侧或另一侧中具有NULL。这就是为什么我们要对NULL进行过滤。 COALESCE用于保证显示非NULL值。

最后，值得强调的是，重复项是通过ID来检测的。如果您希望还按名称进行检测，应将name添加到JOIN中。如果只想按名称进行检测，请仅按name连接。此解决方案（使用JOIN）使您具有灵活性。

顺便说一下，由于您提供了CREATE和INSERT代码，我实际上运行了它们，上面的代码是完全可工作的代码。

- Adriano Carneiro

---

感谢您的解释 - 在更大的表格/多列上，这是否会存在可扩展性问题？我的实际表格每个表格将有大约2-10百万行，每个表格最多有15个左右的列，我认为交叉连接会使事情变慢一些？ - Dibstar

如果在“join”字段上有索引，性能将比使用“EXCEPT”和“INTERSECT”要好得多。 - Adriano Carneiro

如果我要在多个列上进行连接，是否需要跨所有连接列建立覆盖索引，或者只建立许多单列索引就可以了？ - Dibstar

@Adrian：难怪。Join通常是在几列上完成的，而UNION等则检查所有列。 - Andriy M

嗯，我不是“索引专家”，但我认为你应该从所有连接字段的一个索引开始。既然你手头有数据，那么你有一个很好的老师：试错法。实验一下。在这里有很多学习机会！ - Adriano Carneiro

Answer 2

您可以使用 EXCEPT 和 INTERSECT 进行操作：

-- All rows
SELECT * FROM #table1 
UNION
SELECT * FROM #table2
EXCEPT -- except
(
  -- those in both tables
  SELECT * FROM #table1 
  INTERSECT
  SELECT * FROM #table2
)

不确定这是否比你的EXCEPT和UNION示例更好...

- Oded

Answer 3

select id, name
from
 (select *, count(*) over(partition by checksum(*)) as cc
  from (select *
        from #table1
        union all
        select *
        from #table2
       ) as T
 ) as T
where cc = 1

- Mikael Eriksson