title: Setting a default value on NULL columns from LEFT JOIN and filtering using WHERE
body: <p>I have a table of items and I have a separate table containing the individual purchases of each item. Not every item has been purchased. I am selecting the purchase count and joining it with the items table with a statement similar to the following:</p>
  
  <pre><code>SELECT items.name, purchases_count
  FROM `items` 
      LEFT JOIN (SELECT sales.item_name, SUM(unit_sales) AS purchases_count 
          FROM sales GROUP BY item_name) sales ON sales.item_name = items.uid
  </code></pre>
  
  <p>This results in data similar to:</p>
  
  <pre><code>+------+-----------------+
  | name | purchases_count |
  |------+-----------------+
  | Shoe | 12              |
  | Belt | NULL            |
  +------+-----------------+
  </code></pre>
  
  <p>My problem arises when I want to filter this result on the <code>purchases_count</code>. If I use <code>WHERE purchases_count &lt; 10</code> or something similar, items with no sales record at all (i.e. those with a <code>NULL</code> value) are excluded from the search.</p>
  
  <p>I have attempted to use <code>COALESCE(purchases_count,0) AS purchases_count</code> which does correctly set <code>NULL</code> records to 0, but they are still not appearing when using <code>WHERE</code>. I suspect the <code>COALESCE</code> is happening after the <code>WHERE</code>. </p>
  
  <p>I would like to be able to filter on this number using <code>WHERE</code> and include <code>NULL</code> results (as zeros) if possible. Any suggestions?</p>