Join with a max and nulls

Question

I have 2 tables:

People:
ID |  Name
----------
1  | John
2  | David
3  | Jennifer

another which is has a simple FK to the first

Note:
ID | People_ID | Note
----------------------
1  |  1        | A note
2  |  1        | Another note
3  |  3        | Jen's note

I want to get the note associated with the max(ID) from Note for each person, or a null if no notes, so the desired result is:

People_ID | Name   | Note
----------------------------
1         |John    | Another Note
2         |David   | NULL
3         |Jennifer| Jen's Note

I can perform a join, but can't include David because the max criteria doesn't bring back the null column. Any help please?

Answer 1

That's a left join - and I would recommend pre-aggregating the notes in a subquery:

select p.*, n.*
from people p
left join (
    select people_id, max(id) max_note_id
    from note
    group by people_id
) n on n.people_id = p.id

There are situations where a lateral join would be more efficient:

select p.*, n.*
from people p
outer apply (
    select top(1) id max_note_id 
    from note n 
    where n.people_id = p.id
    order by id desc
) n

The nice thing about the lateral join is that you can easily bring more columns from the top matching record in the note table if you want to (like the text of the note, or else).