Оператор join: SQL и оператор JOIN

Оператор соединения 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 и фамилии продавца. Вывод запроса аналогичен предыдущей статье:

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

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

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

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

Стоит отметить, что при группировке в 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

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

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

Оператор LEFT OUTER JOIN

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

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

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

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

Вывод:

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

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

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

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

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, INNER JOIN, LEFT JOIN, RIGHT JOIN — связывание таблиц

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

Синтаксис

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

Примеры

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

Таблица countries:

Таблица cities:

Пример . LEFT JOIN

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

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

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

Пример . RIGHT JOIN

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

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

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

Пример . INNER JOIN

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

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

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

оператор Join — Azure обозреватель данных

• 03/30/2020
• Чтение занимает 8 мин

В этой статье

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

СинтаксисSyntax

Лефттабле | join[Жоинпараметерс] ( RightTable ) on атрибуты ригхттаблеLeftTable | join [JoinParameters] ( RightTable ) on Attributes

АргументыArguments

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

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

• Attributes: одно или несколько правил с разделителями-запятыми, описывающих, как строки из лефттабле сопоставляются со строками из ригхттабле.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Лефттабле. ColumnName == Ригхттабле. ColumnNamewhere LeftTable.ColumnName == RightTable.ColumnName
  Равенство по значениюEquality by value	$left.Лефтколумн == $right. Ригхтколумн$left.LeftColumn == $right.RightColumn	where``$left. LeftColumn == Лефтколумн $right. Ригхтколумнwhere $left.LeftColumn == $right.RightColumn

  Примечание

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

• Жоинпараметерс: ноль или больше параметров, разделенных пробелами, в Name виде = значения имени, которое управляет поведением операции сопоставления строк и плана выполнения.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 параметр не указан, используется флаг соединений по умолчанию 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

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

Выходные записи зависят от флага объединения: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.

Совет

Для лучшей производительности, если одна таблица всегда меньше другой, используйте ее в качестве левой (перенаправленной) стороны объединения.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:

Разновидность соединений по умолчаниюDefault join flavor

Флаг соединений по умолчанию — это внутреннее соединение с дедупликацией левой стороны.The default join flavor is an inner join with left side deduplication. Реализация соединений по умолчанию полезна в типичных сценариях анализа журнала/трассировки, где необходимо сопоставить два события, каждое из которых соответствует определенному критерию фильтрации, под одним и тем же ИДЕНТИФИКАТОРом корреляции.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

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

Таблица XTable X

Таблица YTable Y

Соединение по умолчанию выполняет внутреннее соединение после дедупликации левой части ключа объединения (в результате дедупликации сохраняется первая запись).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 KeyGiven this statement: X | join Y on Key

эффективная левая сторона объединения, таблица X после дедупликации, будет выглядеть следующим образом:the effective left side of the join, table X after deduplication, would be:

а в результате объединения мы получим следующее: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

Примечание

Ключи «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-join flavor

Функция внутреннего объединения похожа на стандартное внутреннее соединение из мира 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

Примечание

• (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-join flavor

Используйте флаг иннеруникуе-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 flavor may yield two possible outputs and both are correct. В первом выводе оператор Join случайным образом выбрал первый ключ, который отображается в T1, со значением val 1.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, со значением «Val 1.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

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

• Kusto оптимизирована для push-фильтров, которые поступают после join , в сторону соответствующей стороны подключения, по возможности влево или вправо.Kusto is optimized to push filters that come after the join, towards the appropriate join side, left or right, when possible.

• В некоторых случаях используется разновидность иннеруникуе , и фильтр распространяется на левую сторону объединения.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"

Разновидность левого внешнего объединенияLeft 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

Конфигурация правого внешнего объединенияRight 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

Полное внешнее соединение, разновидностьFull 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

Разновидность защиты от левого объединенияLeft 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

Примечание

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

Вариант защиты от несоединенийRight 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

Примечание

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

Флаг левой точки подключенияLeft 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

Правая половина объединенияRight 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

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

Kusto изначально не предоставляет версию для перекрестного объединения.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

Указания по соединениюJoin 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 hints are explained in the following articles:

JOINS — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном материале вы узнаете, как использовать JOINS (INNER и OUTER) в Oracle с синтаксисом и примерами.

Описание

Oracle JOINS используются для извлечения данных из нескольких таблиц. JOIN выполняется всякий раз, когда две или более таблиц объединяются в SQL предложении.

Есть 4 различных типа присоединения Oracle:

Рассмотрим синтаксис Oracle JOIN, а также изучим примеры Oracle JOIN.

INNER JOIN (простое соединение)

Скорее всего, вы уже писали запросы в которых используются Oracle INNER JOIN. Это наиболее распространенный тип соединения. Oracle INNER JOINS возвращает все строки из нескольких таблиц, где выполняется условия соединения.

Синтаксис

Синтаксис INNER JOIN в Oracle/PLSQL:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

В этом рисунке, Oracle INNER JOIN возвращает затененную область:

Oracle INNER JOIN будет возвращать записи, где table1 и table2 будут пересекаться.

Пример

Ниже приведен пример Oracle INNER JOIN:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

Этот пример Oracle INNER JOIN возвращает все строки из таблиц suppliers и orders, где имеются соответствующие значение поля supplier_id в обоих таблицах.

Рассмотрим некоторые данные, чтобы понять, как работает INNER JOIN:

У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name) которая содержит следующие данные:

