SQL

БЛОК 3. JOIN — 12. JOIN — расширенные соединения

📚 25 вопросовПройти тест →
Лекция

БЛОК 3. JOIN — 12. JOIN — расширенные соединения

SQL

12. JOIN — расширенные соединения

🧭 Введение: когда базовых JOIN уже недостаточно

INNER JOIN и LEFT JOIN закрывают большую часть задач, но в реальных системах этого мало.
Приходится объединять данные симметрично, связывать таблицу саму с собой, генерировать комбинации и контролировать рост количества строк.
Именно здесь нужны расширенные соединения: RIGHT JOIN, FULL OUTER JOIN, SELF JOIN, CROSS JOIN.
Параллельно нужно понимать, почему появляются дубли и почему некоторые JOIN-запросы резко тормозят.
💡 Совет: Перед выбором типа JOIN фиксируйте две вещи: какую сторону нельзя потерять и какая ожидаемая кардинальность связи (1:1, 1:N, N:N).
✅ Вывод: Расширенные JOIN — это про точный контроль семантики результата и производительности, а не про «редкий синтаксис».

⚠️ Проблема -> решение

Типичная ситуация:
  1. разработчик выбирает тип JOIN «на глаз»,
  2. получает неожиданные дубли строк,
  3. сталкивается с деградацией производительности на больших таблицах.
Последствия:
  1. неверные метрики в отчётах,
  2. тяжёлые API-эндпоинты,
  3. сложный дебаг из-за скрытых ошибок кардинальности.
Решение:
  1. осознанно выбирать тип JOIN под бизнес-вопрос,
  2. проверять причины размножения строк,
  3. проектировать запрос с учётом индексов и объёма данных.
🟢 Если совсем просто: Сначала определяем правильный тип соединения, потом контролируем дубли, потом оптимизируем.
🎯 Как понять, что этап прошёл успешно: Вы можете объяснить и смысл результата, и почему запрос работает именно с такой скоростью.

🛠️ Чем помогает и как работает

Расширенные JOIN полезны в продакшене чаще, чем кажется:
  • сравнение двух справочников;
  • поиск «разрыва» между таблицами;
  • построение оргструктуры (сотрудник -> менеджер);
  • генерация матриц и комбинаций.
🟢 Если совсем просто: Каждый тип JOIN отвечает на свой вопрос о сопоставлении строк.
🎯 Как понять, что этап прошёл успешно: Вы можете по задаче заранее назвать нужный тип JOIN и риск по производительности.
Чем помогает:
  • RIGHT/FULL закрывают симметричные сверки данных;
  • SELF JOIN связывает сущности внутри одной таблицы;
  • CROSS JOIN формирует все комбинации;
  • анализ дублей и кардинальности защищает от ложных цифр.
Как это работает:
  • Шаг 1: определяем обязательную сторону данных.
  • Шаг 2: выбираем тип JOIN по семантике результата.
  • Шаг 3: проверяем кардинальность связи и риск размножения строк.
  • Шаг 4: ограничиваем объём данных фильтрами и индексами.
  • Шаг 5: валидируем метрики после соединения.
✅ Вывод: Правильный JOIN — это одновременно логика данных и инженерия производительности.

📚 Ключевые термины (простыми словами)

Перед практикой синхронизируем словарь.
🟢 Если совсем просто: Термины ниже описывают «какие строки остаются» и «почему их может стать слишком много».
🎯 Как понять, что этап прошёл успешно: Вы различаете смысл каждого JOIN и понимаете источники дублей.
  • RIGHT JOIN — сохраняет все строки правой таблицы.
  • FULL OUTER JOIN — сохраняет все строки обеих таблиц.
  • SELF JOIN — соединение таблицы самой с собой.
  • CROSS JOIN — декартово произведение: все комбинации строк двух таблиц.
  • Cardinality (кардинальность) — тип связи: 1:1, 1:N, N:N.
  • Row multiplication (размножение строк) — рост числа строк из-за множественных совпадений.
  • Join selectivity — насколько условие JOIN сужает набор строк.
  • Join cost — вычислительная стоимость соединения.
