title: Heavy use of ISNULL versus nested SELECT
body: <p>I am wondering if these two statements are equivalent in terms of performance:</p>
  
  <p>Statement 1:</p>
  
  <pre><code>SELECT
      ISNULL(T2.COL4, T1.COL4)
  FROM
      T1 LEFT JOIN T2 ON T1.COL1 = T2.COL1
      JOIN T3 ON T2.COL1 IS NULL AND T3.COL2 = T1.COL2 OR T3.COL2 = T2.COL2
  WHERE
      T3.COL3 = @COL3
  </code></pre>
  
  <p>Statement 2:</p>
  
  <pre><code>SELECT
      T1.COL4
  FROM
      (SELECT
          ISNULL (T2.COL4, T1.COL4) COL4,
          ISNULL (T2.COL2, T1.COL2) COL2
      FROM T1 LEFT JOIN T2 ON T1.COL1 = T2.COL1) T1
      JOIN T3 ON T1.COL2 = T3.COL2
  WHERE
      T3.COL3 = @COL3
  </code></pre>
  
  <p>The two statements are functionnaly equivalent (aren't they?) but common sense would suggest the second statement is slower because we "enumerate" T1 LEFT JOIN T2 before applying the WHERE clause. But is it really slower or does SQL Server have a way to optimize them and make them equivalent?</p>
  
  <p>The thing is the first way of writing it can get really nasty when you have several more columns from the left join to use, whereas the second way restrains the ugliness to the nested SELECT only.</p>