SQL UNION ALL用于消除重复项

Question

我在toptal上找到了这个样例面试问题和答案，现在我不太理解这段代码。为什么UNION ALL会变成UNION (distinct)呢？而且，为什么这段代码可以更快呢？

问题：

使用WHERE子句编写一个SQL查询，使用UNION ALL（而不是UNION），以消除重复项。为什么要这样做？

回答：

您可以使用UNION ALL避免重复，并且仍然比使用UNION DISTINCT（实际上与UNION相同）运行得更快，通过运行以下查询来消除重复项：

SELECT * FROM mytable WHERE a=X UNION ALL SELECT * FROM mytable WHERE b=Y AND a!=X

关键在于 AND a!=X 部分。这样做可以获得 UNION (也称为 UNION DISTINCT) 命令的优点，同时避免了大部分性能问题。

- user3685285

---

这个解释是误导性的...它本质上是在执行一个UNION ALL，但过滤掉了第一个查询的结果。它只是使用WHERE子句进行过滤，而不是使用昂贵的DISTINCT操作。 - Siyual

答案错误。这个查询没有消除表中已经存在的重复项，而使用UNION DISTINCT则会消除重复项。 - Paul Spiegel

@PaulSpiegel，如果存在一个不包含a的主键，则由于select *，不会出现重复项。 - Dan Bracuk

你能引用问题和答案的来源吗？你确定这是问题和答案的全部内容吗？ - DVT

https://www.toptal.com/sql/interview-questions 照原样引述。 - user3685285

Answer 1

但在这个例子中，第一个查询对列a有条件限制，而第二个查询对列b有条件限制。这可能来自于一个难以优化的查询：

SELECT * FROM mytable WHERE a=X OR b=Y

用简单的B树索引难以优化此查询。引擎是在列 a 上搜索索引？还是在列 b 上搜索索引？无论哪种情况，搜索其他术语都需要进行全表扫描。

因此，使用UNION的技巧将其分成两个查询，每个查询只对一个术语进行搜索。每个子查询可以使用最佳索引来搜索每个搜索术语，然后使用UNION组合结果。

但两个子集可能重叠，因为一些具有 b=Y 的行也可能具有 a=X，这种情况下这些行会同时出现在两个子集中。因此，您必须进行重复消除，否则最终结果中可能会看到某些行两次。

SELECT * FROM mytable WHERE a=X 
UNION DISTINCT
SELECT * FROM mytable WHERE b=Y

UNION DISTINCT操作代价高昂，因为典型的实现方式是对行进行排序以查找重复项，就像使用 SELECT DISTINCT ...一样。

我们还有一个感觉，如果你要union的两个子集中有很多行同时出现在这两个子集中，那么这将会更加“浪费“。因为需要消除大量的重复行。

但是，如果你能够保证这两组行已经不同，即不存在重叠部分，那么就没有必要消除重复项了。如果你能够依靠这一点，那么查询总是可以跳过消除重复项的步骤，也因此可以跳过昂贵的排序操作。

如果你修改查询以确保选择不重叠的行子集，那么就可以提高效率。

SELECT * FROM mytable WHERE a=X 
UNION ALL 
SELECT * FROM mytable WHERE b=Y AND a!=X

这两个集合保证没有重叠。如果第一个集合有行满足 a=X，而第二个集合有行满足 a!=X，那么两个集合之间就不会有相同的行。

因此，第二个查询仅能获取到一部分 b=Y 的行，但任何一个同时满足 a=X AND b=Y 的行都已经包含在第一个集合中了。

所以这个查询实现了对两个 OR 条件的优化搜索，避免了产生重复结果，并且不需要执行 UNION DISTINCT 操作。

- Bill Karwin

---

哇，这个答案对我来说非常有道理。但是为什么有些人说它是错误的？它在某些情况下失败了吗？ - user3685285

反对意见是它没有考虑到表本身存在重复行的情况（在规范化的数据库中永远不应该出现这种情况，但有时会发生）。使用UNION DISTINCT的查询将从结果集中消除重复项。UNION ALL保留这样的重复项。 - Bill Karwin

这个解释没有提到的是，是什么使得两个条件通过AND连接（WHERE b=Y AND a!=X）比原来通过OR连接的条件（WHERE a=X OR b=Y）更有效。如果我们不知道AND在某种程度上比OR更好，那么整个练习就是徒劳无功的，我们所做的只是让代码更加晦涩难懂。 - Agent Friday

@AgentFriday，哇，你看过我的回答吗？它实际上只谈到了优化策略及其工作原理。 - Bill Karwin

@Bill Karwin，是的，我刚刚又仔细阅读了一遍。最终解决方案中AND条件相对于原始OR条件有何优势没有提到。 - Agent Friday

Answer 2

我猜这是正确的（Oracle）：

select distinct * from (

select * from test_a

union all

select * from test_b
);      

- Sachin Jawale

Answer 3

最简单的方法是这样的，特别是当你有很多列时：

SELECT *
  INTO table2
  FROM table1
  UNION
SELECT *
  FROM table1
  ORDER BY column1

- Martin Schumann

---

嗨，感谢您的回答。这将创建table2，并不是问题的作者所问的。请尝试回答帖子中的“如何将UNION ALL转换为像UNION（distinct）那样的UNION？此外，为什么这段代码更快？” - Connor Low

Answer 4

这个也能达到同样的效果：

select * from (
select * from table1
union all 
select * from table2
) a group by 
columns
having count(*) >= 1

或者

select * from table1 
union all
select * from table2 b 
where not exists (select 1 from table1 a where a.col1 = b.col1)

- Layman

Answer 5

如果表具有唯一标识符 - 主键，则问题将是正确的。否则，每个选择都可能返回许多相同的行。

为了理解为什么它可以更快，让我们看看数据库如何执行UNION ALL和UNION。

第一个是简单地连接两个独立查询的结果。这些查询可以并行处理，并逐个传递给客户端。

第二个是连接+区分。为了从2个查询中区分记录，db需要将它们全部存储在内存中，或者如果内存不足，则需要将它们存储到临时表中，然后选择唯一的记录。这就是性能下降的地方。DB非常聪明，区分算法也很好，但对于大型结果集，仍可能存在问题。

使用UNION ALL +其他WHERE条件过滤时，如果使用索引，则可以更快。所以，这里就是性能的魔法。

- Vasyl Zvarydchuk

---

如果没有主键（PK），可以使用row_number()函数。 - undefined

Answer 6

我猜它会起作用

select col1 From (
select row_number() over (partition by col1 order by col1) as b, col1 
from (
select col1  From u1
union all
select col1 From u2 ) a
) x
where x.b =1

- Amit Dhiman