При условии A.v = B.v INNER JOIN вернет 5 строк, LEFT — 8, RIGHT — 7, FULL — 10. Две единицы в каждой таблице образуют четыре пары (1, 1); пятерки дают еще одну. NULL = NULL не истинно. CROSS JOIN без условия создаст 6 × 5 = 30 строк, включая все сочетания повторов и NULL.
Что вернут JOIN для таблиц с повторяющимися единицами и NULL?
Точный результат INNER, LEFT, RIGHT, FULL и CROSS JOIN для двух заданных таблиц. Учитываем размножение повторов и то, что NULL не равен NULL.
Короткий ответ
Что ответить на собеседовании
Подробный разбор
Ответ с пояснениями
Условие
В таблице A одно поле со значениями 1, 1, 3, 5, 7, NULL, в B — 1, 1, 2, 5, NULL. Определите результаты основных видов соединений. Назовем единственное поле v и для соединений с условием примем ON a.v = b.v. Без указания условия ответ был бы неоднозначным.
Базовая форма запроса:
SELECT a.v AS a_value, b.v AS b_value
FROM a
INNER JOIN b ON a.v = b.v;
Для остальных вариантов замените INNER JOIN на LEFT JOIN, RIGHT JOIN или FULL OUTER JOIN. Порядок строк без ORDER BY не гарантируется.
Точные результаты с учетом повторов
В ячейках указано, сколько раз соответствующая пара входит в результат. Ноль означает отсутствие пары.
| Пара (A.v, B.v) | INNER | LEFT | RIGHT | FULL |
|---|---|---|---|---|
| (1, 1) | 4 | 4 | 4 | 4 |
| (5, 5) | 1 | 1 | 1 | 1 |
| (3, NULL) | 0 | 1 | 0 | 1 |
| (7, NULL) | 0 | 1 | 0 | 1 |
| (NULL, 2) | 0 | 0 | 1 | 1 |
| (NULL, NULL) | 0 | 1 | 1 | 2 |
| Всего строк | 5 | 8 | 7 | 10 |
Каждая из двух строк A со значением 1 соединяется с обеими строками B со значением 1: получается 2 × 2 = 4 пары. JOIN не удаляет повторяющиеся строки.
В FULL JOIN две пары (NULL, NULL) имеют разное происхождение: одна представляет несовпавшую строку A, другая — несовпавшую строку B. Это не совпадение двух исходных NULL по равенству. При обычном сравнении NULL = NULL результат неизвестен, а не истинен.
CROSS JOIN
SELECT a.v AS a_value, b.v AS b_value
FROM a CROSS JOIN b;
Все 6 строк A сочетаются со всеми 5 строками B: 30 строк. Следующая таблица задает полный результат через количество каждой пары; строки — значение A, столбцы — значение B.
| A \ B | 1 | 2 | 5 | NULL |
|---|---|---|---|---|
| 1 | 4 | 2 | 2 | 2 |
| 3 | 2 | 1 | 1 | 1 |
| 5 | 2 | 1 | 1 | 1 |
| 7 | 2 | 1 | 1 | 1 |
| NULL | 2 | 1 | 1 | 1 |
CROSS JOIN не содержит условия равенства, поэтому NULL здесь не препятствует образованию пары.
Если дополнительно обсуждают semi/anti join, проверка EXISTS оставит из A значения 1, 1, 5, а NOT EXISTS с тем же условием равенства — 3, 7, NULL. Это фильтрация строк A по наличию соответствия, а не вывод пар столбцов. Основные правила соединений приведены в документации PostgreSQL.