How to get unique (not vice-versa) fields on MySQL

Question

The title may be misleading but i don't know how to formulate it better.

Suppose i have these rows on my MySQL table:

table1:

id    column1    column2
1     1          2
2     1          3  
3     2          1
4     3          4

I have written a query to retrieve data that have similar vice-versa values on columns column1 and column2 (id: 1 & id: 3), but I'm having trouble querying over data that don't have similar vice-versa rows (id: 2 & id: 4) or that are sort of unique.

EDIT: The query i've used to get vice-versa rows

SELECT * FROM table1 t1
INNER JOIN table1 t2
ON (t1.column1 = t2.column2 AND t1.column2 = t2.column1);

question from:https://stackoverflow.com/questions/65626194/how-to-get-unique-not-vice-versa-fields-on-mysql

Answer 1

You could use exists logic here:

SELECT id, column1, column2
FROM yourTable t1
WHERE NOT EXISTS (SELECT 1 FROM yourTable t2
                  WHERE LEAST(t2.column1, t2.column2) = LEAST(t1.column1, t1.column2) AND
                        GREATEST(t2.column1, t2.column2) = GREATEST(t1.column1, t1.column2));