✅ Вывод: Эти понятия дают базу для корректных и быстрых расширенных JOIN-запросов.

↪️ 1. RIGHT JOIN: сохраняем правую таблицу

RIGHT JOIN симметричен LEFT JOIN: обязательно сохраняются строки справа.
На практике его часто переписывают в LEFT JOIN, просто меняя порядок таблиц.
🟢 Если совсем просто: RIGHT JOIN = «всё справа обязательно».
🎯 Как понять, что этап прошёл успешно: Вы можете переписать RIGHT JOIN в эквивалентный LEFT JOIN.
Назначение: Показать полный набор правой таблицы с опциональными данными слева.
Простыми словами: Если слева нет пары, поля слева станут NULL, но правая строка останется.
Для новичка: Если команда привыкла к LEFT JOIN, проще переворачивать таблицы и использовать его. В длинных запросах с 3-5 таблицами RIGHT JOIN часто ухудшает читаемость цепочки, потому что «база» визуально оказывается справа.
Аналогия: Это как LEFT JOIN, но вы смотрите на ситуацию «с другой стороны».
Пример:
SELECT  d.id AS department_id,  d.name AS department_name,  e.id AS employee_id,  e.full_nameFROM employees eRIGHT JOIN departments d  ON e.department_id = d.id;
🔎 Как это происходит на практике:
  • Контекст: HR хочет видеть все отделы, включая пустые.
  • Действия: базой делает departments через RIGHT JOIN.
  • Результат: отделы без сотрудников тоже в отчёте.
Характеристики:
  • эквивалентен перевёрнутому LEFT JOIN;
  • удобен для обратного взгляда на связь;
  • может ухудшать читаемость, если стиль команды строится вокруг LEFT JOIN.
Когда использовать: Когда правую таблицу нужно сохранить целиком.
✅ Вывод: RIGHT JOIN полезен, но часто лучше стандартизировать стиль на LEFT JOIN.

🧷 2. FULL OUTER JOIN: полная сверка двух таблиц

FULL OUTER JOIN возвращает:
  1. совпавшие строки,
  2. несопоставленные строки слева,
  3. несопоставленные строки справа.
Это ключевой инструмент для reconciliation-сценариев.
🟢 Если совсем просто: FULL OUTER JOIN = «покажи всё с обеих сторон».
🎯 Как понять, что этап прошёл успешно: Вы умеете находить «что есть только слева» и «что есть только справа».
Назначение: Сверить два набора данных и выявить расхождения.
Простыми словами: Ни одна строка не теряется: если пары нет, другая сторона будет NULL.
Для новичка: После FULL OUTER JOIN почти всегда полезно добавлять флаги расхождения.
Аналогия: Сравнение двух списков клиентов из разных систем.
Пример:
SELECT  COALESCE(a.id, b.external_user_id) AS key_id,  CASE    WHEN a.id IS NULL THEN 'only_in_billing'    WHEN b.external_user_id IS NULL THEN 'only_in_crm'    ELSE 'matched'  END AS diff_type,  a.email AS crm_email,  b.email AS billing_emailFROM crm_users aFULL OUTER JOIN billing_users b  ON b.external_user_id = a.id;
🔎 Как это происходит на практике:
  • Контекст: миграция между системами.
  • Действия: сверяют справочники через FULL OUTER JOIN.
  • Результат: видно, какие записи потеряны или не синхронизированы.
Характеристики:
  • сохраняет все строки обеих таблиц;
  • отлично подходит для аудита и reconciliation;
  • на больших наборах может быть тяжёлым.
Когда использовать: Когда нужно полное сравнение двух источников данных.
✅ Вывод: FULL OUTER JOIN — лучший выбор для задач сверки и поиска разрывов.

🪞 3. SELF JOIN: связь внутри одной таблицы

