将SQL表与自身进行比较（自连接）

Question

我正在尝试基于混合列查找重复行。这是我所拥有的一个例子：

CREATE TABLE Test
(
   id INT PRIMARY KEY,
   test1 varchar(124),
   test2 varchar(124)
)

INSERT INTO TEST ( id, test1, test2 ) VALUES ( 1, 'A', 'B' )
INSERT INTO TEST ( id, test1, test2 ) VALUES ( 2, 'B', 'C' )

现在，如果我运行这个查询：

SELECT [LEFT].[ID] 
FROM [TEST] AS [LEFT] 
   INNER JOIN [TEST] AS [RIGHT] 
   ON [LEFT].[ID] != [RIGHT].[ID] 
WHERE [LEFT].[TEST1] = [RIGHT].[TEST2]

我希望能够得到两个id（1和2），但我只得到了一行的结果。我的想法是应该比较每一行，但这可能是不正确的吗？为了解决这个问题，我已经改变了我的查询语句为：

SELECT [LEFT].[ID] 
FROM [TEST] AS [LEFT] 
   INNER JOIN [TEST] AS [RIGHT] 
   ON [LEFT].[ID] != [RIGHT].[ID] 
WHERE [LEFT].[TEST1] = [RIGHT].[TEST2] 
OR [LEFT].[TEST2] = [RIGHT].[TEST1]

这个查询可以返回所有行，但是性能会随着数据行数的增加而急剧下降。

我想到了一个既能保证性能又能返回正确结果的解决方案，那就是使用union操作符：

SELECT [LEFT].[ID] 
FROM [TEST] AS [LEFT] 
   INNER JOIN [TEST] AS [RIGHT] 
   ON [LEFT].[ID] != [RIGHT].[ID] 
WHERE [LEFT].[TEST1] = [RIGHT].[TEST2] 
UNION
SELECT [LEFT].[ID] 
FROM [TEST] AS [LEFT] 
   INNER JOIN [TEST] AS [RIGHT] 
   ON [LEFT].[ID] != [RIGHT].[ID] 
WHERE [LEFT].[TEST2] = [RIGHT].[TEST1]

总的来说，显然我缺乏对于为什么这不起作用的理解，这意味着我可能做错了什么。请问有人能指点我正确的方向吗？

- Kyle

---

使用以下数据尝试第一个查询： INSERT INTO TEST ( id, test1, test2 ) VALUES ( 1, 'C', 'B' ) INSERT INTO TEST ( id, test1, test2 ) VALUES ( 2, 'B', 'C' )这应该会给你两行数据。 - Chris Cudmore

Answer 1

不要在不等式上使用JOIN，看起来JOIN和WHERE条件是颠倒的。

SELECT t1.id
FROM Test t1
INNER JOIN Test t2
ON ((t1.test1 = t2.test2) OR (t1.test2 = t2.test1))
WHERE t1.id <> t2.id

应该可以正常工作。

- Aaronaught

---

你好， 从一些测试来看，这仍然比使用联合慢 :( 为什么永远不要在不等式上进行连接？难道where语句不一样吗？（尽管可能您的连接返回的行数比其他连接少，从而可能加快查询速度。这是原因吗？） - Kyle

在我的测试中，UNION版本需要超过3倍的时间。你是如何进行测试的？不使用不等式JOIN的原因是优化器必须读取满足该条件（即几乎所有）的每一行，并在之后进行过滤；而这个版本可以利用列test1或test2或两者的索引。除非优化器以某种方式重写了您的查询，否则如果您使用具有适当索引的此版本，则应看到巨大的性能提升。 - Aaronaught

实际上，我想了一下，由于您的模式似乎没有有用的索引，我发布的查询将执行与不等连接查询相同的操作；无论您做什么，最终都会得到两个完整的聚集索引扫描，这是非常糟糕的。您需要在（test1、test2）和（test2、test1）上建立覆盖索引才能获得更好的性能。 - Aaronaught

Answer 2

只有在选择它们时，您才能获取两个ID：

SELECT [LEFT].[ID], [RIGHT].[ID] 
FROM [TEST] AS [LEFT] 
   INNER JOIN [TEST] AS [RIGHT] 
   ON [LEFT].[ID] != [RIGHT].[ID] 
WHERE [LEFT].[TEST1] = [RIGHT].[TEST2]

只获得了一行的原因是只有一行（即第2行）的TEST1等于另一行的TEST2。

- Klaus Byskov Pedersen

---

+1 是因为你解释了原始语法为什么不起作用。而且你的答案是有效的。"这个答案很有用"。 - Ian Boyd

Answer 3

看起来你正在快速地朝着一个笛卡尔积的方向前进。通常，如果你想返回重复的结果，你需要运行类似以下的代码：

SELECT [LEFT].*
FROM [TEST]  AS [LEFT]
INNER JOIN [TEST] AS [RIGHT]
    ON [LEFT].[test1] = [RIGHT].[test1]
        AND [LEFT].[test2] = [RIGHT].[test2]
        AND [LEFT].[id] <> [RIGHT].[id]

如果您需要混合列，则混合所需条件，但请执行以下操作： ```html

如果您需要混合列，则混合所需条件，但请执行以下操作：

```

SELECT [LEFT].*
FROM [TEST] AS [LEFT]
INNER JOIN [TEST] AS [RIGHT]
    ON (
        [LEFT].[test1] = [RIGHT].[test2]
            OR [LEFT].[test2] = [RIGHT].[test1]
       )
        AND [LEFT].[id] <> [RIGHT].[id]

使用这种方法，您可以在每个连接中将右侧与左侧以及左侧与右侧进行比较，从而无需使用WHERE条件。

然而，由于您需要将每一行与每一行进行比较，因此这种查询方式的执行时间会随着表中插入的每一行而呈指数增长。

- Nathan Wheeler

Answer 4

如果我没记错的话，这可以在没有内部连接的情况下完成。 这是我第一次回答 MySQL 相关的问题，但我只是为了在 StackOverflow 上获取更多积分而回答。 逗号非常重要，这样 MySQL 就不会抱怨了。

SELECT [LEFT].[ID] FROM [TEST] AS [LEFT], [TEST] AS [RIGHT] 
WHERE [LEFT].[ID] != [RIGHT].[ID] 
AND [LEFT].[TEST1] = [RIGHT].[TEST2];

- lucky85dog