Оператор 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

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

PartCatPrice
Квартиры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
NULL4565,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
Книги
160
NULL
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 в примерах с описанием

Присоединение таблиц в запросах — это базовый инструмент в работе с базами данных. Давайте рассмотрим какие присоединения (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

Оператор join в Azure Data Explorer

  • Чтение занимает 8 мин

В этой статье

Узнайте, как объединить строки двух таблиц для формирования новой таблицы путем сопоставления значений указанных столбцов каждой таблицы.Merge the rows of two tables to form a new table by matching values of the specified columns from each table.

Table1 | join (Table2) on CommonColumn, $left.Col1 == $right.Col2

СинтаксисSyntax

LeftTable | join [JoinParameters] ( RightTable ) on AttributesLeftTable | join [JoinParameters] ( RightTable ) on Attributes

АргументыArguments

  • LeftTable — левое табличное выражение или таблица, иногда называемая внешней таблицей, строки которой нужно объединить.LeftTable: The left table or tabular expression, sometimes called outer table, whose rows are to be merged. Обозначается как $left.Denoted as $left.

  • RightTable — правое табличное выражение или таблица, иногда называемая внутренней таблицей, строки которой нужно объединить.RightTable: The right table or tabular expression, sometimes called inner table, whose rows are to be merged. Обозначается как $right.Denoted as $right.

  • Attributes — одно или несколько разделенных запятыми правил, которые описывают, как строки из LeftTable сопоставляются со строками RightTable.Attributes: One or more comma-separated rules that describe how rows from LeftTable are matched to rows from RightTable. Несколько правил оцениваются с помощью логического оператора and.Multiple rules are evaluated using the and logical operator.

    Правило может иметь один из следующих типов.A rule can be one of:

    Тип правилаRule kind СинтаксисSyntax PredicatePredicate
    Равенство по имениEquality by name ColumnNameColumnName where LeftTable.ColumnName == RightTable.ColumnNamewhere LeftTable.ColumnName == RightTable.ColumnName
    Равенство по значениюEquality by value $left.LeftColumn == $right.RightColumn$left.LeftColumn == $right.RightColumn where $left.LeftColumn == $right.RightColumnwhere $left.LeftColumn == $right.RightColumn

    Примечание

    Для равенства по значению имена столбцов должны быть обозначены нотациями $left и $right по применимой таблице.For 'equality by value', the column names must be qualified with the applicable owner table denoted by $left and $right notations.

  • JoinParameters — ноль или более разделенных пробелами параметров в виде имя = значение, управляющих поведением операции сопоставления строк и планом выполнения.JoinParameters: Zero or more space-separated parameters in the form of Name = Value that control the behavior of the row-match operation and execution plan. Поддерживаются следующие параметры:The following parameters are supported:

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

Если тип kind не указан, по умолчанию для оператора join выбирается вариант innerunique.If kind isn't specified, the default join flavor is innerunique. Такой подход отличается от подхода в некоторых других продуктах аналитики, которые используют inner в качестве варианта по умолчанию.This is different than some other analytics products that have inner as the default flavor. Чтобы понять различия и гарантировать получение нужных результатов, см. раздел Варианты оператора join.See join-flavors to understand the differences and make sure the query yields the intended results.

Возвращаемое значениеReturns

Схема вывода зависит от варианта оператора join:The output schema depends on the join flavor:

Вариант оператора joinJoin flavor Схема выводаOutput schema
kind=leftanti, kind=leftsemikind=leftanti, kind=leftsemi Итоговая таблица содержит только столбцы из левой части.The result table contains columns from the left side only.
kind=rightanti, kind=rightsemikind=rightanti, kind=rightsemi Итоговая таблица содержит только столбцы из правой части.The result table contains columns from the right side only.
kind=innerunique, kind=inner, kind=leftouter, kind=rightouter, kind=fullouterkind=innerunique, kind=inner, kind=leftouter, kind=rightouter, kind=fullouter Столбец для каждого столбца в каждой из двух таблиц, в том числе соответствующие ключи.A column for every column in each of the two tables, including the matching keys. В случае конфликта имен столбцы в правой части будут автоматически переименованы.The columns of the right side will be automatically renamed if there are name clashes.

Выходные записи зависят от варианта оператора join:Output records depend on the join flavor:

Примечание

Если есть несколько строк с одинаковыми значениями для этих полей, вы получите строки для всех комбинаций.If there are several rows with the same values for those fields, you'll get rows for all the combinations. Соответствие — это строки из обеих таблиц, у которых совпадают значения всех полей on .A match is a row selected from one table that has the same value for all the on fields as a row in the other table.

Вариант оператора joinJoin flavor Выходные записиOutput records
kind=leftanti, kind=leftantisemikind=leftanti, kind=leftantisemi Возвращает все записи из левой части, для которых нет соответствий в правой.Returns all the records from the left side that don't have matches from the right
kind=rightanti, kind=rightantisemikind=rightanti, kind=rightantisemi Возвращает все записи из правой части, для которых нет соответствий в левой.Returns all the records from the right side that don't have matches from the left.
kind не указан, kind=inneruniquekind unspecified, kind=innerunique Каждому значению ключа on соответствует только одна строка из левой части.Only one row from the left side is matched for each value of the on key. Выходные данные содержат по одной строке для каждого соответствия этой строки со строками из правой части.The output contains a row for each match of this row with rows from the right.
kind=leftsemi Возвращает все записи из левой части с соответствиями в правой.Returns all the records from the left side that have matches from the right.
kind=rightsemi Возвращает все записи из правой части с соответствиями в левой.Returns all the records from the right side that have matches from the left.
kind=inner Содержит строку в выходных данных для каждого сочетания соответствующих строк из левой и правой частей.Contains a row in the output for every combination of matching rows from left and right.
kind=leftouter (или kind=rightouter, или kind=fullouter)kind=leftouter (or kind=rightouter or kind=fullouter) Содержит по одной строке для каждой строки в левой и правой частях, даже если совпадения отсутствуют.Contains a row for every row on the left and right, even if it has no match. Выходные ячейки без сопоставлений имеют значения NULL.The unmatched output cells contain nulls.

Совет

Если одна таблица меньше другой, для оптимальной производительности используйте ее в качестве левой (перенаправленной) части оператора join.For best performance, if one table is always smaller than the other, use it as the left (piped) side of the join.

ПримерExample

Получите расширенные сведения о действиях из login, которым некоторые записи помечают время начала и конца действия.Get extended activities from a login that some entries mark as the start and end of an activity.

let Events = MyLogTable | where type=="Event" ;
Events
| where Name == "Start"
| project Name, City, ActivityId, StartTime=timestamp
| join (Events
    | where Name == "Stop"
        | project StopTime=timestamp, ActivityId)
    on ActivityId
| project City, ActivityId, StartTime, StopTime, Duration = StopTime - StartTime
let Events = MyLogTable | where type=="Event" ;
Events
| where Name == "Start"
| project Name, City, ActivityIdLeft = ActivityId, StartTime=timestamp
| join (Events
        | where Name == "Stop"
        | project StopTime=timestamp, ActivityIdRight = ActivityId)
    on $left.ActivityIdLeft == $right.ActivityIdRight
| project City, ActivityId, StartTime, StopTime, Duration = StopTime - StartTime

Разновидности оператора соединенияJoin flavors

Точный вариант оператора join указывается с помощью ключевого слова kind.The exact flavor of the join operator is specified with the kind keyword. Поддерживаются следующие варианты оператора join:The following flavors of the join operator are supported:

Тип и вариант оператора joinJoin kind/flavor ОписаниеDescription
innerunique (или пустое значение по умолчанию)innerunique (or empty as default) Внутреннее соединение с удалением дубликатов в левой частиInner join with left side deduplication
inner Стандартное внутреннее соединениеStandard inner join
leftouter левое внешнее соединение.Left outer join
rightouter Правое внешнее соединениеRight outer join
fullouter Полное внешнее соединениеFull outer join
leftanti, anti или leftantisemileftanti, anti, or leftantisemi Левое антисоединениеLeft anti join
rightanti или rightantisemirightanti or rightantisemi Правое антисоединениеRight anti join
leftsemi Левое полусоединениеLeft semi join
rightsemi Правое полусоединениеRight semi join

Вариант оператора join по умолчаниюDefault join flavor

Вариантом оператора join по умолчанию является внутреннее соединение с удалением дубликатов в левой части.The default join flavor is an inner join with left side deduplication. Реализация оператора join по умолчанию полезна в типичных сценариях анализа журналирования/трассировки, где необходимо коррелировать два события, каждое из которых соответствует определенному критерию фильтрации, с одним идентификатором корреляции.Default join implementation is useful in typical log/trace analysis scenarios where you want to correlate two events, each matching some filtering criterion, under the same correlation ID. При этом не обращайте внимания на видимые аспекты и игнорируйте появление записей трассировки.You want to get back all appearances of the phenomenon, and ignore multiple appearances of the contributing trace records.

X | join Y on Key
 
X | join kind=innerunique Y on Key

Чтобы объяснить операцию соединения с помощью оператора join, ниже приведены два примера таблиц.The following two sample tables are used to explain the operation of the join.

Таблица XTable X

КлючKey Значение1Value1
aa 11
bb 22
bb 33
сc 44

Таблица YTable Y

КлючKey Значение2Value2
bb 1010
сc 2020
сc 3030
dd 4040

Соединение по умолчанию выполняет внутреннее соединение после удаления дубликатов в левой части по ключу соединения (первая запись при дедупликации сохраняется).The default join does an inner join after deduplicating the left side on the join key (deduplication keeps the first record).

Рассмотрим эту инструкцию: X | join Y on Key.Given this statement: X | join Y on Key

Эффективная левая сторона оператора join (таблица X после удаления дубликатов) будет следующей:the effective left side of the join, table X after deduplication, would be:

КлючKey Значение1Value1
aa 11
bb 22
сc 44

а в результате объединения мы получим следующее:and the result of the join would be:

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join Y on Key
КлючKey Значение1Value1 Key1Key1 Значение2Value2
bb 22 bb 1010
сc 44 сc 2020
сc 44 сc 3030

Примечание

Ключи a и d не отображаются в выходных данных, поскольку соответствующие ключи отсутствуют и справа, и слева.The keys 'a' and 'd' don't appear in the output, since there were no matching keys on both left and right sides.

Вариант inner-joinInner-join flavor

Функция inner-join похожа на стандартную функцию inner-join в SQL.The inner-join function is like the standard inner-join from the SQL world. Выходная запись создается каждый раз, когда у записи с левой стороны есть тот же ключ для соединения, что и у записи с правой стороны.An output record is produced whenever a record on the left side has the same join key as the record on the right side.

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=inner Y on Key
КлючKey Значение1Value1 Key1Key1 Значение2Value2
bb 33 bb 1010
bb 22 bb 1010
сc 44 сc 2020
сc 44 сc 3030

Примечание

  • (b,10) с правой стороны соединено дважды: с (b,2) и с (b,3) слева.(b,10) from the right side, was joined twice: with both (b,2) and (b,3) on the left.
  • (c,4) с левой стороны соединено дважды: с (c,20) и с (c,30) справа.(c,4) on the left side, was joined twice: with both (c,20) and (c,30) on the right.

Вариант innerunique-joinInnerunique-join flavor

Используйте вариант innerunique-join для удаления дубликатов ключей с левой стороны.Use innerunique-join flavor to deduplicate keys from the left side. Результат будет представлять собой строку в выходных данных каждого сочетания ключей слева и справа с удаленными дубликатами.The result will be a row in the output from every combination of deduplicated left keys and right keys.

Примечание

innerunique может приводить к получению двух возможных вариантов выходных данных, каждый из которых будет правильным.innerunique flavor may yield two possible outputs and both are correct. В первых выходных данных оператор join случайным образом выбрал первый ключ, который отображается в t1 со значением val1.1, и сопоставил его с ключами t2.In the first output, the join operator randomly selected the first key that appears in t1, with the value "val1.1" and matched it with t2 keys. Во вторых выходных данных оператор join случайным образом выбрал второй ключ, который отображается в t1 со значением val1.2, и сопоставил его с ключами t2.In the second output, the join operator randomly selected the second key that appears in t1, with the value "val1.2" and matched it with t2 keys.

let t1 = datatable(key:long, value:string)  
[
1, "val1.1",  
1, "val1.2"  
];
let t2 = datatable(key:long, value:string)  
[  
1, "val1.3",
1, "val1.4"  
];
t1
| join kind = innerunique
    t2
on key
ключkey valuevalue key1key1 value1value1
11 val1.1val1.1 11 val1.3val1.3
11 val1.1val1.1 11 val1.4val1.4
let t1 = datatable(key:long, value:string)  
[
1, "val1.1",  
1, "val1.2"  
];
let t2 = datatable(key:long, value:string)  
[  
1, "val1.3", 
1, "val1.4"  
];
t1
| join kind = innerunique
    t2
on key
ключkey valuevalue key1key1 value1value1
11 val1.2val1.2 11 val1.3val1.3
11 val1.2val1.2 11 val1.4val1.4
  • Решение Kusto оптимизировано для смещения фильтров (при возможности), которые указаны после join, ближе к соответствующей стороне join (левой или правой).Kusto is optimized to push filters that come after the join, towards the appropriate join side, left or right, when possible.

  • В некоторых случаях используется вариант innerunique, а фильтр распространяется на левую сторону оператора join.Sometimes, the flavor used is innerunique and the filter is propagated to the left side of the join. Этот вариант будет распространяться автоматически, а ключи, применяемые к этому фильтру, всегда будут отображаться в выходных данных.The flavor will be automatically propagated and the keys that apply to that filter will always appear in the output.

  • Используйте приведенный выше пример и добавьте фильтр where value == "val1.2" .Use the example above and add a filter where value == "val1.2" . Он всегда будет давать второй результат и никогда не будет давать первый для наборов данных:It will always give the second result and will never give the first result for the datasets:

let t1 = datatable(key:long, value:string)  
[
1, "val1.1",  
1, "val1.2"  
];
let t2 = datatable(key:long, value:string)  
[  
1, "val1.3", 
1, "val1.4"  
];
t1
| join kind = innerunique
    t2
on key
| where value == "val1.2"
ключkey valuevalue key1key1 value1value1
11 val1.2val1.2 11 val1.3val1.3
11 val1.2val1.2 11 val1.4val1.4

Вариант left outer-joinLeft outer-join flavor

Результат левого внешнего соединения для таблиц X и Y всегда содержит все записи из левой таблицы (X), даже если условие соединения не соответствует ни одной из записей из правой таблицы (Y).The result of a left outer-join for tables X and Y always contains all records of the left table (X), even if the join condition doesn't find any matching record in the right table (Y).

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=leftouter Y on Key
КлючKey Значение1Value1 Key1Key1 Значение2Value2
bb 33 bb 1010
bb 22 bb 1010
сc 44 сc 2020
сc 44 сc 3030
aa 11

Вариант right outer-joinRight outer-join flavor

Вариант для правого внешнего соединения напоминает левое внешнее соединение, но обработка таблиц осуществляется в обратном порядке.The right outer-join flavor resembles the left outer-join, but the treatment of the tables is reversed.

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=rightouter Y on Key
КлючKey Значение1Value1 Key1Key1 Значение2Value2
bb 33 bb 1010
bb 22 bb 1010
сc 44 сc 2020
сc 44 сc 3030
dd 4040

Вариант full outer-joinFull outer-join flavor

Полное внешнее соединение сочетает в себе действие левого и правого внешних соединений.A full outer-join combines the effect of applying both left and right outer-joins. В тех случаях, когда записи в соединяемых таблицах не совпадают, результирующий набор будет иметь значения null для каждого столбца таблицы, в котором нет совпадающей строки.Whenever records in the joined tables don't match, the result set will have null values for every column of the table that lacks a matching row. Для тех записей, для которых соответствия нет, в результирующий набор будет добавлена одна строка (содержащая поля, заполненные из обеих таблиц).For those records that do match, a single row will be produced in the result set, containing fields populated from both tables.

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=fullouter Y on Key
КлючKey Значение1Value1 Key1Key1 Значение2Value2
bb 33 bb 1010
bb 22 bb 1010
сc 44 сc 2020
сc 44 сc 3030
dd 4040
aa 11

Вариант left anti-joinLeft anti-join flavor

Левое антисоединение возвращает все записи с левой стороны, которые не соответствуют ни одной записи с правой стороны.Left anti-join returns all records from the left side that don't match any record from the right side.

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=leftanti Y on Key
КлючKey Значение1Value1
aa 11

Примечание

Антисоединение моделирует запрос NOT IN.Anti-join models the "NOT IN" query.

Вариант right anti-joinRight anti-join flavor

Правое антисоединение возвращает все записи с правой стороны, которые не соответствуют ни одной записи с левой стороны.Right anti-join returns all records from the right side that don't match any record from the left side.

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=rightanti Y on Key
КлючKey Значение2Value2
dd 4040

Примечание

Антисоединение моделирует запрос NOT IN.Anti-join models the "NOT IN" query.

Вариант left semi-joinLeft semi-join flavor

Левое полусоединение возвращает все записи с левой стороны, которые соответствуют записи с правой стороны.Left semi-join returns all records from the left side that match a record from the right side. Возвращаются только столбцы из левой части.Only columns from the left side are returned.

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=leftsemi Y on Key
КлючKey Значение1Value1
bb 33
bb 22
сc 44

Вариант right semi-joinRight semi-join flavor

Правое полусоединение возвращает все записи с правой стороны, которые соответствуют записи с левой стороны.Right semi-join returns all records from the right side that match a record from the left side. Возвращаются только столбцы из правой части.Only columns from the right side are returned.

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=rightsemi Y on Key
КлючKey Значение2Value2
bb 1010
сc 2020
сc 3030

Перекрестное соединениеCross-join

Kusto изначально не предоставляет вариант cross-join.Kusto doesn't natively provide a cross-join flavor. Вы не можете отметить оператор как kind=cross.You can't mark the operator with the kind=cross. Для имитации используйте фиктивный ключ.To simulate, use a dummy key.

X | extend dummy=1 | join kind=inner (Y | extend dummy=1) on dummy

Указания для оператора joinJoin hints

Оператор join поддерживает несколько указаний, управляющих способом выполнения запроса.The join operator supports a number of hints that control the way a query runs. Такие указания не изменяют семантику оператора join, но могут повлиять на его производительность.These hints don't change the semantic of join, but may affect its performance.

Указания для оператора join описаны в следующих статьях:Join hints are explained in the following articles:

Уроки PHP - урок 3.6 - Работа с БД MySQL. Виды оператора JOIN.

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

  1. INNER JOIN
  2. LEFT JOIN
  3. LEFT JOIN без пересечений с правой таблицей
  4. RIGHT JOIN
  5. RIGHT JOIN без пересечений с левой таблицей
  6. FULL OUTER
  7. FULL OUTER где левая или правая таблица пустая

 

А вот иллюстрация к этим видам JOIN:

Я прикреплю к статье файлы нашего сайта, среди которых будет join.php в которых я буду выводить все записи с помощью разных операторов JOIN.

INNER JOIN

Начнем мы с этого оператора INNER JOIN, потому что этот оператор срабатывает по умолчанию, если вы пишите в запросе просто JOIN. Этот оператор выбирает все записи из двух таблиц, где выполняется условие идущее после оператора ON. У нас есть две таблицы Files и Messages:

Таблица Messages:

mid bodytext fid
1 Test NULL
2 Hi 2
3 Hello NULL

 Таблица Files:

fid path
1 /files/1.png
2 /files/2.png
3 /files/3.png

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

SELECT * FROM Messages INNER JOIN Files ON Messages.fid=Files.fid

В результате будут выведены такие записи

mid bodytext fid path
2 Hi 2 /files/2.png

То есть там где fid совпадает, то mysql выведит эти строки.

LEFT JOIN

При LEFT JOIN мы выводим все записи в которых в таблице слева (у нас это Messages),  в том числе и те записи в которых эти значения fid есть в таблице Files.

Таблица Messages:

mid bodytext fid
1 Test 2
2 Hi NULL
3 Hello 3

 Таблица Files:

fid path
1 /files/1.png
2 /files/2.png
3 /files/3.png

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

SELECT * FROM Messages LEFT JOIN Files ON Messages.fid=Files.fid

В результате будут выведены такие записи

mid bodytext fid path
1 Test 2 /files/2.png
2 Hi NULL NULL
3 Hello 3 /files/3.png

LEFT JOIN будет нужен когда выводим все записи сообщений, а есть или нет прикрепленный файл, мы проверим уже через PHP.

LEFT JOIN без пересечений с правой таблицей

LEFT JOIN выводит все записи из левой таблицы, кроме тех в которых fid совпадают в правой таблице.

Таблица Messages:

 

mid bodytext fid
1 Test 2
2 Hi NULL
3 Hello 3

 Таблица Files:

fid path
1 /files/1.png
2 /files/2.png
3 /files/3.png

Запрос с LEFT JOIN без пересечений будет следующий:

SELECT * FROM Messages LEFT JOIN Files ON Messages.fid=Files.fid WHERE Files.fid IS NULL

В результате мы получим вот такую вот выборку:

mid bodytext fid path
2 Hi NULL NULL

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

RIGHT JOIN

RIGHT JOIN выводит все записи из правой таблицы, если есть пересечения, то выводится данные из левой таблицы.

Таблица Messages:

mid bodytext fid
1 Test 2
2 Hi NULL
3 Hello 3

 Таблица Files:

fid path
1 /files/1.png
2 /files/2.png
3 /files/3.png

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

SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid=Files.fid

В результате мы получим вот такую вот выборку:

 

mid bodytext fid path
NULL NULL 1 /files/1.png
1 Test 2 /files/2.png
3 Hello 3 /files/3.png

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

RIGHT JOIN без пересечений

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

Таблица Messages:

mid bodytext fid
1 Test 2
2 Hi NULL
3 Hello 3

Таблица Files:

fid path
1 /files/1.png
2 /files/2.png
3 /files/3.png

Запрос с RIGHT JOIN без пересечений будет следующий:

SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid=Files.fid WHERE Messages.fid IS NULL

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

mid bodytext fid path
NULL NULL 1 /files/1.png

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

FULL OUTER JOIN

Несмотря на то что в языке SQL есть FULL OUTER JOIN, в MySQL этого оператора нет. Дело в том что подобный оператор это огромная нагрузка на сервер. Сейчас у нас 3 файла и 3 сообщения, при этом образуется 4 строк в результате выполнения запроса. Я не уверен, что это хорошая идея писать запрос, который дает в совокупности два запроса LEFT JOIN и RIGHT JOIN. Но все же есть возможность эмулировать запрос FULL OUTER JOIN.

Таблица Messages:

mid bodytext fid
1 Test 2
2 Hi NULL
3 Hello 3

Таблица Files:

fid path
1 /files/1.png
2 /files/2.png
3 /files/3.png

Эмуляция запроса с FULL OUTER JOIN будет следующей:

SELECT * FROM Messages LEFT JOIN Files ON Messages.fid = Files.fid UNION SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid = Files.fid

В этом запросе мы используем оператор UNION, чтобы объединить два запроса LEFT JOIN и RIGHT JOIN.

В результате мы получим следующие записи:

 

mid bodytext fid path
1 Test 2 /files/2.png
2 Hi NULL NULL
3 Hello 3 /files/3.png
NULL NULL 1 /files/1.png

И здесь я уже затрудняюсь сказать зачем потребуется FULL OUTER JOIN. Но раз это есть в SQL, то видимо потребуется потом.

FULL OUTER JOIN без пересечений

Еще один вид JOIN еще более безумный, чем просто FULL OUTER JOIN, а именно FULL OUTER JOIN без пересечений. Я даже не могу предложить где можно использовать этот вид JOIN. Потому что в результате мы получаем файлы которые не используются и сообщения без файлов. И как вы наверно уже догадались этого оператора тоже нет в MySQL. Остается его только эмулировать с помощью двух операторов LEFT JOIN без перечений и RIGHT JOIN без пересечений.

Эмуляция запроса FULL OUTER JOIN без пересечений:

$sql = 'SELECT * FROM Messages LEFT JOIN Files ON Messages.fid = Files.fid WHERE Files.fid IS NULL 
UNION 
SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid = Files.fid WHERE Messages.fid IS NULL';

В результате (исходные таблицы те же что и в примере с FULL OUTER JOIN) мы получим:

mid bodytext fid path
2 Hi NULL NULL
NULL NULL 1 /files/1.png

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

Оператор присоединения

- 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
  

Во многих случаях мы можем захотеть выполнить INNER JOIN из книг, и языков, таблиц, поэтому вместо того, чтобы просматривать бессмысленное значение 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 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] | присоединиться к
(синтаксический анализ "Попытка выполнить задачу *.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

SQL | Объединение (внутреннее, левое, правое и полное объединение)

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

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN

Рассмотрим две таблицы ниже:

Student

StudentCourse

Самым простым соединением является ВНУТРЕННЕЕ СОЕДИНЕНИЕ.

  1. INNER JOIN: Ключевое слово INNER JOIN выбирает все строки из обеих таблиц, пока выполняется условие. Это ключевое слово создаст набор результатов, объединив все строки из обеих таблиц, где условие удовлетворяет i.Значение общего поля будет таким же.
    Синтаксис :
     ВЫБРАТЬ table1.column1, table1.column2, table2.column1, ....
    ИЗ table1
    INNER JOIN table2
    ON table1.matching_column = table2.matching_column;
    
    
      table1 : Первая таблица.
      table2 : Второй стол
      Match_column : Столбец, общий для обеих таблиц.
     

    Примечание : мы также можем написать JOIN вместо INNER JOIN. JOIN - это то же самое, что и INNER JOIN.

    Примеры запросов (INNER JOIN)

    • Этот запрос покажет имена и возраст студентов, обучающихся на разных курсах.
       ВЫБЕРИТЕ StudentCourse.COURSE_ID, Student.NAME, Student.AGE ОТ студента
      INNER JOIN StudentCourse
      ВКЛ Student.ROLL_NO = StudentCourse.ROLL_NO;
       

      Выходные данные :

  2. LEFT JOIN : это соединение возвращает все строки таблицы с левой стороны соединения и соответствующие строки для таблицы с правой стороны соединения.Строки, для которых нет соответствующей строки с правой стороны, набор результатов будет содержать null . LEFT JOIN также известен как LEFT OUTER JOIN. Синтаксис:
     ВЫБРАТЬ table1.column1, table1.column2, table2.column1, ....
    ИЗ table1
    LEFT JOIN table2
    ON table1.matching_column = table2.matching_column;
    
    
    table1: Первая таблица.
    table2: Вторая таблица
    Match_column: столбец, общий для обеих таблиц.
     

    Примечание : мы также можем использовать LEFT OUTER JOIN вместо LEFT JOIN, оба они одинаковы.


    Примеры запросов (LEFT JOIN) :

     SELECT Student.NAME, StudentCourse.COURSE_ID
    ОТ Студента
    ВЛЕВО ПРИСОЕДИНЯТЬСЯ к курсу
    НА StudentCourse.ROLL_NO = Student.ROLL_NO;
     

    Выход :

  3. RIGHT JOIN : RIGHT JOIN аналогичен LEFT JOIN. Это соединение возвращает все строки таблицы с правой стороны соединения и соответствующие строки для таблицы с левой стороны соединения. Строки, для которых нет соответствующей строки с левой стороны, набор результатов будет содержать null .RIGHT JOIN также известен как RIGHT OUTER JOIN. Синтаксис:
     ВЫБРАТЬ table1.column1, table1.column2, table2.column1, ....
    ИЗ table1
    RIGHT JOIN table2
    ON table1.matching_column = table2.matching_column;
    
    
    table1: Первая таблица.
    table2: Вторая таблица
    Match_column: столбец, общий для обеих таблиц.
     

    Примечание : мы также можем использовать RIGHT OUTER JOIN вместо RIGHT JOIN, оба они одинаковы.

    Примеры запросов (RIGHT JOIN) :

     SELECT Student.ИМЯ, StudentCourse.COURSE_ID
    ОТ Студента
    ПРАВО ПРИСОЕДИНЯЙТЕСЬ к курсу
    НА StudentCourse.ROLL_NO = Student.ROLL_NO;
     

    Вывод:

  4. FULL JOIN: FULL JOIN создает набор результатов путем объединения результатов LEFT JOIN и RIGHT JOIN. Набор результатов будет содержать все строки из обеих таблиц. Строки, для которых нет соответствия, набор результатов будет содержать NULL значений. Синтаксис:
     SELECT table1.column1, table1.column2, table2.column1, ....
    ИЗ table1
    FULL JOIN table2
    ON table1.matching_column = table2.matching_column;
    
    
    table1: Первая таблица.
    table2: Вторая таблица
    Match_column: столбец, общий для обеих таблиц.
     

    Примеры запросов (FULL JOIN) :

     SELECT Student.NAME, StudentCourse.COURSE_ID
    ОТ Студента
    FULL JOIN StudentCourse
    НА StudentCourse.ROLL_NO = Student.ROLL_NO;
     

    Выход:


Левое соединение (видео)
Правое соединение (видео)
Полное соединение (видео)
SQL | JOIN (декартово соединение, самосоединение)

Эта статья предоставлена ​​ Harsh Agarwal .Если вам нравится GeeksforGeeks, и вы хотели бы внести свой вклад, вы также можете написать статью с помощью provide.geeksforgeeks.org или отправить ее по электронной почте на [email protected] Посмотрите, как ваша статья появляется на главной странице GeeksforGeeks, и помогите другим гикам.

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

Вниманию читателя! Не прекращайте учиться сейчас. Получите все важные концепции теории CS для собеседований SDE с помощью курса CS Theory Course по приемлемой для студентов цене и станьте готовым к работе в отрасли.

Внутренний, левый внешний, THETA Типы операций объединения

Что такое объединение в СУБД?

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

В этом руководстве по СУБД вы узнаете:

Типы объединений

В СУБД в основном используются два типа объединений:

  1. Внутренние объединения: Theta, Natural, EQUI
  2. Внешнее объединение: левое, правое, полное

Рассмотрим их подробнее:

Inner Join

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

Внутреннее соединение или эквисоединение - это соединение на основе компаратора, которое использует сравнения на равенство в предикате соединения. Однако, если вы используете другие операторы сравнения, такие как ">", это нельзя назвать эквисоединением.

Внутреннее соединение, далее разделенное на три подтипа:

  • Theta join
  • Natural join
  • EQUI join

Theta Join

THETA JOIN позволяет объединить две таблицы на основе условия, представленного тета.Тета-соединения работают для всех операторов сравнения. Обозначается символом θ . Общий случай операции JOIN называется тета-соединением.

Синтаксис:

 A ⋈  θ  B 

Тета-соединение может использовать любые условия в критериях выбора.

Рассмотрим следующие таблицы.

Таблица A Таблица B
столбец 1 столбец 2 столбец 1 столбец 2
1 1 1 1
1 2 1 3

Например:

 A ⋈  A.столбец 2> B. столбец 2  (B) 
A ⋈ A. столбец 2> B. столбец 2 (B)
столбец 1 столбец 2
1 2

EQUI Join

EQUI JOIN выполняется, когда тета-соединение использует только условие эквивалентности. Соединение EQUI - это наиболее сложная операция для эффективной реализации в РСУБД и одна из причин, по которым у РСУБД есть существенные проблемы с производительностью.

Например:

 A ⋈  A. столбец 2 = столбец B. 2  (B) 
A ⋈ столбец A. 2 = столбец B 2 (B)
столбец 1 столбец 2
1 1

Естественное соединение (⋈)

NATURAL JOIN не использует никаких операторов сравнения. В этом типе соединения атрибуты должны иметь одно и то же имя и домен.В естественном соединении между двумя отношениями должен быть хотя бы один общий атрибут.

Выполняет выбор, формирующий равенство тех атрибутов, которые появляются в обоих отношениях, и устраняет повторяющиеся атрибуты.

Пример:

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

 C ⋈ D 
C ⋈ D
Число Квадрат Куб
2 4 8
3 9 18

Внешнее соединение

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

Три типа внешних соединений:

  • Левое внешнее соединение
  • Правое внешнее соединение
  • Полное внешнее соединение

Левое внешнее соединение (AB)

ЛЕВОЕ СОЕДИНЕНИЕ возвращает все строки из таблицы слева даже если в таблице справа не найдено ни одной подходящей строки. Если в таблице справа не найдено соответствующей записи, возвращается NULL.

Рассмотрим следующие 2 таблицы

A
Число Квадрат
2 4
3 9
4 16
 AB 
A ⋈ B
Число Квадрат Куб
2 4 8
3 9 18
4 16 -

Правое внешнее соединение (AB)

RIGHT JOIN возвращает все столбцы из таблицы справа, даже если совпадающие строки не были найдены в таблица слева.Если в таблице слева совпадений не найдено, возвращается NULL. RIGHT external JOIN противоположен LEFT JOIN

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

 AB 
A ⋈ B
Число Куб Квадрат
2 8 4
3 18 9
5 75 -

Полное внешнее соединение (AB)

В случае FULL OUTER JOIN все кортежи из обоих отношений включаются в результат независимо от условия сопоставления.

Пример:

 AB 
A ⋈ B
Число Квадрат Куб
2 4 8
3 9 18
4 16 -
5 - 75

Резюме:

  • В СУБД в основном два типа объединений 1) Внутреннее объединение 2 ) Внешнее соединение
  • Внутреннее соединение - это широко используемая операция соединения, которую можно рассматривать как тип соединения по умолчанию.
  • Inner Join делится на три подтипа: 1) Theta join 2) Natural join 3) EQUI join
  • Theta Join позволяет объединить две таблицы на основе условия, представленного тета
  • Когда тета-соединение использует только условие эквивалентности , он становится равным соединением.
  • Естественное соединение не использует никаких операторов сравнения.
  • Внешнее соединение не требует, чтобы каждая запись в двух таблицах соединения имела соответствующую запись.
  • Внешнее объединение далее делится на три подтипа: 1) Левое внешнее объединение 2) Правое внешнее объединение 3) Полное внешнее объединение
  • ЛЕВОЕ внешнее объединение возвращает все строки из таблицы слева, даже если совпадающие строки не имеют был найден в таблице справа.
  • ПРАВИЛЬНОЕ внешнее объединение возвращает все столбцы из таблицы справа, даже если в таблице слева не найдено совпадающих строк.
  • При полном внешнем соединении все кортежи из обоих отношений включаются в результат независимо от условия сопоставления.