SELF JOIN нужен, когда строки одной таблицы связаны между собой.
Классический пример: сотрудник и его менеджер в одной таблице employees.
🟢 Если совсем просто: SELF JOIN = таблица соединяется сама с собой под разными алиасами.
🎯 Как понять, что этап прошёл успешно: Вы уверенно используете два алиаса одной таблицы и не путаете их роли.
Назначение: Вытащить иерархические или парные связи внутри одной сущности.
Простыми словами: Один алиас — сотрудник, второй — его руководитель.
Для новичка: Всегда давайте говорящие алиасы (e, m) и явно подписывайте поля.
Аналогия: Один и тот же справочник людей, но в разных ролях: подчинённый и начальник.
Пример:
SELECT  e.id AS employee_id,  e.full_name AS employee_name,  m.id AS manager_id,  m.full_name AS manager_nameFROM employees eLEFT JOIN employees m  ON m.id = e.manager_id;
🔎 Как это происходит на практике:
  • Контекст: оргструктура компании.
  • Действия: таблица employees присоединяется сама к себе.
  • Результат: в одной строке и сотрудник, и его менеджер.
Характеристики:
  • требует аккуратных алиасов;
  • часто используется в иерархиях;
  • легко ошибиться и соединить «не те роли».
Когда использовать: Когда связь «родитель -> потомок» хранится в одной таблице.
✅ Вывод: SELF JOIN решает иерархические задачи без дополнительной таблицы.

🧮 4. CROSS JOIN: все комбинации строк

CROSS JOIN строит декартово произведение: каждая строка слева соединяется с каждой строкой справа.
Это мощно, но потенциально очень дорого.
🟢 Если совсем просто: Если слева 100 строк и справа 200, результат CROSS JOIN даст 20 000 строк.
🎯 Как понять, что этап прошёл успешно: Вы заранее оцениваете размер результата и ограничиваете входные наборы.
Назначение: Генерировать матрицы, комбинации и сетки значений.
Простыми словами: CROSS JOIN создаёт все возможные пары.
Для новичка: Используйте CROSS JOIN только когда действительно нужна полная комбинация. В PostgreSQL часто используют generate_series() + CROSS JOIN LATERAL, чтобы генерировать контролируемые наборы.
Аналогия: Все размеры * все цвета товара = все варианты SKU.
Пример:
SELECT  s.size_code,  c.color_codeFROM product_sizes sCROSS JOIN product_colors c;
🔎 Как это происходит на практике:
  • Контекст: генерация каталога вариаций товара.
  • Действия: соединяют справочник размеров и цветов.
  • Результат: получают полный список SKU-кандидатов.
Характеристики:
  • количество строк = произведение размеров наборов;
  • без фильтров быстро становится тяжёлым;
  • требует строгого контроля входных таблиц.
Когда использовать: Когда по бизнес-логике нужны все комбинации.
✅ Вывод: CROSS JOIN полезен для матриц, но опасен без контроля объёма.

🧨 5. Почему появляются дубли строк после JOIN

Дубли после JOIN чаще всего не «баг SQL», а следствие кардинальности.
Если связь 1:N, строка слева повторится N раз.
Если связь N:N, рост может быть ещё сильнее.
🟢 Если совсем просто: JOIN меняет гранулярность результата: одна сущность может стать многими строками.
🎯 Как понять, что этап прошёл успешно: Перед агрегатами вы можете объяснить, почему строк стало больше.
Назначение: Предотвращать неверные метрики из-за размножения строк.
Простыми словами: 1 пользователь + 3 заказа = 3 строки, это ожидаемо.
Для новичка: Если нужен «один ряд на сущность», добавляйте агрегацию, подзапрос, DISTINCT ON или оконную логику.
Аналогия: Один клиент с несколькими чеками в выгрузке появится несколько раз.
Пример:
SELECT  u.id AS user_id,  o.id AS order_idFROM users uLEFT JOIN orders o  ON o.user_id = u.id;
🔎 Как это происходит на практике:
  • Контекст: аналитик считает пользователей через COUNT(*) после JOIN.
  • Действия: получает завышенную цифру из-за 1:N связи.
  • Результат: метрика исправляется на COUNT(DISTINCT u.id) или пересобранный запрос.
Характеристики:
  • рост строк при 1:N и N:N — нормален;
  • проблема возникает, когда не учтена гранулярность;
  • особенно критично для COUNT(*), SUM(...) и дашбордов.
