title: Union which excludes values from the first table
body: <p>The origional problem I am attempting to solve is that I need to show all rows from a specific "joined" table. However these are sometimes blank with no totals and normally would not show (think categories and counts for each).</p>
  
  <p>So what I am attempting to do is union to a "0 value" data set to show all categories. However when I do the union it shows a 0 value row, as well as the normal data. Here is an example..</p>
  
  <pre><code>SELECT category_name, COUNT(files_number)
  FROM files
  LEFT JOIN categories ON categories.category_id = files.category_id
  
  UNION 
  
  SELECT category_name, 0
  FROM categories
  </code></pre>
  
  <p>This will give me a result set that looks similar to this: </p>
  
  <pre><code>category_name | value
  ----------------------
  open file     | 0
  open file     | 23
  closed file   | 0
  </code></pre>
  
  <p>Is there any way to remove duplicate zero value entries? Please not there is also a complex WHERE clause in the actual query, so avoiding duplication on it is preferred.</p>