SQL INNER JOIN: объединение совпадающих строк

Урок 5.2 · Время чтения: ~6 мин · Задачи для практики ↓

INNER JOIN в SQL объединяет строки двух таблиц и возвращает только те пары, для которых истинно условие соединения, то есть строки, у которых есть совпадение в обеих таблицах. В этом уроке вы изучите синтаксис INNER JOIN, узнаете, зачем нужны псевдонимы таблиц, как соединить три таблицы и что происходит со строками без совпадения.

В предыдущем уроке вы узнали, почему данные разнесены по разным таблицам и как ключи их связывают. INNER JOIN - самый распространённый способ собрать данные обратно: город с его страной, платёж с клиентом, актёра с его фильмами.

Что возвращает INNER JOIN

INNER JOIN проверяет условие соединения для пар строк из двух таблиц и оставляет только пары, для которых оно истинно.

• Строка первой таблицы, у которой нет подходящей строки во второй таблице, в результат не попадает.
• Строка второй таблицы без совпадения тоже не попадает.
• Строка с несколькими совпадениями появляется несколько раз - по разу на каждое совпадение.

Поэтому INNER JOIN часто описывают как пересечение двух таблиц.

Базовый синтаксис

SELECT table1.column1, table2.column2
FROM table1
INNER JOIN table2 ON table1.key_column = table2.key_column;

• INNER JOIN указывает вторую таблицу.
• ON задаёт условие соединения, обычно «внешний ключ = первичный ключ».
• Ключевое слово INNER необязательно: просто JOIN означает INNER JOIN.

Соединение двух таблиц

В таблице city хранится country_id - ссылка на таблицу country. Чтобы увидеть название страны рядом с каждым городом:

SELECT city.city, country.country
FROM city
INNER JOIN country ON city.country_id = country.country_id
ORDER BY country.country, city.city;

Результат: 600 городов, у каждого - название его страны.

Если в обеих таблицах есть столбец с одинаковым именем, как здесь country_id, перед ним нужно указать имя таблицы, иначе база данных сообщит о неоднозначном столбце.

Псевдонимы таблиц

Длинные имена таблиц затрудняют чтение соединений. Псевдоним таблицы - это короткое имя, которое задаётся после таблицы:

SELECT ci.city, co.country
FROM city AS ci
INNER JOIN country AS co ON ci.country_id = co.country_id
ORDER BY co.country, ci.city;

Результат тот же. Если у таблицы есть псевдоним, используйте его везде в запросе. Выбирайте короткие, но понятные псевдонимы: ci и co читать легче, чем a и b.

Соединение трёх таблиц

Актёр может сниматься во многих фильмах, а у фильма много актёров. Такая связь «многие ко многим» хранится в связующей таблице: film_actor содержит пары (actor_id, film_id). Чтобы получить актёров с названиями фильмов, соедините все три таблицы:

SELECT a.first_name, a.last_name, f.title
FROM actor AS a
INNER JOIN film_actor AS fa ON fa.actor_id = a.actor_id
INNER JOIN film AS f ON f.film_id = fa.film_id
ORDER BY a.last_name, a.first_name, f.title
LIMIT 10;

Результат: первые 10 пар «актёр - фильм» в алфавитном порядке актёров.

1. Первое соединение добавляет к каждому актёру его строки из film_actor.
2. Второе соединение добавляет к каждой из этих строк фильм.
3. Каждый JOIN работает с результатом предыдущего, поэтому можно соединить столько таблиц, сколько нужно.

JOIN вместе с WHERE и ORDER BY

После соединения идут уже знакомые предложения. Например, фильмы с Ником Уолбергом:

SELECT f.title, f.release_year, f.rating
FROM actor AS a
INNER JOIN film_actor AS fa ON fa.actor_id = a.actor_id
INNER JOIN film AS f ON f.film_id = fa.film_id
WHERE a.first_name = 'NICK'
  AND a.last_name = 'WAHLBERG'
ORDER BY f.title;

Результат: названия, годы выпуска и рейтинги всех фильмов с этим актёром.

Строки без совпадения пропадают

INNER JOIN молча отбрасывает строки без совпадения. В Sakila 1000 фильмов, но копии в таблице inventory есть только у 958 из них:

SELECT f.title, i.inventory_id, i.store_id
FROM film AS f
INNER JOIN inventory AS i ON i.film_id = f.film_id
ORDER BY f.title
LIMIT 10;

Результат: фильмы с их копиями; 42 фильма без копий вообще не появляются, а фильм с несколькими копиями появляется несколько раз.

Если строки без совпадения должны остаться в результате, используйте LEFT JOIN, который вы изучите в следующем уроке.

Ключевые выводы:

• INNER JOIN возвращает только пары строк, удовлетворяющие условию ON.
• Строки без совпадения в другой таблице отбрасываются; строки с несколькими совпадениями повторяются.
• JOIN и INNER JOIN - одно и то же.
• Используйте псевдонимы таблиц и указывайте таблицу перед столбцами, которые есть в обеих таблицах.
• Объединяйте несколько JOIN подряд, чтобы пройти через связующие таблицы вроде film_actor.

Часто задаваемые вопросы

Чем отличаются JOIN и INNER JOIN?

Ничем. JOIN без указания типа - это INNER JOIN во всех основных СУБД. Явное INNER JOIN делает намерение понятнее, когда в запросе смешаны разные типы соединений.

Почему INNER JOIN возвращает повторяющиеся строки?

Потому что у строки несколько совпадений в другой таблице. Фильм с четырьмя копиями в inventory появится четыре раза после соединения film и inventory. Это нормальное поведение соединения; если нужна одна строка на фильм, используйте агрегацию или фильтр.

Можно ли соединить больше двух таблиц в SQL?

Да. Добавляйте по одному JOIN ... ON ... на каждую таблицу. Каждое соединение подключает новую таблицу к уже соединённым.