title: MySQL Query Performance - Subquery Vs Join
body: <p>I'm not a SQL expert, so am trying to understand why two queries have substantially different execution times. </p>
  
  <p>common_stats is a large table (3 million rows). I'm just trying to understand why the huge difference in performance.</p>
  
  <p>The below query takes ~15 seconds:</p>
  
  <pre><code>select distinct cs.basesalary_id
  from common_stats AS cs
  LEFT JOIN basesalary AS b ON b.id = cs.basesalary_id
  WHERE (b.employee_id= 100 AND cs.amount &gt; 0 AND cs.pay_id is null );
  </code></pre>
  
  <p>This query takes ~.1 seconds:</p>
  
  <pre><code>select distinct basesalary_id from (
      select cs.basesalary_id, cs.pay_id
      from common_stats AS cs
      LEFT JOIN basesalary AS b ON b.id = cs.basesalary_id
      WHERE (b.employee_id= 100 AND cs.amount &gt; 0)
  ) as temp
  where pay_id is null;
  </code></pre>