Мини-шпаргалка: как получить 1 строку на сущность:
  • Агрегация: когда нужно посчитать метрики по сущности (например, заказы по пользователю).
  • Подзапрос/оконная логика: когда нужно выбрать одну «лучшую» строку (например, последний заказ пользователя).
  • EXISTS: когда нужен только факт наличия связанной записи без размножения строк.
Когда использовать: Всегда проверять кардинальность перед вычислением метрик.
✅ Вывод: Контроль кардинальности — обязательный шаг после любого нетривиального JOIN.

⚡ 6. Базовое влияние JOIN на производительность

JOIN влияет на производительность через объём данных, кардинальность и индексы.
Даже логически правильный запрос может быть дорогим, если соединяет большие таблицы без селективных условий.
🟢 Если совсем просто: Чем больше строк участвует в JOIN, тем дороже запрос.
🎯 Как понять, что этап прошёл успешно: Вы умеете назвать минимум 3 практики ускорения JOIN-запроса.
Назначение: Сделать JOIN-запросы предсказуемыми по времени выполнения.
Простыми словами: Сначала режем объём, потом соединяем, потом считаем.
Для новичка: Проверьте индексы на колонках связи (FK, PK) и на частых фильтрах.
Аналогия: Это как сортировка документов: быстрее сначала отобрать нужные папки, потом сравнивать.
Пример:
SELECT  o.id AS order_id,  u.emailFROM orders oINNER JOIN users u  ON u.id = o.user_idWHERE o.created_at >= DATE '2026-01-01'  AND o.created_at < DATE '2026-02-01'  AND o.status = 'paid';
🔎 Как это происходит на практике:
  • Контекст: API со списком заказов стал медленным.
  • Действия: добавляют фильтры до JOIN и индексы на ключи связи.
  • Результат: запрос сканирует меньше строк и стабилизирует время ответа.
Характеристики:
  • индексы на ключах JOIN почти обязательны;
  • ранняя фильтрация уменьшает стоимость соединения;
  • функции и приведения типов в ON (например, lower(email) или id::text) могут мешать использованию индекса;
  • SELECT * увеличивает сетевой и CPU-расход.
Когда использовать: Всегда при работе с большими таблицами и отчётными JOIN-запросами.
✅ Вывод: Быстрый JOIN — это комбинация правильной логики и дисциплины по объёму данных.

🆚 Сравнение: расширенные JOIN

Тип JOINЧто сохраняетГлавный кейсРиск
RIGHT JOINВсе строки справаСверка «с правой стороны»Читаемость в командах, где стандарт LEFT
FULL OUTER JOINВсе строки обеих таблицПолная сверка источниковВысокая стоимость на больших данных
SELF JOINЗависит от типа (INNER/LEFT)Иерархии внутри одной таблицыПутаница алиасов и ролей
CROSS JOINВсе комбинацииМатрицы/вариацииВзрывной рост числа строк
✅ Вывод: Выбор расширенного JOIN всегда привязан к задаче и ожидаемой кардинальности.

🧠 Must-Know (запомнить)

  • RIGHT JOIN можно переписать в LEFT JOIN, поменяв стороны.
  • FULL OUTER JOIN нужен для сверки и поиска расхождений.
  • SELF JOIN требует чётких алиасов ролей.
  • CROSS JOIN быстро увеличивает число строк (N*M).
  • Дубли после JOIN чаще всего связаны с кардинальностью, а не с ошибкой SQL.
  • Для метрик после JOIN проверяйте гранулярность результата.
  • Производительность JOIN зависит от объёма, индексов и селективности фильтров.
✅ Вывод: Если помнить эти 7 правил, расширенные JOIN становятся контролируемым инструментом, а не источником сюрпризов.

❌ Частые мифы

