title: Explanation needed for missing rows with left join and count()
body: <p>Can someone please help me understand the following behavior that occurs when I add a WHERE clause to a query that has a LEFT JOIN with COUNT(*)?</p>
  
  <p>I have two tables:
  <br></p>
  
  <pre><code>TABLE 1: customers
  customer_id | name
  ------------------
  1 | Bob
  2 | James
  3 | Fred
  
  TABLE 2: orders
  order_id | customer_id | order_timestamp
  ----------------------------------------
  1000 | 1 | 2011-01-01 00:00
  1001 | 1 | 2011-01-05 00:00
  1002 | 2 | 2011-01-10 00:00
  </code></pre>
  
  <p>Now the following query tells me how many orders each customer placed:
  <br></p>
  
  <pre><code>select c.customer_id, count(o.order_id)
  from customers c
  left join orders o using (customer_id)
  group by 1
  
  customer_id | count
  -------------------
  1 | 2
  2 | 1
  3 | 0
  </code></pre>
  
  <p>This works great BUT if I add a WHERE clause to the query, the query no longer outputs  counts of zeroes for customers who did not place any orders even though I'm doing a LEFT JOIN:</p>
  
  <pre><code>select c.customer_id, count(o.order_id)
  from customers c
  left join orders o using (customer_id)
  where o.order_timestamp &gt;= '2011-01-05'
  group by 1
  
  customer_id | count
  -------------------
  1 | 1
  2 | 1
  </code></pre>
  
  <p>Now if I move the WHERE condition as part of the LEFT JOIN like the following, I get back my zero counts for customers who did not place orders:</p>
  
  <pre><code>select c.customer_id, count(o.order_id)
  from customers c
  left join orders o on (c.customer_id = o.customer_id) and (o.order_timestamp &gt;= '2011-01-05')
  group by 1
  </code></pre>
  
  <p>I'm confused at why the second query does not work, but the third one does? Can someone please provide me with an explanation? Also not sure if this matters, but I'm using postgres. Thanks!</p>