INNER JOIN в SQL: примеры, потеря строк, дубли и три таблицы

Коротко

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

• JOIN без слова INNER — то же самое соединение: INNER подразумевается по умолчанию.
• Строки пропадают, когда в другой таблице нет нужного ключа или ключ равен NULL. Ошибки при этом нет, меняется только число строк.
• Если на один ключ приходится несколько строк, каждая даёт свою пару. Строк становится больше, а SUM и COUNT(*) по «размноженной» стороне завышаются.
• Три таблицы и больше соединяют цепочкой: каждый следующий JOIN присоединяется к уже собранному результату.
• Для INNER JOIN условие в ON и то же условие в WHERE дают одинаковый результат. Для LEFT JOIN — разный.
• Нужны все строки одной из таблиц, даже без пары, — это задача для LEFT JOIN.

Что делает INNER JOIN: пример на двух таблицах

В таблице payments учебной базы лежат платежи: номер, пользователь, дата, сумма. Канала привлечения там нет, он записан в users. Чтобы увидеть платёж и канал в одной строке, таблицы соединяют по общему столбцу user_id.

База берёт строку из payments, ищет в users строки, для которых условие u.user_id = p.user_id истинно, и склеивает каждую найденную пару в одну строку результата. В списке SELECT после этого доступны столбцы обеих таблиц. Чтобы не путаться, откуда какой столбец, таблицам дают короткие алиасы — p и u — и пишут их перед именем столбца.

В примере шесть первых платежей. У каждого платежа ровно один пользователь, поэтому строк в результате столько же, сколько платежей: шесть. Это самый спокойный случай соединения — «многие к одному»: к таблице фактов присоединили справочник, число строк не изменилось.

select
  p.payment_id,
  p.paid_at,
  p.amount,
  u.user_id,
  u.channel,
  u.signup_date
from payments as p
inner join users as u on u.user_id = p.user_id
where p.payment_id <= 6
order by p.payment_id;

JOIN и INNER JOIN: есть ли разница

Разницы нет. По стандарту SQL слово INNER необязательное, и JOIN без уточнения — это внутреннее соединение. Так работают PostgreSQL, MySQL, SQL Server, SQLite и DuckDB. Писать ли INNER, решает договорённость в команде: одни пишут ради явности, другие экономят слово. Главное — не смешивать оба варианта в одном запросе, иначе читатель начнёт искать смысл в различии, которого нет.

Есть ещё две записи того же соединения. USING (user_id) — сокращение для случая, когда ключ в обеих таблицах называется одинаково. И старая форма через запятую: таблицы перечислены в FROM, а условие связи стоит в WHERE. Она до сих пор встречается в старом коде и учебниках. Работает так же, но пользоваться ею не стоит: условие связи теряется среди фильтров, а если его забыть, база молча соединит каждую строку с каждой.

Запрос ниже считает строки всеми четырьмя способами и возвращает четыре одинаковых числа — 1 251, по числу платежей.

select
  (select count(*)
   from payments as p
   inner join users as u on u.user_id = p.user_id) as inner_join_rows,
  (select count(*)
   from payments as p
   join users as u on u.user_id = p.user_id) as join_rows,
  (select count(*)
   from payments as p
   join users as u using (user_id)) as using_rows,
  (select count(*)
   from payments as p, users as u
   where u.user_id = p.user_id) as comma_rows;

Какие строки пропадают при INNER JOIN

Вернёмся к отчёту по странам. В users страна записана кодом: RU, KZ, AM, BY. Названия берут из справочника. В учебной базе такой таблицы нет, поэтому соберём её прямо в запросе, в CTE countries, и нарочно сделаем неаккуратной, какими справочники и бывают: Армению забыли, зато есть Грузия, откуда пользователей пока нет, и строка «Не указана» с пустым кодом.