У нас есть еще одна таблица orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:

Если мы выполним Oracle оператор SELECT (который содержит INNER JOIN) ниже:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

Наш результирующий набор будет выглядеть следующим образом:

Строки для Microsoft и NVIDIA из таблицы suppliers будут опущены, так как значения supplier_id 10002 и 10003 не существует в обеих таблицах. Строка order_id 500127 из таблицы orders будет опущена, так как supplier_id 10004 не существует в таблице suppliers.

Старый Синтаксис

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

SELECT suppliers.supplier_id,
suppliers.supplier_name,
orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;

LEFT OUTER JOIN

Другой тип соединения называется Oracle LEFT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с левосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны.

Синтаксис

Синтаксис для Oracle LEFT OUTER JOIN:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

В некоторых базах данных LEFT OUTER JOIN заменяется на LEFT JOIN.

На этом рисунке, Oracle LEFT OUTER JOIN возвращает затененную область:

Oracle LEFT OUTER JOIN возвратит все записи из table1 и только те записи из table2, которые пересекаются с table1.

Пример

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

Этот пример LEFT OUTER JOIN возвратит все строки из таблицы suppliers, и только те строки из таблицы orders, где объединяемые поля равны.

Если значение supplier_id в таблице suppliers не существует в таблице orders, все поля таблицы orders будут отображаться в результирующем наборе как NULL.

Рассмотрим некоторые данные, чтобы понять, как работает LEFT OUTER JOIN:

У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name) которая содержит следующие данные:

У нас есть еще одна таблица orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:

Если мы выполним Oracle оператор SELECT (который содержит LEFT OUTER JOIN) ниже:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

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

Строки для Microsoft и NVIDIA будут включены, так как был использован LEFT OUTER JOIN. Тем не менее, вы заметите, что поле order_date для этих записей содержит значение NULL.

RIGHT OUTER JOIN

Другой тип соединения называется Oracle RIGHT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с правосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны.

Синтаксис

Синтаксис Oracle RIGHT OUTER JOIN:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

В некоторых базах данных, RIGHT OUTER JOIN заменяется на RIGHT JOIN.

На этом рисунке, Oracle RIGHT OUTER JOIN возвращает затененную область:

Oracle RIGHT OUTER JOIN возвратит все записи из table2 и только те записи из table1, которые пересекаются с table2.

Пример

Ниже приведен пример Oracle RIGHT OUTER JOIN:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers RIGHT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

Этот пример RIGHT OUTER JOIN возвращает все строки из таблицы orders и только те строки из таблицы suppliers, где объединяемые поля равны.

Если значение supplier_id в таблице orders не существует в таблице suppliers, все поля в таблице suppliers будут отображаться в результирующем наборе как NULL.

Рассмотрим некоторые данные, чтобы понять, как работает RIGHT OUTER JOIN:

У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name) которая содержит следующие данные:

У нас есть вторая таблица orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:

Если мы выполним Oracle оператор SELECT (который содержит RIGHT OUTER JOIN) ниже:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers RIGHT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

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

Строка для order_id 500127 будет включена, так как был использован RIGHT OUTER JOINS. Тем не менее, вы заметите, что поле supplier_name для этой записи содержит значение NULL.

