Returning Unique rows from Left Outer Join

Question

I am trying to build a query which will give me unique rows. Details:-

Table 1 (F1, F2 are the columns)
F1  F2
1   A1
2   A2
3   A3
4   A4

Table 2 (F3,F4 are the columns)
F3  F4
1   B1
1   B11
2   B2
2   B22

My Query (Incorrect)
select rrn(A), F1,F2,F3,F4 from rush2hem1.T1 A left outer join rush2hem1.T2 B on A.F1=B.F3

This gives me below output which is not what I am looking for:-

RRN F1  F2  F3  F4
1   1   A1 1    B1 
1   1   A1 1    B11 
2   2   A2 2    B2 
2   2   A2 2    B22 
3   3   A3 (null)   (null)
4   4   A4 (null)   (null)

Expected output that I am building query for is:-

RRN F1  F2  F3  F4
1   1   A1 1    B1 
2   2   A2 2    B2 
3   3   A3 (null)   (null)
4   4   A4 (null)   (null)

Please let me know if you have any suggestions.

Answer 1

This problem could be solved differently in different RDBMS. In any case, you have to specify which one record from Table2 do you want to get (by order by clause)

If your database have window function row_number(), you can use it like this:

select
    F1, F2, F3, F4
from (
    select
        T1.F1, T1.F2, T2.F3, T2.F4,
        -- order by specifying which row you would get from Table2
        row_number() over(partition by T1.F1 order by T2.F4) as rn
    from Table1 as T1
        left outer join Table2 as T2 on T2.F3 = T1.F1
) as cte  
where rn = 1;

In SQL Server, you can use outer apply:

select
    T1.F1, T1.F2, T2.F3, T2.F4
from Table1 as T1
    outer apply (
        select top 1 T2.F3, T2.F4
        from Table2 as T2
        where T2.F3 = T1.F1
        order by T2.F3 asc   -- This is important line
    ) as T2;

In PostgreSQL, you can use distinct on syntax:

select distinct on (T1.F1)
    T1.F1, T1.F2, T2.F3, T2.F4
from Table1 as T1
    left outer join Table2 as T2 on T2.F3 = T1.F1
order by T1.F1, T2.F4; -- sort by F4 is important

• SQL Server sqlfiddle demo
• PostgreSQL sqlfiddle demo

Answer 2

Not tested, It's a SQL server version.

select rrn(A), F1,F2,F3,F4
from
(
 select rrn(A), F1,F2,F3,F4,row_number() over(partition by RRN order by RRN) as rn
from rush2hem1.T1 A left outer join rush2hem1.T2 B 
on A.F1=B.F3
) as dt
where dt.rn = 1

Answer 3

Please check the result with OUTER APPLY

SELECT 
    * 
FROM 
    Table1 a
OUTER APPLY 
    (SELECT 
        TOP 1 * 
    FROM 
        Table2 b 
    WHERE a.F1=b.F3)c