# HackerRank: [SQL Advanced Join] (3/5) SYMMETRIC PAIRS | cross join, having, union, exists in SQL

I started studying SQL from a very famous site - HackerRank. Here I will try to provide multiple approaches & solutions to the same problem. It will help you learn and understand SQL in a better way.

Please make use of my blog posts for learning purpose only and feel free to ask your questions in the comment box below in case of any doubt.

Click Here for the previous blog-post in the series.

Recommended SQL Courses:

**SQL Problem Statement:**

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

Two pairs (X1, Y1) and (X, Y) 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 X ≤ Y.

**Sample Input:**

**Sample Output:**

**Solution-1: USING UNION & CROSS JOIN (MySQL Query):**

**NOTE:**

Lets divide the output in 2 parts:

**(a)**X=Y**(b)**X<Y- for the
**(a) X=Y**, We should check whether the entry (X,Y) WHERE X=Y is present in the table twice. If it is present 2 times then we can say the symmetric pair is present and add it in the output.

we can check the count in 2 ways:**1.**using HAVING clause (as shown in SOLUTION-1 & SOLUTION-2) OR**2.**using CASE WHEN statement in HAVING clause (as shown in SOLUTION-3).**NOTE:**

In SOLUTION-3, We have checked the count for X=Y should be >2 (instead of =2), It's because, due to the usage of CROSS-JOIN there, if the count for a pair (X1,Y1) is 2 in the original table, after the CROSS-JOIN the count will become 4. original count = 3 will become 6 after CROSS-JOIN and so on...

So, checking the count > 2, will be correct major to check if duplicate entry is present in the original table. - for the
**(b) X<Y**, We should check whether the entries are present in the table where X1=Y2 and X2=Y1.

This can be done in 3 ways:**1.**using CROSS-JOIN (as shown in SOLUTION-1) OR**2.**using EXISTS clause (as shown in SOLUTION-2) OR**3.**using CASE WHEN statement in HAVING clause (as shown in SOLUTION-3).

**Solution-2: USING UNION & EXISTS (MySQL Query):**

**NOTE:**

- The
**EXISTS**operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records..

**Solution-3: USING CROSS JOIN & HAVING CASE WHEN (MySQL Query):**

**NOTE:**

In SOLUTION-3, We have checked the count for X=Y should be >2 (instead of =2),

It's because, due to the usage of CROSS-JOIN there, if the count for a pair (X1,Y1) is 2 in the original table, after the CROSS-JOIN the count will become 4. original count = 3 will become 6 after CROSS-JOIN and so on...

**Expected Output:**

--------------------------------------------------------------------------------

**Click here**to see solutions for all

**Machine Learning**Coursera Assignments.

&

**Click here**to see more codes for

**Raspberry Pi 3**and similar Family.

&

**Click here**to see more codes for

**NodeMCU ESP8266**and similar Family.

&

**Click here**to see more codes for

**Arduino Mega (ATMega 2560)**and similar Family.

Feel free to ask doubts in the comment section. I will try my best to answer it.

If you find this helpful by any mean like, comment and share the post.

This is the simplest way to encourage me to keep doing such work.

Thanks & Regards,

**-Akshay P Daga**

## No comments