Forcing left join to only return one row from matching Ids in the right table

Question

I have two tables in which I'd like to join, the right table sometimes has more than 1 row for each ID. But I'm not interested to have all the matches, only the first one is enough.

How can I do that?

Example:

Foo:

     Id             FooColumns....
     100             xxxxxxxx
     200             xxxxxxxx
     300             xxxxxxxx
     400             xxxxxxxx

Bar:

     Id             BarColumns....
     100             yyyyyyyy
     100             zzzzzzzz
     200             yyyyyyyy
     200             zzzzzzzz

What I want to have is :

FooBar:

     Id             FooColumns....     BarColumns
     100             xxxxxxxx            yyyyyyyy
     200             xxxxxxxx            yyyyyyyy
     300             xxxxxxxx              nulls
     400             xxxxxxxx              nulls

---

Query: 
   Select F.*,B.* from Foo f left join Bar b on f.Id=B.Id   ?? 

Answer 1

One method uses row_number():

Select F.*, B.*
from Foo f left join
     (select b.*, row_number() over (partition by b.id order by id) as seqnum
      from bar b
     ) b
     on f.Id = B.Id and seqnum = 1; 

The order by specifies what you mean by "first". The order by id is an arbitrary ordering.

And alternative method uses outer apply:

Select F.*, B.*
from Foo f outer apply
     (select top 1 b.*
      from bar b
      where f.Id = B.Id
     ) b; 

In this case, you would add an order by to the subquery to get the "first" based on some column. Also, this should have better performance than the previous version.