How to dedupe rows with mirrored values in 2 columns in SQL-CodePudding

I was wondering if there is a way to dedupe a table that contains 2 columns that has mirrored values (so the expected table should be half the size).

For example, let's say I have the following as Table 1:

C1 || C2
========
A  ||  B
B  ||  A

Is there a deduping query where the result is only one of the above, such as:

C1 || C2
========
A  ||  B

I am thinking some kind of inner join is involved, but can't wrap my head around it.

CodePudding user response：

SELECT *
FROM mytable t
WHERE NOT EXISTS (
  SELECT 1
  FROM mytable t1
  WHERE t1.c1 < t1.c2 AND t.c1 = t1.c2 AND t.c2 = t1.c1
)