❌ Миф: RIGHT JOIN — это отдельная магия, не связанная с LEFT JOIN. ✅ Как правильно: это симметричная форма LEFT JOIN; обычно можно переписать запрос и сохранить смысл. 📎 Почему это важно: так проще стандартизировать стиль SQL в команде.
❌ Миф: FULL OUTER JOIN нужен только в учебниках. ✅ Как правильно: это рабочий инструмент сверки и reconciliation между системами. 📎 Почему это важно: без него сложно увидеть полную картину «только слева / только справа».
❌ Миф: дубли после JOIN означают баг в СУБД. ✅ Как правильно: чаще это естественный эффект 1:N или N:N связи. 📎 Почему это важно: иначе можно неверно интерпретировать метрики и сломать отчёты.
❌ Миф: если SQL корректный, производительность JOIN не проблема. ✅ Как правильно: JOIN может быть очень дорогим без индексов и ранней фильтрации. 📎 Почему это важно: производительность влияет на SLA API и UX.

🎤 Часто спрашивают на собеседованиях

❓ Вопрос: Когда использовать RIGHT JOIN, если есть LEFT JOIN? ✅ Ответ: обычно RIGHT JOIN применяют реже; чаще переписывают в LEFT JOIN для единообразия, меняя порядок таблиц.
❓ Вопрос: Что возвращает FULL OUTER JOIN? ✅ Ответ: все совпавшие строки плюс строки без пары с обеих сторон (NULL на противоположной стороне).
❓ Вопрос: Для чего нужен SELF JOIN? ✅ Ответ: для связей внутри одной таблицы, например «сотрудник -> менеджер» или «категория -> родительская категория».
❓ Вопрос: Чем опасен CROSS JOIN? ✅ Ответ: он создаёт декартово произведение и может резко увеличить число строк и стоимость запроса.
❓ Вопрос: Почему после JOIN появляются дубли? ✅ Ответ: из-за кардинальности связи (1:N, N:N), когда одной строке слева соответствуют несколько строк справа.
❓ Вопрос: Какие базовые шаги ускоряют JOIN? ✅ Ответ: индексы на ключах связи, ранние фильтры, отказ от SELECT *, контроль кардинальности до агрегаций.

🚨 Типичные ошибки

  • Использовать RIGHT JOIN, хотя команда договорилась о стиле через LEFT JOIN.
  • Применять FULL OUTER JOIN без реальной необходимости полной сверки.
  • Путать алиасы в SELF JOIN и получать логически неверные пары.
  • Запускать CROSS JOIN на больших таблицах без ограничений.
  • Считать метрики после JOIN без учёта размножения строк.
  • Использовать функции/касты прямо в ON и терять индексный план.
  • Игнорировать индексы на колонках соединения.
  • Прятать тяжёлый JOIN за SELECT * и удивляться медленной выдаче.
✅ Вывод: Большинство проблем в расширенных JOIN предсказуемы и решаются дисциплиной проектирования запроса.

✅ Best Practices

  • Стандартизируйте стиль JOIN в команде (LEFT JOIN как базовый при необходимости).
  • Для сверок явно маркируйте строки «только слева / только справа».
  • В SELF JOIN используйте говорящие алиасы (child, parent, employee, manager).
  • Перед CROSS JOIN всегда оценивайте ожидаемое число строк.
  • Проверяйте кардинальность до расчёта агрегатов.
  • Старайтесь сравнивать в ON поля одного типа и без функций над колонками.
  • Ставьте индексы на ключи JOIN и часто используемые фильтры.
  • В проде используйте EXPLAIN для тяжёлых соединений.
✅ Вывод: Расширенные JOIN дают мощные возможности, если сочетать правильную семантику с контролем объёма и плана выполнения.

🏁 Заключение

Расширенные соединения нужны не только «для редких кейсов», а для зрелой инженерной работы с данными.
RIGHT, FULL OUTER, SELF и CROSS JOIN позволяют решать задачи сверки, иерархий и генерации комбинаций.
Если вы контролируете кардинальность, причины дублей и базовые аспекты производительности, JOIN-запросы становятся надёжными и предсказуемыми.
✅ Вывод: Сильный инженер владеет не одним JOIN, а системой выбора соединения под бизнес-смысл и нагрузку.
🎯

Проверьте знания

Закрепите материал — пройдите тест по теме «БЛОК 3. JOIN — 12. JOIN — расширенные соединения»

Пройти тест →