Geek Answers Handbook

Finding the intersection between two columns

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

First I tried:

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

which was syntactically valid, but the runtime was too long. The number of rows in the table is ~ 300,000, and two columns are not indexed . I assume the runtime is n ^ 2 or n ^ 3 depending on whether MySQL executes the subquery again for each table element or temporarily saves the result of the subquery.

Next, I thought about joining two columns and deleting individual elements, because if an element appears in this join more than once, it should be present in both columns (provided that both columns contain only separate elements).

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

+5
source share
2 answers
SELECT t1.*
    FROM table t1
        INNER JOIN table t2
            ON t1.col1 = t2.col2

Creating indexes in col1and col2will be very long to help with this query.

+11
source

If you only need values, try the command INTERSECT:

(SELECT col1 FROM table) INTERSECT (SELECT col2 FROM table)
-1
source

More articles:


All Articles