Присоединение — операция объединения двух таблиц в одну при которой записи результирующей таблицы представляют собой комбинации записей в исходных таблицах. При этом две такие записи, составляющие одну комбинацию в результирующей таблице, как правило, имеют общее значение одного или нескольких общих полей. Такое присоединение проще всего представить если к одной таблице сбоку добавляются столбцы другой таблицы.
Операция присоединения имеет следующий синтаксис:
[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 [ Код Товар Длина Цвет Материал 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 выглядит следующим образом:
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.2020
1000
ООО «Железная тема»
00-00001
Гвоздь
150
Металл
08.01.2020
300
ООО «Железная тема»
00-00001
Гвоздь
150
Металл
15.01.2020
150
ООО «Железная тема»
00-00002
Шуруп
35
Металл
01.01.2020
50
ООО «Железная тема»
00-00002
Шуруп
35
Металл
14.01.2020
150
ООО «Железная тема»
00-00006
Гайка
Металл
03.01.2020
100
ИП Иванов
00-00010
Шуруп
45
Белый
Металл
01.01.2020
50
ООО «Железная тема»
00-00010
Шуруп
45
Белый
Металл
14.01.2020
150
ООО «Железная тема»
Обратите внимание что в результирующей таблице:
нет товара Скоба из таблицы Поставки поскольку такого товара нет в таблице Товары;
нет товара Винт из таблицы Товары поскольку его нет в таблице Поставки;
строк с товаром Шуруп стало четыре хотя в каждой таблице их по две. Это случилось потому, что каждая строка таблицы Поставки присоединилась к каждой строке таблицы Товары;
Обратите внимание на отличие INNER JOIN от остальных JOIN — эта трансформация оставила только записи где значение поля Товар есть в обоих таблицах.
OUTER JOIN
OUTER JOIN — внешнее присоединение. Для указания внешнего объединения перед префиксом JOIN может стоять префикс OUTER, а может и не стоять — то есть если вид присоединения не указан, то используется внешнее присоединение.
При внешнем присоединении создаются все возможные комбинации двух таблиц. Результирующая таблица, таким образом, будет содержать комбинации значений полей из таблиц исходных данных с представлением связанных значений полей в одной или обеих таблицах. OUTER JOIN еще называют полным присоединением, результат полного присоединения можно отобразить в виде следующего изображения:
Скрипт загрузки данных для примера работы OUTER JOIN выглядит следующим образом:
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.2020
1000
ООО «Железная тема»
00-00001
Гвоздь
150
Металл
08.01.2020
300
ООО «Железная тема»
00-00001
Гвоздь
150
Металл
15.01.2020
150
ООО «Железная тема»
00-00002
Шуруп
35
Металл
01.01.2020
50
ООО «Железная тема»
00-00002
Шуруп
35
Металл
14.01.2020
150
ООО «Железная тема»
00-00005
Винт
20
Металл
—
—
—
00-00006
Гайка
Металл
03.01.2020
100
ИП Иванов
00-00008
Клипса
35
Черный
Пластик
—
—
—
00-00009
Дюбель
60
Серый
Пластик
—
—
—
00-00010
Шуруп
45
Белый
Металл
01.01.2020
50
ООО «Железная тема»
00-00010
Шуруп
45
Белый
Металл
14.01.2020
150
ООО «Железная тема»
—
Скоба
—
—
—
08.01.2020
50
ИП Иванов
Прочерками в таблицы отображаются пустые поля (содержащие значения NULL). Глядя на таблицу мы видим, что в результирующей таблице есть как все значения поля Товар из левой таблицы Поставки так и все значения поля Товар из правой таблицы Товары. То есть трансформация OUTER JOIN оставила все строки обеих таблиц, а в тех строках где значения были только в левой или только в правой таблице появились значения NULL в присоединенных полях.
LEFT JOIN и RIGHT JOIN
LEFT JOIN и RIGHT JOIN это левое и правое присоединение соответственно. Суть этих вариантов присоединения в том, что они оставляют как пересекающиеся значения обеих таблиц так и полностью все строки одной из таблиц, участвующих в присоединении (для LEFT JOIN — остаются строки левой таблицы, для RIGHT JOIN остаются строки правой таблицы). На изображениях с множествами это выглядит следующим образом:
Разберем работу LEFT JOIN на примере. Выполняем следующий скрипт:
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.2020
1000
ООО «Железная тема»
00-00001
Гвоздь
150
Металл
08.01.2020
300
ООО «Железная тема»
00-00001
Гвоздь
150
Металл
15.01.2020
150
ООО «Железная тема»
00-00002
Шуруп
35
Металл
01.01.2020
50
ООО «Железная тема»
00-00002
Шуруп
35
Металл
14.01.2020
150
ООО «Железная тема»
00-00006
Гайка
Металл
03.01.2020
100
ИП Иванов
00-00010
Шуруп
45
Белый
Металл
01.01.2020
50
ООО «Железная тема»
00-00010
Шуруп
45
Белый
Металл
14.01.2020
150
ООО «Железная тема»
—
Скоба
—
—
—
08.01.2020
50
ИП Иванов
В таблице остались все значения поля Товар и связанные с ними строки из левой таблицы Поставки, причем если таких же значений поля Товар не нашлось в правой таблице Товары, то система добавила в присоединенные ячейки значения NULL. Это видно на товаре Скоба — его не было в таблице Товары, но в результирующей выборке он остался, поскольку мы использовали LEFT JOIN.
Итоги
Я не представляю как можно обойтись без присоединения таблиц. Это один из основных вариантов трансформации данных как при использовании скрипта загрузки так и при использовании Диспетчера данных. Но иногда использование JOIN иногда несет в себе опасности, например появляются неожиданные дубликаты строк или даже отказ сервера. Последнее мы хорошо показали в обучающем видео на нашем канале, рекомендуем посмотреть, так как ошибка «Джойна смерти» довольно распространена.
