При работе с реляционными базами данных неизбежно и постоянно придется работать с соединениями. Помимо понимания самих соединений очень важно - это понимание NULL. В базах данных Оракл NULL следует понимать, как отсутствие значения. Любое сравнение с NULL будет на выходе давать NULL (неопределнное значение).
Возможно, нелишним будет проверить это поведение на своей БД (есть версия, что в некоторых БД проверка NULL с NULL дает True).

Основные типы JOIN в SQL

Поведение различных типов соединений в таблицах, содержащих NULL, рассмотрим их на примере двух таблиц: table_a и table_b.

Создание и наполнение таблиц

DROP TABLE table_a;
CREATE TABLE table_a (
    id INT,
    value VARCHAR(10)
);

INSERT INTO table_a VALUES (1, 'A');
INSERT INTO table_a VALUES (2, NULL);
INSERT INTO table_a VALUES (3, 'B');
INSERT INTO table_a VALUES (4, NULL);
INSERT INTO table_a VALUES (5, 'C');
INSERT INTO table_a VALUES (6, NULL);
INSERT INTO table_a VALUES (7, 'D');
INSERT INTO table_a VALUES (8, NULL);
INSERT INTO table_a VALUES (9, 'E');
INSERT INTO table_a VALUES (10, NULL);
-- Добавим второе значение 'B'
INSERT INTO table_a VALUES (11, 'B');

DROP TABLE table_b;
CREATE TABLE table_b (
    id INT,
    value VARCHAR(10)
);

INSERT ALL
  INTO table_b VALUES (1, 'A')
  INTO table_b VALUES (2, NULL)
  INTO table_b VALUES (3, NULL)
  INTO table_b VALUES (4, 'B')
  INTO table_b VALUES (5, NULL)
  INTO table_b VALUES (6, 'C')
  INTO table_b VALUES (7, NULL)
  INTO table_b VALUES (8, NULL)
  INTO table_b VALUES (9, 'W')
  INTO table_b VALUES (10, NULL)
  INTO table_b VALUES (11, NULL)
  INTO table_b VALUES (12, 'V')
  -- Добавим второе значение 'C'
  INTO table_b VALUES (13, 'C')
SELECT * FROM dual;

Содержимое таблиц

table_a:

table_b:

Виды JOIN

1. INNER JOIN

Описание: Возвращает только те строки, у которых есть совпадения в обеих таблицах по условию соединения.

SELECT a.id as a_id, a.value as a_value, b.id as b_id, b.value as b_value
FROM table_a a
INNER JOIN table_b b ON a.value = b.value;

Результат:

2. LEFT JOIN

Описание: Возвращает все строки из левой таблицы (table_a) и совпадающие строки из правой (table_b). Если совпадения нет — значения из правой таблицы будут NULL.
При соединениях важно понимать, что при совпадении будут возвращаться все значения из второй таблицы.
Лично мне первое время было трудно к этому привыкнуть, как активному пользователю функции ВПР а Excel (VLOOKUP), я ожидал, что значение будет только одно.
Чаще всего такое соединение применяется, когда нужно на выходе гарантированно получить все строки исходной таблицы и проверить наличие совпадений во второй таблице по ключу.

SELECT a.id as a_id, a.value as a_value, b.id as b_id, b.value as b_value
FROM table_a a
LEFT JOIN table_b b ON a.value = b.value;

Результат (сокращённо):

3. RIGHT JOIN

Описание: Возвращает все строки из правой таблицы (table_b) и совпадающие строки из левой (table_a). Если совпадения нет — значения из левой таблицы будут NULL.

SELECT a.id as a_id, a.value as a_value, b.id as b_id, b.value as b_value
FROM table_a a
RIGHT JOIN table_b b ON a.value = b.value;

Результат (сокращённо):

Эти два запроса эквивалентны по результату (если правильно указаны алиасы):

SELECT *
FROM table_a a
RIGHT JOIN table_b b ON a.value = b.value;

и

SELECT *
FROM table_b b
LEFT JOIN table_a a ON a.value = b.value;

Почему?
RIGHT JOIN и LEFT JOIN — это зеркальные операции.
RIGHT JOIN возвращает все строки из правой таблицы (table_b), а LEFT JOIN — все строки из левой (table_b в данном случае).
Если поменять местами таблицы и тип соединения, результат будет одинаковым по содержанию (но порядок столбцов может отличаться).

Вывод:
a RIGHT JOIN b ON ... ≡ b LEFT JOIN a ON ...

4. FULL OUTER JOIN

