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

Таблица B car_color

user_id	car_color
1	red
3	green
4	yellow

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

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

В вашем проекте dbt неизбежно будут случаи, когда полные внешние соединения будут полезны. Однако из-за природы dbt, который сильно поощряет модульность и сухость, необходимость в полных внешних соединениях может немного снизиться. Тем не менее, два основных случая использования полных внешних соединений, которые мы обычно видим, связаны с консолидацией или объединением нескольких сущностей и проверкой данных.

• Объединение таблиц: Полное внешнее соединение между двумя таблицами может объединить эти сущности, независимо от совпадения ключа соединения. Этот тип соединения часто можно обойти, используя другие соединения, объединения, повороты и их комбинации, но иногда полное внешнее соединение требует немного меньше работы 🤷
• Проверка данных: Полные внешние соединения могут быть невероятно полезны при проверке данных; например, в пакете dbt-audit-helper полное внешнее соединение используется в тесте compare_column_values для определения, где значения столбцов не совпадают между двумя моделями dbt.