Symmetric Pairs in SQL | HackerRank Solution

Hello coders, today we are going to solve Symmetric Pairs HackerRank Solution in SQL.

Problem

You are given a table, Functions, containing two columns: X and Y.

Column	Type
X	Integer
Y	Integer

Two pairs (X₁, Y₁) and (X₂, Y₂) are said to be symmetric pairs if X₁ = Y₂ and X₂ = Y₁.

Write a query to output all such symmetric pairs in ascending order by the value of X. List the rows such that X₁ ≤ Y₁.

Sample Input

X	Y
20	20
20	20
20	21
23	22
22	23
21	20

Sample Output

20 20
20 21
22 23

Solution – Symmetric Pairs in SQL

MySQL

SELECT X,
       Y
FROM FUNCTIONS F1
WHERE EXISTS
    (SELECT *
     FROM FUNCTIONS F2
     WHERE F2.Y = F1.X
       AND F2.X = F1.Y
       AND F2.X > F1.X)
  AND (X != Y)
UNION
SELECT X,
       Y
FROM FUNCTIONS F1
WHERE X = Y
  AND (
         (SELECT COUNT(*)
          FROM FUNCTIONS
          WHERE X = F1.X
            AND Y = F1.X) > 1)
ORDER BY X;

Disclaimer: The above Problem (Symmetric Pairs) is generated by Hacker Rank but the Solution is Provided by CodingBroz. This tutorial is only for Educational and Learning Purpose.