Клиенты и их города

Задача

Маркетолог планирует региональную рассылку: «Мне нужны клиенты с их городами. В таблице клиентов города нет — там какой-то address_id. Разберись и собери список: имя, фамилия, город. Первые 20 по алфавиту городов хватит для примера».

Верни имя, фамилию клиента и название его города.

Схема данных

Учебная база проката dvdrental. Адрес вынесен в отдельные таблицы (это нормализация — чтобы не дублировать город в каждой строке клиента):

• customer(customer_id, first_name, last_name, address_id, ...) — клиенты; хранят ссылку на адрес.
• address(address_id, address, city_id, ...) — адреса; знают свой город по city_id.
• city(city_id, city, ...) — справочник городов.

Решение

SELECT c.first_name, c.last_name, ci.city
FROM customer c
JOIN address a ON a.address_id = c.address_id
JOIN city ci ON ci.city_id = a.city_id
ORDER BY ci.city, c.last_name, c.first_name, c.customer_id
LIMIT 20;

Разбор

Город лежит не в таблице клиента, а через два справочника — так спроектированы почти все реальные базы. Это нормализация: город хранится один раз в city, адрес ссылается на него по city_id, а клиент ссылается на адрес по address_id. Плюс — нет дублей и рассинхрона (переименовали город один раз, и он поменялся везде). Минус — чтобы показать город рядом с клиентом, приходится «разворачивать» цепочку джойнами.

Идём по ссылкам: customer.address_id → address.address_id, затем address.city_id → city.city_id. Каждое звено — обычный INNER JOIN; поскольку у каждого клиента адрес заполнен, ни одна строка не теряется. Здесь я пишу условие через ON a.address_id = c.address_id явно, а не USING, — так нагляднее видно направление связи, хотя USING (address_id) дал бы тот же результат.

Про сортировку: ORDER BY ci.city ставит города по алфавиту, но внутри одного города клиенты без второго ключа встали бы в произвольном порядке. Добавляю фамилию, имя и уникальный customer_id — теперь вывод детерминирован, и LIMIT 20 каждый раз отрезает одну и ту же двадцатку. Про сам механизм соединения таблиц и его виды — в вопросе виды JOIN и их различия.

Если дальше захочется сгруппировать клиентов по городам и посчитать, где их больше, — это уже GROUP BY поверх такого же джойна, смотри задачу топ городов по числу клиентов.

Ожидаемый результат

first_name | last_name | city
--- | --- | ---
Julie | Sanchez | A Corua (La Corua)
Peggy | Myers | Abha
Tom | Milner | Abu Dhabi
Glen | Talbert | Acua
Larry | Thrasher | Adana
Sean | Douglass | Addis Abeba
Ella | Oliver | Aden
Adam | Gooch | Adoni
Sherri | Rhodes | Ahmadnagar
Julian | Vest | Akishima
… всего строк: 20 (показаны первые 10)