Соединение SQL Equi - w3resource

Что такое Equi Join в SQL?

SQL EQUI JOIN выполняет СОЕДИНЕНИЕ с равенством или сопоставлением значений столбцов связанных таблиц.Знак равенства (=) используется в качестве оператора сравнения в предложении where для ссылки на равенство.

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

Синтаксис:

 ВЫБРАТЬ список_столбцов
ИЗ table1, table2 ....
ГДЕ table1.column_name =
table2.column_name;
 

или

 ВЫБРАТЬ *
ИЗ table1
ПРИСОЕДИНЯЙТЕСЬ к таблице2
[ВКЛ (условие_соединения)]
 

Изображение:

Пример:

Вот пример Equi Join в SQL.

Примерная таблица: агенты

Образец таблицы: заказчик

Чтобы получить столбец имени агента из таблицы агентов и столбцы имени клиента и города клиента из таблицы клиентов после объединения указанных двух таблиц со следующим условием -

1. рабочая зона агентов и город клиента в таблице клиентов должны совпадать,

можно использовать следующий оператор SQL:

Код SQL:

  ВЫБЕРИТЕ агентов.имя_агента, customer.cust_name,
customer.cust_city
ОТ агентов, заказчика
ГДЕ agent.working_area = customer.cust_city;
  

