SQL Left join avoid duplicates

Question

Let's say this is my Table A:

Col1 Col2 Col3 Col4  Col5
a    b    c    d     e
a    b    c    x     f
b    i    j    l     m
b    i    j    v     t

And my second table B:

Col1 Col2 Col3  Col6
a    b    c     g
a    b    c     s
b    i    j     u
b    i    j     h

Table A and B have common colums ( here Col 1, Col 2, and Col 3) and table B has only unique rows, no duplicates. What I want to have is:

Col1 Col2 Col3 Col4 Col5 Col6
a    b    c    d    e    g 
a    b    c    x    f    null
b    i    j    l    m    u
b    i    j    v    t    null

So the thing to do a is a left join on only first match and all others rows from table B that match should be null/empty. I have tried this query :

SELECT  A.*, B.Col6,
FROM    A
LEFT JOIN   
B
ON 
A.Col1 =  B.Col1
AND A.Col2 = B.Col2
AND A.Col3 = B.Col3

But this gives me duplicates. I also tried with distinct, row_number()b ut still not the expected results. I cannot used subqueries and TOP 1 and limit 1 also does not give the expected result. I have the feeling that it is quite simple but yet still no solution.

Can someone help me?

Answer 1

You can do a lateral join, as in:

select
  x.col1, x.col2, x.col3, x.col4, x.col5,
  case when x.rn = 1 then y.col6 end as col6
from (select *, row_number() 
      over(partition by col1, col2, col3 order by col4) as rn from a) x
left join lateral (
  select * from b where (b.col1, b.col2, b.col3) = (x.col1, x.col2, x.col3) 
  order by col6 limit 1
) y on true

Result:

 col1  col2  col3  col4  col5  col6 
 ----- ----- ----- ----- ----- ---- 
 a     b     c     d     e     g    
 a     b     c     x     f     null 
 b     i     j     l     m     u    
 b     i     j     v     t     null 

See running example at DB Fiddle.

Answer 2

Maybe OUTER APPLY can help you:

SELECT  A.*, B.Col6
FROM    A
LEFT OUTER APPLY (SELECT TOP 1 *
                  FROM B WHERE A.Col1 =  B.Col1
                               AND A.Col2 = B.Col2
                               AND A.Col3 = B.Col3) B

It will returns you exactly one matching from left side, if not you will have NULL as you already shown in your example.

This will work on SQL Server.