Оператор join: SQL JOIN — соединение таблиц базы данных

Содержание

SQL JOIN — соединение таблиц базы данных

Оператор языка SQL JOIN предназначен для соединения двух или более таблиц базы данных по совпадающему условию. Этот оператор существует только в реляционных базах данных. Именно благодаря JOIN реляционные базы данных обладают такой мощной функциональностью, которая позволяет вести не только хранение данных, но и их, хотя бы простейший, анализ с помощью запросов. Разберём основные нюансы написания SQL-запросов с оператором JOIN, которые являются общими для всех СУБД (систем управления базами данных). Для соединения двух таблиц оператор SQL JOIN имеет следующий синтаксис:

SELECT ИМЕНА_СТОЛБЦОВ (1..N) FROM ИМЯ_ТАБЛИЦЫ_1 JOIN ИМЯ_ТАБЛИЦЫ_2 ON УСЛОВИЕ

После одного или нескольких звеньев с оператором JOIN может следовать необязательная секция WHERE или HAVING, в которой, также, как в простом SELECT-запросе, задаётся условие выборки. Общим для всех СУБД является то, что в этой конструкции вместо JOIN может быть указано INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN (или, как вариант, запятая).

Запрос с оператором INNER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON.

То же самое делает и просто JOIN. Таким образом, слово INNER — не обязательное.

Есть база данных портала объявлений — 2. В ней есть таблица Categories (категории объявлений) и Parts (части, или иначе — рубрики, которые и относятся к категориям). Например, части Квартиры, Дачи относятся к категории Недвижимость, а части Автомобили, Мотоциклы — к категории Транспорт.

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке

.

Скрипт для создания базы данных портала объявлений — 2, её таблиц и заполения таблиц данными — в файле по этой ссылке.

Таблицы этой базы данных с заполненными данными имеют следующий вид.

Таблица Categories:

CatnumbCat_namePrice
10Стройматериалы105,00
505Недвижимость210,00
205Транспорт160,00
30Мебель77,00
45Техника65,00

Таблица Parts:

Part_IDPartCat
1Квартиры505
2
Автомашины
205
3Доски10
4Шкафы30
5Книги160

Заметим, что в таблице Parts Книги имеют Cat — ссылку на категорию, которой нет в таблице Categories, а в таблице Categories Техника имеет номер категории Catnumb — значение, ссылки на которое нет в таблице Parts.

Пример 1. Требуется соединить данные этих двух таблиц так, чтобы в результирующей таблице были поля Part (Часть), Cat (Категория) и Price (Цена подачи объявления) и чтобы данные полностью пересекались по условию. Условие — совпадение номера категории (Catnumb) в таблице Categories и ссылки на категорию в таблице Parts. Для этого пишем следующий запрос:

SELECT Parts.Part, Categories.Catnumb AS Cat, Categories.Price FROM Parts INNER JOIN Categories ON Parts.Cat = Categories.Catnumb

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

PartCatPrice
Квартиры505210,00
Автомашины205160,00
Доски10105,00
Шкафы3077,00

В результирующей таблице нет Книг, так как эта запись ссылается на категорию, которой нет в таблице Categories, и Техники, так как эта запись имеет внешний ключ в таблице Categories, на который нет ссылки в таблице Parts.

В ряде случаев при соединениях таблиц составить менее громоздкие запросы можно с помощью предиката EXISTS и без использования JOIN.

Есть база данных «Театр». Таблица Play содержит данные о постановках. Таблица Team — о ролях актёров. Таблица Actor — об актёрах. Таблица Director — о режиссёрах. Поля таблиц, первичные и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).

Пример 2. Определить самого востребованного актёра за последние 5 лет.

Оператор JOIN использовать 2 раза. Использовать COUNT(), CURDATE(), LIMIT 1.

Правильное решение и ответ.

Пример 3. Вывести список актеров, которые в одном спектакле играют более одной роли, и количество их ролей.

Оператор JOIN использовать 1 раз. Использовать HAVING, GROUP BY.

Подсказка. Оператор HAVING применяется к числу ролей, подсчитанных агрегатной функцией COUNT.

Правильное решение и ответ.

Запрос с оператором LEFT OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями из первой по порядку (левой) таблицы, даже если они не соответствуют условию. У записей левой таблицы, которые не соответствуют условию, значение столбца из правой таблицы будет NULL (неопределённым).

Пример 4. База данных и таблицы — те же, что и в примере 1.

Для получения результирующей таблицы, в которой данные из двух таблиц полностью пересекаются по условию и дополняются всеми данными из таблицы Parts, которые не соответствуют условию, пишем следующий запрос:

SELECT Parts.Part, Categories.Catnumb AS Cat, Categories.Price FROM Parts LEFT OUTER JOIN Categories ON Parts.Cat = Categories.Catnumb

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

Part
CatPrice
Квартиры505210,00
Автомашины205160,00
Доски10105,00
Шкафы3077,00
Книги160NULL

В результирующей таблице, в отличие от таблицы из примера 1, есть Книги, но значение столбца Цены (Price) у них — NULL, так как эта запись имеет идентификатор категории, которой нет в таблице Categories.

Запрос с оператором RIGHT OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями из второй по порядку (правой) таблицы, даже если они не соответствуют условию. У записей правой таблицы, которые не соответствуют условию, значение столбца из левой таблицы будет NULL (неопределённым).

Пример 5. База данных и таблицы — те же, что и в предыдущих примерах.

Для получения результирующей таблицы, в которой данные из двух таблиц полностью пересекаются по условию и дополняются всеми данными из таблицы Categories, которые не соответствуют условию, пишем следующий запрос:

SELECT Parts.Part, Categories.Catnumb AS Cat, Categories.Price FROM Parts RIGHT OUTER JOIN Categories ON Parts.Cat = Categories.Catnumb

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

PartCatPrice
Квартиры505210,00
Автомашины205160,00
Доски10105,00
Шкафы3077,00
NULL
45
65,00

В результирующей таблице, в отличие от таблицы из примера 1, есть запись с категорией 45 и ценой 65,00, но значение столбца Части (Part) у неё — NULL, так как эта запись имеет идентификатор категории, на которую нет ссылок в таблице Parts.

Запрос с оператором FULL OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями из первой (левой) и второй (правой) таблиц, даже если они не соответствуют условию. У записей, которые не соответствуют условию, значение столбцов из другой таблицы будет NULL (неопределённым).

Пример 6. База данных и таблицы — те же, что и в предыдущих примерах.

Для получения результирующей таблицы, в которой данные из двух таблиц полностью пересекаются по условию и дополняются всеми данными как из таблицы Parts, так и из таблицы Categories, которые не соответствуют условию, пишем следующий запрос:

SELECT Parts.Part, Categories.Catnumb AS Cat, Categories.Price FROM Parts FULL OUTER JOIN Categories ON Parts.Cat = Categories.Catnumb

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

PartCatPrice
Квартиры505210,00
Автомашины205160,00
Доски10105,00
Шкафы3077,00
Книги160NULL
NULL4565,00

В результирующей таблице есть записи Книги (из левой таблицы) и с категорией 45 (из правой таблицы), причём у первой из них неопределённая цена (столбец из правой таблицы), а у второй — неопределённая часть (столбец из левой таблицы).

В предыдущих запросах мы указывали с названиями извлекаемых столбцов из разных таблиц полные имена этих таблиц. Такие запросы выглядят громоздко: одно и то же слово повторяется несколько раз. Нельзя ли как-то упростить конструкцию? Оказывается, можно. Для этого следует использовать псевдонимы таблиц — их сокращённые имена. Псевдоним может состоять и из одной буквы. Возможно любое количество букв в псевдониме, главное, чтобы запрос после сокращения был понятен Вам самим. Общее правило: в секции запроса, определяющей соединение, то есть вокруг слова JOIN нужно указать полные имена таблиц, а за каждым именем должен следовать псевдоним таблицы.

Пример 7. Переписать запрос из примера 1 с использованием псевдонимов соединяемых таблиц.

Запрос будет следующим:

SELECT P.Part, C.Catnumb AS Cat, C.Price FROM Parts P INNER JOIN Categories C ON P.Cat = C.Catnumb

Запрос вернёт то же самое, что и запрос в примере 1, но он гораздо компактнее.

Реляционные базы данных должны подчиняться требованиям целостности и неизбыточности данных, в связи с чем данные об одном бизнес-процессе могут содержаться не только в одной, двух, но и в трёх и более таблицах. В этих случаях для анализа данных используются цепочки соединённых таблиц: например, в одной (первой) таблице содержится некоторый количественный показатель, вторую таблицу с первой и третьей связывают внешние ключи — данные пересекаются, но только третья таблица содержит условие, в зависимости от которого может быть выведен количественный показатель из первой таблицы. И таблиц может быть ещё больше. При помощи оператора SQL JOIN в одном запросе можно соединить большое число таблиц. В таких запросах за одной секцией соединения следует другая, причём каждый следующий JOIN соединяет со следующей таблицей таблицу, которая была второй в предыдущем звене цепочки. Таким образом, синтаксис SQL запроса для соединения более двух таблиц следующий:

SELECT ИМЕНА_СТОЛБЦОВ (1..N) FROM ИМЯ_ТАБЛИЦЫ_1 JOIN ИМЯ_ТАБЛИЦЫ_2 ON УСЛОВИЕ JOIN ИМЯ_ТАБЛИЦЫ_3 ON УСЛОВИЕ … JOIN ИМЯ_ТАБЛИЦЫ_M ON УСЛОВИЕ

Пример 8. База данных — та же, что и в предыдущих примерах. К таблицам Categories и Parts в этом примере добавится таблица Ads, содержащая данные об опубликованных на портале объявлениях. Приведём фрагмент таблицы Ads, в котором среди записей есть записи о тех объявлениях, срок публикации которых истекает 2018-04-02.

A_IdPart_IDDate_startDate_endText
211‘2018-02-11’‘2018-04-20’«Продаю…»
221‘2018-02-11’‘2018-05-12’«Продаю…»
271‘2018-02-11’‘2018-04-02’«Продаю…»
282‘2018-02-11’‘2018-04-21’«Продаю…»
292‘2018-02-11’‘2018-04-02’«Продаю…»
303‘2018-02-11’‘2018-04-22’«Продаю…»
314‘2018-02-11’‘2018-05-02’«Продаю…»
324‘2018-02-11’‘2018-04-13’«Продаю…»
333‘2018-02-11’‘2018-04-12’«Продаю…»
344‘2018-02-11’‘2018-04-23’«Продаю…»

Представим, что сегодня ‘2018-04-02’, то есть это значение принимает функция CURDATE() — текущая дата. Требуется узнать, к каким категориям принадлежат объявления, срок публикации которых истекает сегодня. Названия категорий есть только в таблице CATEGORIES, а даты истечения срока публикации объявлений — только в таблице ADS. В таблице PARTS — части категорий (или проще, подкатегории) опубликованных объявлений. Но внешним ключом Cat_ID таблица PARTS связана с таблицей CATEGORIES, а таблица ADS связана внешним ключом Part_ID с таблицей PARTS. Поэтому соединяем в одном запросе три таблицы и этот запрос можно с максимальной корректностью назвать цепочкой.

Запрос будет следующим:

SELECT C.Cat_name FROM Categories C JOIN Parts P ON P.Cat=C.Catnumb JOIN ads A ON A.Part_id=P.Part_id WHERE A.Date_end=CURDATE()

Результат запроса — таблица, содержащая названия двух категорий — «Недвижимость» и «Транспорт»:

Cat_name
Недвижимость
Транспорт

Использование оператора SQL CROSS JOIN в наиболее простой форме — без условия соединения — реализует операцию декартова произведения в реляционной алгебре. Результатом такого соединения будет сцепление каждой строки первой таблицы с каждой строкой второй таблицы. Таблицы могут быть записаны в запросе либо через оператор CROSS JOIN, либо через запятую между ними.

Пример 9. База данных — всё та же, таблицы — Categories и Parts. Реализовать операцию декартова произведения этих двух таблиц.

Запрос будет следующим:

SELECT (*) Categories CROSS JOIN Parts

Или без явного указания CROSS JOIN — через запятую:

SELECT (*) Categories, Parts

Запрос вернёт таблицу из 5 * 5 = 25 строк, фрагмент которой приведён ниже:

CatnumbCat_namePricePart_IDPartCat
10Стройматериалы105,001Квартиры505
10Стройматериалы105,002Автомашины205
10Стройматериалы105,003Доски10
10Стройматериалы105,004Шкафы30
10Стройматериалы105,005Книги160
45Техника65,001Квартиры505
45Техника65,002Автомашины205
45Техника65,003Доски10
45Техника65,004Шкафы30
45Техника65,005Книги160

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

