Left Join with count behavior

1.SELECT  a.id, COUNT(*)

2.FROM    a

3.LEFT JOIN

4.b

5.ON      b.a = a.id

6.GROUP BY

7.a.id

This query intends to count number of matches in b for each record in a.

The issue is that COUNT(*) will never return a 0 in above query. If there is no match for a certain record in a, the record will be still returned and counted.

COUNT should be made to count only the actual records in b. Since COUNT(*), when called with an argument, ignores NULLs, we can pass b.a to it. As a join key, it can never be a null in an actual match, but will be if there were no match:

view sourceprint?

1.SELECT  a.id, COUNT(b.a)

2.FROM    a

3.LEFT JOIN

4.b

5.ON      b.a = a.id

6.GROUP BY

7.a.id