MySQL Left Outer Join not returning NULL values for COUNT(*)

Question

I am attempting to left outer join 2 tables: TABLE1 contains a list of users and TABLE2 contains a list of forms completed by users. I want to display the count of forms by user, created after a given date, where status equals COMPLETED.

The following query is working but not displaying NULL values (which I need):

select TABLE1.USERNAME, count(TABLE2.FORMS)
from TABLE1
left outer join TABLE2 on (TABLE2.USERID = TABLE1.USERID)
and TABLE2.STATUS = COMPLETED
where TABLE2.DATE > 20140801
group by TABLE1.USERNAME;

What do I need to do to include users with NULL counts i.e. no forms in TABLE2?

Thanks

Answer 1

You need to move the condition in the where clause to the on clause:

select TABLE1.USERNAME, count(TABLE2.FORMS)
from TABLE1 left outer join
     TABLE2
     on (TABLE2.USERID = TABLE1.USERID) and TABLE2.STATUS = COMPLETED and
        TABLE2.DATE > 20140801
group by TABLE1.USERNAME;

In rows that don't match, the table2 columns are set to NULL. That, in turn, causes your where clause to fail (because comparisons to NULL return NULL, which is treated as false).

When using left outer join, filters on the second table go in the on clause. Filters on the first table go in the where clause.