SELECT DISTINCT f1.x AS x1, f1.y AS y1 FROM functions f1 JOIN functions f2 ON f1.x = f2.y AND f1.y = f2.x WHERE f1.x <= f1.y group by f1.x,f1.y having (count(x1)>1 and f1.x=f1.y) or (f1.x<>f1.y) ORDER BY f1.x

i tried this

select * from (select * from functions where (y, x) in (select * from functions) and x < y union select * from functions where x = y group by x, y having count(*) = 2) t order by x, y

Simple Query for mySQL: with Temp as ( select row_number()over (order by f1.x asc) as r,f1.x,f1.y from functions f1) select T1.x,T1.y from temp T1 join Temp T2 on T1.x=T2.y and T1.y=T2.x and T1.r != T2.r where T1.x<=T1.y group by T1.x,T1.y order by T1.x

Simple query SELECT DISTINCT p1.x, p1.y FROM Functions p1 JOIN Functions p2 ON p1.x = p2.y AND p1.y = p2.x WHERE p1.x < p1.y OR p1.x IN ( SELECT x FROM Functions WHERE x = y GROUP BY x HAVING COUNT(*) > 1 ) ORDER BY p1.x;

With cte

WITH SymmetricPairs AS ( SELECT f1.x, f1.y FROM Functions f1 JOIN Functions f2 ON f1.x = f2.y AND f1.y = f2.x ), Filtered AS ( SELECT DISTINCT x, y FROM SymmetricPairs WHERE x < y

UNION

SELECT x, y FROM Functions WHERE x = y GROUP BY x, y HAVING COUNT(*) > 1 )

SELECT * FROM Filtered ORDER BY x;

Explanation: It's not much tricky with few pitfalls to avoid we can solve it easily by two approaches, one by the use of CROSS JOIN and a simple INNER JOIN. The cte here, allocating a unique row number to each row, is to avoid joining a row with itself which may happen here as the rows are coming from the same tables, here cte. The outer most select clause just after the cte definition is to avoid redundancy which may arise due to both the ctes having the values of {x<sub>1</sub>, y<sub>1</sub>} and {x<sub>2</sub>, y<sub>2</sub>} . So, a row which is to be present in the output table which was join by x<sub>1</sub> = y<sub>2</sub> and y<sub>1</sub>} = x<sub>2</sub> can again appear by the y<sub>2</sub> = x<sub>1</sub> and x<sub>2</sub> = y<sub>1</sub>} .

MySQL Code:

with cte as
(
    select
        x,
        y,
        row_number() over (order by x,y) as r
    from functions
)
select 
    distinct x, y
from
    (select 
        case
            when t1.x <= t1.y then t1.x
            else t2.x
        end x,
        case
            when t1.x <= t1.y then t1.y
            else t2.y
        end y
    from 
        cte as t1
        inner join
        cte as t2
        on t1.x = t2.y and t2.x = t1.y and t1.r != t2.r) as tabl
order by x;