SELECT
    f.province AS loc,
    IFNULL(COUNT(f.province), 0) AS count
FROM
    project_name_data d
RIGHT JOIN full_province f ON (
    TRIM(
        REPLACE (
            REPLACE (
                SUBSTRING_INDEX(d.location, '|', 1),
                '省',
                ''
            ),
            '壮族',
            ''
        )
    ) = f.province
)
GROUP BY
    loc;

这是我的查询sql，full\ u province是一个包含中国所有省份的表，project\ u name\ u data是指主数据。现在我要计算每个省有多少记录，没有记录的省给0。但是我的sql只返回包含记录的省份。里面有错误吗？
举个不恰当的例子：

-- main table
id   location      comment  other columns
1    Los Angeles   aaa        ...
2    New York      bbb        ...
3    Cambridge     ccc        ...
4    Philadelphia  ddd        ...
5    New York      eee        ...
6    Cambridge     fff        ...

--full_province table
id    location
1     Los Angeles
2     New York
3     Cambridge
4     Philadelphia
5     Beijing
6     Tokyo
7     Barcelona
8     Paris
9     Toronto

预期输出：

location      count
1     Los Angeles     1
2     New York        2
3     Cambridge       2
4     Philadelphia    1
5     Beijing         0
6     Tokyo           0
7     Barcelona       0
8     Paris           0
9     Toronto         0