title: Why does sql server do a scan on joins when there are no records in source table
body: <p>The idea of the below query is to use the CTE to get the primary key of all rows in [Archive].[tia_tia_object] that meet the filter. </p>
  
  <p>The execution time for the query within the CTE is 0 seconds.</p>
  
  <p>The second part is supposed to do joins on other tables, to filter the data some more, but only if there are any rows returned in the CTE. This was the only way I could get the SQL server to use the correct indexes. </p>
  
  <p>Why does it spend time (see execution plan) looking in TIA_TIA_AGREEMENT_LINE and TIA_TIA_OBJECT, when CTE returns 0 rows?</p>
  
  <pre><code>WITH cte_vehicle 
       AS (SELECT O.[Seq_no], 
                  O.Object_No 
           FROM   [Archive].[tia_tia_object] O 
           WHERE  O.RECORD_TIMESTAMP &gt; 
              (SELECT LastLoadTimeStamp FROM staging.Ufngetlastloadtimestamp('Staging.CoveredObject')) 
              AND O.[Meta_iscurrent] = 1 
              AND O.OBJECT_TYPE IN ( 'BIO01', 'CAO01', 'DKV', 'GFO01', 
                                     'KMA', 'KNO01', 'MCO01', 'VEO01', 
                                     'SVO01', 'AUO01' )) 
  SELECT O.[Seq_no]                      AS [Bkey_CoveredObject], 
         Cast(O.[Agr_Line_No] AS BIGINT) AS [Agr_Line_No], 
         O.[Cover_Start_Date]            AS [CoverageFrom], 
         O.[Cover_End_Date]              AS [CoverageTo], 
         O.[Timestamp]                   AS [TIMESTAMP], 
         O.[Record_Timestamp]            AS [RECORD_TIMESTAMP], 
         O.[Newest]                      AS [Newest], 
         O.LOCATION_ID                   AS LocationNo, 
         O.[Cust_no], 
         O.[N01] 
  FROM   cte_vehicle AS T 
         INNER JOIN [Archive].[tia_tia_object] O 
                 ON t.Object_No = O.Object_No 
                    AND t.Seq_No = O.Seq_No 
         INNER JOIN [Archive].[tia_tia_agreement_line] AL 
                 ON O.Agr_line_no = AL.Agr_line_no 
         INNER JOIN [Archive].[tia_tia_policy] P
                  ON AL.Policy_no = P.Policy_no 
  WHERE  P.[Transaction_type] &lt;&gt; 'D' 
  </code></pre>
  
  <p>Execution plan: </p>
  
  <p><a href="https://i.stack.imgur.com/KnJlS.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KnJlS.png" alt="enter image description here"></a></p>