Performance of checking for data in related tables 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
Example 4 has the fewest scans and reads:

Example 1


  SQL Server parse and compile time: 
  &nbsp;&nbsp;&nbsp;&nbsp;CPU time = 4 ms, elapsed time = 4 ms.
  
  SQL Server Execution Times:
  &nbsp;&nbsp;&nbsp;&nbsp;CPU time = 0 ms,  elapsed time = 0 ms.


example1    Id    FirstName       
-----------------------------
1           2     Aaron           
1           3     John            
1           8     Aaron           
1           9     John            
1           14    Aaron           
1           15    John            
1           20    Aaron           
1           21    John            



  (8 row(s) affected)


Table 'Person'.
Scan count 9, logical reads 27, physical reads 0, 

Rows                 Executes             StmtText                
-------------------- -------------------- ------------------------
8                    1                    select 1 example1




, 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 
on child.ParentId = parent.Id
where (@LastName is null or parent.Las 1           1           0   
0       0     |--Compute Scalar(DEFINE:([Expr1005]=(1), [Expr1
8       1          |--Nested Loops(Left Outer Join, OUTER REFE
8       1               |--Clustered Index Scan(OBJECT:([sub].
3       8               |--Stream Aggregate(DEFINE:([Expr1004]
7       8                    |--Clustered Index Scan(OBJECT:([



  (6 row(s) affected)





  SQL Server Execution Times:
  &nbsp;&nbsp;&nbsp;&nbsp;CPU time = 0 ms,  elapsed time = 0 ms.


Example 2


  SQL Server parse and compile time: 
  &nbsp;&nbsp;&nbsp;&nbsp;CPU time = 0 ms, elapsed time = 4 ms.





  SQL Server Execution Times:
  &nbsp;&nbsp;&nbsp;&nbsp;CPU time = 0 ms,  elapsed time = 0 ms.


example2  Id   FirstName                                            
----------------------------
2         2    Aaron                                                     
2         3    John                                                      
2         8    Aaron                                                     
2         9    John                                                      
2         14   Aaron                                                     
2         15   John                                                      
2         20   Aaron                                                     
2         21   John         



  (8 row(s) affected)


Table 'Person'.
Scan count 9, logical reads 27, physical reads 0,

Rows   Executes   StmtText                                         
------ ---------- -----------------------
8      1          select 2 example2




, parent.Id
, parent.FirstName
, parent.LastName 
, coalesce(child.hasChildren, 0) HasChildren
from Person parent
left outer join 
(
    select distinct parentId, 1 hasChildren
    from Person 
    where parentId is not null 
) child 




 1           1           0           NULL                          
0     0       |--Compute Scalar(DEFINE:([Expr1007]=(2), [Expr1
8     1            |--Nested Loops(Left Outer Join, OUTER REFE
8     1                 |--Clustered Index Scan(OBJECT:([sub].
3     8                 |--Stream Aggregate(DEFINE:([Expr1006]
0     0                      |--Compute Scalar(DEFINE:([Expr10
7     8                           |--Clustered Index Scan(OBJE



  (7 row(s) affected)





  SQL Server Execution Times:
  &nbsp;&nbsp;&nbsp;&nbsp;CPU time = 0 ms,  elapsed time = 0 ms.


Example 3

SQL Server parse and compile time: 
   CPU time = 7 ms, elapsed time = 7 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

example3 Id   FirstName                                            
---- ------ ------------------------
3    2      Aaron                                                  
3    3      John                                                   
3    8      Aaron                                                  
3    9      John                                                   
3    14     Aaron                                                  
3    15     John                                                   
3    20     Aaron                                                  
3    21     John   


(8 row(s) affected)

Table 'Person'. Scan count 9, logical reads 41, physical reads 0, r

Rows     Executes   StmtText                                       
------   ---------  ------------------
8        1          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 3 example3
, parentCTE.Id
, parentCTE.FirstName
, parentCTE.La 1           1           0           NULL            
0     0     |--Compute Scalar(DEFINE:([Expr1011]=(3), [Expr1
8     1          |--Nested Loops(Left Outer Join, OUTER REFE
8     1               |--Clustered Index Scan(OBJECT:([sub].
3     8               |--Stream Aggregate(DEFINE:([Expr1010]
0     0                    |--Compute Scalar(DEFINE:([Expr10
7     8                         |--Nested Loops(Inner Join, 
7     8                              |--Clustered Index Scan
7     7                              |--Clustered Index Seek


(9 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Example 4

SQL Server parse and compile time: 
   CPU time = 3 ms, elapsed time = 3 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

example4 Id  FirstName                                             
---- ----- -------------
4    2     Aaron        
4    3     John         
4    8     Aaron        
4    9     John         
4    14    Aaron        
4    15    John         
4    20    Aaron        
4    21    John         


(8 row(s) affected)

Table 'Person'. Scan count 3, logical reads 26, physical reads 0, 

Rows     Executes   StmtText             
-------- ---------- ---------------------
8        1          select 4 example4
, parent.Id
, parent.FirstName
, parent.LastName
, coalesce((select top 1 1 from Person child where child.parentId =
from Person parent
where (@LastName is null or LastName like @LastName)
and (@FirstName  1           1           0           NULL          
0       0     |--Compute Scalar(DEFINE:([Expr1002]=(4), [Expr1
8       1          |--Nested Loops(Left Outer Join, PASSTHRU:(
8       1               |--Nested Loops(Left Outer Join, OUTER
8       1               |    |--Clustered Index Scan(OBJECT:([
0       0               |    |--Compute Scalar(DEFINE:([Expr10
3       8               |         |--Top(TOP EXPRESSION:((1)))
3       8               |              |--Clustered Index Scan
0       0               |--Compute Scalar(DEFINE:([Expr1008]=(
3       3                    |--Top(TOP EXPRESSION:((1)))     
3       3                         |--Clustered Index Scan(OBJE


(11 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

You want the fewest scans possible.  The more times we have to scan the table, the longer it will take.