Performance of checking for data in related tables
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.