До соединения в users 4 613 строк, в справочнике пять. После — 4 041 строка и три страны. Пропали строки с обеих сторон. Слева — 572 пользователя: у 517 код AM, которого в справочнике нет, у 55 страна не заполнена. Справа — Грузия, потому что с кодом GE никто не зарегистрирован, и строка «Не указана».

Последняя потеря самая неочевидная. Кажется, что пользователи с пустой страной должны найти свою строку «Не указана»: NULL слева, NULL справа. Но сравнение NULL = NULL даёт не истину, а NULL, и ON такую пару не принимает. Ключ со значением NULL в INNER JOIN не соединяется ни с чем, в том числе с другим NULL.

Потерянные строки находят тем же соединением, только внешним: LEFT JOIN оставляет пользователей без пары, а условие c.country_name is null отбирает именно их. Второй запрос показывает, сколько людей выпало и с какими кодами. Этот приём подробно разобран в статье про LEFT JOIN.

with countries (country, country_name) as (
  values
    ('RU', 'Россия'),
    ('KZ', 'Казахстан'),
    ('BY', 'Беларусь'),
    ('GE', 'Грузия'),
    (null, 'Не указана')
)
select
  (select count(*) from users) as users_before,
  (select count(*) from countries) as countries_before,
  count(*) as rows_after,
  count(distinct c.country_name) as countries_after
from users as u
inner join countries as c on c.country = u.country;

with countries (country, country_name) as (
  values
    ('RU', 'Россия'),
    ('KZ', 'Казахстан'),
    ('BY', 'Беларусь'),
    ('GE', 'Грузия'),
    (null, 'Не указана')
)
select
  u.country,
  count(*) as lost_users
from users as u
left join countries as c on c.country = u.country
where c.country_name is null
group by u.country
order by lost_users desc;

Почему после INNER JOIN появляются дубли

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

В учебной базе у пользователя одна подписка, а платежей может быть несколько: первый и продления. Соединим subscriptions с payments и посмотрим на четыре первые подписки. В результате девять строк: подписка 1 встретилась один раз, подписка 2 — дважды, подписки 3 и 4 — по три раза. Вместе с ними повторилась и цена тарифа monthly_price.

Называть это дублями не совсем точно: строки разные, у каждой свой payment_id. Изменилось зерно результата — то, что означает одна строка. До соединения строка была подпиской, после стала платежом по подписке. Всё, что относилось к подписке, теперь записано столько раз, сколько у неё платежей. Такая связь называется «один ко многим», и подробно о зерне рассказывает статья «JOIN без потерь и дублей».

select
  s.subscription_id,
  s.user_id,
  s.plan,
  s.monthly_price,
  p.payment_id,
  p.paid_at,
  p.payment_type
from subscriptions as s
inner join payments as p on p.user_id = s.user_id
where s.subscription_id <= 4
order by s.subscription_id, p.paid_at;

Как размножение строк ломает SUM и COUNT

Пока вы просто смотрите на строки, повторы безвредны. Проблема начинается с агрегатов. Допустим, нужна месячная выручка по активным подпискам, MRR: сумма monthly_price по тарифам. Заодно хочется видеть, сколько платежей уже прошло, поэтому в запрос добавили соединение с payments.

Запрос ниже выполняется и возвращает правдоподобную таблицу: 790 подписок и MRR 19 350. На самом деле активных подписок 515, а их MRR — 12 595. Оба числа завышены в полтора раза: цена каждой подписки попала в сумму столько раз, сколько у неё платежей, и count(*) посчитал платежи, а не подписки.

Счётчик можно спасти через count(distinct s.subscription_id) — он вернёт 290, 169 и 56. С суммой так не получится: sum(distinct s.monthly_price) сложит разные цены по одному разу и выдаст по тарифу цену одной подписки. Надёжный способ один: привести правую таблицу к одной строке на ключ до соединения. В исправленном запросе платежи сначала сворачиваются до пользователя в CTE paid, и связь становится «один к одному». Подписок 290, 169 и 56, MRR 5 510, 4 901 и 2 184, а число платежей лежит в отдельном столбце и ничего не раздувает.