Выход:

 AGENT_NAME CUST_NAME CUST_CITY
---------------------------------------- ---------- ------------------------------ ------------
Рави Кумар Равиндран Бангалор
Рамасундар Равиндран Бангалор
Суббарао Равиндран Бангалор
Рави Кумар Шринивас Бангалор
Рамасундар Шринивас Бангалор
Суббарао Шринивас Бангалор
Рави Кумар Рангараппа Бангалор
Рамасундар Рангараппа Бангалор
Subbarao Rangarappa Bangalore
Рави Кумар Венкатпати Бангалор
Рамасундар Венкатпати Бангалор
Суббарао Венкатпати Бангалор
Андерсон Флеминг Брисбан
Андерсон Джекс Брисбан
Андерсон Уинстон Брисбан
Сантакумар Еараннаиду Ченнаи
...........
...........
 

В чем разница между Equi Join и Inner Join в SQL?

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

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

Изображение: EQUI JOIN Vs.ВНУТРЕННЕЕ СОЕДИНЕНИЕ

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

Нажмите на следующую ссылку, чтобы просмотреть слайды -

Практика выполнения упражнений SQL

Хотите улучшить статью выше? Публикуйте свои заметки / комментарии / примеры через Disqus.

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

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

Ваш адрес email не будет опубликован. Обязательные поля помечены *

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

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