What are the benefits of left outer join vs nested aggregate selects to find the newest rows in a table?
- by RenderIn
I'm doing:
select * from mytable y
where y.year = (select max(yi.year)
from mytable yi
where yi.person = y.person)
Is that better or worse from a performance aspect than:
select y.* from mytable y
left outer join mytable y2
on y.year < y2.year
and y.person = y2.person
where y2.year is null
The explain plan/anecdotal evidence is inconclusive so I am wondering if in general one is better than the other.