select
  s.plan,
  count(*) as subscriptions,
  sum(s.monthly_price) as mrr
from subscriptions as s
inner join payments as p on p.user_id = s.user_id
where s.status = 'active'
group by s.plan
order by s.plan;

with paid as (
  select
    user_id,
    count(*) as payments
  from payments
  group by user_id
)
select
  s.plan,
  count(*) as subscriptions,
  sum(s.monthly_price) as mrr,
  sum(pd.payments) as payments
from subscriptions as s
inner join paid as pd on pd.user_id = s.user_id
where s.status = 'active'
group by s.plan
order by s.plan;

INNER JOIN нескольких таблиц: соединяем три

Соединений в запросе может быть сколько угодно. Они выполняются цепочкой: первый JOIN собирает результат из двух таблиц, второй присоединяет третью уже к этому результату. В условии ON каждого следующего соединения можно ссылаться на любую таблицу, которая стоит выше.

Задача: выручка по каналам привлечения с разбивкой по текущему статусу подписки. Суммы лежат в payments, канал — в users, статус — в subscriptions. Начинаем с таблицы фактов, payments, и присоединяем к ней две таблицы, в каждой из которых на пользователя одна строка. Оба соединения — «многие к одному», поэтому число строк не меняется: 1 251 платёж до соединений и столько же после. Проверить легко — сложите столбец payments в результате.

Результат сам по себе любопытный. В paid_search из 86 плательщиков активная подписка осталась у восьми, а 48 ушли. В referral картина обратная: 191 активный и 23 ушедших. По одной таблице этого не увидеть.

Порядок таблиц в цепочке INNER JOIN на результат не влияет, но на читаемость — очень. Удобное правило: в FROM стоит таблица, строки которой вы считаете, а дальше идут справочники к ней. Если же в цепочке оказались две таблицы «многие» — например, платежи и события одного пользователя, — их строки перемножатся, и лечится это той же свёрткой в CTE. Когда таблицу нужно соединить с самой собой, получается self join.

select
  u.channel,
  s.status,
  count(*) as payments,
  count(distinct p.user_id) as payers,
  sum(p.amount) as revenue
from payments as p
inner join users as u on u.user_id = p.user_id
inner join subscriptions as s on s.user_id = p.user_id
group by u.channel, s.status
order by u.channel, s.status;

INNER JOIN ON с несколькими условиями

В ON стоит не обязательно одно равенство. Это обычное логическое выражение, и условия в нём соединяют через AND. Парой считаются только те строки, для которых истинно всё выражение целиком.

Несколько условий нужны в двух случаях. Первый — составной ключ: строку определяет не один столбец, а сочетание, например пользователь и дата. Второй — когда из нескольких строк справа нужна одна определённая. Возьмём те же подписки и платежи. Соединение по одному user_id даёт все платежи пользователя. Добавим второе условие — дата платежа равна дате начала подписки — и получим только первый платёж. Связь снова «один к одному»: 912 подписок, 912 строк, по тарифам 514, 296 и 102.

Условие не обязано быть равенством. p.paid_at > s.started_at оставит только продления, а сравнение дат с интервалом — платежи в первые тридцать дней. Соединения по неравенству размножают строки охотнее, чем по ключу, поэтому подсчёт до и после здесь обязателен.

select
  s.plan,
  count(*) as subscriptions,
  sum(p.amount) as first_payments
from subscriptions as s
inner join payments as p
  on p.user_id = s.user_id
 and p.paid_at = s.started_at
group by s.plan
order by s.plan;

Условие в ON или в WHERE

Частый вопрос: куда писать фильтр вроде u.channel = 'paid_search' — в ON или в WHERE? Для INNER JOIN результат одинаковый. В ON условие не даёт паре образоваться. В WHERE пара сначала образуется, а потом отбрасывается. Строка, не прошедшая условие, в результат не попадает в обоих случаях, потому что INNER JOIN строки без пары не хранит.

