FULL OUTER JOIN и CROSS JOIN

FULL OUTER JOIN и CROSS JOIN

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

FULL OUTER JOIN: объединение двух таблиц полностью

FULL OUTER JOIN (или FULL JOIN) возвращает все строки из обеих таблиц. Там, где совпадение нашлось, строки объединяются. Там, где строка из одной таблицы не нашла пары в другой — ставятся NULL для колонок таблицы без пары.

SELECT c.name, o.product
FROM   customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

Таблица customers — 3 клиента. Таблица orders — 3 заказа, плюс один с несуществующим клиентом:

name         | product
-------------+-----------
Анна Иванова | Ноутбук
Анна Иванова | Наушники
Борис Петров | Мышь
Вера Сидорова| NULL        ← клиент без заказов
NULL         | Загадочный  ← заказ без клиента

Оба «сиротских» случая попали в результат: и клиент без заказов (Вера), и заказ без клиента.

Когда нужен FULL OUTER JOIN

FULL OUTER JOIN встречается редко — в специфических аналитических задачах:

Сравнение двух списков. Проверить, есть ли записи в A, которых нет в B, и наоборот. Например, сравнить список сотрудников в двух системах:

SELECT a.employee_id, a.name AS hr_name,
       b.name AS payroll_name
FROM   hr_employees     a
FULL OUTER JOIN payroll b ON a.employee_id = b.employee_id
WHERE  a.employee_id IS NULL    -- в зарплатной ведомости, но нет в HR
    OR b.employee_id IS NULL;   -- в HR, но нет в зарплатной ведомости

Аудит данных между двумя источниками. Когда нужно найти расхождения между двумя таблицами, которые должны быть согласованы.

В повседневных запросах FULL OUTER JOIN почти не нужен. Если видите его в чужом коде — это сигнал, что автор сравнивал два независимых набора данных.

Как думать о типах JOIN

Можно представить типы JOIN через диаграмму множеств:

LEFT     = всё из A + пересечение A∩B
INNER    = только пересечение A∩B
RIGHT    = пересечение A∩B + всё из B
FULL     = всё из A + пересечение + всё из B

На практике:

• INNER JOIN — хочу только совпавшие строки
• LEFT JOIN — хочу все из левой таблицы + данные из правой (если есть)
• FULL OUTER JOIN — хочу все строки из обеих таблиц

CROSS JOIN: намеренное декартово произведение

CROSS JOIN возвращает все возможные комбинации строк из двух таблиц. Это то самое декартово произведение, которое обычно является ошибкой, но иногда нужно намеренно.

SELECT a.value AS col_a, b.value AS col_b
FROM   table_a a
CROSS JOIN table_b b;

Или в эквивалентном старом синтаксисе:

SELECT a.value, b.value FROM table_a a, table_b b;

Если в table_a 3 строки и в table_b 4 строки — результат содержит 3 × 4 = 12 строк.

Когда CROSS JOIN нужен

Типичный сценарий — генерация комбинаций. Например:

Расписание: каждый сотрудник на каждый день недели:

SELECT e.name AS employee, d.day_name
FROM   employees e
CROSS JOIN (
  VALUES ('Понедельник'), ('Вторник'), ('Среда'),
         ('Четверг'), ('Пятница')
) AS d(day_name)
ORDER BY e.name, d.day_name;

Матрица цен: каждый товар в каждой валюте:

SELECT p.title, c.currency, p.price * c.rate AS local_price
FROM   products   p
CROSS JOIN currencies c;

VALUES (...) — это встроенный в SQL способ создать временную таблицу из литеральных значений. Используется здесь для создания списка дней без отдельной таблицы.

Проверь себя: если таблица sizes имеет 5 строк и таблица colors имеет 4 строки, сколько строк вернёт CROSS JOIN?

CROSS JOIN и производительность