FULL OUTER JOIN

Другой тип соединения называется Oracle FULL OUTER JOIN. Этот тип соединения возвращает все строки из левой таблицы и правой таблицы с NULL — значениями в месте, где условие объединения не выполняется.

Синтаксис

Синтаксис для Oracle FULL OUTER JOIN:

SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;

В некоторых базах данных, FULL OUTER JOIN заменяются FULL JOIN.

На этом рисунке, FULL OUTER JOIN возвращает затененную область:

Oracle FULL OUTER JOIN будет возвращать все записи из обеих таблиц table1 и table2.

Пример

Ниже приведен пример Oracle FULL OUTER JOIN:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers FULL OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

Этот пример FULL OUTER JOIN возвратит все строки из таблицы suppliers и все строки из таблицы orders и всякий раз, когда условие соединения не выполняется, то поля в результирующем наборе будут принимать значения NULL.

Если значение поля supplier_id в таблице suppliers не существует в таблице orders, то все поля в таблице orders будут отображаться в результирующем наборе как NULL. Если значение supplier_id в таблице orders не существует в таблице suppliers, то все поля в таблице suppliers будут отображаться результирующем наборе как NULL .

Рассмотрим некоторые данные, чтобы понять, как работает FULL OUTER JOIN:

У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name). Она содержит следующие данные:

У нас есть вторая таблица orders с тремя полями (order_id, supplier_id и order_date), которая содержит следующие данные:

Если мы выполним Oracle оператор SELECT (который содержит FULL OUTER JOIN) ниже:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers FULL OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

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

Строки для Microsoft и NVIDIA будут включены, так как используется FULL OUTER JOIN. Тем не менее, вы заметите, что поле order_date для этих записей содержит значение NULL.

Строка для supplier_id 10004 также будет включена, так как используется FULL OUTER JOIN. Тем не менее, вы заметите, что supplier_id и поле supplier_name для этих записей содержат значение NULL.

JOIN в SQL

Вы здесь: Главная — MySQL — SQL — JOIN в SQL

Команда JOIN в SQL-запросе служит для объединения выборки из нескольких таблиц в один результирующий набор, причём в результирующей выборке находятся все поля всех таблиц, участвующих в запросе. Давайте с Вами разберём подробнее использование JOIN в SQL.

Существует несколько вариантов запроса JOIN, начнём мы с самого популярного, а именно INNER JOIN:

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

Особенностью INNER JOIN является то, что в результат входят все поля со всеми значениями. Количество записей ровно столько, сколько удовлетворили условиям у обеих таблиц.

Теперь давайте перейдём к следующей разновидности JOIN, а точнее к LEFT OUTER JOIN:

Особенностью данного запроса является то, что результат выборки содержит записи, удовлетворяющие левой таблице. Если они ещё и удовлетворяют условиям правой таблицы, то это идентично INNER JOIN, иначе вместо значений в правой таблице будет NULL.

Теперь перейдём к RIGHT OUTER JOIN:

Зеркальная противоположность LEFT OUTER JOIN, теперь NULL присутствуют в первой таблице, то есть слева.

И, наконец, последний тип JOIN — это CROSS JOIN:

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

Я ещё не рассказал о FULL OUTER JOIN, который не поддерживается MySQL. Но, на мой взгляд, он и не нужен. Его особенностью является то, что он выводит строку, в которой есть хотя бы одно совпадение с любой из таблиц. Соответственно, в другой таблице (в которой нет совпадения с условием) идут NULL. Фактически, это объединение LEFT OUTER JOIN и RIGHT OUTER JOIN:

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

• Создано 21.01.2013 10:22:31
• Михаил Русаков

