How to get count of records as zero if records are not found with left join?

Question

I am trying to get the records with count 0 using left join. I am using MySQL Database.

portal table:

SELECT * FROM portal;

    id  name
    -----------
    1   Barnet
    2   Brexley
    3   Balby

application table:

id     portal_id    updated
-----------------------------
1         1        2011-08-20
2         1        2011-08-20
3         1        2011-08-21
4         2        2011-08-21
5         2        2011-08-20
6         2        2011-08-20
7         2        2011-08-21
8         2        2011-08-21

Here is my query:

SELECT 
    portal.name, COALESCE(COUNT(application.id), 0) AS count
FROM
    portal
        LEFT JOIN
    application ON application.portal_id = portal.id
GROUP BY portal.name;

Below is the output SQL Fiddle:

Name     Count
--------------
Balby      0
Barnet     3
Brexley    5

But when I include a WHERE clause, I am not getting the records with count 0.

SELECT 
    portal.name, COALESCE(COUNT(application.id), 0) AS count
FROM
    portal
        LEFT JOIN
    application ON application.portal_id = portal.id
WHERE
    application.updated > '2011-08-20'
GROUP BY portal.name;

Output SQL Fiddle 2:

Name     Count
--------------
Barnet     1
Brexley    3

Shift the condition from `WHERE` to `LEFT JOIN ..ON ...` If you put conditions on the right-side table of a `LEFT JOIN`, inside the `WHERE` clause => it becomes an `INNER JOIN` basically. — Madhur Bhaiya, Jul 16 '19 at 09:04
Check the updated query in this fiddle: http://www.sqlfiddle.com/#!9/35e17d4/8 — Madhur Bhaiya, Jul 16 '19 at 09:09
Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, Jul 16 '19 at 21:09
This is a faq. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings, names & line numbers & then read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. PS You added code with unexpected result. It's good that you have first enquired about your misunderstanding/error rather than your overall goal. But your title asks about the latter rather than the former. So your title & body don't agree, that's unclear. — philipxy, Jul 16 '19 at 21:09

score 1 · Answer 1 · answered Jul 16 '19 at 09:11

As per the comment of @MadhurBhaiya, do not use WHERE, and put the test application.updated > '2011-08-20' in the JOIN condition instead.

SELECT 
    portal.name, COUNT(COALESCE(application.id, 0) AS count
FROM
    portal
        INNER JOIN
    application ON (
        application.portal_id = portal.id
            AND
        application.updated > '2011-08-20'
    )
GROUP BY portal.name;

Note: I didn't test the code and I don't speak SQL well.

Shushil Bohara · Answer 2 · 2019-07-26T03:32:24.067

0

Condition in the WHERE treated as INNER JOIN even it belongs to LEFT JOIN so please you can place date condition just below the left join to filter/restrict data from that table only

SELECT 
    portal.name, COUNT(application.id) AS count
FROM portal
LEFT JOIN application ON application.portal_id = portal.id
    AND application.updated > '2011-08-20'
GROUP BY portal.name;

edited Jul 26 '19 at 03:32

answered Jul 16 '19 at 09:48

Shushil Bohara

5,556
2
15
32

score 0 · Answer 3 · answered Jul 16 '19 at 11:08

The condition on application should be in the on clause. But, I would recommend writing the query as:

SELECT p.name, COUNT(a.id) AS count
FROM portal p LEFT JOIN
     application a
     ON a.portal_id = p.id AND
        a.updated > '2011-08-20'
GROUP BY p.name;

Notes:

COUNT() does not return NULL values, so COALESCE() is unnecessary.
Use table aliases that are abbreviation of the table names.
You might want to also sort by p.id, unless you know that the names are unique.

How to get count of records as zero if records are not found with left join?

3 Answers3