CROSS JOIN — дорогая операция на больших данных. 1000 × 1000 строк = 1 000 000 строк в результате. На реальных данных с тысячами строк CROSS JOIN может вызвать проблемы с памятью и временем выполнения.

Поэтому CROSS JOIN применяется только тогда, когда одна или обе таблицы маленькие: список дней недели (7 строк), список размеров (5-10 строк), список регионов (сотни строк максимум).

FULL OUTER JOIN через LEFT + RIGHT (для понимания)

В некоторых СУБД (старые версии MySQL) нет FULL OUTER JOIN. Его можно эмулировать:

SELECT c.name, o.product FROM customers c LEFT JOIN orders o ON c.id = o.customer_id
UNION
SELECT c.name, o.product FROM customers c RIGHT JOIN orders o ON c.id = o.customer_id;

UNION объединяет результаты двух запросов и убирает дубликаты. В PostgreSQL FULL OUTER JOIN поддерживается нативно, поэтому это нужно знать только для понимания логики. Тема UNION — в модуле 7.

Практический пример: матрица доступности

Задача: сгенерировать матрицу «все сотрудники × все проекты» и пометить, кто уже назначен:

SELECT e.name,
       p.title,
       CASE WHEN ep.employee_id IS NOT NULL THEN '✓' ELSE '—' END AS assigned
FROM   employees     e
CROSS JOIN projects  p
LEFT JOIN employee_projects ep
       ON ep.employee_id = e.id AND ep.project_id = p.id
ORDER BY e.name, p.title;

CROSS JOIN создаёт все пары «сотрудник + проект», LEFT JOIN добавляет пометку «назначен» там, где связь существует. Результат — полная матрица с пустыми ячейками там, где назначения нет.

Таблица сравнения всех JOIN

К этому моменту вы знаете все основные виды соединений. Удобная шпаргалка:

INNER JOIN — выбор по умолчанию для большинства запросов. LEFT JOIN — когда нужен полный список из одной таблицы. FULL OUTER JOIN и CROSS JOIN — специальные случаи, используются редко.

Ошибка: FULL JOIN vs FULL OUTER JOIN

В PostgreSQL оба варианта синтаксиса корректны и означают одно и то же:

-- Оба варианта эквивалентны
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.id;
SELECT * FROM a FULL JOIN b ON a.id = b.id;

Слово OUTER в LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN — необязательное. В большинстве команд принято его опускать для краткости. Исключение — если стиль команды требует явности.

Краткий итог

• FULL OUTER JOIN возвращает все строки из обеих таблиц; строки без пары получают NULL
• Используется редко: сравнение двух источников данных, аудит расхождений
• CROSS JOIN возвращает все комбинации строк из двух таблиц (декартово произведение)
• Используется намеренно: генерация расписаний, матриц, комбинаций
• CROSS JOIN на больших таблицах — потенциально дорогая операция

Типичные ошибки с FULL OUTER JOIN

1. Неправильный WHERE после FULL OUTER JOIN:

-- Ошибка: WHERE исключит NULL-строки, превращая FULL в LEFT
SELECT c.name, o.product
FROM   customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id
WHERE  o.amount > 1000;   -- строки без заказов (Вера) исчезнут

Если нужно сохранить строки без пары, а фильтровать только существующие данные:

SELECT c.name, o.product
FROM   customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id
WHERE  o.amount > 1000 OR o.id IS NULL;  -- или перенести условие в ON

2. CROSS JOIN по ошибке:

Если забыть условие ON при JOIN, PostgreSQL выдаст синтаксическую ошибку. Но в старом синтаксисе FROM a, b без WHERE a.id = b.id — декартово произведение без ошибки. Это классическая ловушка:

-- Старый синтаксис — декартово произведение если нет условия WHERE
SELECT * FROM employees, departments;  -- N*M строк!

-- Современный синтаксис — явный CROSS JOIN выглядит намеренным
SELECT * FROM employees CROSS JOIN departments;

Что дальше

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