NB: this question is purely academic / to help improve my understanding of SQL Server performance.
Given a master table which relates to one or more other tables, how would you determine the best approach to querying that master table for records, which include an indicator to the presence of records in the related tables?
For example, say we have a Person table and wanted to get a list of all people along with an indicator of whether they have children (in this example Person can be reused as the related table):
create table Person
(
Id bigint not null constraint pk_Person primary key clustered
, ParentId bigint null constraint fk_Person_Parent foreign key references Person(Id)
, FirstName nvarchar(256) not null
, LastName nvarchar(256) not null
)
We could run any of the queries below to check for the presence of related children:
--variables for restricting our result set, just to keep things interesting
declare @LastName nvarchar(256) = 'Be%'
, @FirstName nvarchar(256) = null
example 1
-- fairly straight forward, but requires grouping to account for the
-- potential of a parent having multiple kids (which I don't care about here)
-- which could be adding some inefficiency.
select parent.Id
, parent.FirstName
, parent.LastName
, case when max(child.Id) is null then 0 else 1 end HasChildren
from Person parent
left outer join Person child --1:n
on child.ParentId = parent.Id
where (@LastName is null or parent.LastName like @LastName)
and (@FirstName is null or parent.FirstName like @FirstName)
group by parent.Id, parent.FirstName, parent.LastName --resolve 1:n
example 2
-- avoid the need to group the results by first getting
-- a single child per parent.
-- may be inefficient because we get children for all parents
-- even if we filter for only a few parents.
select parent.Id
, parent.FirstName
, parent.LastName
, coalesce(child.hasChildren, 0) HasChildren
from Person parent
left outer join --1:? (0 or 1)
(
select distinct parentId, 1 hasChildren
from Person
where parentId is not null --not sure if this adds value
) child
on child.ParentId = parent.Id
where (@LastName is null or LastName like @LastName)
and (@FirstName is null or FirstName like @FirstName)
--group by removed since we're 1:?
example 3
-- same as #2 except we limit the child results to those
-- related to the parents we're interested in / having stored
-- them in a CTE to avoid querying for the same parent data
-- in the inner query and outer query.
-- Getting a bit silly now, but could overcome some inefficienies?
;with parentCTE as (
select Id, FirstName, LastName
from person
where (@LastName is null or LastName like @LastName)
and (@FirstName is null or FirstName like @FirstName)
)
select parentCTE.Id
, parentCTE.FirstName
, parentCTE.LastName
, coalesce(child.hasChildren, 0) HasChildren
from parentCTE
left outer join --1:? (0 or 1)
(
select distinct parentId, 1 hasChildren
from Person
where parentId in --reduce the amount of data we return here based on the records we're interested in
(
select Id
from parentCTE
)
) child
on child.ParentId = parentCTE.Id
example 4
-- back to a simple one; just check for children on our parents
-- but this time having brought back the full parent set.
-- may be inefficient because we're querying the table once per
-- matching parent to check for children.
select parent.Id
, parent.FirstName
, parent.LastName
, coalesce((select top 1 1 from Person child where child.parentId = parent.Id),0) HasChildren
from Person parent
where (@LastName is null or LastName like @LastName)
and (@FirstName is null or FirstName like @FirstName)
I'm after information on how to better understand the trade-offs involved in such situations as opposed to a simple example 3's best. Pointers to articles which may help me understand would also be welcome.
Related SQL Fiddle: http://sqlfiddle.com/#!6/edc17/3
