To query just ID from a table to multiple table with query containing left outer join, subquery, Max, Inner Join

Transact-SQL
21
  1. Do you have any indexing on the columns you are joining on?
  2. You are going to have to do some work yourself to examine and clean up a lot of stuff
    for example why are you doing the following? You are joining to the same table twice.
LEFT OUTER JOIN tbl1.tbcd C1 
ON a.row_id = c1.row_id 
    AND c1.country_type = 'Residence' 
    AND a.bdate = c1.bdate 
LEFT OUTER JOIN tbl1.tbcd C2 
ON a.row_id = c2.row_id 
    AND c2.country_type = 'JC' 
    AND a.bdate = c2.bdate

change to

LEFT OUTER JOIN tbl1.tbcd C1 
ON a.row_id = c1.row_id 
    AND c1.country_type IN ('JC' , 'Residence' )
    AND a.bdate = c1.bdate
22

what does the execution plan say?