muaaz muaaz - 1 month ago 13
SQL Question

Non-Equi Self Join

I have the following user table

USERS



name city
A New York
B Paris
C London
D London
E Paris


I want to select the two users from the same City using non equi self join
such that the result follows

name name city
B E Paris
C D London

APC APC
Answer

A "self-join" is just like a normal join execpet that the same table appears on both sides of the join clause. "non-equi" means finding rows which don't match on some column.

So in your case you need to join on CITY and filter on differences in NAME:

select t1.name as t1_name
       , t2.name as t2_name
       , t1.city
from users t1
     join users t2
     on t2.city = t1.city
where t1.name < t2.name

Note the filter condition is less than: using not equals would double the result set.(*)

Obviously this solution will work where CITY has two entries. If there are more than two entries you will still get multiple rows (one per combination).


(*) Some times using != is desirable: if we're investigating a data quality issue then returning all columns from both rows can help us understand what's going on.