mysql找不到记录提示,如果找不到记录，MySQL计数返回零

I have a two tables.

cities - id_city, city_name

properties - id_property, id_city, property_name

I want to display cities.city_name and next to it [properties.count(id_city)]

How do I make a query that still returns zero if no records are found instead of NULL, so that I get results like this:

London [123]

New York [0]

Berlin [11]

where "New York" is [0], not NULL and not 1?

解决方案

Use an outer join:

select cities.city_name, count(properties.id_city)

from cities left join properties on cities.id_city = properties.id_city

group by 1