Non-Equi Self Join

Question

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

Answer 1

You can use below way with the use of cte also

WITH CITY
AS (
SELECT ROW_NUMBER() OVER(PARTITION BY A.CITY ORDER BY A.CITY) RNO,A.NAME,A.CITY FROM Table1 A
)

SELECT A.NAME,B.NAME,A.CITY FROM 
CITY A JOIN 
CITY B
ON 
A.city=B.city AND A.NAME<>B.name AND A.RNO<=B.RNO

Answer 2

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.