muaaz muaaz - 5 months ago 46
SQL Question

Non-Equi Self Join

I have the following user table


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


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 as t1_name
       , as t2_name
from users t1
     join users t2
     on =
where <

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.