Присоединение данных с помощью JOIN

Июл 16, 2021 Qlik

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

Операция присоединения имеет следующий синтаксис:

[inner | outer | left | right ]Join [ (tablename ) ]( loadstatement | selectstatement )

Join — это присоединение, образуемое по всем общим полям (полям с одинаковыми наименованиями). Перед оператором join можно задать один из префиксов inner, outer, left или right. Добавление префикса влияет на алгоритм присоединения.

Вместо loadstatement или selectestatement можно указать LOAD или SELECT запросы.

Рассмотрим как работают разные варианты присоединений в Qlik Sense и QlikView на примере двух таблиц Товары и Поставки. Таблица Товары выглядит следующим образом:

КодТоварДлинаЦветМатериал
00-00001Гвоздь150 Металл
00-00002Шуруп35 Металл
00-00005Винт20 Металл
00-00006Гайка  Металл
00-00008Клипса35ЧерныйПластик
00-00009Дюбель60СерыйПластик
00-00010Шуруп45БелыйМеталл

Таблица Поставки выглядит следующим образом:

ДатаТоварКоличествоПоставщик
01.01.2020Гвоздь1000ООО «Железная тема»
08.01.2020Гвоздь300ООО «Железная тема»
01.01.2020Шуруп50ООО «Железная тема»
03.01.2020Гайка100ИП Иванов
08.01.2020Гвоздь300ООО «Железная тема»
14.01.2020Шуруп150ООО «Железная тема»
15.01.2020Гвоздь150ООО «Железная тема»
08.01.2020Скоба50ИП Иванов

У этих двух таблиц только одно общее поле (поле имеющее одинаковое наименование) и это поле называется Товар. Вы можете потренироваться загрузив обе таблицы с помощью следующего скрипта загрузки данных:

Поставки:
LOAD * INLINE [
Дата Товар Количество Поставщик
01.01.2020 Гвоздь 1000 ‘ООО «Железная тема»‘
08.01.2020 Гвоздь 300 ‘ООО «Железная тема»‘
01.01.2020 Шуруп 50 ‘ООО «Железная тема»‘
03.01.2020 Гайка 100 ‘ИП Иванов’
08.01.2020 Гвоздь 300 ‘ООО «Железная тема»‘
14.01.2020 Шуруп 150 ‘ООО «Железная тема»‘
15.01.2020 Гвоздь 150 ‘ООО «Железная тема»‘
08.01.2020 Скоба 50 ‘ИП Иванов’
] (delimiter is spaces);

Товары:
LOAD * INLINE [
Код Товар Длина Цвет Материал
00-00001 Гвоздь 150 ‘ ‘ Металл
00-00002 Шуруп 35 ‘ ‘ Металл
00-00005 Винт 20 ‘ ‘ Металл
00-00006 Гайка ‘ ‘ ‘ ‘ Металл
00-00008 Клипса 35 Черный Пластик
00-00009 Дюбель 60 Серый Пластик
00-00010 Шуруп 45 Белый Металл
] (delimiter is spaces);

INNER JOIN

Если этот префикс используется перед join, то он указывает, что необходимо выполнить внутреннее объединение. Результирующая таблица таким образом будет содержать только комбинации значений полей из таблиц исходных данных с представлением связанных значений полей в обеих таблицах. Обычно для того, чтобы показать как работает INNER JOIN рисуют вот такую картинку с изображением множеств А и B:

Хотя такая картинка не передает тот факт что при присоединении таблиц часто происходит дублирование строк она хорошо отражает смысл — внутреннее присоединение оставляет только те строки, в которых значения по общим полям совпадают.

Первая загружаемая таблица (на изображении это множество А) будет называться ЛЕВОЙ таблицей, а вторая, которая присоединяется (на изображении это множество B) соответственно ПРАВОЙ.

Скрипт загрузки данных для примера работы INNER JOIN выглядит следующим образом:

Поставки:
LOAD * INLINE [
Дата Товар Количество Поставщик
01.01.2020 Гвоздь 1000 ‘ООО «Железная тема»‘
08.01.2020 Гвоздь 300 ‘ООО «Железная тема»‘
01.01.2020 Шуруп 50 ‘ООО «Железная тема»‘
03.01.2020 Гайка 100 ‘ИП Иванов’
08.01.2020 Гвоздь 300 ‘ООО «Железная тема»‘
14.01.2020 Шуруп 150 ‘ООО «Железная тема»‘
15.01.2020 Гвоздь 150 ‘ООО «Железная тема»‘
08.01.2020 Скоба 50 ‘ИП Иванов’
] (delimiter is spaces);

