找到两列之间的交集

I'm trying to find the (set) intersection between two columns in the same table in MySQL. I basically want to find the rows that have either a col1 element that is in the table's col2, or a col2 element that is in the table's col1.

我试图找到MySQL中同一个表中两列之间的（集合）交集。我基本上想要找到具有表格col2中的col1元素或表格col1中的col2元素的行。

Initially I tried:

最初我尝试过：

SELECT * FROM table WHERE col1 IN (SELECT col2 FROM table)

which was syntactically valid, however the run-time is far too high. The number of rows in the table is ~300,000 and the two columns in question are not indexed. I assume the run time is either n^2 or n^3 depending on whether MySQL executes the subquery again for each element of the table or if it stores the result of the subquery temporarily.

这在语法上是有效的，但运行时间太长了。表中的行数约为300,000，并且未对两个列进行索引。我假设运行时间是n ^ 2或n ^ 3，这取决于MySQL是否为表的每个元素再次执行子查询，或者它是否临时存储子查询的结果。

Next I thought of taking the union of the two columns and removing distinct elements, because if an element shows up more than once in this union then it must have been present in both columns (assuming both columns contain only distinct elements).

接下来我想到了两列的并集并删除了不同的元素，因为如果一个元素在这个联合中出现多次，那么它必须存在于两列中（假设两列只包含不同的元素）。

Is there a more elegant (i.e. faster) way to find the set intersection between two columns of the same table?

是否有更优雅（即更快）的方法来查找同一个表的两列之间的集合交集？

2 个解决方案

#1

SELECT t1.*
    FROM table t1
        INNER JOIN table t2
            ON t1.col1 = t2.col2

Creating indexes on col1 and col2 would go a long way to help this query as well.

在col1和col2上创建索引对于帮助此查询也有很长的路要走。

#2

-1

If you only want the values, try the INTERSECT command:

如果您只想要值，请尝试使用INTERSECT命令：

(SELECT col1 FROM table) INTERSECT (SELECT col2 FROM table)

#1

SELECT t1.*
    FROM table t1
        INNER JOIN table t2
            ON t1.col1 = t2.col2