SQL OUTER JOIN

Полные внешние соединения SQL существуют, и поэтому мы должны о них поговорить, но они крайне маловероятно будут тем типом соединения, который вы регулярно используете в своей работе с данными. Проще говоря, полное внешнее соединение SQL — это соединение между двумя таблицами, которое возвращает все строки из обеих таблиц, независимо от успешности совпадения ключа соединения; сравните это с левыми, внутренними или правыми соединениями, которые требуют успешного совпадения для возврата определенных строк.

На этой странице мы разберем, как создать полное внешнее соединение и продемонстрируем, когда оно может понадобиться в вашей аналитической инженерной работе.

Как создать полное внешнее соединение

Как и для всех соединений, вам понадобятся некоторые объекты базы данных (например, таблицы/представления), ключи для соединения и оператор select для выполнения полного внешнего соединения:

select
    <fields>
from <table_1> as t1
full outer join <table_1> as t2
on t1.id = t2.id 

В приведенном выше примере используется только одно поле для соединения таблиц; если вы соединяете объекты базы данных, которые требуют нескольких полей, вы можете использовать операторы AND/OR, а предпочтительнее — суррогатные ключи. Вы также можете добавить WHERE, GROUP BY, ORDER BY, HAVING и другие операторы после ваших соединений для создания фильтрации, упорядочивания и выполнения агрегаций.

Замечание о полных внешних соединениях: это может звучать очевидно, но поскольку полные внешние соединения могут возвращать все строки между двумя таблицами, они могут возвращать много строк, что не обязательно является рецептом для эффективности. Когда вы используете полные внешние соединения, вы часто можете найти альтернативы, используя другие соединения или объединения, чтобы потенциально обойти основные неэффективности, вызванные полным внешним соединением.

Пример полного внешнего соединения SQL

Таблица A car_type

user_id	car_type
1	van
2	sedan
3	truck

Loading table...

Таблица B car_color

user_id	car_color
1	red
3	green
4	yellow

Loading table...

select
   car_type.user_id as user_id,
   car_type.car_type as type,
   car_color.car_color as color
from {{ ref('car_type') }} as car_type
full outer join {{ ref('car_color') }} as car_color
on car_type.user_id = car_color.user_id
order by 1

Этот простой запрос вернет все строки из таблиц A и B, независимо от успешности совпадения user_id между двумя таблицами:

user_id	type	color
1	van	red
2	sedan	null
3	truck	green
4	null	yellow

Loading table...

Примеры использования полного внешнего соединения SQL

В вашем проекте dbt неизбежно найдутся валидные сценарии использования full outer join. Однако из‑за природы dbt, который активно поощряет модульность и DRY‑подход, необходимость в full outer join может немного снизиться. Тем не менее, на практике мы обычно видим два основных случая применения full outer join: консолидация (или объединение) нескольких сущностей и валидация данных.

• Объединение таблиц: full outer join между двумя таблицами позволяет объединить эти сущности независимо от совпадения ключей соединения. Часто такого типа объединения можно избежать, используя другие типы join’ов, union, pivot или их комбинации, но, честно говоря, иногда full outer join — это просто немного меньше работы 🤷
• Валидация данных: full outer join может быть чрезвычайно полезен при выполнении валидации данных. Например, в пакете dbt-audit-helper full outer join используется в тесте compare_column_values, чтобы помочь определить, в каких местах значения столбцов не совпадают между двумя dbt‑моделями.

Нашли ошибку?

Создать GitHub Issue