Получить перечень всех городов с числом их пользователей, включая города без пользователей

Условие

Вывести список всех городов вместе с количеством связанных с ними пользователей, при этом отобразить также те города, где пользователей нет.

-- Таблица "cities"
create table cities (
    id    serial primary key,
    name  text not null
);

insert into cities (name)
values
    ('Москва'),
    ('Санкт-Петербург'),
    ('Краснодар');

-- Таблица "users"
create table users (
    id      serial primary key,
    name    text not null,
    city_id int not null references cities(id)
);

insert into users (name, city_id)
values
    ('Иван', 1),
    ('Анна', 1),
    ('Олег', 2);

Ответ

Нужен LEFT JOIN от cities к users и COUNT(u.id). Так все города сохранятся, а отсутствие пользователей даст ноль.

Запрос:

SELECT c.id, c.name, COUNT(u.id) AS user_count
FROM cities AS c
LEFT JOIN users AS u ON u.city_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

Результат примера: Москва 2, Санкт-Петербург 1, Краснодар 0. COUNT(*) ошибочно посчитал бы одну строку внешнего соединения для пустого города. Группировка по id не объединяет разные города с одинаковыми названиями.