Practice > SQL > Advanced Join > Symmetric Pairs

Problem

Two pairs (X1, Y1) and (X2, Y2) are said to be symmetric pairs if X1 = Y2 and X2 = Y1.
Write a query to output all such symmetric pairs in ascending order by the value of X. List the rows such that X1 ≤ Y1.
문제링크

Answer

1. UNION을 이용한 풀이

SELECT f1.x
     , f1.y
FROM Functions AS f1
    INNER JOIN Functions AS f2 ON f1.x = f2.y and f1.y=f2.x
WHERE f1.x < f1.y -- List the rows such that X1 ≤ Y1.

UNION -- 두개의 값을 합치는 UNION

SELECT *
FROM functions
WHERE X=Y
GROUP BY X, Y
HAVING COUNT(*) = 2

ORDER BY X -- ORDER BY는 아래에 적어주면 전체 값을 순서대로 정렬함

2. HAVING절에서 모든 조건을 입력한 풀이

SELECT f1.x, f1.y
FROM functions AS f1
    INNER JOIN functions AS f2 ON f1.x=f2.y AND f1.y=f2.x
GROUP BY f1.x, f1.y
HAVING count(*)>=2 or f1.x < f1.y 
ORDER BY f1.x
-- HAVING을 써서 푼 이 문제가 좀 더 쉽고 간편하다