Копирование материалов разрешается только с указанием автора (Михаил Русаков) и индексируемой прямой ссылкой на сайт (http://myrusakov.ru)!

Добавляйтесь ко мне в друзья ВКонтакте: http://vk.com/myrusakov.
Если Вы хотите дать оценку мне и моей работе, то напишите её в моей группе: http://vk.com/rusakovmy.

Если Вы не хотите пропустить новые материалы на сайте,
то Вы можете подписаться на обновления: Подписаться на обновления

Если у Вас остались какие-либо вопросы, либо у Вас есть желание высказаться по поводу этой статьи, то Вы можете оставить свой комментарий внизу страницы.

Порекомендуйте эту статью друзьям:

Если Вам понравился сайт, то разместите ссылку на него (у себя на сайте, на форуме, в контакте):

1. Кнопка:
   <a href=»https://myrusakov.ru» target=»_blank»><img src=»https://myrusakov.ru/images/button.gif» alt=»Как создать свой сайт» /></a>

   Она выглядит вот так:

2. Текстовая ссылка:
   <a href=»https://myrusakov.ru» target=»_blank»>Как создать свой сайт</a>

   Она выглядит вот так: Как создать свой сайт

3. BB-код ссылки для форумов (например, можете поставить её в подписи):
   [URL=»https://myrusakov.ru»]Как создать свой сайт[/URL]

Joins оператор MySQL — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном пособии вы узнаете, как использовать JOINS (INNER и OUTER) в MySQL с синтаксисом, рисунками и примерами.

Описание

MySQL JOINS используются для извлечения данных из нескольких таблиц. JOIN выполняется всякий раз, когда две или более таблиц объединяются в SQL предложении.

Существуют различные типы соединений MySQL:

Рассмотрим синтаксис MySQL JOIN, а также изучим примеры MySQL JOIN.

INNER JOIN (простое соединение)

Скорее всего, вы уже писали запросы в которых используются MySQL INNER JOIN. Это наиболее распространенный тип соединения. MySQL INNER JOINS возвращает все строки из нескольких таблиц, где выполняется условия соединения.

Синтаксис

Синтаксис INNER JOIN в MySQL:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

В этом рисунке, MySQL INNER JOIN возвращает затененную область:

MySQL INNER JOIN будет возвращать записи, где table1 и table2 будут пересекаться.

Пример

Ниже приведен пример MySQL INNER JOIN:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

Этот пример MySQL INNER JOIN возвращает все строки из таблиц suppliers и orders, где имеются соответствующие значение поля supplier_id в обоих таблицах.

Рассмотрим некоторые данные, чтобы понять, как работает INNER JOIN:

У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name) которая содержит следующие данные:

У нас есть еще одна таблица orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:

Если мы выполним MySQL оператор SELECT (который содержит INNER JOIN) ниже:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

Наш результирующий набор будет выглядеть следующим образом:

Строки для Microsoft и NVIDIA из таблицы suppliers будут опущены, так как значения supplier_id 10002 и 10003 не существует в обеих таблицах. Строка order_id 500127 из таблицы orders будет опущена, так как supplier_id 10004 не существует в таблице suppliers.

Старый Синтаксис

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

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;

LEFT OUTER JOIN

Другой тип соединения называется MySQL LEFT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с левосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны.

Синтаксис

Синтаксис для LEFT OUTER JOIN в MySQL:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

В некоторых базах данных LEFT OUTER JOIN заменяется на LEFT JOIN.

На этом рисунке, MySQL LEFT OUTER JOIN возвращает затененную область:

MySQL LEFT OUTER JOIN возвратит все записи из table1 и только те записи из table2, которые пересекаются с table1.

Пример

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT JOIN orders ON suppliers.supplier_id = orders.supplier_id;

Этот пример LEFT OUTER JOIN возвратит все строки из таблицы suppliers, и только те строки из таблицы orders, где объединяемые поля равны.

Если значение supplier_id в таблице suppliers не существует в таблице orders, все поля таблицы orders будут отображаться в результирующем наборе как NULL.

Рассмотрим некоторые данные, чтобы понять, как работает LEFT OUTER JOIN:

У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name) которая содержит следующие данные:

У нас есть еще одна таблица orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:

Если мы выполним MySQL оператор SELECT (который содержит LEFT OUTER JOIN) ниже:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

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

Строки для Microsoft и NVIDIA будут включены, так как был использован LEFT OUTER JOIN. Тем не менее, вы заметите, что поле order_date для этих записей содержит значение NULL.

RIGHT OUTER JOIN

Другой тип соединения называется MySQL RIGHT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с правосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны.

Синтаксис

СинтаксиRIGHT OUTER JOIN в MySQL:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

В некоторых базах данных, RIGHT OUTER JOIN заменяется на RIGHT JOIN.

На этом рисунке, MySQL RIGHT OUTER JOIN возвращает затененную область:

