Как работает full join?
Что проверяют
Понимаете ли вы, какие строки и сколько вернёт FULL JOIN, и умеете ли применить его к задаче аналитика. Следом обычно дают пример на сверку двух источников и спрашивают, где в результате появятся NULL и как их обработать.
Как отвечать
FULL OUTER JOIN возвращает три группы строк: пары, совпавшие по условию, строки левой таблицы без пары и строки правой таблицы без пары. Недостающие колонки заполняются NULL. По сути это LEFT JOIN плюс строки правой таблицы, которых нет слева.
Типовые задачи аналитика:
- сверка источников: заказы из CRM против оплат из биллинга.
NULLв колонках заказа означает оплату без заказа, в колонках оплаты заказ без оплаты; - сравнение периодов: продажи по товарам за этот и прошлый месяц, включая товары, которые продавались только в одном из них;
- объединение справочников: полный список ключей из двух систем.
Ключ в выборке берут из обеих сторон: COALESCE(a.id, b.id) AS id. Метрики заполняют так же, COALESCE(b.amount, 0), иначе суммы и разницы превратятся в NULL.
Про поддержку в СУБД: PostgreSQL, SQL Server, Oracle и ClickHouse FULL JOIN умеют, MySQL нет. Там его собирают из LEFT JOIN, UNION ALL и RIGHT JOIN с условием WHERE a.id IS NULL. В ClickHouse на месте отсутствующих значений по умолчанию будут не NULL, а значения по умолчанию типа, 0 или пустая строка, пока не включить join_use_nulls = 1.
Где спотыкаются
Берут ключ только из одной таблицы. SELECT a.id для строки, которой нет в a, вернёт NULL, и запись без пары потеряет идентификатор.
Фильтруют в WHERE по колонке одной стороны, например WHERE b.status = 'paid'. Строки, у которых нет пары в b, выпадут, и FULL JOIN молча превратится в RIGHT JOIN. Такой фильтр применяют заранее, в подзапросе или CTE.
Не проверяют уникальность ключа. Если на ключ приходится две строки слева и три справа, в результате будет шесть строк, и суммы задвоятся. Перед сверкой данные агрегируют до строки на ключ. И помните, что NULL в ключе не равен другому NULL: такие строки пары не найдут.