Эквивалент LEFT OUTER JOIN без использования OUTER JOIN

39. Переписать LEFT JOIN без использования LEFT JOIN

Условие задачи:
Даны две таблицы:

t1(A);
t2(B).

Исходный запрос:

SELECT *
FROM t1
LEFT OUTER JOIN t2
    ON t1.A = t2.B;

Необходимо получить эквивалентный результат без использования LEFT OUTER JOIN.

Спойлеры к решению

Подсказки

💡 Результат LEFT JOIN можно разделить на две части.
💡 Первая часть — строки, для которых найдено совпадение через INNER JOIN.
💡 Вторая часть — строки из t1, для которых совпадений в t2 нет.
💡 Для поиска отсутствующих строк удобно использовать NOT EXISTS.
💡 Объедини результаты через UNION ALL, чтобы сохранить дубликаты.

Решение

SELECT
    t1.A,
    t2.B
FROM t1
JOIN t2
    ON t1.A = t2.B

UNION ALL

SELECT
    t1.A,
    NULL AS B
FROM t1
WHERE NOT EXISTS (
    SELECT 1
    FROM t2
    WHERE t2.B = t1.A
);

Первая часть возвращает все совпавшие строки:

SELECT
    t1.A,
    t2.B
FROM t1
JOIN t2
    ON t1.A = t2.B;

Вторая часть возвращает строки из t1, для которых соответствующей строки в t2 нет:

SELECT
    t1.A,
    NULL AS B
FROM t1
WHERE NOT EXISTS (
    SELECT 1
    FROM t2
    WHERE t2.B = t1.A
);

Используется именно UNION ALL, а не UNION, поскольку LEFT JOIN сохраняет дубликаты.

Вариант через NOT IN в общем случае не является эквивалентным:

WHERE t1.A NOT IN (
    SELECT t2.B
    FROM t2
)

Он некорректно работает при наличии NULL, поэтому для точного аналога LEFT JOIN лучше использовать NOT EXISTS.