Но для CROSS JOIN можно задать условие соединения! Результат будет совсем иным. При использовании оператора «запятая» вместо явного указания CROSS JOIN условие соединения задаётся не словом ON, а словом WHERE.

Пример 10. Та же база данных портала объявлений, таблицы Categories и Parts. Используя перекрестное соединение, соединить таблицы так, чтобы данные полностью пересекались по условию. Условие — совпадение идентификатора категории в таблице Categories и ссылки на категорию в таблице Parts.

Запрос будет следующим:

SELECT P.Part, C.Catnumb AS Cat, C.Price FROM Parts P, Categories C WHERE P.Cat = C.Cat_ID

Запрос вернёт то же самое, что и запрос в примере 1:

PartCatPrice
Квартиры505210,00
Автомашины205160,00
Доски10105,00
Шкафы3077,00

И это совпадение не случайно. Запрос c перекрестным соединением по условию соединения полностью аналогичен запросу с внутренним соединением — INNER JOIN — или, учитывая, что слово INNER — не обязательное, просто JOIN.

Таким образом, какой вариант запроса использовать — вопрос стиля или даже привычки специалиста по работе с базой данных. Возможно, перекрёстное соединение с условием для двух таблиц может представляться более компактным. Но преимущество перекрестного соединения для более чем двух таблиц (это также возможно) весьма спорно. В этом случае WHERE-условия пересечения перечисляются через слово AND. Такая конструкция может быть громоздкой и трудной для чтения, если в конце запроса есть также секция WHERE с условиями выборки.

Поделиться с друзьями

Реляционные базы данных и язык SQL

Оператор SQL INNER JOIN: синтаксис, примеры

Оператор SQL INNER JOIN формирует таблицу из записей двух или нескольких таблиц. Каждая строка из первой (левой) таблицы, сопоставляется с каждой строкой из второй (правой) таблицы, после чего происходит проверка условия. Если условие истинно, то строки попадают в результирующую таблицу. В результирующей таблице строки формируются конкатенацией строк первой и второй таблиц.

Оператор SQL INNER JOIN имеет следующий синтаксис:

SELECT
    column_names [,... n]
FROM
    Table_1 INNER JOIN Table_2
ON condition

Условие для сравнения задается в операторе ON.


Примеры оператора SQL INNER JOINИмеются две таблицы:

Authors — содержит в себе информацию об авторах книг:

AuthorID AuthorName
1 Bruce Eckel
2 Robert Lafore
3 Andrew Tanenbaum

Books — содержит в себе информацию о названии книг:

BookID BookName
3 Modern Operating System
1 Thinking in Java
3 Computer Architecture
4 Programming in Scala

В таблице Books поле BookID являются внешним ключом и ссылаются на таблицу Authors.

Пример 1. Используя оператор SQL INNER JOIN вывести на экран, какими авторами были написаны какие из книг:

SELECT *
FROM Authors INNER JOIN Books
ON Authors.AuthorID = Books.BookID

В данном запросе оператора SQL INNER JOIN условие сравнения — это равенство полей AuthorID и BookID. В результирующую таблицу не попадет книга под названием Programming in Scala, так как значение ее BookID не найдет равенства ни с одной строкой AuthorID.

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

Authors.AuthorID Authors.AuthorName Books.BookID Books.BookName
3 Andrew Tanenbaum 3 Modern Operating System
1 Bruce Eckel 1 Thinking in Java
3 Andrew Tanenbaum 3 Computer Architecture

Соединение таблиц или действие оператора SQL JOIN на примерах : WEBCodius

Оператор JOIN используется для соединения двух или нескольких таблиц. Соединение таблиц может быть внутренним (INNER) или внешним (OUTER), причем внешнее соединение может быть левым (LEFT), правым (RIGHT) или полным (FULL). Далее на примере двух таблиц рассмотрим различные варианты их соединения.

Синтаксис соединения таблиц оператором JOIN имеет вид:

FROM <таблица 1>
 [INNER]
 {{LEFT | RIGHT | FULL } [OUTER]} JOIN <таблица 2>
[ON <предикат>]

Предикат в этой конструкции определяет условие соединения строк из разных таблиц.

Допустим есть две таблицы (Auto слева и Selling справа), в каждой по четыре записи. Одна таблица содержит названия марок автомобилей (Auto), вторая количество проданных автомобилей (Selling):

id   name          id   sum
--   ----          --   ----
1    bmw           1    250
2    opel          5    450
3    kia           3    300
4    audi          6    400

Далее соединим эти таблицы по полю id несколькими различными способами. Совпадающие значения выделены красным для лучшего восприятия.

1. Внутреннее соединение (INNER JOIN) означает, что в результирующий набор попадут только те соединения строк двух таблиц, для которых значение предиката равно TRUE. Обычно используется для объединения записей, которые есть и в первой и во второй таблице, т. е. получения пересечения таблиц:

Красным выделена область, которую мы должны получить.

Итак, сам запрос:

SELECT * FROM 'Auto'
INNER JOIN 'Selling' ON 'Auto'.id = 'Selling'.id

И результат:

id   name          id   sum
--   ----          --   ----
1    bmw           1    250
3    kia           3    300

Ключевое слово INNER в запросе можно опустить.

В итоге запрос отбирает и соединяет те записи, у которых значение поля id в обоих таблицах совпадает.

2. Внешнее соединение (OUTER JOIN) бывает нескольких видов. Первым рассмотрим полное внешнее объединение (FULL OUTER JOIN), которое объединяет записи из обоих таблиц (если условие объединения равно true) и дополняет их всеми записями из обоих таблиц, которые не имеют совпадений. Для записей, которые не имеют совпадений из другой таблицы, недостающее поле будет иметь значение NULL. Граф выборки записей будет иметь вид:

Переходим к запросу:

SELECT * FROM 'Auto'
FULL OUTER JOIN 'Selling' ON 'Auto'.id = 'Selling'.id

Результат:

id   name          id   sum
--   ----          --   ----
1    bmw           1    250
2    opel        NULL   NULL
3    kia           3    300
4    audi        NULL   NULL
NULL NULL          5    450
NULL NULL          6    400

То есть мы получили все записи, которые есть в обоих таблицах. Записи у которых значение поля id совпадает соединяются, а у записей для которых совпадений не найдено недостающие поля заполняются значением NULL.

Ключевое слово OUTER можно опустить.

3. Левое внешнее объединение (LEFT OUTER JOIN). В этом случае получаем все записи удовлетворяющие условию объединения, плюс все оставшиеся записи из внешней таблицы, которые не удовлетворяют условию объединения. Граф выборки:

Запрос:

SELECT * FROM 'Auto'
LEFT OUTER JOIN 'Selling' ON 'Auto'.id = 'Selling'.id

Результат:

id   name          id   sum
--   ----          --   ----
1    bmw           1    250
2    opel        NULL   NULL
3    kia           3    300
4    audi        NULL   NULL

Запрос также можно писать без ключевого слова OUTER.

В итоге здесь мы получили все записи таблицы Auto. Записи для которых были найдены совпадения по полю id в таблице Selling соединяются, для остальных недостающие поля заполняются значением NULL.

Еще существует правое внешнее объединение (RIGHT OUTER JOIN). Оно работает точно также как и левое объединение, только  в качестве внешней таблицы будет использоваться правая (в нашем случае таблица Selling или таблица Б на графе).

Далее рассмотрим остальные возможные выборки с использованием объединения двух таблиц.

4. Получить все записи из таблицы А, которые не имеют объединения из таблицы Б. Граф:

То есть в нашем случае, нам надо получить все автомобили из таблицы Auto, которые не имеют продаж в таблице Selling.

Запрос:

SELECT * FROM 'Auto'
LEFT OUTER JOIN 'Selling' ON 'Auto'.id = 'Selling'.id
WHERE 'Selling'.id IS null

Результат:

id   name          id   sum
--   ----          --   ----
2    opel        NULL   NULL
4    audi        NULL   NULL

5. И последний вариант, получить все записи из таблицы А и Таблицы Б, которые не имеют объединений. Граф:

В нашем случае мы должны получить все записи из таблицы Auto, которые не связаны с таблицей Selling, и все записи из таблицы Selling, которые не имеют сопоставления из таблицы Auto.

Запрос:

SELECT * FROM 'Auto'
FULL OUTER JOIN 'Selling'
ON 'Auto'.id = 'Selling'.id
WHERE 'Auto'.id IS null
OR 'Selling'.id IS null

Результат:

id   name          id   sum
--   ----          --   ----
2    opel        NULL   NULL
4    audi        NULL   NULL
NULL NULL          5    450
NULL NULL          6    400

На этом все, до новых встреч на страницах блога.

Команды JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN — связывание таблиц

Команды JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN используются для связывания таблиц по определенным полям связи.

Синтаксис

SELECT поля FROM имя_таблицы
LEFT JOIN имя_связанной_таблицы ON условие_связи 
WHERE условие_выборки

Примеры

Все примеры будут по таблицам countries и cities, если не сказано иное.

Таблица countries:

id
айди
name
имя
1 Беларусь
2 Россия
3 Украина

Таблица cities:

id
айди
name
имя
country_id
айди страны
1 Минск 1
2 Витебск 1
3 Москва 2
4 Питер 2
5 Лондон 0

Пример . LEFT JOIN

В данном примере …:

SELECT
	cities.id as city_id, cities.name as city_name, cities.country_id as city_country_id,
	countries.id as country_id, countries.name as country_name
FROM cities
LEFT JOIN countries ON countries.id=cities.country_id

SQL запрос выберет следующие строки:

city_id
айди города
city_name
название города
city_country_id
айди страны
country_id
айди страны
country_name
название страны
1 Минск 1 1 Беларусь
2 Витебск 1 1 Беларусь
3 Москва 2 2 Россия
4 Питер 2 2 Россия
5 Лондон 0 NULL

Пример . RIGHT JOIN

В данном примере … Лондон не выберется, а Украина наоборот

SELECT
	cities.id as city_id, cities.name as city_name, cities.country_id as city_country_id,
	countries.id as country_id, countries.name as country_name
FROM cities
RIGHT JOIN countries ON countries.id=cities.country_id

SQL запрос выберет следующие строки:

city_id
айди города
city_name
название города
city_country_id
айди страны
country_id
айди страны
country_name
название страны
1 Минск 1 1 Беларусь
2 Витебск 1 1 Беларусь
3 Москва 2 2 Россия
4 Питер 2 2 Россия
NULL NULL NULL 3 Украина

Пример . INNER JOIN

В данном примере … Лондон и Украина не выберется

SELECT
	cities.id as city_id, cities.name as city_name, cities.country_id as city_country_id,
	countries.id as country_id, countries.name as country_name
FROM cities
INNER JOIN countries ON countries.id=cities.country_id

SQL запрос выберет следующие строки:

city_id
айди города
city_name
название города
city_country_id
айди страны
country_id
айди страны
country_name
название страны
1 Минск 1 1 Беларусь
2 Витебск 1 1 Беларусь
3 Москва 2 2 Россия
4 Питер 2 2 Россия

SQL join в примерах с описанием

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

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

Persons (Сотрудники)

Positions (должности)

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

INNER JOIN

Внутреннее присоединение. Равносильно просто JOIN или CROSS JOIN (верно для MYSQL, в стандарте SQL INNER JOIN не эквивалентен синтаксически CROSS JOIN, т.к. используется с выражением ON).

SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p INNER JOIN `positions` ps ON ps.id = p.post_id

SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`

FROM `persons` p

INNER JOIN `positions` ps ON ps.id = p.post_id

Такое присоединение покажет нам данные из таблиц только если условие связывания соблюдается — т.е. для сотрудника указан существующий в словаре идентификатор должности.

Если поменять порядок соединения таблиц — получим тот же результат.

Условно представим себе эти таблицы, как пересекающиеся множества, где пересечение — это наличие связи между таблицами. Получим картинку:

Далее проследим как получить разные части (подмножества) данного множества.

OUTER JOIN

Внешнее присоединение. Различают LEFT OUTER JOIN и RIGHT OUTER JOIN, и обычно опускают слово «OUTER».

Внешнее присоединение включает в себя результаты запроса INNER и добавляются «неиспользованные» строки из одной из таблиц. Какую таблицу использовать в качестве «добавки» — указывает токен LEFT или RIGHT.

LEFT JOIN

Внешнее присоединение «слева».

SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id

SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`

FROM `persons` p

LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id