Описание: Возвращает все строки из обеих таблиц. Если совпадения нет — значения из отсутствующей таблицы будут NULL. Тут важно обратить внимание, что NULL в одной таблице не равно NULL в другой таблице.

FULL OUTER JOIN по сути возвращает объединение результатов LEFT JOIN и RIGHT JOIN, но с одним важным отличием:
- Если одна и та же строка попадает и в LEFT JOIN, и в RIGHT JOIN (то есть есть совпадение по ключу), то в результате FULL OUTER JOIN эта строка будет только один раз.

Если просто написать:

SELECT ... FROM a LEFT JOIN b ON ...
UNION
SELECT ... FROM a RIGHT JOIN b ON ...

— это даст тот же результат, что и FULL OUTER JOIN, если вы используете UNION, а не UNION ALL.
UNION убирает дубликаты, а UNION ALL — нет.

Важно:
- В некоторых СУБД (например, MySQL) нет FULL OUTER JOIN, и такой подход с UNION используется как замена.
- В стандартном SQL лучше использовать FULL OUTER JOIN, если он поддерживается.

SELECT a.id as a_id, a.value as a_value, b.id as b_id, b.value as b_value
FROM table_a a
FULL OUTER JOIN table_b b ON a.value = b.value;

Результат (сокращённо):

5. CROSS JOIN

Описание: Декартово произведение — каждая строка из первой таблицы соединяется с каждой строкой из второй.

SELECT a.id as a_id, a.value as a_value, b.id as b_id, b.value as b_value
FROM table_a a
CROSS JOIN table_b b;

Результат:
В результате будет 11 (строк в table_a) × 13 (строк в table_b) = 143 строки.
Ниже приведён пример строк a_id = 1 и строк с a_id = 2 из первой таблицы, чтобы было понятно, что на выходе будут все строки, в том числе NULL:

...

6. LEFT JOIN ON 1=1 (Декартово произведение через LEFT JOIN)

Описание:
Если использовать LEFT JOIN с условием ON 1=1, то каждая строка из левой таблицы соединяется с каждой строкой из правой таблицы, потому что условие соединения всегда истинно. Это фактически превращает результат в декартово произведение, аналогичное CROSS JOIN, НО если правая таблица пуста, то LEFT JOIN всё равно вернёт все строки из левой таблицы с NULL-значениями справа, а CROSS JOIN вернёт пустой результат.

Такой JOIN мне понадобился при разборе XML документа, в котором содержались модели техники и ее свойства в виде вложенной структуры. Для некоторы моделей свойства отсутствовали, но важно было, чтобы это можно было получить в результате.

SELECT a.id as a_id, a.value as a_value, b.id as b_id, b.value as b_value
FROM table_a a
LEFT JOIN table_b b ON 1=1;

Примечание:
- Для непустых таблиц результат совпадает с CROSS JOIN.
- Формально, LEFT JOIN ... ON 1=1 и CROSS JOIN — не одно и то же, но на практике для двух обычных таблиц результат одинаков.

Ниже еще пример, где это может пригодиться.

Пример: "Разворачивание" свойств для каждой строки

Задача:
У вас есть таблица товаров (products) и отдельная таблица возможных свойств (properties). Не для всех товаров есть значения всех свойств, но вы хотите получить для каждого товара полный список свойств (даже если значения отсутствуют).

Структура:

products
---------
id | name
---|------
1  | Телефон
2  | Ноутбук

properties
-----------
id | property_name
---|--------------
1  | Цвет
2  | Вес
3  | Размер

Ваша цель:
Для каждого товара получить все возможные свойства, даже если значения ещё не заданы.

Решение:

SELECT p.id as product_id, p.name, pr.property_name
FROM products p
LEFT JOIN properties pr ON 1=1

Результат:
Каждый товар будет "размножен" по всем свойствам. Это удобно, если вы хотите, например, подготовить шаблон для ввода значений свойств для каждого товара (например, в интерфейсе администратора).

Вывод:
LEFT JOIN ... ON 1=1 полезен, когда нужно "развернуть" одну таблицу по всем строкам другой, даже если между ними нет прямой связи. Это часто используется для генерации шаблонов, отчетов или подготовки данных для дальнейшего заполнения.

Заключение

Использование различных видов JOIN позволяет гибко объединять данные из нескольких таблиц в зависимости от поставленных задач. Важно понимать, как работает каждый тип соединения, чтобы получать корректные результаты при работе с базами данных. И очень важно понимать, как работает NULL в базе данных.