title: SQL Joining by field that *may* be empty
body: <p>In my SQL CE database I have three tables: <code>customer</code>, <code>list</code> and <code>customerlist</code> (a junction table between <code>customer</code> and <code>list</code> - as it is a many-to-many relationship).</p>
  
  <p>I am trying to run a query that will display all current lists with also the number of customers who are currently subscribed to that list (counting from <code>customerlist</code> table).</p>
  
  <p>Here is my current query:</p>
  
  <pre><code>select list.listid, count(customerlist.customerid) as numppl, list.ShortDesc
  from list inner join customerlist on list.listid=customerlist.listid
  group by list.ShortDesc, list.listid
  order by numppl desc
  </code></pre>
  
  <p>The current structure of this database is:</p>
  
  <pre><code>[Customer]           [List]             [CustomerList]
  CustomerId           ListId             CustomerListId
  Name                 ShortDesc          CustomerId
  Other details                           ListId
  </code></pre>
  
  <p>This currently returns all the lists who have customers currently assigned to them - but not lists which are empty. Empty lists are hidden.</p>
  
  <p>I would like to modify this query to also display empty lists but I am struggling. My desired output is:</p>
  
  <pre><code>Name      numppl
  listA     375
  listB     45
  listC     0
  </code></pre>
  
  <p>(In the example above, listC is currently not being returned).</p>
  
  <p>Any thoughts on how to also show listC in the query?</p>