MySQL RIGHT OUTER JOIN возвратит все записи из table2 и только те записи из table1, которые пересекаются с table2.

Пример

Ниже приведен пример MySQL RIGHT OUTER JOIN:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers RIGHT JOIN orders ON suppliers.supplier_id = orders.supplier_id;

Этот пример RIGHT OUTER JOIN возвращает все строки из таблицы orders и только те строки из таблицы suppliers, где объединяемые поля равны.

Если значение supplier_id в таблице orders не существует в таблице suppliers, все поля в таблице suppliers будут отображаться в результирующем наборе как NULL.

Рассмотрим некоторые данные, чтобы понять, как работает RIGHT OUTER JOIN:

У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name) которая содержит следующие данные:

У нас есть вторая таблица orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:

Если мы выполним MySQL оператор SELECT (который содержит RIGHT OUTER JOIN) ниже:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers RIGHT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

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

Строка для order_id 500127 будет включена, так как был использован RIGHT OUTER JOINS. Тем не менее, вы заметите, что поле supplier_name для этой записи содержит значение NULL.

SQL Join | Leo Life Blog

Увы редко получается писать, но после среды скорее всего в плотную вернуть к блогу.

Сегодня поговорим о JOIN. Согласно Wiki:

JOIN — оператор языка SQL, который является реализацией операции соединения реляционной алгебры. Входит в раздел FROM операторов SELECT, UPDATE или DELETE.

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

• в схему таблицы-результата входят столбцы обеих исходных таблиц (таблиц-операндов), то есть схема результата является «сцеплением» схем операндов;

• каждая строка таблицы-результата является «сцеплением» строки из одной таблицы-операнда со строкой второй таблицы-операнда.

Большинство начинающих разработчиков используют CROSS JOIN сами того не подозревая что создает при большом объеме выборки не рентабельные затраты на использование системы БД.

Для примера будем использовать 2 примитивные таблицы

CROSS JOIN

и напишем «классический» SELECT

SELECT d1, d2 FROM T1, T2 WHERE k1 = k2

Результат(где k1 = k2):

1:10

2:20

Вроде все ок со стороны пользователя, что просили то и получили. А теперь заглянем под «копот».

На самом деле выполняется сначала первая часть выражения:

SELECT d1, d2 FROM T1, T2

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

На первый взгляд ничего критичного, но если у нас пару миллионов значений в каждой таблице?

В результате будет 4 биллиона значений в которых WHERE начнет фильтровать. В нашем случае из 16 остается только 2

Избегается CROSS JION с помощью нормальных выражений.

INNER JOIN

Оператор внутреннего соединения INNER JOIN соединяет две таблицы. Порядок таблиц для оператора неважен, поскольку оператор является симметричным.

SELECT d1,d2 FROM T1 INNER JOIN T2 ON T1.k1=T2.k2

Результат:

LEFT JOIN

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

SELECT d1,d2 FROM T1 LEFT JOIN T2 ON T1.k1=T2.k2

Результат:

RIGHT JOIN

RIGHT JOIN противоположен LEFT.

Так как мы работаем от правой таблицы , а в ней не существует ключа 3 и 4 то в результате мы увидим 50 и 60 справа так как правая таблица делает полную выборку. А слева будет Null так как ключа 5 и 6 нет в таблице 1

SELECT d1,d2 FROM T1 RIGHT JOIN T2 ON T1.k1=T2.k2

Результат:

 FULL JOIN (не все БД поддерживают)

FULL JOIN сливает таблицу где ключи равны либо отсутствуют в одной из ьаблиц.

Так как мы работаем от правой таблицы , а в ней не существует ключа 3 и 4 то в результате мы увидим 50 и 60 справа так как правая таблица делает полную выборку. А слева будет Null так как ключа 5 и 6 нет в таблице 1

SELECT d1,d2 FROM T1 FULL JOIN T2 ON T1.k1=T2.k2

Результат:

 Так как поддерживается FULL Join не всеми базами данных есть альтернативное решение через UNION

 UNION

UNION не имеет ничего общего с JOIN. UNION означает продолжить данные. То есть взять таблицу 1 и после нее продолжить таблицу 2

SELECT d1,d2 FROM T1 LEFT JOIN T2 ON T1.k1=T2.k2 UNION ALL SELECT d1,d2 FROM T1 RIGHT JOIN T2 ON T1.k1=T2.k2 WHERE d1 is Null