«Левая» таблица persons, содержит строку id#3 — «Александр», где указан идентификатор должности, отсутствующей в словаре.

На картинке это можно показать вот так:

RIGHT JOIN

Присоединение «справа».

SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id

SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`

FROM `persons` p

RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id

Словарь должностей (правая таблица) содержит неиспользуемую запись с id#3 — «программист». Теперь она попала в результат запроса.

Полное множество

MySQL не знает соединения FULL OUTER JOIN. Что если нужно получить полное множество?

Первый способ — объединение запросов LEFT и RIGHT.

(SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id) UNION (SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id)

(SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`

FROM `persons` p

LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id)

UNION

(SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`

FROM `persons` p

RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id)

При таком вызове UNION, после слияния результатов, SQL отсечет дубли (как DISTINCT). Для отсечения дублей SQL прибегает к сортировке. Это может сказываться на быстродействии.

Второй способ — объединение LEFT и RIGHT, но в одном из запросов мы исключаем часть, соответствующую INNER. А объединение задаём как UNION ALL, что позволяет движку SQL обойтись без сортировки.

(SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id) UNION ALL (SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id WHERE p.id IS NULL)

(SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`

FROM `persons` p

LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id)

UNION ALL

(SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`

FROM `persons` p

RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id

WHERE p.id IS NULL)

Этот пример показывает нам как исключить пересечение и получить только левую или правую часть множества.

Левое подмножество

LEFT JOIN ограничиваем проверкой, что данных из второй таблицы нет.

SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id WHERE ps.id is NULL

SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`

FROM `persons` p

LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id

WHERE ps.id is NULL

В нашем примере — это специалисты, у которых не задана должность или нет должности с указанным ключом.

Правое подмножество

Точно также выделяем правую часть.

SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id WHERE p.id is NULL

SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`

FROM `persons` p

RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id

WHERE p.id is NULL

В нашем случае получим должности, которые никому не назначены.

Всё кроме пересечения

Остался один вариант, тот когда исключено пересечение множеств. Его можно сложить из двух предыдущих запросов через UNION ALL (т.к. подмножества не пересекаются).

(SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id WHERE ps.id is NULL) UNION ALL (SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность` FROM `persons` p RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id WHERE p.id is NULL)

(SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`

FROM `persons` p

LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id

WHERE ps.id is NULL)

UNION ALL

(SELECT p.id, p.name `Имя сотрудника`, ps.id `pos.id`, ps.name `Должность`

FROM `persons` p

RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id

WHERE p.id is NULL)

Данная запись опубликована в 19.09.2017 20:19 и размещена в mySQL. Вы можете перейти в конец страницы и оставить ваш комментарий.

Оператор соединения JOIN SQL — CodeTown.ru

Доброго времени суток! В этой статье по языку SQL мы познакомимся с оператором соединения двух таблиц — JOIN. Как и всегда, разберем практические примеры и посмотрим на различные варианты применения оператора JOIN в SQL.

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

Введение

В прошлых статьях мы уже работали с запросами, которые используют данные из двух и более таблиц: многотабличные запросы и вложенные запросы в SQL. Но те способы имели свои минусы либо по скорости, либо по избыточности данных. Оператор соединения JOIN наиболее выгоден среди этих способов, но его использование подразумевает операции только с двумя таблицами. Тем не менее, запросы выполняются гораздо быстрее, чем вложенные запросы.

В этой статье мы разберем несколько вариантов применения оператора JOIN:

  • INNER JOIN
  • OUTER JOIN
    • RIGHT OUTER JOIN
    • LEFT OUTER JOIN

Про эти варианты использования мы и поговорим подробнее.

Оператор INNER JOIN

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

Вывести сумму заказов и дату, которые проводил продавец с фамилией Колованов. Используйте оператор INNER JOIN.

SELECT amt, odate
FROM orders
INNER JOIN salespeople on orders.snum = salespeople.snum and sname = 'Колованов'

В этом запросе четко видно, что мы берем два поля из таблицы orders, а затем присоединяем таблицу salespeople с нужными нам условиями, которые задаются после ключевого слова ON. В данном случае проверка идет по snum и фамилии продавца. Вывод запроса аналогичен предыдущей статье:

amtodate
3482017-04-08
802017-09-02

Рассмотрим еще один пример на оператор INNER JOIN, уже сложнее:

Вывести среднюю суму заказов для каждого продавца.

SELECT AVG(amt) as 'Средняя цена', salespeople.sname
FROM orders
INNER JOIN salespeople on orders.snum = salespeople.snum 
GROUP BY salespeople.sname

В этом запросе уже выводятся два поля из разных таблиц, помимо этого происходит группировка по фамилиям продавца.

Средняя ценаsname
214Колованов
315.667Кучеров
1180Мозякин
640Плотников
900Проворов

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

Также заметьте, что всего в нашей таблицы 8 продавцов, а тут всего 5 строк — просто у других продавцов нет заказов в таблице orders.

Оператор OUTER JOIN

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

Оператор RIGHT OUTER JOIN

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

SELECT AVG(amt) as 'Средняя цена', salespeople.sname
FROM orders
RIGHT OUTER JOIN salespeople on orders.snum = salespeople.snum 
GROUP BY salespeople.sname

И вот, что поменялось в выводе:

Средняя ценаsname
214Колованов
315.667Кучеров
NULLМалкин
1180Мозякин
NULLПетров
640Плотников
900Проворов
NULLШипачев

Как уже было сказано, такой запрос покажет все значения для второй таблицы (то есть правой), даже если у них нет значений в левой таблице — стоит NULL. Посмотрите еще раз на вывод предыдущего запроса с INNER JOIN и этот, и проанализируйте разницу.

Оператор LEFT OUTER JOIN

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

Вывести дату заказов и фамилии абсолютно всех покупателей для этих заказов (если покупатель не совершал заказ, то его фамилию тоже необходимо вывести.

Итак, нам нужны все покупатели — значит в качестве первой (левой) таблицы возьмем таблицу customers, а затем будем присоединять таблицу orders.

SELECT customers.cname, odate
FROM customers
LEFT OUTER JOIN orders on orders.cnum = customers.cnum

Вывод:

cnameodate
Чудинов2016-01-01
Лосев2016-04-10
Краснов2017-04-08
Кириллов2016-06-07
Колесников2017-12-04
Колесников2016-03-03
Лермонтов2017-09-02
Деснов2016-03-07
Кириллов2017-10-07
Пушкин2016-01-08
ЕрмолаевNULL
БелыйNULL

Очевидно, что в выводе присутствуют все фамилии покупателей, а некоторые даже несколько раз, потому что не было никаких условий или группировок.

Примеры на соединение таблиц в SQL

1.Напишите запрос, который бы использовал оператор INNER JOIN для получения всех Заказов для покупателя с фамилией Краснов.

SELECT onum, amt, odate, cname
FROM orders
INNER JOIN customers on orders.cnum = customers.cnum and cname = 'Краснов'

2.Напишите запрос, который бы вывел фамилии и города продавцов, комиссия которых составляет более 20%.

SELECT DISTINCT(sname), city, comm
FROM salespeople
INNER JOIN orders on orders.snum = salespeople.snum and comm > 20

3.Напишите запрос, который бы вывел суммарную сумму заказов для городов в которых работают продавцы.

SELECT SUM(amt), salespeople.city
FROM orders
INNER JOIN salespeople on orders.snum = salespeople.snum 
GROUP BY salespeople.city

4.Повторите предыдущий запрос, но выведите все города, даже если в них не совершалась сделка.

SELECT SUM(amt), salespeople.city
FROM orders
RIGHT OUTER JOIN salespeople on orders.snum = salespeople.snum 
GROUP BY salespeople.city

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

SELECT customers.city, MAX(amt)
FROM customers
LEFT OUTER JOIN orders on orders.cnum = customers.cnum 
GROUP BY customers.city

Заключение

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

Поделиться ссылкой:

Похожее

Оператор JOIN

Оператор JOIN служит для объединения данных из нескольких таблиц. DB2 поддерживает несколько способов объединения таблиц, включая INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN.

Для демонстрации этих операторов создадим две таблицы.

Table1:

Table2:

Id2Field2
1AAA
2BBB
2CCC
6DDD
7EEE

INNER JOIN

INNER JOIN – внутреннее объединение при котором из двух таблиц выбираются данные, которые совпадают в обеих таблицах. Критерий по которому происходит объединение данных указывается в инструкции ON.

Для примера напишем запрос для объединения тестовых таблиц по полям Id1 и Id2:

SELECT 
    *
FROM 
    "Table1" 
INNER JOIN
    "Table2" 
ON 
    Id1 = Id2;

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

Id1Field1Id2Field2
1A1AAA
2B2BBB
2B2CCC
6D6DDD

В результате объединения тестовых таблиц в выборку попали только те строки в которых поля Id1, Id2 совпадают. Схематично это можно представить на рисунке:

Результат объединения INNER JOIN
LEFT JOIN

Инструкция LEFT JOIN выбирает все данные из первой таблицы и объединяет их с теми данными второй таблицы, в которых есть совпадения с первой таблицей. Те строки первой таблицы в которых не найдено совпадений со второй таблицей дополняются значением NULL.

Критерий по которому происходит объединение данных указывается в инструкции ON.

LEFT JOIN относится в внешнему объединению (OUTER JOIN). В некоторых базах данных допускается использование LEFT OUTER JOIN, что аналогично использованию LEFT JOIN.

Выполним следующий запрос к тестовым таблицам:

SELECT 
    *
FROM 
    "Table1" 
LEFT JOIN
    "Table2" 
ON 
    Id1 = Id2;

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

Id1Field1Id2Field2
1A1AAA
2B2BBB
2B2CCC
4CNULLNULL
6D6DDD

В результате объединения тестовых таблиц в выборку попали записи из первой таблицы, они были дополнены записями из второй таблицы в которых поля Id1, Id2 совпадают, те записи первой таблицы для которых не нашлось соответствия были дополнены значением NULL. Схематично это можно представить на рисунке:

Результат объединения LEFT JOIN
RIGTH JOIN

Инструкция RIGHT JOIN, как следует из названия, прямая противоположность инструкции LEFT JOIN. Она выбирает все данные из второй таблицы и объединяет их с теми данными первой таблицы, в которых есть совпадения со второй таблицей. Те строки второй таблицы в которых не найдено совпадений со строками первой таблицей дополняются значением NULL.

Критерий по которому происходит объединение данных указывается в инструкции ON.

RIGHT JOIN относится в внешнему объединению (OUTER JOIN). В некоторых базах данных допускается использование RIGHT OUTER JOIN, что аналогично использованию RIGHT JOIN.

Запрос для тестирования этой инструкции выглядит следующим образом:

SELECT 
    *
FROM 
    "Table1" 
RIGHT JOIN
    "Table2" 
ON 
    Id1 = Id2;

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

Id1Field1Id2Field2
1A1AAA
2B2BBB
2B2CCC
6D6DDD
NULLNULL7EEE

В результате объединения тестовых таблиц в выборку попали записи из второй таблицы, они были дополнены записями из первой таблицы в которых поля Id1, Id2 совпадают, те записи второй таблицы, для которых не нашлось соответствия были дополнены значением NULL. Схематичное изображение этой операции представлено на рисунке:

Результат объединения RIGHT JOIN
FULL JOIN

Инструкция FULL JOIN объединяет все данные первой таблицы со всеми данными второй таблицы. Те строки в которых не найдено совпадений дополняются значением NULL.

Критерий по которому происходит объединение данных указывается в инструкции ON.

Запрос для тестирования инструкции:

SELECT 
    *
FROM 
    "Table1" 
FULL JOIN
    "Table2" 
ON 
    Id1 = Id2;

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

Id1Field1Id2Field2
NULLNULL7EEE
1A1AAA
2B2BBB
2B2CCC
4CNULLNULL
6D6DDD

Схематичное изображение операции FULL JOIN представлено на рисунке:

Результат объединения FULL JOIN Оператор присоединения

— Azure Data Explorer

  • 10 минут на чтение

В этой статье

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

  Таблица1 | присоединиться (Таблица2) к CommonColumn, $ left.Col1 == $ right.Col2
  

Синтаксис

Левый стол | присоединиться [ JoinParameters ] ( RightTable ) на Атрибуты

Аргументы

  • LeftTable : таблица left или табличное выражение, иногда называемое внешней таблицей , строки которой должны быть объединены.Обозначается как $ слева .

  • RightTable : правая таблица или табличное выражение , иногда называемая внутренней таблицей , строки которой должны быть объединены. Обозначается как $ справа .

  • Атрибуты : одно или несколько правил , разделенных запятыми, , которые описывают, как строки из LeftTable сопоставляются со строками из RightTable . Множественные правила оцениваются с помощью логических операторов и .

    Правило может быть одним из:

    Вид правила Синтаксис Предикат
    Равенство по имени Имя столбца где LeftTable . Имя столбца == Правый стол . Имя столбца
    Равенство по значению $ осталось. LeftColumn == $ справа. Правая колонка где осталось $. LeftColumn == $ справа. Правая колонка

    Примечание

    Для «равенства по значению» имена столбцов должны быть квалифицированы как с соответствующей таблицей владельцев, обозначенной обозначениями $ left и $ right .

  • JoinParameters : Ноль или более параметров, разделенных пробелами в виде Имя = Значение , которое управляет поведением операции сопоставления строк и планом выполнения.Поддерживаются следующие параметры:

    Имя Значения Описание
    вид Присоединяйтесь к вкусам См. Объединить ароматы
    подсказка. Пульт авто , слева , местное , справа
    подсказка. Стратегия Подсказки выполнения См. Подсказки по объединению

Предупреждение

Если вид не указан, по умолчанию используется вид соединения innerunique .Это отличается от некоторых других аналитических продуктов, в которых по умолчанию используется внутренний . Просмотрите варианты соединения, чтобы понять различия и убедиться, что запрос дает желаемые результаты.

Возврат

Схема вывода зависит от типа соединения:

Присоединяйтесь к аромату Схема вывода
вид = leftanti , вид = leftsemi Таблица результатов содержит столбцы только с левой стороны.
вид = rightanti , вид = rightsemi Таблица результатов содержит столбцы только с правой стороны.
вид = внутренняя уникальность , вид = внутренний , вид = левый наружный , вид = правый внешний , вид = полный внешний Столбец для каждого столбца в каждой из двух таблиц, включая соответствующие ключи. Столбцы с правой стороны будут автоматически переименованы, если возникнут конфликты имен.

Выходные записи зависят от типа соединения:

Примечание

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

Присоединяйтесь к аромату Выходные записи
вид = leftanti , вид = leftantisemi Возвращает все записи с левой стороны, для которых нет совпадений с правой стороны
вид = rightanti , вид = rightantisemi Возвращает все записи с правой стороны, для которых нет совпадений с левой стороны.
вид неуказанный, вид = внутренний уникальный Только одна строка с левой стороны соответствует каждому значению на ключе . Выходные данные содержат строку для каждого совпадения этой строки со строками справа.
вид = левый полу Возвращает все записи с левой стороны, совпадающие с правой.
вид = правый полу Возвращает все записи с правой стороны, которые совпадают с левой.
вид = внутренний Содержит строку в выводе для каждой комбинации совпадающих строк слева и справа.
вид = левый внешний (или вид = правый внешний или вид = полный внешний ) Содержит по строке для каждой строки слева и справа, даже если она не соответствует. Несопоставленные выходные ячейки содержат нули.

Подсказка

Для лучшей производительности, если одна таблица всегда меньше другой, используйте ее как левую (конвейерную) сторону соединения.

Пример

Получите расширенные действия от имени входа , которые некоторые записи отмечают как начало и конец действия.

  let Events = MyLogTable | где type == "Событие";
События
| где Name == "Start"
| Название проекта, Город, ActivityId, StartTime = отметка времени
| присоединиться (События
    | где Name == "Stop"
        | проект StopTime = отметка времени, ActivityId)
    on ActivityId
| Город проекта, ActivityId, StartTime, StopTime, Duration = StopTime - StartTime
  
  let Events = MyLogTable | где type == "Событие";
События
| где Name == "Start"
| Название проекта, Город, ActivityIdLeft = ActivityId, StartTime = отметка времени
| присоединиться (События
        | где Name == "Stop"
        | проект StopTime = отметка времени, ActivityIdRight = ActivityId)
    на $ слева.ActivityIdLeft == $ right.ActivityIdRight
| Город проекта, ActivityId, StartTime, StopTime, Duration = StopTime - StartTime
  

Присоединяйтесь к ароматам

Точный вид оператора соединения указывается с помощью ключевого слова kind . Поддерживаются следующие разновидности оператора соединения:

Тип соединения по умолчанию

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

  X | присоединиться к Y on Key
 
X | присоединиться к kind = innerunique Y on Key
  

Следующие две таблицы используются для объяснения операции объединения.

Стол X

Ключ Значение1
1
б 2
б 3
с 4

Стол Y

Ключ Значение2
б 10
с 20
с 30
г 40

Соединение по умолчанию выполняет внутреннее соединение после дедупликации левой части ключа соединения (при дедупликации сохраняется первая запись).

Учитывая это заявление: X | присоединиться к Y по ключу

эффективная левая часть соединения, таблица X после дедупликации, будет:

и результат соединения будет:

  пусть X = datatable (Ключ: строка, Значение1: длинный)
[
    'а', 1,
    'Би 2,
    'b', 3,
    'c', 4
];
let Y = datatable (Ключ: строка, Значение2: длинный)
[
    'b', 10,
    'c', 20,
    'c', 30,
    'д', 40
];
X | присоединиться к Y on Key
  
Ключ Значение1 Ключ1 Значение2
б 2 б 10
с 4 c 20
с 4 с 30

Примечание

Ключи ‘a’ и ‘d’ не появляются в выводе, так как не было совпадающих ключей ни с левой, ни с правой стороны.

Ароматизатор внутреннего соединения

Функция внутреннего соединения похожа на стандартное внутреннее соединение из мира SQL. Выходная запись создается всякий раз, когда запись с левой стороны имеет тот же ключ соединения, что и запись с правой стороны.

  пусть X = datatable (Ключ: строка, Значение1: длинный)
[
    'а', 1,
    'Би 2,
    'b', 3,
    'c', 4
];
let Y = datatable (Ключ: строка, Значение2: длинный)
[
    'b', 10,
    'c', 20,
    'c', 30,
    'д', 40
];
X | присоединиться к виду = внутренний Y по ключу
  
Ключ Значение1 Ключ1 Значение2
б 3 б 10
б 2 б 10
с 4 с 20
с 4 с 30

Примечание

  • (б, 10) с правой стороны, был соединен дважды: оба (б, 2) и (б, 3) слева.
  • (c, 4) слева, был соединен дважды: оба (c, 20) и (c, 30) справа.

Innerunique-join аромат

Используйте тип innerunique-join для дедупликации ключей с левой стороны. Результатом будет строка на выходе каждой комбинации дедуплицированных левых и правых клавиш.

Примечание

внутренний уникальный аромат может дать два возможных результата, и оба они верны. В первом выводе оператор соединения случайным образом выбрал первый ключ, который появляется в t1, со значением «val1.1 «и сопоставил его с ключами t2. Во втором выводе оператор соединения случайным образом выбрал второй ключ, который появляется в t1, со значением «val1.2» и сопоставил его с ключами t2.

  пусть t1 = datatable (ключ: длинный, значение: строка)
[
1, "val1.1",
1, "val1.2"
];
пусть t2 = datatable (ключ: длинный, значение: строка)
[
1, "val1.3",
1, "val1.4"
];
t1
| присоединиться к виду = innerunique
    t2
по ключу
  
ключ значение ключ1 значение1
1 val1.1 1 значение1.3
1 значение1.1 1 значение 1,4
  let t1 = datatable (ключ: длинный, значение: строка)
[
1, "val1.1",
1, "val1.2"
];
пусть t2 = datatable (ключ: длинный, значение: строка)
[
1, "val1.3",
1, "val1.4"
];
t1
| присоединиться к виду = innerunique
    t2
по ключу
  
ключ значение ключ1 значение1
1 val1.2 1 значение1.3
1 значение1.2 1 значение 1,4
  • Kusto оптимизирован для выталкивания фильтров, которые идут после соединения , к соответствующей стороне соединения, влево или вправо, когда это возможно.

  • Иногда используется ароматизатор innerunique , и фильтр распространяется на левую часть соединения. Вариант будет автоматически распространен, и ключи, которые применяются к этому фильтру, всегда будут отображаться в выходных данных.

  • Используйте приведенный выше пример и добавьте фильтр , где значение == "val1.2" . Он всегда будет давать второй результат и никогда не даст первый результат для наборов данных:

  пусть t1 = datatable (ключ: длинный, значение: строка)
[
1, "val1.1",
1, "val1.2"
];
пусть t2 = datatable (ключ: длинный, значение: строка)
[
1, "val1.3",
1, "val1.4"
];
t1
| присоединиться к виду = innerunique
    t2
по ключу
| где value == "val1.2"
  
ключ значение ключ1 значение1
1 val1.2 1 значение1.3
1 значение1.2 1 значение 1,4

Ароматизатор левого внешнего соединения

Результат левого внешнего соединения для таблиц X и Y всегда содержит все записи левой таблицы (X), даже если условие соединения не находит ни одной совпадающей записи в правой таблице (Y).

  пусть X = datatable (Ключ: строка, Значение1: длинный)
[
    'а', 1,
    'Би 2,
    'b', 3,
    'c', 4
];
let Y = datatable (Ключ: строка, Значение2: длинный)
[
    'b', 10,
    'c', 20,
    'c', 30,
    'д', 40
];
X | присоединиться к виду = leftouter Y на ключ
  
Ключ Значение1 Ключ1 Значение2
б 3 б 10
б 2 б 10
с 4 с 20
с 4 с 30
1

Ароматизатор правого наружного соединения

Вариант правого внешнего соединения напоминает левое внешнее соединение, но обработка таблиц обратная.

  пусть X = datatable (Ключ: строка, Значение1: длинный)
[
    'а', 1,
    'Би 2,
    'b', 3,
    'c', 4
];
let Y = datatable (Ключ: строка, Значение2: длинный)
[
    'b', 10,
    'c', 20,
    'c', 30,
    'д', 40
];
X | присоединиться к виду = справа Y по ключу
  
Ключ Значение1 Ключ1 Значение2
б 3 б 10
б 2 б 10
с 4 с 20
с 4 с 30
г 40

Ароматизатор полного наружного соединения

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

  пусть X = datatable (Ключ: строка, Значение1: длинный)
[
    'а', 1,
    'Би 2,
    'b', 3,
    'c', 4
];
let Y = datatable (Ключ: строка, Значение2: длинный)
[
    'b', 10,
    'c', 20,
    'c', 30,
    'д', 40
];
X | присоединиться kind = fullouter Y on Key
  
Ключ Значение1 Ключ1 Значение2
б 3 б 10
б 2 б 10
с 4 с 20
с 4 с 30
г 40
1

Ароматизатор, предотвращающий слипание, левый

Левое антисоединение возвращает все записи с левой стороны, которые не соответствуют ни одной записи с правой стороны.

  пусть X = datatable (Ключ: строка, Значение1: длинный)
[
    'а', 1,
    'Би 2,
    'b', 3,
    'c', 4
];
let Y = datatable (Ключ: строка, Значение2: длинный)
[
    'b', 10,
    'c', 20,
    'c', 30,
    'д', 40
];
X | присоединиться к виду = leftanti Y on Key
  

Примечание

Anti-join моделирует запрос «NOT IN».

Ароматизатор против слипания, правый

Правое антисоединение возвращает все записи с правой стороны, которые не соответствуют ни одной записи с левой стороны.

  пусть X = datatable (Ключ: строка, Значение1: длинный)
[
    'а', 1,
    'Би 2,
    'b', 3,
    'c', 4
];
let Y = datatable (Ключ: строка, Значение2: длинный)
[
    'b', 10,
    'c', 20,
    'c', 30,
    'д', 40
];
X | присоединиться к kind = rightanti Y on Key
  

Примечание

Anti-join моделирует запрос «NOT IN».

Ароматизатор полусоединения левый

Левое полусоединение возвращает все записи с левой стороны, которые соответствуют записи с правой стороны. Возвращаются только столбцы с левой стороны.

  пусть X = datatable (Ключ: строка, Значение1: длинный)
[
    'а', 1,
    'Би 2,
    'b', 3,
    'c', 4
];
let Y = datatable (Ключ: строка, Значение2: длинный)
[
    'b', 10,
    'c', 20,
    'c', 30,
    'д', 40
];
X | присоединиться к виду = leftsemi Y на ключе
  

Ароматизатор правого полусоединения

Правое полусоединение возвращает все записи с правой стороны, которые соответствуют записи с левой стороны.Возвращаются только столбцы с правой стороны.

  пусть X = datatable (Ключ: строка, Значение1: длинный)
[
    'а', 1,
    'Би 2,
    'b', 3,
    'c', 4
];
let Y = datatable (Ключ: строка, Значение2: длинный)
[
    'b', 10,
    'c', 20,
    'c', 30,
    'д', 40
];
X | присоединиться kind = rightsemi Y on Key
  
Ключ Значение2
б 10
с 20
с 30

Поперечное соединение

Kusto изначально не предоставляет возможности кросс-соединения.Вы не можете пометить оператора с помощью kind = cross . Для моделирования используйте фиктивный ключ.

X | удлинить dummy = 1 | вид соединения = внутренний (Y | удлинить манекен = 1) на манекене

Подсказки присоединения

Оператор join поддерживает ряд подсказок, управляющих способом выполнения запроса. Эти подсказки не изменяют семантику соединения , но могут повлиять на его производительность.

Подсказки соединения описаны в следующих статьях:

Использование левого и правого объединения с использованием знака плюса (+) в Oracle

Как практически все реляционные базы данных, Oracle позволяет генерировать запросы, которые объединяют или JOIN строк из двух или более таблиц для создания окончательного набора результатов.Хотя существует множество типов соединений, которые могут быть выполнены, наиболее распространенными являются INNER JOIN и OUTER JOIN .

В этом руководстве мы кратко рассмотрим разницу между INNER и OUTER JOIN , а затем рассмотрим сокращенный метод, который Oracle предоставляет для выполнения OUTER JOINS , в частности, с использованием символа оператора + .

Что такое внутреннее соединение?

ВНУТРЕННЕЕ СОЕДИНЕНИЕ в реляционной базе данных — это просто соединение двух или более таблиц, в результате которого будет содержать только данные, удовлетворяющие всем условиям соединения.

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

  ВЫБРАТЬ * ИЗ библиотеки.languages;

имя id
1 английский
2 французских
3 Немецкий
4 мандаринский
5 Испанский
6 арабский
7 Японский
8 Русский
9 греческий
10 итальянский
  

Между тем, наша таблица books имеет строку language_id , которая для большинства, но не всех книг просто содержит language_id , связанный с исходным опубликованным языком книги:

  ВЫБРАТЬ * ИЗ
  книги
СОРТИРОВАТЬ ПО
  я бы
ВЫБРАТЬ ТОЛЬКО ПЕРВЫЕ 10 РЯДОВ;

id название автор year_published language_id
1 В поисках утраченного времени Марсель Пруст 1913 2
2 Улисс Джеймс Джойс 1922 1
3 Дон Кихот Мигель де Сервантес 1605 5
4 Моби Дик Герман Мелвилл 1851 1
5 Гамлет Уильям Шекспир 1601 (null)
6 Война и мир Лев Толстой 1869 8
7 Одиссея Гомера -700 9
8 Великий Гэтсби Ф.Скотт Фицджеральд 1925 1
9 Божественная комедия Данте Алигьери 1472 10
10 Мадам Бовари Гюстав Флобер 1857 2
  

Во многих случаях мы можем захотеть выполнить ВНУТРЕННЕЕ СОЕДИНЕНИЕ для книг, и языков, таблиц, поэтому вместо того, чтобы просматривать бессмысленное значение language_id каждой книги, мы фактически можем увидеть вместо этого имя языка .

  ВЫБРАТЬ
  делать ставку,
  б. название,
  б. автор,
  b.year_published,
  л.язык названий
ИЗ
  книги б
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
  library.languages ​​l
НА
  b.language_id = l.id
СОРТИРОВАТЬ ПО
  делать ставку
ВЫБРАТЬ ТОЛЬКО ПЕРВЫЕ 10 РЯДОВ;

id название автор year_published language
1 В поисках утраченного времени Марсель Пруст 1913 Французский
2 Ulysses Джеймс Джойс 1922 г., английский язык
3 Дон Кихот Мигель де Сервантес 1605 Испанский
4 Моби Дик Герман Мелвилл 1851 Английский
6 Война и мир Лев Толстой 1869 Русский
7 Одиссея Гомера -700 Греческий
8 Великий Гэтсби Ф. Скотт Фицджеральд 1925 г. Английский язык
9 Божественная комедия Данте Алигьери 1472 Итальянский
10 Мадам Бовари Гюстав Флобер 1857 Французский
11 Братья Карамазовы Фёдор Достоевский 1880 Русский
  

Здесь важно отметить, что наш набор результатов немного отличался в двух вышеупомянутых запросах.В первом мы просто перечислили первые 10 книг, но в запросе INNER JOIN мы возвращаем только результаты, которые удовлетворяют всем условиям из обеих таблиц. По этой причине запись Hamlet (которая имеет значение language_id null или пусто) игнорируется и не возвращается в результате нашего INNER JOIN .

Что такое внешнее соединение?

Вместо того, чтобы исключительно возвращать результаты, которые удовлетворяют всем условиям соединения INNER JOIN , OUTER JOIN возвращает не только результаты, которые удовлетворяют всем условиям, но также возвращает строки из одной таблицы, которые не удовлетворяют условию.Таблица, которая выбирается для этого «обхода» условных требований, определяется направленностью или «стороной» соединения, обычно называемым внешними соединениями LEFT или RIGHT .

При определении стороны для вашего ВНЕШНЕЕ СОЕДИНЕНИЕ вы указываете, какая таблица всегда будет возвращать свою строку, даже если таблица , противоположная на другой стороне соединения, имеет отсутствующие или нулевые значения как часть условия соединения.

Следовательно, если мы выполним тот же базовый JOIN , что и выше, для получения книг и языков с именами , мы знаем, что наша таблица books всегда должна возвращать данные, поэтому наша сторона JOIN должна «указывать на» нашу books table, тем самым превращая таблицу languages ​​ в таблицу OUTER , которую мы присоединяем к ней.

Для этого мы просто меняем:

  книг b Библиотека INNER JOIN, языки l
  

… к этому:

  книг b LEFT OUTER JOIN library.languages ​​l
  

Таким образом, весь запрос и набор результатов выглядят почти идентично INNER JOIN , за исключением небольшого изменения:

  ВЫБРАТЬ
  делать ставку,
  б. название,
  б. автор,
  b.year_published,
  l.название язык
ИЗ
  книги б
ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ
  library.languages ​​l
НА
  б.language_id = l.id
СОРТИРОВАТЬ ПО
  делать ставку
ВЫБРАТЬ ТОЛЬКО ПЕРВЫЕ 10 РЯДОВ;

id название автор year_published language
1 В поисках утраченного времени Марсель Пруст 1913 Французский
2 Ulysses Джеймс Джойс 1922 г., английский язык
3 Дон Кихот Мигель де Сервантес 1605 Испанский
4 Моби Дик Герман Мелвилл 1851 Английский
5 Гамлет Уильям Шекспир 1601 (null)
6 Война и мир Лев Толстой 1869 Русский
7 Одиссея Гомера -700 Греческий
8 Великий Гэтсби Ф. Скотт Фицджеральд 1925 г. Английский язык
9 Божественная комедия Данте Алигьери 1472 Итальянский
10 Мадам Бовари Гюстав Флобер 1857 Французский
  

Как и ожидалось, используя LEFT OUTER JOIN вместо предыдущего INNER JOIN , мы получаем лучшее из обоих миров: мы не пропускаем никакие книги записей (например, Hamlet ) просто потому, что значение language_id для этой записи равно null , но для всех записей, где существует language_id , мы получаем красиво отформатированное имя языка , полученное из нашей таблицы languages ​​.

Выполнение внешних соединений с использованием символа (+)

Как указано в официальной документации, Oracle предоставляет специальный оператор внешнего соединения (символ + ), который является сокращением для выполнения OUTER JOINS .

На практике символ + помещается непосредственно в условный оператор и сбоку от необязательной таблицы (той, которая может содержать пустые или нулевые значения в условном выражении).

Следовательно, мы можем еще раз переписать наш вышеупомянутый оператор LEFT OUTER JOIN , используя оператор + следующим образом:

  ВЫБРАТЬ
  делать ставку,
  б. название,
  б. автор,
  b.year_published,
  l.название язык
ИЗ
  книги б,
  library.languages ​​l
ГДЕ
  l.id (+) = b.language_id
СОРТИРОВАТЬ ПО
  делать ставку
ВЫБРАТЬ ТОЛЬКО ПЕРВЫЕ 10 РЯДОВ;
  

Результаты такие же, как и в приведенном выше стандартном примере LEFT OUTER JOIN , поэтому мы не будем включать их здесь. Однако следует обратить внимание на один важный аспект синтаксиса с использованием оператора + для OUTER JOINS .

Оператор + должен находиться слева от условного оператора (слева от знака равно = ). Следовательно, в этом случае, поскольку мы хотим убедиться, что наша таблица языков является дополнительной таблицей, которая может возвращать нулевых значений во время этого сравнения, мы поменяли порядок таблиц в этом условном условии, поэтому языков находится на слева (и необязательно), а книг справа.

Наконец, из-за переупорядочения сторон таблицы в условном выражении при использовании оператора + важно понимать, что приведенное выше является просто сокращением для RIGHT OUTER JOIN .Это означает, что этот фрагмент запроса:

  ИЗ
  книги б,
  library.languages ​​l
ГДЕ
  l.id (+) = b.language_id
  

… фактически идентично этому:

  ИЗ
  library.languages ​​l
ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ
  книги б
НА
  b.language_id = l.id
  

SQL-соединений с операторами сравнения | Средний уровень SQL

Начиная с этого места? Этот урок является частью полного руководства по использованию SQL для анализа данных.Проверьте начало.

В этом уроке мы рассмотрим:

В этом уроке используются те же данные из предыдущих уроков, которые были извлечены из Crunchbase 5 февраля 2014 г. Подробнее об этом наборе данных.

Использование операторов сравнения с объединениями

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

  ВЫБРАТЬ компании.постоянная ссылка,
       company.name,
       company.status,
       COUNT (investments.investor_permalink) AS инвесторы
  ОТ tutorial.crunchbase_companies компании
  LEFT JOIN tutorial.crunchbase_investments_part1 investments
    ON companies.permalink = investments.company_permalink
   И инвестиции.funded_year> companies.founded_year + 5
 ГРУППА ПО 1,2, 3
  

Этот метод особенно полезен для создания диапазонов дат, как показано выше. Важно отметить, что это дает другой результат, чем следующий запрос, потому что он объединяет только строки, которые соответствуют инвестициям .funded_year> companies.founded_year + 5 условие вместо объединения всех строк с последующей фильтрацией:

  ВЫБЕРИТЕ комп. Постоянная ссылка,
       company.name,
       company.status,
       COUNT (investments.investor_permalink) AS инвесторы
  ОТ tutorial.crunchbase_companies компании
  LEFT JOIN tutorial.crunchbase_investments_part1 investments
    ON companies.permalink = investments.company_permalink
 ГДЕ investments.funded_year> companies.founded_year + 5
 ГРУППА ПО 1,2, 3
  

Чтобы узнать больше об этих различиях, вернитесь к уроку Соединения SQL с использованием WHERE или ON.

SQL и оператор JOIN — SQLServerCentral

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

Но реальные запросы часто намного сложнее, чем эти простые операторы SELECT.

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

Раньше администраторы баз данных и разработчики помещали все необходимые таблицы и / или представления в предложение FROM, а затем использовали предложение WHERE для определения того, как записи из каждой таблицы будут объединяться с другими записями. (Чтобы сделать этот текст более читабельным, с этого момента я буду упрощать его и говорить «таблица» вместо «таблица и / или представление»).

Но прошло много времени с тех пор, как у нас есть стандарт для объединения этих данных.И это делается с помощью оператора JOIN (ANSI-SQL 92). К сожалению, есть некоторые подробности об операторах JOIN, которые остаются неясными для многих.

Ниже я покажу различные синтаксисы объединений, поддерживаемые T-SQL (то есть SQL SERVER 2008). Я выделю несколько концепций, которые, я считаю, не следует забывать каждый раз, когда мы объединяем данные из двух или более таблиц.

Начало работы: 1 стол, без соединения

Когда у вас есть только один объект для запроса, синтаксис будет довольно простым, и соединение не будет использоваться.Это будет старый добрый « SELECT fields FROM object » плюс любое другое необязательное предложение, которое вы, возможно, захотите использовать (то есть WHERE, GROUP BY, HAVING или ORDER BY).

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

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

Логика объединения таблиц

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

Рисунок на рис. 1 очень похож на те, что можно найти в книжках моих детей для первого класса. Идея состоит в том, чтобы найти соответствующие объекты в различных наборах. Что ж, это именно то, что мы делаем с SQL JOINs!

Рисунок 1 : Объединение объектов из разных наборов

Как только вы поймете аналогию, все обретет смысл.

Считайте, что 2 набора в , рис. 1, — это таблицы, а числа, которые мы видим, — это ключи, которые мы будем использовать для соединения таблиц. Таким образом, в каждом наборе вместо представления всех записей мы видим только ключевые поля из каждой таблицы. Набор результатов этой комбинации будет определяться типом рассматриваемого соединения, и это тема, которую я сейчас покажу. Чтобы проиллюстрировать наши примеры, представьте, что у нас есть 2 таблицы, показанные ниже:

Стол1

ключ1 поле1 поле 2 ключ2 ключ3
3 Эрик 8 1 6
4 Джон 3 4 4
6 Марка 3 7 1
7 Питер 6 8 5
8 Гарри 0 9 2

Стол 2

ключ2 поле1 поле 2 поле3
1 Нью-Йорк A N
2 Сан-Паулу B N
4 Париж С Y
5 Лондон С Y
6 Рим С Y
9 Мадрид С Y
0 Бангалор D N

Сценарий для создания и заполнения этих таблиц доступен в виде одного прикрепленного файла ( SQLServerCentral.com_JOIN.sql ) в разделе «Ресурсы» ниже.

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

Что ж, теперь мы готовы к работе. Давайте проверим типы объединений, которые мы можем использовать в T-SQL, соответствующий синтаксис и результирующий набор, который каждый из них будет генерировать.

Внутреннее соединение

Это наиболее распространенное соединение, которое мы используем в SQL. Возвращает пересечение двух наборов. Или с точки зрения таблиц, он приносит только записи из обеих таблиц, которые соответствуют заданному критерию.

Мы можем видеть на рисунке 2 диаграмму Венна, иллюстрирующую внутреннее соединение двух таблиц. Набор результатов операции — это область красного цвета.

Рисунок 2 : Представление ВНУТРЕННЕГО СОЕДИНЕНИЯ

Теперь проверьте синтаксис, чтобы объединить данные из Table1 и Table2 с помощью INNER JOIN.

ВЫБЕРИТЕ t1.key1, t1.field1 как Name, t1.key2 как T1Key, 
t2.key2 как T2Key, t2.field1 как City
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.key2 = t2.key2;

Набор результатов этого оператора будет:

ключ1 Имя T1Key T2Key Город
3 Эрик 1 1 Нью-Йорк
4 Джон 4 4 Париж
8 Гарри 9 9 Мадрид

Обратите внимание, что он вернул только данные из записей, которые имеют одинаковое значение для key2 как в Table1 , так и в Table2 .

В отличие от ВНУТРЕННЕГО СОЕДИНЕНИЯ существует также ВНЕШНЕЕ СОЕДИНЕНИЕ. Существует 3 типа ВНЕШНИХ СОЕДИНЕНИЙ: полное, левое и правое. Мы подробно рассмотрим каждый из них ниже.

ПОЛНОЕ СОЕДИНЕНИЕ

Это также известно как ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ (зарезервированное слово ВНЕШНИЙ является необязательным). ПОЛНЫЕ СОЕДИНЕНИЯ работают как объединение двух наборов. Теперь у нас есть в рис. 3 диаграмма Венна, иллюстрирующая ПОЛНОЕ СОЕДИНЕНИЕ двух таблиц. Набор результатов операции снова является красной областью.

Рисунок 3 : Представление ПОЛНОГО СОЕДИНЕНИЯ

Синтаксис почти такой же, как мы видели раньше.

ВЫБЕРИТЕ t1.key1, t1.field1 как Name, t1.key2 как T1Key, 
t2.key2 как T2Key, t2.field1 как City
FROM Table1 t1
FULL JOIN Table2 t2 ON t1.key2 = t2.key2;

Набор результатов этого оператора будет:

ключ1 Имя T1Key T2Key Город
3 Эрик 1 1 Нью-Йорк
4 Джон 4 4 Париж
6 Марка 7 null null
7 Питер 8 null null
8 Гарри 9 9 Мадрид
null null null 2 Сан-Паулу
null null null 5 Лондон
null null null 6 Рим
null null null 0 Бангалор

FULL JOIN возвращает все записи из Table1 и Table2 без дублирования данных.

ЛЕВОЕ СОЕДИНЕНИЕ

Также известный как LEFT OUTER JOIN, это частный случай FULL JOIN. Он приносит все запрошенные данные из таблицы, которая появляется слева от оператора JOIN, а также данные из правой таблицы, которая пересекается с первой. Ниже у нас есть диаграмма Венна, иллюстрирующая ЛЕВОЕ СОЕДИНЕНИЕ двух таблиц на Рисунке 4.

Рисунок 4: Представление ЛЕВОГО СОЕДИНЕНИЯ

См. Синтаксис ниже.

ВЫБЕРИТЕ t1.key1, t1.field1 как Name, t1.key2 как T1Key, 
t2.key2 как T2Key, t2.field1 как City
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.key2 = t2.key2;

Набор результатов этого оператора будет:

ключ1 Имя T1Key T2Key Город
3 Эрик 1 1 Нью-Йорк
4 Джон 4 4 Париж
6 Марка 7 null null
7 Питер 8 null null
8 Гарри 9 9 Мадрид

Третья и четвертая записи ( key1 равно 6 и 7) показывают значения NULL в последних полях, потому что нет информации, которая должна быть извлечена из второй таблицы.Это означает, что у нас есть значение в поле key2 в Table1 без соответствующего значения в Table2 . Мы могли бы избежать этой «несогласованности данных», если бы у нас был внешний ключ в поле key2 в Table1 .

ПРАВОЕ СОЕДИНЕНИЕ

Также известный как RIGHT OUTER JOIN, это еще один частный случай FULL JOIN. Он приносит все запрошенные данные из таблицы, которая появляется справа от оператора JOIN, а также данные из левой таблицы, которая пересекается с правой.Диаграмма Венна для ПРАВОГО СОЕДИНЕНИЯ двух таблиц представлена ​​на рисунке 5.

Рисунок 5: Представление ПРАВОГО СОЕДИНЕНИЯ

Как видите, синтаксис очень похож.

ВЫБЕРИТЕ t1.key1, t1.field1 как Name, t1.key2 как T1Key, 
t2.key2 как T2Key, t2.field1 как City
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.key2 = t2.key2;

Набор результатов этого оператора будет:

ключ1 Имя T1Key T2Key Город
null null null 0 Бангалор
3 Эрик 1 1 Нью-Йорк
null null null 2 Сан-Паулу
4 Джон 4 4 Париж
null null null 5 Лондон
null null null 6 Рим
8 Гарри 9 9 Мадрид

Обратите внимание, что записи с key1 , равными 6 и 7, больше не появляются в наборе результатов.Это потому, что у них нет соответствующей записи в правой таблице. Есть 4 записи, показывающие значения NULL в первых полях, потому что они недоступны в левой таблице.

КРЕСТ СОЕДИНЕНИЕ

CROSS JOIN на самом деле является декартовым произведением. Использование CROSS JOIN генерирует точно такой же результат вызова двух таблиц (разделенных запятой) вообще без какого-либо JOIN. Это означает, что мы получим огромный набор результатов, где каждая запись Table1 будет дублироваться для каждой записи в Table2 .Если Table1, имеет N1 записей, а Table2 имеет N2 записей, на выходе будет N1 умноженное на N2 записей.

Я не верю, что есть способ представить этот результат на диаграмме Венна. Думаю, это будет трехмерное изображение. Если это действительно так, диаграмма будет больше сбивать с толку, чем объяснять.

Синтаксис CROSS JOIN будет:

ВЫБЕРИТЕ t1.key1, t1.field1 как Name, t1.key2 как T1Key, 
t2.key2 как T2Key, t2.field1 как City
FROM Table1 t1
CROSS JOIN Table2 t2;

Поскольку Таблица1, имеет 5 записей, а Таблица2, — еще 7, выходные данные для этого запроса будут иметь 35 записей (5 x 7).

Проверьте прикрепленный файл (SQLServerCentral.com_JOIN_CrossJoin.rpt).

Честно говоря, я не помню в данный момент ни одной реальной ситуации, в которой мне действительно нужно было бы сгенерировать декартово произведение двух таблиц. Но всякий раз, когда вам нужно, CROSS JOIN все равно есть.

Кроме того, стоит позаботиться о производительности. Допустим, вы случайно запустили на своем производственном сервере запрос с CROSS JOIN для двух таблиц с 1 миллионом записей.Это наверняка вызовет у вас головную боль. Вероятно, ваш сервер начнет показывать проблемы с производительностью, так как ваш запрос может выполняться в течение некоторого времени, потребляя значительный объем ресурсов сервера.

САМОСОЕДИНЕНИЕ

Оператор JOIN может использоваться для объединения любой пары таблиц, включая объединение таблицы с самой собой. Это «самостоятельное присоединение». Самостоятельное присоединение может использовать любой оператор JOIN.

Например, посмотрите этот классический пример возврата начальника сотрудника (на основе Table1 ).В этом примере мы считаем, что значение в поле field2 на самом деле является кодовым номером начальника, поэтому связано с key1 .

ВЫБЕРИТЕ t1.key1, t1.field1 как имя,
   t1.field2, mirror.field1 как босс
FROM Table1 t1 
LEFT JOIN Table1 mirror ON t1.field2 = mirror.key1;

И это результат этого запроса.

ключ1 Имя поле 2 Бобышка
3 Эрик 8 Гарри
4 Джон 3 Эрик
6 Марка 3 Эрик
7 Питер 6 Гарри
8 Гарри 0 null

В этом примере последняя запись показывает, что у Гарри нет начальника, или, другими словами, он №1 в иерархии компании.

Без пересечения множеств

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

Обратите внимание на наборы результатов выше, и вы увидите, что вам нужно только добавить предложение WHERE к вашему оператору SQL, ища записи, которые имеют значение NULL для ключа Table2 .Итак, набор результатов, который мы ищем, представляет собой красную область, показанную на диаграмме Венна ниже (рисунок 6).

Рисунок 6: Несовпадающие записи из Table1 .

Мы можем написать LEFT JOIN для этого запроса, например:

ВЫБЕРИТЕ t1.key1, t1.field1 как Name, t1.key2 как T1Key, 
t2.key2 как T2Key, t2.field1 как City
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.key2 = t2.key2
WHERE t2. key2 ЕСТЬ NULL;

И, наконец, набор результатов будет:

ключ1 Имя T1Key T2Key Город
6 Марка 7 null null
7 Питер 8 null null

Когда мы выполняем такой запрос, мы должны обращать внимание на то, какое поле мы выбираем для предложения WHERE.Мы должны использовать поле, которое не допускает значений NULL. В противном случае набор результатов может включать нежелательные записи. Поэтому я предложил использовать ключ второй таблицы. В частности, его первичный ключ. Поскольку первичные ключи не принимают значения NULL, они гарантируют, что наш набор результатов будет именно тем, что нам нужно.

Одно слово о планах выполнения

Эти комментарии приводят нас к важному выводу. Обычно мы не перестаем думать об этом, но заметим, что план выполнения SQL-запросов сначала вычисляет набор результатов для предложения FROM и оператора JOIN (если есть), а затем будет выполнено предложение WHERE.

Это так же верно для SQL Server, как и для любой другой СУБД.

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

Рисунок 7: План выполнения запроса с использованием LEFT JOIN

Объединения и индексы

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

Когда мы пишем наши запросы, мы ожидаем, что оптимизатор запросов SQL Server будет использовать индексы таблиц для повышения производительности вашего запроса. Мы также можем помочь оптимизатору запросов выбрать проиндексированные поля, которые будут частью вашего запроса.

Например, при использовании оператора JOIN идеальный подход состоит в том, чтобы основать условие соединения на индексированных полях.Снова проверив план выполнения, мы замечаем, что использовался кластеризованный индекс на Table2 . Этот индекс был автоматически построен на key2 при создании этой таблицы, поскольку key2 является первичным ключом для этой таблицы.

С другой стороны, Table1 не имеет индекса в поле key2 . Из-за этого оптимизатор запросов попытался проявить достаточную смекалку и повысить производительность запроса key2 с использованием единственного доступного индекса. Это был кластерный индекс таблицы, основанный на key1 , первичный ключ на Table1 .Как видите, оптимизатор запросов — действительно умный инструмент. Но вы бы очень помогли ему, создав новый индекс (некластеризованный) на key2 .

Вспомнив немного о ссылочной целостности, вы увидите, что key2 должен быть внешним ключом в Table1 , потому что он связан с другим полем в другой таблице (это Table2.key2 ).

Лично я считаю, что внешние ключи должны существовать во всех реальных моделях баз данных. И неплохо создать некластеризованные индексы для всех внешних ключей.Вы всегда будете запускать множество запросов, а также использовать оператор JOIN на основе вашего первичного и внешнего ключей.

(ВАЖНО: SQL Server автоматически создает кластеризованный индекс по первичным ключам. Но по умолчанию он ничего не делает с внешними ключами. Поэтому убедитесь, что у вас есть правильные настройки в вашей базе данных).

Неравные сравнения

Когда мы пишем операторы SQL с помощью оператора JOIN, мы обычно сравниваем, равно ли одно поле в одной таблице другому полю в другой таблице.Но это не обязательный синтаксис. Мы могли бы использовать любой логический оператор, например, отличный от (<>), больше (>), меньше (<) и так далее.

Хотя эти причудливые вещи могут создать впечатление, что SQL дает столько возможностей, я считаю, что это скорее косметическая функция. Рассмотрим этот пример. См. Таблицу 1 выше, где у нас есть 5 записей. Теперь рассмотрим следующий оператор SQL.

ВЫБЕРИТЕ t1.key1, t1.field1 как Name, t1.key2 как T1Key, 
t2.key2 как T2Key, t2.field1 как City
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.key2 <= t2.key2
WHERE t1.key1 = 3;

Обратите внимание, что здесь используется внутреннее соединение, и мы специально выбираем одну запись из Table1 , где key1 равно 3. Единственная проблема заключается в том, что есть 6 записей и Table2 , которые удовлетворяют условию соединения. Взгляните на результат этого запроса.

ключ1 Имя T1Key T2Key Город
3 Эрик 1 1 Нью-Йорк
3 Эрик 1 2 Сан-Паулу
3 Эрик 1 4 Париж
3 Эрик 1 5 Лондон
3 Эрик 1 6 Рим
3 Эрик 1 9 Мадрид

Проблема с неравными объединениями в том, что они обычно дублируют записи.И это не то, что вам нужно регулярно. В любом случае, теперь вы знаете, что можете это сделать.

Множественные СОЕДИНЕНИЯ

SQL JOIN всегда сводится к объединению пары таблиц и поиску связанных объектов, которые подчиняются заданному правилу (обычно, но не ограничиваясь, равными значениями). Мы можем присоединиться к нескольким столам. Например, чтобы объединить 3 таблицы, вам понадобится 2 объединения. И для каждой новой таблицы потребуется новое соединение. Если вы используете соединение на каждом шаге, чтобы объединить N таблиц, вы будете использовать соединения N-1.

Важным моментом является то, что SQL позволяет использовать в одном операторе различные типы объединений.

Но администраторы баз данных и разработчики должны быть осторожны при объединении слишком большого количества таблиц. Несколько раз я встречал ситуации, когда запросы требовали 10, 20 таблиц или даже больше. По соображениям производительности не рекомендуется выполнять один запрос для объединения всех данных. Оптимизатор запросов будет работать лучше, если вы разбите его на несколько более мелких и простых запросов.

Теперь представьте, что у нас есть третья таблица с названием Table3 , показанная ниже.

Стол 3

ключ3 поле1
1 Инженер
2 Хирург
3 DBA
4 Юрист
5 Учитель
6 Актер

А теперь напишем заявление, в котором укажите имя сотрудника, город, в котором он живет, и по профессии.Это потребует от нас присоединиться ко всем трем столам. Просто помните, что объединения записываются парами. Итак, сначала мы соединим Table1 с Table2 . А затем мы объединим Table1 и Table3 . Результирующий сценарий показан ниже.

ВЫБЕРИТЕ t1.key1, t1.field1 как Сотрудник, 
t2.key2, t2.field1 как Город,
t3.key3, t3.field1 как Профессию
ИЗ Table1 t1
INNER JOIN Table2 t2 ON t1.key2 = t2.key2
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Table3 t3 ON t1.key3 = t3.key3;

Поскольку мы выполняем только ВНУТРЕННИЕ СОЕДИНЕНИЯ, у нас будут только записи, соответствующие комбинации трех таблиц. См. Вывод ниже.

ключ1 Имя ключ2 Город ключ3 Профессия
3 Эрик 1 Нью-Йорк 6 Актер
4 Джон 4 Париж 4 Юрист
6 Гарри 9 Мадрид 2 Хирург

Помимо операторов SELECT

Использование операторов JOIN не ограничивается операторами SELECT.В T-SQL вы также можете использовать объединения в операторах INSERT, DELETE и UPDATE. Но имейте в виду, что в большинстве СУБД, которые у нас есть в настоящее время, объединения не поддерживаются в операторах DELETE и UPDATE. Поэтому я бы посоветовал вам ограничить использование объединений только операторами SELECT и INSERT, даже в коде SQL Server. Это важно, если вы хотите, чтобы ваши скрипты легче переносились на разные платформы.

Заключение

Объединение данных из нескольких таблиц очень важно при работе с СУБД.Операторы JOIN предоставляют мощный, быстрый и гибкий способ извлечения необходимых данных.

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

SQL JOIN — Оператор JOIN (явное соединение, неявное соединение)

В предыдущих разделах этой главы было продемонстрировано использование оператора SELECT для запроса строк из одной таблицы базы данных.Если бы язык Transact-SQL поддерживал только такие простые операторы SELECT, присоединение двух или более таблиц для извлечения данных было бы невозможным. Следовательно, все данные базы данных должны храниться в одной таблице. Хотя хранение всех данных базы данных внутри одной таблицы возможно, у него есть один главный недостаток — хранимые данные сильно избыточны.

Transact-SQL предоставляет оператор соединения, который извлекает данные из более чем одной таблицы. Этот оператор, вероятно, является наиболее важным оператором для систем реляционных баз данных, поскольку он позволяет распределить данные по множеству таблиц и, таким образом, обеспечивает жизненно важное свойство систем баз данных — неизбыточность данных.

Примечание — Оператор UNION также присоединяет две или более таблиц. Однако оператор UNION всегда присоединяет два или более операторов SELECT, в то время как оператор соединения «объединяет» две или более таблиц, используя только один SELECT. Кроме того, оператор UNION присоединяет строки таблиц, в то время как, как вы увидите позже в этом разделе, оператор соединения «объединяет» столбцы таблиц.

Оператор соединения применяется к базовым таблицам и представлениям. В этой главе обсуждаются соединения между базовыми таблицами, а соединения, относящиеся к представлениям, будут обсуждаться в главе «Представления».

Есть несколько различных форм оператора соединения. В этом разделе обсуждаются следующие основные типы:

  • Естественное соединение
  • Декартово произведение (перекрестное соединение)
  • Внешнее соединение
  • Тета-соединение, самосоединение и полусоединение

Прежде чем объяснять различные формы соединения, в этом разделе описываются различные синтаксические формы оператора соединения.

Две формы синтаксиса для реализации объединений

Для объединения таблиц можно использовать две разные формы:

  • Явный синтаксис объединения (ANSI SQL: 1992 синтаксис объединения)
  • Неявный синтаксис объединения (синтаксис объединения в старом стиле)

Синтаксис соединения ANSI SQL: 1992 был введен в стандарт SQL92 и явно определяет операции соединения, то есть с использованием соответствующего имени для каждого типа операции соединения.Ключевые слова, относящиеся к явному определению соединения:

  • CROSS JOIN
  • [INNER] JOIN
  • LEFT [OUTER] JOIN
  • RIGHT [OUTER] JOIN
  • FULL [OUTER] JOIN

CROSS JOIN из двух таблиц. INNER JOIN определяет естественное соединение двух таблиц, в то время как LEFT OUTER JOIN и RIGHT OUTER JOIN характеризуют операции соединения с одинаковыми именами, соответственно. Наконец, FULL OUTER JOIN определяет объединение правого и левого внешних соединений.(Все эти различные операции соединения объясняются в следующих разделах.)

Синтаксис неявного соединения — это синтаксис «старого стиля», где каждая операция соединения определяется неявно через предложение WHERE с использованием так называемых столбцов соединения (см. второй оператор в примере 6.57).

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

присоединиться — Sumo Logic

  1. Последнее обновление
  2. Сохранить как PDF
  1. Синтаксис
  2. Правила
  3. Ограничения
  4. Примеры
    1. Выполнение запроса оператора соединения
    2. Производительность
    3. Использование соединения с оператором Diff
    4. Работа с полями после предложения ON
Оператор

объединяет записи двух или более потоков данных.Результаты принимаются на лету, что позволяет создавать таблицы в реальном времени. Значения, общие для каждой таблицы, затем доставляются в качестве результатов поиска.

Оператор соединения в Sumo Logic работает так же, как внутреннее соединение SQL.

Синтаксис

... | присоединиться к
(синтаксический анализ "начальный поток из *" AS a) как t1,
(синтаксический анализ "запуск поиска * из родительского потока *" AS b, c) как t2,
(синтаксический анализ "запуск сохранения * из родительского потока *" AS d , e) как t3
на t1.a = t2.c
и t1.a = t3.e

Временное окно может быть добавлено, чтобы ограничить, насколько далеко друг от друга во времени разрешено объединение записей, используя следующий синтаксис:

... | присоединиться к
(синтаксический анализ "начальный поток из *" AS a) как t1,
(синтаксический анализ "запуск поиска * из родительского потока *" AS b, c) как t2,
(синтаксический анализ "запуск сохранения * из родительского потока *" AS d , e) как t3
на t1.a = t2.c
и t1.a = t3.e
[временное окно 10 мин]

Для работы с полями в каждой таблице после предложения ON используйте следующий синтаксис:

... | присоединиться к
(проанализировать "начальный поток из *" AS a) AS t1,
(проанализировать "начальный поиск * из родительского потока *" AS b, c) AS t2
на t1.a = t2.c
| поля t1_a, t2_b

Правила

  • Для запроса необходимо создать две или более таблицы.
  • Выражение соединения не может указывать область действия, если она игнорируется. Он должен начинаться с выражения синтаксического анализа.
  • Данные должны присутствовать в диапазоне времени, выбранном вами для запроса.
  • Объединение
  • можно использовать на панелях инструментальной панели, но в запросе они должны быть включены после первой фразы group by .
  • Подзапросы поддерживаются и могут включать агрегатные операторы.

Ограничения

  • Всего существует ограничение в 50 000 вводимых сообщений, которое принудительно составляет 25 000 для каждой таблицы для операции соединения двух таблиц. Если вы превысите этот лимит, вы получите сообщение об ошибке.
  • Существует ограничение в 10 миллионов выводимых сообщений, поскольку возможно иметь больше выводимых сообщений, чем вводимых сообщений из двух таблиц, для которых вы можете выполнять соединение .Если вы превысите этот предел, вы получите следующее сообщение об ошибке: Количество выходных сообщений превышает 10 000 000. Уточните свой поиск или сократите временной диапазон, чтобы уменьшить количество выходных сообщений.
  • Разрешены только конъюнктивные условия (И). Использование условий НЕ или ИЛИ не поддерживается.
  • Оповещения
  • в реальном времени не поддерживают оператора соединения.
  • Оператор соединения использует скользящие окна для хранения кандидатов в соединения, чтобы предотвратить неограниченное использование памяти при соединении между двумя большими отношениями.Из-за этого результат соединения может быть неполным и непоследовательным от запуска к запуску.
  • Следующие условия в настоящее время не поддерживаются в предложении ON:
    t1.a = 3
    t1.a! = T2.c
    НЕ t1.a
    t1.a = t2.c ИЛИ t1.b = t2.d

Примеры

Выполнение запроса оператора соединения

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

стартовый поток из потока-2454
стартовый поток из потока-7343
запуск поиска search-733434 из родительского потока stream-2454
запуск поиска search-854343 из родительского потока stream-7343
стартовый поток из потока-6543
запуск поиска search-455563 из родительского потока stream-6543
запуск поиска search-32342 из родительского потока stream-7343 

Выполнение запроса типа:

* | присоединиться к
(проанализировать "начальный поток из *" AS a) AS T1,
(проанализировать "начальный поиск * из родительского потока *" AS b, c) AS T2
на T1.а = Т2.с

возвращает результаты, похожие на:

а б в
поток-2454 поиск-733434 поток-2454
поток-7343 поиск-854343 поток-7343
поток-7343 поиск-32342 поток-7343
поток-6543 поиск-854343 поток-6543
Производительность

Оператор соединения может занять значительное время обработки.Избирательность уменьшает количество сообщений журнала, которые необходимо учитывать. Чтобы повысить производительность оператора соединения, поместите операторы синтаксического анализа в начало выражения запроса, перенеся привязки поиска в начало области поиска, как в этом примере:

(«начало потока от» ИЛИ «начало поиска») | присоединиться к
(проанализировать "начальный поток из *" AS a) AS t1,
(проанализировать "начальный поиск * из родительского потока *" AS b, c) AS t2
на t1.a = t2.c ...

Использование соединения с оператором Diff

Допустим, наши журналы выглядят примерно так:

событие = сеанс входа = 12345 время = 20130512
событие = покупка сеанса = 12345 значение = 50
событие = сеанс входа = 23456 время = 20130513
событие = сеанс покупки = 12345 значение = 100
событие = сеанс покупки = 23456 значение = 120
событие = покупка сеанса = 23456 стоимость = 200
событие = сеанс покупки = 23456 значение = 20 

Выполнение запроса типа:

* | присоединиться к
(проанализировать "событие = сеанс входа в систему = * время = *" AS s1, время) как t1,
(проанализировать "событие = сессия покупки = * значение = *" AS s2, v2) как t2
на t1.s1 = t2.s2

Дает аналогичные результаты на:

s1 время с2 версия 2
12345 20130512 12345 50
12345 20130512 12345 100
23456 20130513 23456 120
23456 20130513 23456 200
23456 20130513 23456 20

Добавление оператора Diff, например:

* | присоединиться к
(проанализировать "событие = сеанс входа в систему = * время = *" AS s1, время) как t1,
(проанализировать "событие = сессия покупки = * значение = *" AS s2, v2) как t2
на t1.s1 = t2.s2
| разница t2_v2 по t2_s2

дает результаты, аналогичные:

s1 время с2 версия 2 _diff
12345 20170512 12345 50 null
12345 20170512 12345 100 50
23456 20170513 23456 120 null
23456 20170513 23456 200 80
23456 20170513 23456 20 -180

В другом примере с diff выполняется такой запрос, как:

_sourceCategory = [sourceCategoryName] | join
(синтаксический анализ "Попытка выполнить задачу *.delay: * ms. "как taskId, delay) как t1,
(синтаксический анализ" Завершенное выполнение задачи *. Продолжительность выполнения: * s "как taskId, duration) как t2
на t1.taskId = t2.taskId
| diff t1_delay as delay_diff
| поля t1_taskId, t1_delay, delay_diff, t2_duration

Выдает результаты на вкладке Aggregate , например:

Оперировать с полями после предложения ON

Предположим, у вас есть запрос на соединение, например:

* | присоединиться к
(проанализировать "начальный поток из *" AS a) AS t1,
(проанализировать "начальный поиск * из родительского потока *" AS b, c) AS t2
на t1.а = t2.c

После оператора Join, чтобы использовать поля T1.a и T2.b в последующих предложениях, вы должны вместо этого называть их T1_a и T1_b. Например, чтобы использовать оператор Fields для выделения значений T1.a и T2.b, используйте следующий запрос:

* | присоединиться к
(синтаксический анализ "начальный поток из *" AS a) AS t1,
(синтаксический анализ "начальный поиск * из родительского потока *" AS b, c) AS t2
на t1.a = t2.c
полей t1_a, t2_b

Использование оператора соединения

Оператор соединения объединяет кортежи из двух входных потоков. которые соответствуют условию теста (предикату) и необязательно находятся в пределах указанного расстояние в их полях заказа.Оператор соединения предполагает, что каждый входной поток правильно заказал. Он сравнивает упорядоченные кортежи из первого входного потока с упорядоченные кортежи из второго входного потока в пределах расстояния ограничение. Результаты объединений публикуются в одном выходном потоке.

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

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

Примечание

На некоторых вкладках оператора соединения, описанных в этом разделе, используются выражения которые относятся к полям во входных потоках. Когда важно указать, какие входной поток, в котором находится поле, вы можете уточнять имена полей, используя условные обозначения input1. название поля и вход2. название поля . input1. Префикс относится к данным, поступающим на «верхний» порт (# 1), в то время как input2. Префикс относится к данным, поступающим в «нижний» порт (№2). Примеры: input1.SKU и вход 2.СКУ .

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

Способ хранения: укажите настройки для этого оператора с помощью то же самое в куче и в параметры транзакционной памяти, описанные на вкладке «Аннотации» редактора EventFlow. По умолчанию установлено Наследовать от содержащего модуля.

Включить порт вывода ошибок: установите этот флажок, чтобы добавить Порт ошибки к этому компоненту. На холсте EventFlow порт ошибки отображается как красный выходной порт, всегда последний порт для компонента. См. Использование портов ошибок, чтобы узнать об ошибке. Порты.

Описание: при желании введите текст для краткого описания назначение и функции компонента.На холсте редактора EventFlow вы можете увидеть описание, нажав Ctrl , пока отображается всплывающая подсказка компонента.

Свойства: вкладка «Параметры объединения»

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

Присоединяйтесь к
  • Объединения на основе значений полезны, когда важно иметь StreamBase верните полный ответ, а не приблизительный. Присоединяясь к values ​​означает, что значения кортежей в одном входном потоке будут по сравнению со значениями кортежей в другом входном потоке.

  • Соединения на основе кортежей полезны, когда нет необходимости (или невозможно) иметь полный ответ (например, при обработке неопределенных или неограниченные потоки). Соединения на основе кортежей генерируют приблизительные решения для запросы соединения (как в операторах SQL). Кортежи в одном входном потоке будет сравниваться с кортежами в другом входном потоке, независимо от значение.

Предикат

Укажите выражение, которое будет оценивать как true или ложь . Когда истина, условие соединения выполнено, и оператор Join генерирует выходные данные, указанные в параметре Вкладка «Параметры вывода».

Примечание

Ваш выбор Присоединиться к опциям приведет к следующему вкладка в представлении «Свойства объединения» с названием «Параметры на основе значений» или «На основе кортежей». Настройки.

Свойства: вкладка «Настройки на основе значений»

Вкладка «Параметры на основе значений» доступна только в том случае, если на вкладке «Параметры объединения» выбрано «Объединение по значениям».Используйте эту вкладку, чтобы выбрать поля, которые вы хотите упорядочить, и диапазон значений, за пределами которого эти два поля не могут различаются:

  • В поле «Поля для заказа» укажите поля, в которых данные с каждого из двух входных портов будут упорядочены.

  • В поле «Диапазон объединения» укажите диапазон значений, которые могут содержать поля упорядочения:

    • Введите минимально допустимую разницу между значениями заказа. сравниваемых полей в двух потоках.

    • Введите максимально допустимую разницу.

    В обоих случаях разница выражается в виде значения относительно заказа. поле в первом потоке.

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

В следующем примере диапазон соединения составляет 120: значение в Time_C может быть на 60 меньше. чем значение в Time_R, и на 60 больше:

Примечание

Поскольку оператор соединения чувствителен к порядку, результаты непредсказуемы, если кортежи поступают не по порядку в отношении полей для заказа.

Свойства: вкладка «Настройки на основе кортежей»

Вкладка «Параметры на основе кортежей» доступна только в том случае, если на вкладке «Параметры объединения» выбрано «Объединение по кортежам». Когда вы присоединяетесь по кортежу, кортежи на одном входном порту сравниваются с кортежами на другом входном порту, независимо от того, ценности.

Примечание

Только соединения на основе кортежей имеют размеры окна, и эти окна увеличиваются на 1.

Размеры окон

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

Тайм-аут кортежа

Истекшее время в секундах, по истечении которого кортежи должны быть удалены из соединения. буфер.

Свойства: вкладка «Параметры вывода»

Вкладка Параметры вывода позволяет вам указать имена полей и выражения, которые это Оператор соединения должен отпустить. Освобождение происходит, когда предикат в настройке соединения tab оценивается как true .

Укажите поля вывода, используя один из двух вариантов вывода:

  • Выберите параметр всех полей ввода, чтобы автоматически передать все поля ввода в поток вывода.

    По умолчанию этот параметр добавляет префиксы к именам полей ввода, как показано на следующем экране:

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

  • Выберите явно указанные поля, чтобы указать поля вывода вручную.

    Если выбран явный параметр, выход Таблица полей изначально пуста. Добавьте строку для каждого поля вывода, которое вы хотите, указав имя выходного поля и его Выражение. В качестве альтернативы используйте Pass All кнопку, чтобы загрузить любое или все поля ввода в таблицу.

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

Свойства: вкладка параллелизма

Используйте вкладку Параллелизм, чтобы указать параллельные регионы для этого экземпляра этого компонент, или варианты множественности, или и то, и другое. Параметры вкладки «Параллелизм»: описано в Параллелизме Параметры и стили отправки описаны в разделе «Стили отправки».

Осторожность

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

Пример 1: Соединение на основе значений

В этом примере отслеживается выполнение установленного образца приложения Join. с StreamBase.Приложение объединяет сделки из двух каналов: ReutersIn и ComstockIn. Схема входного потока содержит следующие поля: символ акции, его цена и его временная метка.

Соединение происходит, когда кортеж на одном порту совпадает с кортежем на другом порту. Чтобы соответствовать, у двух кортежей должны быть одинаковые символы акций, а разница в цене должна быть больше или равно 1, используя следующее выражение

Symbol_R == Symbol_C && abs (PricePS_R - PricePS_C)> = 1.0
 

В дополнение к выражению ценовой разницы устанавливается ограничение расстояния так, чтобы совпадающие пары должны иметь временные метки в пределах 60 секунд друг от друга. Выходные кортежи содержат разницу в цене между согласованными кортежами, цена за акцию из каждый и значение времени из кортежа Reuters.

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

  1. Кортеж входит в оператор соединения на входном порту ReutersIn (здесь сокращено до R) и хранится в буфере:

  2. Кортеж поступает в порт ComstockIn (сокращенно до C).Ценность первого поле (B) совпадает с символом в первом кортеже, но разница в цене (в второе поле) меньше единицы. Следовательно, присоединение ничего не освобождает. оператор:

  3. Кортеж на порте R имеет совпадение на другом порте, и его разница в цене равно 1. Условия для операции соединения выполнены, и оператор соединения испускает выходной кортеж:

  4. В очередь ставится кортеж, содержащий новый символ акций (A).Потому что это не так соответствуют любым существующим кортежам, вывод не производится:

  5. Кортеж поступает в порт C, символ которого соответствует двум кортежам из другого порт. В обоих случаях разница в цене больше 1. В результате два выпускаются кортежи — по одному на каждое совпадение:

  6. Новый кортеж соответствует символу одного существующего кортежа на другом входном порту, с разницей в цене 2.Следовательно, оператор Join генерирует еще один кортеж:

  7. Последний кортеж, поставленный в очередь на входном порту C, соответствует двум кортежам на порту R. Однако новый кортеж приводит к тому, что диапазон значений времени в совпадающих кортежах чтобы превысить указанный диапазон соединения в 60 секунд. Новый кортеж продвигает верхний предел диапазона до 100, а другие совпадающие кортежи теперь ниже присоединиться к диапазону.В результате совпадающие кортежи на другом порту удаляются из буфер, и вывод не выводится:

Пример 2: Соединение на основе кортежей

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

Каждый шаг этого примера показывает, что новый кортеж ставится в очередь к оператору соединения.В содержимое двух окон отображается после поступления каждого кортежа и после любых кортежей выпущены. Можно сказать, что окна «перемещаются» по потоку данных: обратите внимание, что когда количество прибывающих кортежей превышает выделенный размер окна, самый старый кортежи удаляются из окна.

  1. Для начала в очередь ставятся четыре кортежа. Кортежи, входящие в первый входной порт, буферизуются в первом окне оператора соединения, а входящие во втором порт хранятся во втором окне.Пока ничего не выводится, потому что ни один из кортежи, входящие в первый порт, соответствуют любому из второго порта:

  2. Затем кортеж поступает во второй входной порт и соответствует кортежу в первом буфер. Поэтому происходит соединение, и оператор освобождает соответствующий кортеж.В кроме того, обратите внимание, что когда окно заполнено и поступает новый кортеж, window перемещается по потоку кортежей: самый старый кортеж в окне (B, 60), смывается из окна.

  3. Теперь шестой кортеж, входящий в первый входной порт, заполняет первый входной порт. окно. Обратите внимание, что вывода не происходит: вспомните, что соответствующий кортеж, который был сохраненный ранее в другом окне был удален на предыдущем шаге.

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

.
Оставить комментарий

Добавить комментарий

Ваш адрес email не будет опубликован.

© 2019 Штирлиц Сеть печатных салонов в Перми

Цифровая печать, цветное и черно-белое копирование документов, сканирование документов, ризография в Перми.