На учебной базе оба варианта возвращают 103 строки — платежи пользователей из paid_search. Раз результат один, выбирают по читаемости: в ON оставляют то, что описывает связь таблиц, в WHERE — фильтры отчёта. Тогда запрос читается в два шага: как таблицы соединены и что из соединённого нужно.

С LEFT JOIN та же перестановка меняет ответ. Условие в ON решает только, что считать парой: платёж пользователя из другого канала остаётся в результате с NULL на месте столбцов users, и строк получается 1 251, все платежи. Условие в WHERE применяется после соединения и убирает строки с NULL: остаётся 103, и LEFT JOIN превращается в INNER. Поэтому привычку «фильтр в WHERE» нельзя механически переносить на внешние соединения. Разбор с примерами — в статье про LEFT JOIN.

select
  (select count(*)
   from payments as p
   inner join users as u
     on u.user_id = p.user_id
    and u.channel = 'paid_search') as inner_on,
  (select count(*)
   from payments as p
   inner join users as u on u.user_id = p.user_id
   where u.channel = 'paid_search') as inner_where,
  (select count(*)
   from payments as p
   left join users as u
     on u.user_id = p.user_id
    and u.channel = 'paid_search') as left_on,
  (select count(*)
   from payments as p
   left join users as u on u.user_id = p.user_id
   where u.channel = 'paid_search') as left_where;

Виды JOIN в SQL: чем INNER отличается от остальных

Виды JOIN различаются одним: что происходит со строкой, которой не нашлось пары. INNER JOIN её отбрасывает. Внешние соединения — LEFT, RIGHT и FULL — сохраняют и ставят NULL на месте недостающих столбцов. CROSS JOIN условия не имеет вовсе и соединяет каждую строку с каждой.

Выбор между INNER и LEFT — это вопрос о том, чьи строки терять нельзя. Считаете выручку по каналам — подойдёт INNER JOIN: платёж без пользователя невозможен, а пользователи без платежей в сумму ничего не добавят. Считаете долю платящих — нужен LEFT JOIN от users: неплатившие должны остаться в знаменателе, иначе конверсия окажется равна 100%. Если не уверены, начните с LEFT JOIN и посмотрите, есть ли строки с NULL справа. Нет — соединения дадут одинаковый результат.

Иногда соединение не нужно вовсе. Вопрос «у каких пользователей был хотя бы один платёж» — это проверка существования, а не склейка столбцов. Её пишут через EXISTS или IN: пользователь попадёт в результат один раз, сколько бы платежей у него ни было.

Карта ниже — оглавление, а не разбор. Числа строк для каждого вида на одних и тех же таблицах есть в обзорной статье «JOIN в SQL: INNER, LEFT и FULL».

Частые вопросы про INNER JOIN

Что попробовать на учебной базе

Все запросы из статьи выполняются в песочнице симулятора SQL-аналитика как есть: скопируйте блок и запустите. Три упражнения, чтобы закрепить.

Если хочется больше практики, на сайте есть задачи по SQL с проверкой ответа, в том числе на соединения, и редактор SQL онлайн с готовой базой и примерами запросов. В курсе «SQL с нуля» соединениям отведён модуль «Соединения таблиц: JOIN» из четырёх уроков, первый из них — про INNER JOIN. Первые 10 уроков курса бесплатно; модуль о соединениях идёт после них.

• В запросе со справочником стран добавьте в CTE строку ('AM', 'Армения') и сравните rows_after: должно получиться 4 558. Оставшиеся 55 — пользователи с пустой страной, и строкой справочника их не вернуть.
• В запросе с двумя условиями в ON замените p.paid_at = s.started_at на p.paid_at > s.started_at. Останутся только продления: в сумме по тарифам 339 строк.
• Соедините три таблицы: experiment_exposures, users и payments — и посчитайте платежи по вариантам эксперимента. Должно получиться 401 в checklist и 438 в control.