INNER JOIN(Поставки)
LOAD * INLINE [
Код Товар Длина Цвет Материал
00-00001 Гвоздь 150 ‘ ‘ Металл
00-00002 Шуруп 35 ‘ ‘ Металл
00-00005 Винт 20 ‘ ‘ Металл
00-00006 Гайка ‘ ‘ ‘ ‘ Металл
00-00008 Клипса 35 Черный Пластик
00-00009 Дюбель 60 Серый Пластик
00-00010 Шуруп 45 Белый Металл
] (delimiter is spaces);

После запуска скрипта мы получаем одну таблицу содержащую следующие данные:

КодТоварДлинаЦветМатериалДатаКоличествоПоставщик
00-00001Гвоздь150 Металл01.01.20201000ООО «Железная тема»
00-00001Гвоздь150 Металл08.01.2020300ООО «Железная тема»
00-00001Гвоздь150 Металл15.01.2020150ООО «Железная тема»
00-00002Шуруп35 Металл01.01.202050ООО «Железная тема»
00-00002Шуруп35 Металл14.01.2020150ООО «Железная тема»
00-00006Гайка  Металл03.01.2020100ИП Иванов
00-00010Шуруп45БелыйМеталл01.01.202050ООО «Железная тема»
00-00010Шуруп45БелыйМеталл14.01.2020150ООО «Железная тема»

Обратите внимание что в результирующей таблице:

  • нет товара Скоба из таблицы Поставки поскольку такого товара нет в таблице Товары;
  • нет товара Винт из таблицы Товары поскольку его нет в таблице Поставки;
  • строк с товаром Шуруп стало четыре хотя в каждой таблице их по две. Это случилось потому, что каждая строка таблицы Поставки присоединилась к каждой строке таблицы Товары;

Обратите внимание на отличие INNER JOIN от остальных JOIN — эта трансформация оставила только записи где значение поля Товар есть в обоих таблицах.

OUTER JOIN

OUTER JOIN — внешнее присоединение. Для указания внешнего объединения перед префиксом JOIN может стоять префикс OUTER, а может и не стоять — то есть если вид присоединения не указан, то используется внешнее присоединение.

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

Скрипт загрузки данных для примера работы OUTER JOIN выглядит следующим образом:

Поставки:
LOAD * INLINE [
Дата Товар Количество Поставщик
01.01.2020 Гвоздь 1000 ‘ООО «Железная тема»‘
08.01.2020 Гвоздь 300 ‘ООО «Железная тема»‘
01.01.2020 Шуруп 50 ‘ООО «Железная тема»‘
03.01.2020 Гайка 100 ‘ИП Иванов’
08.01.2020 Гвоздь 300 ‘ООО «Железная тема»‘
14.01.2020 Шуруп 150 ‘ООО «Железная тема»‘
15.01.2020 Гвоздь 150 ‘ООО «Железная тема»‘
08.01.2020 Скоба 50 ‘ИП Иванов’
] (delimiter is spaces);

OUTER JOIN(Поставки)
LOAD * INLINE [
Код Товар Длина Цвет Материал
00-00001 Гвоздь 150 ‘ ‘ Металл
00-00002 Шуруп 35 ‘ ‘ Металл
00-00005 Винт 20 ‘ ‘ Металл
00-00006 Гайка ‘ ‘ ‘ ‘ Металл
00-00008 Клипса 35 Черный Пластик
00-00009 Дюбель 60 Серый Пластик
00-00010 Шуруп 45 Белый Металл
] (delimiter is spaces);

После запуска скрипта мы получаем одну таблицу содержащую следующие данные:

