Transact-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.

推荐答案

在小数据集上，当涉及到性能时，它们会被清洗.编入索引后，LOJ 会好一些.

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).

这篇关于Transact-SQL - 子查询还是左连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！