[ACCEPTED]-Comparing SQL Table to itself (Self-join)-self-join

Accepted answer

Score: 11

Do not JOIN on an inequality; it seems that 2 the JOIN and WHERE conditions are inverted.

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

Should 1 work fine.

Score: 6

You only get back both id's if you select 3 them:

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

The reason that only get one ROW is 2 that only one row (namely row #2) has a 1 TEST1 that is equal to another row's TEST2.

Score: 2

I looks like you're working very quickly 11 toward a Cartiesian Join. Normally if you're looking to 10 return duplicates, you need to run something 9 like:

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]

If you need to mix the columns, then 8 mix the needed conditions, but do something 7 like:

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]

Using that, you compare the right to 6 the left and the left to the right in each 5 join, eliminating the need for the WHERE 4 altogether.

However, this style of query 3 grows exponentially in execution time for 2 each row inserted into the table, since 1 you're comparing each row to every row.

Score: 0

This can be done with out inner joins if 5 I am not mistaken. This my first time answering 4 mysql kind of question but I am just answering 3 to get more points here on StackOverflow. The 2 comma is very important so that mysql does 1 not complain.

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

Source: stackoverflow.com

[ACCEPTED]-Comparing SQL Table to itself (Self-join)-self-join

How to join two tables permanently with SQL?

Table rownum returns 0 records if rownum is tested for value greater than 100

MYSQL - Select only if row in LEFT JOIN is not present

Update Table with a "Select query" with a where clause

Is NATURAL (JOIN) considered harmful in production environment?

Joining 100 tables

what is the difference between join keyword and inner join keyword in oracle sql?

Show only most recent date from joined MySQL table

Speed up a query, simple inner join with one large table and one small table

Mysql query to join three tables