КодТоварДлинаЦветМатериалДатаКоличествоПоставщик
00-00001Гвоздь150 Металл01.01.20201000ООО «Железная тема»
00-00001Гвоздь150 Металл08.01.2020300ООО «Железная тема»
00-00001Гвоздь150 Металл15.01.2020150ООО «Железная тема»
00-00002Шуруп35 Металл01.01.202050ООО «Железная тема»
00-00002Шуруп35 Металл14.01.2020150ООО «Железная тема»
00-00005Винт20 Металл
00-00006Гайка  Металл03.01.2020100ИП Иванов
00-00008Клипса35ЧерныйПластик
00-00009Дюбель60СерыйПластик
00-00010Шуруп45БелыйМеталл01.01.202050ООО «Железная тема»
00-00010Шуруп45БелыйМеталл14.01.2020150ООО «Железная тема»
Скоба08.01.202050ИП Иванов

Прочерками в таблицы отображаются пустые поля (содержащие значения NULL). Глядя на таблицу мы видим, что в результирующей таблице есть как все значения поля Товар из левой таблицы Поставки так и все значения поля Товар из правой таблицы Товары. То есть трансформация OUTER JOIN оставила все строки обеих таблиц, а в тех строках где значения были только в левой или только в правой таблице появились значения NULL в присоединенных полях.

LEFT JOIN и RIGHT JOIN

LEFT JOIN и RIGHT JOIN это левое и правое присоединение соответственно. Суть этих вариантов присоединения в том, что они оставляют как пересекающиеся значения обеих таблиц так и полностью все строки одной из таблиц, участвующих в присоединении (для LEFT JOIN — остаются строки левой таблицы, для RIGHT JOIN остаются строки правой таблицы). На изображениях с множествами это выглядит следующим образом:

Разберем работу LEFT JOIN на примере. Выполняем следующий скрипт:

Поставки:
LOAD * INLINE [
Дата Товар Количество Поставщик
01.01.2020 Гвоздь 1000 ‘ООО «Железная тема»‘
08.01.2020 Гвоздь 300 ‘ООО «Железная тема»‘
01.01.2020 Шуруп 50 ‘ООО «Железная тема»‘
03.01.2020 Гайка 100 ‘ИП Иванов’
08.01.2020 Гвоздь 300 ‘ООО «Железная тема»‘
14.01.2020 Шуруп 150 ‘ООО «Железная тема»‘
15.01.2020 Гвоздь 150 ‘ООО «Железная тема»‘
08.01.2020 Скоба 50 ‘ИП Иванов’
] (delimiter is spaces);

LEFT JOIN(Поставки)
LOAD * INLINE [
Код Товар Длина Цвет Материал
00-00001 Гвоздь 150 ‘ ‘ Металл
00-00002 Шуруп 35 ‘ ‘ Металл
00-00005 Винт 20 ‘ ‘ Металл
00-00006 Гайка ‘ ‘ ‘ ‘ Металл
00-00008 Клипса 35 Черный Пластик
00-00009 Дюбель 60 Серый Пластик
00-00010 Шуруп 45 Белый Металл
] (delimiter is spaces);

Результирующая таблица выглядит следующим образом:

КодТоварДлинаЦветМатериалДатаКоличествоПоставщик
00-00001Гвоздь150 Металл01.01.20201000ООО «Железная тема»
00-00001Гвоздь150 Металл08.01.2020300ООО «Железная тема»
00-00001Гвоздь150 Металл15.01.2020150ООО «Железная тема»
00-00002Шуруп35 Металл01.01.202050ООО «Железная тема»
00-00002Шуруп35 Металл14.01.2020150ООО «Железная тема»
00-00006Гайка  Металл03.01.2020100ИП Иванов
00-00010Шуруп45БелыйМеталл01.01.202050ООО «Железная тема»
00-00010Шуруп45БелыйМеталл14.01.2020150ООО «Железная тема»
Скоба08.01.202050ИП Иванов

В таблице остались все значения поля Товар и связанные с ними строки из левой таблицы Поставки, причем если таких же значений поля Товар не нашлось в правой таблице Товары, то система добавила в присоединенные ячейки значения NULL. Это видно на товаре Скоба — его не было в таблице Товары, но в результирующей выборке он остался, поскольку мы использовали LEFT JOIN.

Итоги

Я не представляю как можно обойтись без присоединения таблиц. Это один из основных вариантов трансформации данных как при использовании скрипта загрузки так и при использовании Диспетчера данных. Но иногда использование JOIN иногда несет в себе опасности, например появляются неожиданные дубликаты строк или даже отказ сервера. Последнее мы хорошо показали в обучающем видео на нашем канале, рекомендуем посмотреть, так как ошибка «Джойна смерти» довольно распространена.