Результат:

Стоит обратить внимания на

 в разных базах своя проверка на Null будьте внимательней.

У кого все еще остались вопросы, рекомендую к просмотру видео Vladimira Mozhenkova

— Azure Data Explorer

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

В этой статье

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

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

Синтаксис

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

Аргументы

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

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

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

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

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

  Примечание

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

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

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

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

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

Возврат

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

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

Примечание

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

Подсказка

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

Пример

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

  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 = timestamp
| присоединиться (События
        | где 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

Стол Y

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

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

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

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

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

Примечание

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

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

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

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

Примечание

• (б, 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
по ключу
  

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

• 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"
  

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

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

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

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

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

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

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

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

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

Левый ароматизатор, препятствующий соединению

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

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

Примечание

Anti-join моделирует запрос «НЕ В».

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

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

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

Примечание

Anti-join моделирует запрос «НЕ В».

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

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

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

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

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

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

Перекрестное соединение

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

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

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

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

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

 - Выбрать все строки из таблицы городов, даже если в таблице графств нет соответствующей строки
   ВЫБЕРИТЕ названия городов, стран.
   ИЗ городов, стран
   ГДЕ city.country_id = country.id (+); 

 - Определить таблицы
   СОЗДАТЬ ТАБЛИЦУ страны
   (
       НОМЕР id (3),
       имя VARCHAR2 (70)
   );

   СОЗДАТЬ ТАБЛИЦЫ города
   (
       имя VARCHAR2 (70),
       country_id НОМЕР (3)
   );

   -- Данные
   ВСТАВИТЬ ЗНАЧЕНИЯ стран (1, «Франция»);

   ВСТАВИТЬ ЦЕННОСТИ города («Париж», 1);
   ВСТАВИТЬ ЦЕННОСТИ города ('Лондон', 2);
   COMMIT; 

 - Использование внутреннего соединения
   ВЫБЕРИТЕ названия городов, стран.
   ИЗ городов, стран
   ГДЕ city.country_id = country.id; 

 - Использование оператора внешнего соединения
   ВЫБЕРИТЕ названия городов, стран.
   ИЗ городов, стран
   ГДЕ city.country_id = country.id (+); 

 - Использование синтаксиса внешнего соединения ANSI SQL
   ВЫБЕРИТЕ названия городов, стран.
   ИЗ городов ВЛЕВО ВНЕШНИЙ ПРИСОЕДИНЯЙТЕСЬ к странам НА cities.country_id = countries.id; 

 ВЫБЕРИТЕ названия городов, стран.
   ИЗ городов, стран
   ГДЕ cities.country_id * = country.id;
   -- Ошибка:
   - Сообщение 4147, уровень 15, состояние 1, строка 3
   - В запросе используются операторы внешнего соединения, отличные от ANSI («* =» или «= *»).
   - Чтобы выполнить этот запрос без изменений, установите уровень совместимости для текущей базы данных на 80,
   - с помощью опции SET COMPATIBILITY_LEVEL команды ALTER DATABASE.- Настоятельно рекомендуется переписать запрос, используя операторы внешнего соединения ANSI (LEFT OUTER JOIN,
   - ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ).
   - В будущих версиях SQL Server операторы соединения, отличные от ANSI, не будут поддерживаться даже в
   - режимы обратной совместимости 

 - Включить оператор внешнего соединения * =
   ALTER DATABASE test SET COMPATIBILITY_LEVEL = 80;

   ВЫБЕРИТЕ города.имя, country.name
   ИЗ городов, стран
   ГДЕ cities.country_id * = country.id; 

 - Попробуйте включить оператор внешнего соединения * =
   ALTER DATABASE test SET COMPATIBILITY_LEVEL = 80;
   -- Ошибка:
   - Сообщение 15048, уровень 16, состояние 1, строка 1
   - Допустимые значения уровня совместимости базы данных: 90, 100 или 110.
   ALTER DATABASE test SET COMPATIBILITY_LEVEL = 90;

   ВЫБЕРИТЕ названия городов, стран.
   ИЗ городов, стран
   ГДЕ cities.country_id * = country.id;
   -- Ошибка:
   - Сообщение 102, уровень 15, состояние 1, строка 3
   - Неправильный синтаксис рядом с "* =".