Как правильно указать условия объединения таблиц для поиска пользователей по локации

Что проверяют

Понимаете ли вы разницу между условием в ON и в WHERE и умеете ли выбрать тип соединения под задачу. Почти всегда следом спрашивают, что станет с LEFT JOIN, если фильтр по правой таблице перенести в WHERE.

Как отвечать

Базовый запрос: SELECT u.id, u.name FROM users u JOIN locations l ON l.id = u.location_id WHERE l.city = %s. В ON пишут условие связи по ключам, в WHERE фильтр результата. Для INNER JOIN это вопрос читаемости, планировщик обработает оба варианта одинаково.

Дальше по типам соединений:

• LEFT JOIN: фильтр по правой таблице в ON сохраняет всех пользователей, у несовпавших поля локации будут NULL. Тот же фильтр в WHERE отбрасывает эти строки и фактически превращает соединение во внутреннее. Пользователей без локации ищут через WHERE l.id IS NULL;
• один-ко-многим: если у пользователя несколько адресов через таблицу user_locations, JOIN размножит строки. Для условия «есть хотя бы один адрес в городе» используют WHERE EXISTS (SELECT 1 FROM user_locations ul ...), а не DISTINCT поверх результата;
• индексы: PostgreSQL сам индексирует первичный ключ, но не внешний, поэтому индекс на users.location_id создают руками. Плюс индекс на locations.city, если по нему фильтруют.

В Python значение передают параметром: %s в psycopg, bind-параметры в SQLAlchemy, никаких f-строк. В ORM: Django User.objects.filter(location__city=city) строит INNER JOIN, SQLAlchemy 2.0 select(User).join(User.location).where(Location.city == city). По связи «многие» Django тоже вернёт дубли, тогда помогает Exists() или .distinct().

Поиск по радиусу решают уже не JOIN, а PostGIS: ST_DWithin по geography с GiST-индексом.

Где спотыкаются

Пишут LEFT JOIN, а условие на город ставят в WHERE. Запрос выглядит как «все пользователи», а возвращает только совпавших.

Получают дубли от связи один-ко-многим и лечат их DISTINCT. Список выглядит правильно, но агрегаты в том же запросе уже посчитаны по раздутому набору.

Сравнивают города строкой из пользовательского ввода: «Москва», «москва» и «г. Москва» дают три разных результата. Надёжнее фильтровать по id локации из справочника.