Sql – Transact-SQL – sub query or left-join


I have two tables containing Tasks and Notes, and want to retrieve a list of tasks with the number of associated notes for each one. These two queries do the job:

select t.TaskId,
       (select count(n.TaskNoteId) from TaskNote n where n.TaskId = t.TaskId) 'Notes'
from   Task t

-- or
select t.TaskId,
       count(n.TaskNoteId) 'Notes'
from   Task t
left join
       TaskNote n
on     t.TaskId = n.TaskId
group by t.TaskId

Is there a difference between them and should I be using one over the other, or are they just two ways of doing the same job? Thanks.

Best Solution

On small datasets they are wash when it comes to performance. When indexed, the LOJ is a little better.

I've found on large datasets that an inner join (an inner join will work too.) will outperform the subquery by a very large factor (sorry, no numbers).