title: Derived Tables vs. Correlated subquery in HAVING clause SQL Server 2008
body: <p>In this example I've only got two tables with which I'm working.  I was checking to see which might run faster and the execution plans were identical.  The purpose of these queries is to find the minimum date associated with one record that's larger than the maximum date of another.  The first query is more concise (although it doesn't allow the <code>DATEDIFF</code> column the second one does).  I feel like writing a query the second way will, in the long run, be more costly performance wise, perhaps when I have many joins.  Are there any general rules about which method, or is it a case by case scenario and you let the optimizer work it out?  </p>
  
  <p>first</p>
  
  <pre><code>select c.patid,min(c.admitDate) as minDiabetesDate
      from clm_extract as c
      inner join icdClm as ic on ic.clmid=c.clmid
      where ic.icd like '250%' 
      group by c.patid
      having min(c.admitdate) &gt; 
      (
          select MAX(c2.admitDate) as maxPreDiabetesDate
              from clm_extract as c2
              inner join icdClm as ic2 on ic2.clmid = c2.clmid
              where ic2.icd ='79029' and c2.patid=c.patid
              group by c2.patid
      )
  </code></pre>
  
  <p>second </p>
  
  <pre><code>select distinct x.patid,x.minDiabetesDate,y.maxPreDiabetesDate 
  from
  (   
  
  select c.patid, min(c.admitdate) as minDiabetesDate
      from clm_extract as c 
      inner join icdClm as ic on ic.clmid=c.clmid
      where ic.icd like '250%'
      group by c.patid
  )x
  inner join 
  (
  select c2.patid, MAX(c2.admitdate) as maxPreDiabetesDate
      from clm_extract as c2
      inner join icdClm as ic2 on ic2.clmid=c2.clmid
      where ic2.icd ='79029'
      group by c2.patid
  )y on x.patid=y.patid
  group by x.minDiabetesDate,y.maxPreDiabetesDate,x.patid
  having DATEDIFF(dd,y.MaxPreDiabetesDate,x.minDiabetesDate) &gt; 0
  </code></pre>