I have the following user table
A New York
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 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.