Cross join sql: Оператор SQL CROSS JOIN: примеры, синтаксис

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

Оператор SQL CROSS JOIN формирует таблицу перекрестным соединением (декартовым произведением) двух таблиц. При использовании оператора SQL CROSS JOIN каждая строка левой таблицы сцепляется с каждой строкой правой таблицы. В результате получается таблица со всеми возможными сочетаниями строк обеих таблиц.

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

SELECT
    column_names [,... n]
FROM
    Table_1 CROSS JOIN Table_2

Обратите внимание, что в операторе за ненадобностью отсутствует условие (ON).

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

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

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

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

Пример 1. Произвести декартово произведение обеих таблиц с помощью оператора SQL CROSS JOIN:

SELECT *
FROM Authors CROSS JOIN Books

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

CROSS JOIN против INNER JOIN в SQL

CROSS JOIN = (ВНУТРЕННИЙ) JOIN = запятая («,»)

TL; DR Единственная разница между SQL CROSS JOIN, (INNER) JOIN и запятой («,») (кроме запятой, имеющей более низкий приоритет для порядка оценки) состоит в том, что (INNER) JOIN имеет ON, а CROSS JOIN и запятую — нет.

Повторные промежуточные продукты

Все три производят промежуточный концептуальный реляционный «декартовский» продукт в стиле SQL, то есть перекрестное соединение, всех возможных комбинаций строки из каждой таблицы. Это включено и / или ГДЕ, что уменьшает количество строк. SQL Fiddle

Стандарт SQL определяет <запятую> через продукт (7.5 1.b.ii), <перекрестное соединение> через <запятую> (7.7 1.a) и JOIN ON <условие поиска> через <запятую> плюс WHERE (7.7 1.b ).

Как говорит Википедия:

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

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

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

[…] Результат объединения может быть определен как результат первого взятия декартова произведения (или перекрестного объединения) всех записей в таблицах (объединение каждой записи в таблице A с каждой записью в таблице B) и затем возврата все записи, которые удовлетворяют предикату соединения.

В «неявной нотации объединения» просто перечисляются таблицы для объединения в предложении FROM оператора SELECT с использованием запятых для их разделения. Таким образом, он определяет перекрестное соединение

Re OUTER JOINs и использование ON против ГДЕ в них см. Условия в LEFT JOIN (ВНЕШНЕЕ СОЕДИНЕНИЕ) против INNER JOIN .

Зачем сравнивать столбцы между таблицами?

Когда нет повторяющихся строк:

Каждая таблица содержит строки, которые составляют истинный оператор из определенного шаблона оператора заполнения-заполненных-именованных-бланков. (Это делает истинное предложение из — удовлетворяет — определенный (характерный) предикат .)

• Базовая таблица содержит строки, которые делают истинный оператор из некоторого заданного DBA шаблона оператора:

  /* rows where
  customer C.CustomerID has age C.Age and ...
  */
  FROM Customers C

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

  /* rows where
      customer C. CustomerID has age C.Age and ...
  AND movie M.Movie is rented by customer M.CustomerID and ...
  */
  FROM Customers C CROSS JOIN Movies M

• ON & WHERE условия AND, чтобы дать дополнительный шаблон. Значение снова строки, которые удовлетворяют этому шаблону:

  /* rows where
      customer C.CustomerID has age C.Age and ...
  AND movie M.Movie is rented by customer M.CustomerID and ...
  AND C.CustomerID = M.CustomerID
  AND C.Age >= M.[Minimum Age]
  AND C.Age = 18
  */
  FROM Customers C INNER JOIN Movies M
  ON C.CustomerID = M.CustomerID
  AND C.Age >= M.[Minimum Age]
  WHERE C.Age = 18

В частности, сравнение столбцов для (SQL) равенства между таблицами означает, что строки, хранящиеся в продукте из частей шаблона объединенных таблиц, имеют одинаковое (не NULL) значение для этих столбцов. Это просто совпадение, что многие строки обычно удаляются путем сравнения на равенство между таблицами — что необходимо и достаточно для характеристики нужных вам строк.

Просто напишите SQL для шаблона для строк, которые вы хотите!

Значение запросов (и таблиц и условий) см. В разделе:
Как получить совпадающие данные из другой таблицы SQL для двух разных столбцов: Inner Join и / или Union?
Есть ли эмпирическое правило для построения SQL-запроса из понятного человеку описания?

Перегрузка «перекрестное соединение»

К сожалению, термин «перекрестное соединение» используется для:

• Промежуточный продукт.
• CROSS JOIN
• (ВНУТРЕННИЙ) СОЕДИНИТЕ с ON или WHERE, который не сравнивает столбцы из одной таблицы с столбцами из другой. (Так как это имеет тенденцию возвращать так много промежуточных строк продукта.)

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

Использование CROSS JOIN против (INNER) JOIN против запятой

Общее соглашение:

• Используйте CROSS JOIN тогда и только тогда, когда вы не сравниваете столбцы между таблицами. Это означает, что отсутствие сравнений было преднамеренным.
• Используйте (INNER) JOIN с ON тогда и только тогда, когда вы сравниваете столбцы между таблицами. (Плюс, возможно, другие условия.)
• Не используйте запятую.

Как правило, условия не для пар таблиц сохраняются для WHERE. Но их, возможно, придется поместить в (n INNER) JOIN ON, чтобы получить соответствующие строки для аргумента RIGHT, LEFT или FULL (OUTER) JOIN.

Re «Не используйте запятую» Смешивание запятой с явным JOIN может ввести в заблуждение, потому что запятая имеет меньший приоритет. Но учитывая роль промежуточного продукта в значении CROSS JOIN, (INNER) JOIN и запятой, аргументы для соглашения выше о том, что он вообще не используется, шатки. Перекрестное соединение или запятая — это как (ВНУТРЕННЕЕ) соединение, находящееся в состоянии ИСТИНА. Промежуточный продукт, ON и WHERE, все вводят AND в соответствующем предикате. Тем не менее, можно думать о INNER JOIN ON — скажем, о создании выходной строки только при поиске пары входных строк, удовлетворяющих условию ON — тем не менее он возвращает строки перекрестного соединения, которые удовлетворяют условию. Единственная причина, по которой ON должен был дополнить запятую в SQL, это написать OUTERСоединения. Конечно, выражение должно прояснить его значение; но то, что ясно, зависит от того, что вещи означают.

Диаграммы Венна Диаграмма Венна с двумя пересекающимися кругами может иллюстрировать разницу между выходными строками для INNER, LEFT, RIGHT & FULL JOIN для одного и того же входа. И когда ВКЛЮЧЕНО, безусловно, ИСТИНА, результат ВНУТРЕННЕГО СОЕДИНЕНИЯ такой же, как ВРЕМЕННОЕ СОЕДИНЕНИЕ. Также он может иллюстрировать строки ввода и вывода для INTERSECT, UNION и EXCEPT. И когда оба входа имеют одинаковые столбцы, результат INTERSECT такой же, как для стандартного SQL NATURAL JOIN, а результат EXCEPT такой же, как для некоторых идиом, включающих LEFT & RIGHT JOIN. Но это не иллюстрирует, как (INNER) JOIN работает в целом. На первый взгляд это кажется правдоподобным . Он может определить детали ввода и / или вывода дляособые случаиON, PK (первичные ключи), FK (внешние ключи) и / или SELECT. Все, что вам нужно сделать, чтобы увидеть это, это определить, какие именно элементы наборов представлены кружками . (Какие запутанные представления никогда не проясняются.) (Помните, что обычно для выходных строк объединений заголовки отличаются от входных строк . А таблицы SQL — это пакеты, а не наборы строк с NULL .)

Cross join sql пример — Вэб-шпаргалка для интернет предпринимателей!

Продолжаем изучать основы SQL, и пришло время поговорить о простых объединениях JOIN. И сегодня мы рассмотрим, как объединяются данные по средствам операторов LEFT JOIN, RIGHT JOIN, CROSS JOIN и INNER JOIN, другими словами, научимся писать запросы, которые объединяют данные, и как обычно изучать все это будем на примерах.

Объединения JOIN очень важны в SQL, так как без умения писать запросы с объединением данных разных объектов, просто не обойтись программисту SQL, да и просто админу который время от времени выгружает какие-то данные из базы данных, поэтому это относится к основам SQL и каждый человек, который имеет дело с SQL, должен иметь представление, что это такое.

Примечание! Все примеры будем писать в Management Studio SQL Server 2008.

Мы с Вами уже давно изучаем основы SQL, и если вспомнить начинали мы с оператора select, и вообще было уже много материала на этом сайте по SQL, например:

И много другого, даже уже рассматривали объединения union и union all, но, так или иначе, более подробно именно об объединениях join мы с Вами не разговаривали, поэтому сегодня мы восполним этот пробел в наших знаниях.

И начнем мы как обычно с небольшой теории.

Объединения JOIN — это объединение двух или более объектов базы данных по средствам определенного ключа или ключей или в случае cross join и вовсе без ключа. Под объектами здесь подразумевается различные таблицы, представления (views), табличные функции или просто подзапросы sql, т.е. все, что возвращает табличные данные.

Объединение SQL LEFT и RIGHT JOIN

LEFT JOIN – это объединение данных по левому ключу, т. е. допустим, мы объединяем две таблицы по left join, и это значит что все данные из второй таблицы подтянутся к первой, а в случае отсутствия ключа выведется NULL значения, другими словами выведутся все данные из левой таблицы и все данные по ключу из правой таблицы.

RIGHT JOIN – это такое же объединение как и Left join только будут выводиться все данные из правой таблицы и только те данные из левой таблицы в которых есть ключ объединения.

Теперь давайте рассматривать примеры, и для начала создадим две таблицы:

Вот такие простенькие таблицы, И я для примера заполнил их вот такими данными:

Теперь давайте напишем запрос с объединением этих таблиц по ключу number, для начала по LEFT:

Как видите, здесь данные из таблицы t1 вывелись все, а данные из таблицы t2 не все, так как строки с number = 4 там нет, поэтому и вывелись NULL значения.

А что будет, если бы мы объединяли по средствам right join, а было бы вот это:

Другими словами, вывелись все строки из таблицы t2 и соответствующие записи из таблицы t1, так как все те ключи, которые есть в таблице t2, есть и в таблице t1, и поэтому у нас нет NULL значений.

Объединение SQL INNER JOIN

Inner join – это объединение когда выводятся все записи из одной таблицы и все соответствующие записи из другой таблице, а те записи которых нет в одной или в другой таблице выводиться не будут, т.е. только те записи которые соответствуют ключу. Кстати сразу скажу, что inner join это то же самое, что и просто join без Inner. Пример:

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

Обновим наши данные, просто проставим в колонку number2 значение 1:

И давайте напишем запрос с объединением по двум ключам:

И результат будет таким же, как и в предыдущем примере:

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

Запрос тот же самый, а вот результат:

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

Объединение SQL CROSS JOIN

CROSS JOIN – это объединение SQL по которым каждая строка одной таблицы объединяется с каждой строкой другой таблицы. Лично у меня это объединение редко требуется, но все равно иногда требуется, поэтому Вы также должны уметь его использовать. Например, в нашем случае получится, конечно, не понятно что, но все равно давайте попробуем, тем более синтаксис немного отличается:

Здесь у нас каждой строке таблицы test_table соответствует каждая строка из таблицы test_table_2, т.е. в таблице test_table у нас 4 строки, а в таблице test_table_2 3 строки 4 умножить 3 и будет 12, как и у нас вывелось 12 строк.

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

Как видите, я здесь объединяю и по left и по right и по inner просто, для того чтобы это было наглядно.

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

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

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

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

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

При необходимости соединения не двух, а нескольких таблиц, операция соединения применяется несколько раз (последовательно).

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

Содержание

Описание оператора [ править | править код ]

В большинстве СУБД при указании слов LEFT , RIGHT , FULL слово OUTER можно опустить. Слово INNER также в большинстве СУБД можно опустить.

В общем случае СУБД при выполнении соединения проверяет условие (предикат) condition. Если названия столбцов, по которым происходит соединение таблиц, совпадают, то вместо ON можно использовать USING . Для CROSS JOIN условие не указывается.

Для перекрёстного соединения (декартова произведения) CROSS JOIN в некоторых реализациях SQL используется оператор «запятая» (,):

Виды оператора JOIN [ править | править код ]

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

INNER JOIN [ править | править код ]

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

Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.

Тело результата логически формируется следующим образом. Каждая строка одной таблицы сопоставляется с каждой строкой второй таблицы, после чего для полученной «соединённой» строки проверяется условие соединения (вычисляется предикат соединения). Если условие истинно, в таблицу-результат добавляется соответствующая «соединённая» строка.

Описанный алгоритм действий является строго логическим, то есть он лишь объясняет результат, который должен получиться при выполнении операции, но не предписывает, чтобы конкретная СУБД выполняла соединение именно указанным образом. Существует несколько способов реализации операции соединения, например, соединение вложенными циклами (англ. inner loops join ), соединение хешированием (англ. hash join ), соединение слиянием (англ. merge join ). Единственное требование состоит в том, чтобы любая реализация логически давала такой же результат, как при применении описанного алгоритма.

OUTER JOIN [ править | править код ]

Соединение двух таблиц, в результат которого обязательно входят все строки либо одной, либо обеих таблиц.

LEFT OUTER JOIN [ править | править код ]

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

Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.

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

1. В результат включается внутреннее соединение ( INNER JOIN ) левой и правой таблиц по предикату p.
2. Затем в результат добавляются те строки левой таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких строк столбцы, соответствующие правой таблице, заполняются значениями NULL .

RIGHT OUTER JOIN [ править | править код ]

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

Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.

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

1. В результат включается внутреннее соединение ( INNER JOIN ) левой и правой таблиц по предикату p.
2. Затем в результат добавляются те строки правой таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких строк столбцы, соответствующие левой таблице, заполняются значениями NULL .

FULL OUTER JOIN [ править | править код ]

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

Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.

Тело результата логически формируется следующим образом. Пусть выполняется соединение первой и второй таблиц по предикату (условию) p. Слова «первой» и «второй» здесь не обозначают порядок в записи выражения (который неважен), а используются лишь для различения таблиц.

1. В результат включается внутреннее соединение ( INNER JOIN ) первой и второй таблиц по предикату p.
2. В результат добавляются те строки первой таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких строк столбцы, соответствующие второй таблице, заполняются значениями NULL .
3. В результат добавляются те строки второй таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких строк столбцы, соответствующие первой таблице, заполняются значениями NULL .

Person.Name	Person.CityId	City. Id	City.Name
Андрей	1	1	Москва
Сергей	1	1	Москва
Леонид	2	2	Санкт-Петербург
NULL	NULL	3	Казань
Григорий	4	NULL	NULL

CROSS JOIN [ править | править код ]

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

Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.

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

Person.Name	Person.CityId	City. Id	City.Name
Андрей	1	1	Москва
Андрей	1	2	Санкт-Петербург
Андрей	1	3	Казань
Леонид	2	1	Москва
Леонид	2	2	Санкт-Петербург
Леонид	2	3	Казань
Сергей	1	1	Москва
Сергей	1	2	Санкт-Петербург
Сергей	1	3	Казань
Григорий	4	1	Москва
Григорий	4	2	Санкт-Петербург
Григорий	4	3	Казань

Если в предложении WHERE добавить условие соединения (предикат p), то есть ограничения на сочетания кортежей, то результат эквивалентен операции INNER JOIN с таким же условием:

Таким образом, выражения t1, t2 WHERE p и t1 INNER JOIN t2 ON p синтаксически являются альтернативными формами записи одной и той же логической операции внутреннего соединения по предикату p. Синтаксис CROSS JOIN + WHERE для операции соединения называют устаревшим, его не рекомендует стандарт SQL ANSI [1] [2] .

Автор: Wagner Crivelini

Опубликовано: 09.07.2010

Версия текста: 1.1

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

Но настоящие запросы зачастую гораздо сложнее, чем простые выражения SELECT.

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

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

Однако, чтобы стандартизовать объединение данных, понадобилось довольно много времени. Это было сделано с помощью оператора JOIN (ANSI-SQL 92). К сожалению, некоторые детали использования оператора JOIN так и остаются неизвестными очень многим.

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

Начало: одна таблица, никакого JOIN

Если запрос обращается только к одному объекту, синтаксис будет очень простым, и никакое соединение не потребуется. Выражение будет старым добрым » SELECT fields FROM object » с другими необязательными операторами (то есть WHERE, GROUP BY, HAVING или ORDER BY).

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

Как бы то ни было, соединения в БД всегда есть, даже если конечный пользователь их и не видит.

Логика, стоящая за соединением таблиц

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

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

Рисунок 1. Комбинируем объекты из разных множеств.

Если вы поняли эту аналогию, все становится более осмысленным.

Представьте, что 2 множества на рисунке 1 – это таблицы, а цифры – это ключи, используемые для соединения таблиц. Таким образом, в каждом из множеств вместо целой записи мы видим только ключевые поля каждой таблицы. Результирующий набор комбинаций будет определяться типом используемого соединения, и это я как раз и собираюсь показать. Чтобы проиллюстрировать примеры, возьмем 2 таблицы, показанные ниже:

Таблица Table1

Таблица Table2

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

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

Теперь мы готовы. Давайте рассмотрим типы JOIN, имеющиеся в T-SQL, их синтаксис и результаты, генерируемые ими.

INNER JOIN

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

На рисунке 2 показана диаграмма Венна, иллюстрирующая пересечение двух таблиц. Результат операции – закрашенная область.

Рисунок 2. INNER JOIN.

Теперь посмотрите на синтаксис объединения данных из таблиц Table1 и Table2 с использованием INNER JOIN.

Вот набор результатов, возвращаемый этим выражением:

Заметьте, что выдаются только данные из записей, имеющих одинаковые значения key2 в таблицах Table1 и Table2 .

Противоположностью INNER JOIN является OUTER JOIN. Существует три типа OUTER JOIN – полный, левый и правый. Рассмотрим каждый из них.

FULL JOIN

Полностью это соединение называется FULL OUTER JOIN (зарезервированное слово OUTER необязательно). FULL JOIN работает как объединение двух множеств. На рисунке 3 показана диаграмма Венна для FULL JOIN двух таблиц. Результатом операции опять же является закрашенная область.

Рисунок 3. FULL JOIN.

Синтаксис почти такой же, как показанный выше:

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

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

LEFT JOIN

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

Рисунок 4. LEFT JOIN.

Результатом этого выражения будет:

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

RIGHT JOIN

Также известен как RIGHT OUTER JOIN, и является еще одним частным случаем FULL JOIN. Он выдает все запрошенные данные из таблицы, стоящей в правой части оператора JOIN, плюс данные из левой таблицы, пересекающиеся с правой. Диаграмма Венна для RIGHT JOIN двух таблиц показана на рисунке 5.

Рисунок 5. RIGHT JOIN.

Как видите, синтаксис очень похож на показанный выше:

Результатом этого выражения будет:

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

CROSS JOIN

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

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

Синтаксис CROSS JOIN таков:

Поскольку в Table1 содержится 5 записей, а в Table2 – еще 7, результат этого запроса будет содержать 35 записей (5 x 7).

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

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

SELF JOIN

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

Посмотрите на классический пример, возвращающий имя начальника сотрудника (по таблице 1). В этом примере мы полагаем, что значение в field2 – фактически кодовый номер босса, следовательно, он связан с key1.

А вот результат запроса:

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

Исключение пересечения множеств

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

Рисунок 6. Непересекающиеся записи в Таблице 1.

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

Можно в этом запросе написать LEFT JOIN, например:

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

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

Слово о планах исполнения

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

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

Базовое понимание работы SQL важно для любого администратора БД или разработчика. Это помогает в работе. Если вам интересно, посмотрите на план выполнения запроса, приведенного выше (рисунок 7).

Рисунок 7. План исполнения запроса, использующего LEFT JOIN.

JOIN и индексы

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

При создании запросов мы ожидаем, что SQL Server Query Optimizer будет использовать индексы таблиц для увеличения производительности. Мы также можем помочь Query Optimizer-у выбрать индексированные поля, являющиеся частью запроса.

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

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

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

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

Важно: SQL Server автоматически создает кластерный индекс для первичных ключей. Однако по умолчанию он ничего не делает с внешними ключами. Проверьте, что ваша СУБД настроена надлежащим образом.

Неравенства

При создании SQL-запросов, использующих оператор JOIN, мы обычно сравниваем, равно ли одно поле одной таблицы другому полю другой таблицы. Но это не обязательный синтаксис. Можно использовать любой логический оператор, например, «не равно» (<>), «больше» (>), «меньше» ( Table1 , ту, у которой key1 равен 3. Проблема в том, что есть 6 записей и Table2, удовлетворяющая условиям соединения. Посмотрите на результат запроса:

Рекомендуем к прочтению

SQL — CARTESIAN или CROSS JOINS

CARTESIAN JOIN или CROSS JOIN возвращают декартово произведение множеств записей из двух или более соединенных таблиц. Таким образом, он приравнивается к внутреннему соединению, где условие WHERE всегда принимает значение ИСТИНЫ или где условие WHERE отсутствует в запросе.

Синтаксис

Базовый синтаксис CARTESIAN JOIN или CROSS JOIN следующий:

SELECT table1.column1, table2.column2...
FROM  table1, table2 [, table3 ]

Пример

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

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

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Maxim    |  35 | Moscow    | 21000.00 |
|  2 | AndreyEx |  38 | Krasnodar | 55500.00 |
|  3 | Oleg     |  33 | Rostov    | 34000.00 |
|  4 | Masha    |  35 | Moscow    | 34000.00 |
+----+----------+-----+-----------+----------+

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

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2017-01-11 00:00:00 |           3 |  34000 |
| 100 | 2017-01-11 00:00:00 |           3 |  34000 |
| 101 | 2017-02-02 00:00:00 |           2 |  12500 |
| 103 | 2017-03-05 00:00:00 |           4 |  45000 |
+-----+---------------------+-------------+--------+

Теперь, давайте объединим эти две таблицы, используя INNER JOIN следующим образом:

SQL> SELECT  ID, NAME, AMOUNT, DATE
   FROM CUSTOMERS, ORDERS;

Это произведет следующий результат:

+----+----------+-----+--------+
| ID | NAME     | AGE | AMOUNT |
+----+----------+-----+--------+
|  1 | Maxim    |  35 |  34000 |
|  1 | Maxim    |  35 |  34000 |
|  1 | Maxim    |  35 |  12500 |
|  1 | Maxim    |  35 |  45000 |
|  2 | AndreyEx |  38 |  34000 |
|  2 | AndreyEx |  38 |  34000 |
|  2 | AndreyEx |  38 |  12500 |
|  2 | AndreyEx |  38 |  45000 |
|  3 | Oleg     |  33 |  34000 |
|  3 | Oleg     |  33 |  34000 |
|  3 | Oleg     |  33 |  12500 |
|  3 | Oleg     |  33 |  45000 |
|  4 | Masha    |  35 |  34000 |
|  4 | Masha    |  35 |  34000 |
|  4 | Masha    |  35 |  12500 |
|  4 | Masha    |  35 |  45000 |
+----+----------+-----+--------+

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Перекрестное соединение против внутреннего соединения в SQL

Перекрестное соединение = (INNER) JOIN = запятая («,»)

TL;DR единственное различие между SQL CROSS JOIN, (INNER) JOIN и запятой («,») (кроме того, что запятая имеет более низкий приоритет для порядка оценки) заключается в том, что (INNER) JOIN имеет ON, а CROSS JOIN и запятая-нет.

Вновь промежуточными продуктами

Все три производят промежуточный концептуальный реляционный продукт «Cartesian» в стиле SQL, он же cross join, из всех возможных комбинаций строк из каждой таблицы. Именно ON и/или WHERE уменьшают количество строк. SQL Fiddle

Стандартный SQL определяет <comma> через продукт (7.5 1.b.ii), присоединяйтесь <cross> через <comma> (7.7 1.a) и присоединиться на <search условие> через <comma> плюс WHERE (7.7 1.b).

В Википедии ставит его:

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

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

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

[…] результат соединения может быть определен как результат первого взятия декартова произведения (или перекрестного соединения) всех записей в таблицах (объединения каждой записи в таблице а с каждой записью в таблице В), а затем возврата всех записей, удовлетворяющих предикату соединения.

«implicit join notation» просто перечисляет таблицы для объединения в предложении FROM оператора SELECT, используя запятые для их разделения. Таким образом он определяет перекрестное соединение

Повторное наружное JOINs и через ON против WHERE в них условия, в левое соединение (внешнее объединение) против внутреннего соединения .

Зачем сравнивать столбцы между таблицами?

Когда нет повторяющихся строк:

Каждая таблица содержит строки, которые составляют истинный оператор из определенного шаблона оператора fill-in-the-[named-]blanks. (Он делает истинное предложение из-удовлетворяет-некоторому (характеристическому) предикату .)

• Базовая таблица содержит строки, которые составляют истинный оператор из некоторого шаблона оператора DBA-given:

  /* rows where
  customer C.CustomerID has age C.Age and ...
  */
  FROM Customers C
  

• Промежуточный продукт соединения содержит строки, которые составляют истинный оператор из AND шаблонов его операндов:

  /* rows where
      customer C.CustomerID has age C.Age and ...
  AND movie M.Movie is rented by customer M.CustomerID and ...
  */
  FROM Customers C CROSS JOIN Movies M
  

• ON & WHERE условия ANDed in, чтобы дать еще один шаблон. Значение снова является строками, которые удовлетворяют этому шаблону:

  /* rows where
      customer C.CustomerID has age C.Age and ...
  AND movie M.Movie is rented by customer M.CustomerID and ...
  AND C.CustomerID = M. CustomerID
  AND C.Age >= M.[Minimum Age]
  AND C.Age = 18
  */
  FROM Customers C INNER JOIN Movies M
  ON C.CustomerID = M.CustomerID
  AND C.Age >= M.[Minimum Age]
  WHERE C.Age = 18
  

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

Просто напишите SQL для шаблона для строк, которые вы хотите!

Re значение запросов (и таблиц против условий) см.:

Как получить соответствующие данные из другой таблицы SQL для двух разных столбцов: Inner Join и/или Union?

Существует ли какое-либо эмпирическое правило для построения запроса SQL из удобочитаемого описания?

Перегрузка «cross join»

К сожалению, термин «cross join» используется для:

• Промежуточный продукт.
• ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ.
• (INNER) JOIN с ON или WHERE, которые не сравнивают никакие столбцы из одной таблицы с любыми столбцами другой. (Поскольку это имеет тенденцию возвращать так много строк промежуточного продукта.)

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

Использование перекрестного соединения vs (INNER) JOIN vs запятая

Общая конвенция такова:

• Используйте перекрестное соединение тогда и только тогда, когда вы не сравниваете столбцы между таблицами. То есть показать, что отсутствие сравнений было намеренным.
• Используйте (INNER) JOIN с ON, когда и только когда вы сравниваете столбцы между таблицами. (Плюс, возможно, другие условия.)
• Не используйте запятую.

Обычно также условия, не относящиеся к парам таблиц, сохраняются для WHERE. Но они могут быть помещены в соединение a(n INNER), чтобы получить соответствующие строки для аргумента a RIGHT, LEFT или FULL (OUTER) JOIN.

Re «Don’t use comma» смешивание запятой с явным JOIN может ввести в заблуждение, поскольку запятая имеет более низкий приоритет. Но учитывая роль промежуточного продукта в значении перекрестного соединения, (INNER) JOIN и запятой, аргументы в пользу приведенного выше соглашения о том, чтобы вообще не использовать его, являются шаткими. Перекрестное соединение или запятая — это точно так же, как (INNER) JOIN, то есть ON a TRUE условие. Промежуточный продукт, ON и WHERE все вводят AND в соответствующий предикат. Однако можно подумать о другом внутреннем соединении-скажем, о создании выходной строки только при нахождении пары входных строк, удовлетворяющих условию ON, — тем не менее оно возвращает строки перекрестного соединения, удовлетворяющие этому условию. Единственная причина, по которой ON должен был дополнить запятую в SQL, состояла в том, чтобы написать внешний JOINs. Конечно, выражение должно прояснять свой смысл, но то, что ясно, зависит от того, что понимать под этим словом.

Диаграмма Венна с двумя пересекающимися окружностями может проиллюстрировать разницу между выходными строками для INNER, LEFT, RIGHT & и JOINs для одного и того же входного сигнала. И когда ON безусловно TRUE, результат внутреннего соединения такой же, как и перекрестное соединение. Также он может проиллюстрировать входные и выходные строки для INTERSECT, UNION & EXCEPT. И когда оба входа имеют одинаковые столбцы, результат INTERSECT такой же, как для стандартного SQL естественного соединения, а результат EXCEPT такой же, как для некоторых идиом, включающих LEFT & правое соединение. Но это не иллюстрирует, как (INNER) JOIN работает в целом. Это только кажется правдоподобным на первый взгляд . Он может идентифицировать части ввода и/или вывода для особых случаев ON, PKs (первичные ключи), FKs (внешние ключи) и/или SELECT. Все, что вам нужно сделать, чтобы увидеть это, — это определить, какие именно элементы множеств представлены кругами . (Что путаные презентации никогда не проясняют.) (помните, что в целом для соединений выходные строки имеют разные заголовки от входных строк . И SQL таблиц-это мешки , а не наборы строк с NULLs .)

Использование CROSS JOIN для задач поиска пересечений в исторических данных

Время прочтения: 5 мин.

CROSS JOIN (или полное соединение таблиц без условий) — декартова перемножение множеств. Говоря простым языком — для каждого варианта первого множества будет сопоставлены все варианты второго множества.

Данным типом соединения нужно пользоваться с осторожностью, т.к. виртуальная таблица занимает n*m объема данных в ОЗУ (для соединения 3-х таблиц — n*m*t, и т.д.). Тем не менее, при правильном использовании данный тип соединения таблиц может решать достаточно трудоемкие задачи, такие как — нахождение пересечений в исторических данных. Примером данной задачи может являться: недопущение автоматического распределения заявок клиента на андеррайтера с родственными связями.

Структура таблиц ниже представленных примеров следующая:

Допустим: клиент Сидоров Степан Павлович подал заявку на кредит. На некотором этапе рассмотрения потребовался анализ платежеспособности клиента андеррайтером.

Заявка автоматически распределилась на сотрудника Петрова Екатерина Павловна.

На первый взгляд ничего подозрительного нет — в ФИО людей совпадает только отчество, что является широко распространенной ситуацией.

Однако, при детальном анализе выясняется, что девичья фамилия андеррайтера и клиента совпадают (Сидорова / Сидоров).

SELECT fh.fio_history_id
     , fh.fio_id
     , fh.person_id
     , ln.lname, fn.fname, mn.mname
     , fh.actual
  FROM fio_history fh
 INNER JOIN fio f ON fh.fio_id = f.fio_id
 INNER JOIN lname ln ON f.lname_id = ln.lname_id
 INNER JOIN fname fn ON f.fname_id = fn.fname_id
 INNER JOIN mname mn ON f.mname_id = mn.mname_id
 WHERE person_id IN (1, 5)
 ORDER BY fh.actual DESC;

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

Для решения данной задачи можно использовать простой запрос с CROSS JOIN:

SELECT results.*
  FROM (SELECT underwriter.person_id as u_person_id
             , underwriter.fio_id as u_fio_id
             , underwriter.lname as u_lname
             , client.person_id as c_person_id
             , client.fio_id as c_fio_id
             , client.lname as c_lname
             , CASE 
                WHEN underwriter.lname_id = client.lname_id 
                     OR underwriter.lname_stem = client.lname_stem
                THEN 1
                ELSE 0
               END as is_equal_lnames
          FROM (-- Андеррайтер "Петрова Екатерина Павловна"
                SELECT fh.person_id, fh.fio_id, ln.lname_id, ln.lname
                     , regexp_replace(upper(ln.lname), '(А|ИЙ|АЯ)$', '')) as lname_stem
                  FROM fio_history fh
                 INNER JOIN fio f ON fh.fio_id = f.fio_id
                 INNER JOIN lname ln ON f.lname_id = ln.lname_id
                 WHERE fh.person_id = 1) underwriter
         CROSS JOIN (-- Клиент "Сидоров Степан Павлович"
                     SELECT fh.person_id, fh.fio_id, ln.lname_id, ln.lname
                          , regexp_replace(upper(ln.lname), '(А|ИЙ|АЯ)$', '')) as lname_stem
                       FROM fio_history fh
                      INNER JOIN fio f ON fh.fio_id = f.fio_id
                      INNER JOIN lname ln ON f.lname_id = ln.lname_id
                      WHERE fh.person_id = 5) client) results
 WHERE results.is_equal_lnames = 1;

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

Данный пример является достаточно простым в проверке, т.к. родственные связи находятся в пределах одного поколения.

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

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

Пусть будет ситуация аналогичная предыдущей: клиент Иванов Алексей Николаевич оформляет заявку на кредит, которая впоследствии распределяется на андеррайтера Петров Юрий Александрович.

На первый взгляд, очевидно, что люди не являются родственниками.

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

SELECT fh.fio_history_id
     , fh.fio_id
     , fh.person_id
     , ln.lname, fn.fname, mn.mname
     , fh.actual
  FROM fio_history fh
 INNER JOIN fio f ON fh.fio_id = f.fio_id
 INNER JOIN lname ln ON f.lname_id = ln.lname_id
 INNER JOIN fname fn ON f.fname_id = fn.fname_id
 INNER JOIN mname mn ON f.mname_id = mn.mname_id
 WHERE fh.person_id IN (1, 2, 3, 4)
 ORDER BY actual DESC;

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

WITH people_info AS (
  SELECT person_id
       , fio_id
       , NULL as relative_person_id
       , NULL as relative_fio_id
    FROM fio_history
   UNION ALL
  SELECT pinf.person_id
       , pinf.fio_id
       , fh.person_id as other_person_id
       , fh.fio_id as other_fio_id
    FROM fio_history fh
   CROSS JOIN people_info pinf
   WHERE pinf.relative_person_id IS NULL
)

SELECT results.*
  FROM (SELECT underwriter.person_id as u_person_id
             , underwriter.fio_id as u_fio_id
             , underwriter.relative_person_id as u_relative_person_id
             , underwriter.relative_fio_id as u_relative_fio_id
             , underwriter.lname as u_lname
             , client.person_id as c_person_id
             , client.fio_id as c_fio_id
             , client.relative_person_id as c_relative_person_id
             , client.relative_fio_id as c_relative_fio_id
             , client.lname as c_lname
             , CASE 
                WHEN underwriter.lname_id = client.lname_id 
                     OR underwriter.lname_stem = client.lname_stem
                THEN 1
                ELSE 0
               END as is_equal_lnames
          FROM (-- Андеррайтер "Петров Юрий Александрович"
                SELECT pinf.person_id, pinf.fio_id
                     , pinf.relative_person_id, pinf.relative_fio_id, ln.lname_id, ln.lname
                     , regexp_replace(upper(ln.lname), '(А|ИЙ|АЯ)$', '')) as lname_stem
                  FROM people_info pinf
                 INNER JOIN fio f ON pinf.relative_fio_id = f.fio_id
                 INNER JOIN lname ln ON f.lname_id = ln.lname_id
                 WHERE     pinf.relative_person_id IS NOT NULL
                       AND pinf.person_id IN (4)
                       AND pinf.relative_person_id IN (1, 4)) underwriter
         CROSS JOIN (-- Клиент "Иванов Алексей Николаевич"
                     SELECT pinf.person_id, pinf.fio_id
                          , pinf.relative_person_id, pinf.relative_fio_id, ln.lname_id, ln.lname
                          , regexp_replace(upper(ln.lname), '(А|ИЙ|АЯ)$', '')) as lname_stem
                       FROM people_info pinf
                      INNER JOIN fio f ON pinf.relative_fio_id = f.fio_id
                      INNER JOIN lname ln ON f.lname_id = ln.lname_id
                      WHERE     pinf.relative_person_id IS NOT NULL
                            AND pinf.person_id IN (3)
                            AND pinf.relative_person_id IN (2, 3)) client) results
 WHERE results.is_equal_lnames = 1;

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

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

В Sql, Какая Разница Между Join И Cross Join?

SQL имеет следующие типы объединений, все из которых вытекают из теории множеств:

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

From A inner join B является эквивалентом A & cap; B, предоставляя набор элементов, общих для обоих наборов.

Левое внешнее соединение.
From A left outer join B является эквивалентом (A & min; B) & cup; (A & cap; B). Каждый А появится как минимум один раз; если имеется несколько совпадающих B, то A будет повторяться один раз для соответствия B.

Прямое внешнее соединение.
From A right outer join B является эквивалентом (A & cap; B) & cup; (B & min; A). Он идентичен левому соединению со столами торговых мест. Каждый B появится как минимум один раз; если существует несколько совпадений As, каждый B будет повторяться один раз для каждого соответствия B.

Полное внешнее соединение.
From A full outer join B является эквивалентом (A & minus; B) & cup; (A & cap; B) & cup; (B & min; A). Каждый A и каждый B будут отображаться, по крайней мере, один раз. Если A соответствует нескольким Bs, он будет повторяться один раз за матч; если бит B соответствует нескольким Как это будет повторяться один раз за матч.

Cross Join.
From A cross join B вырабатывает декартово произведение A & times; B. Каждый A будет повторяться один раз для каждого B. Если A имеет 100 строк, а B имеет 100 строк, набор результатов будет состоять из 10 000 строк.

Следует отметить, что теоретическое выполнение запроса select состоит из следующих шагов, выполненных в следующем порядке:

Вычислить полное декартово произведение исходного набора (ов) в предложении from, чтобы заполнить набор результатов кандидата.

Примените критерии соединения в предложении from и уменьшите набор результатов.

Примените критерии в where clause для дальнейшего уменьшения набора результатов кандидата.

разделяйте набор результатов кандидата на группы на основе критериев в предложении group by.

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

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

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

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

Закажите окончательный результат, заданный критериями в order by, и испустите его.

Есть еще несколько шагов, связанных с вещами типа compute и compute by, но этого достаточно, чтобы получить теоретическое представление о том, как это работает.

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

SQL CROSS JOIN с примерами

В этой статье мы изучим концепцию SQL CROSS JOIN и подкрепим наши знания простыми примерами, которые поясняются иллюстрациями.

Введение

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

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

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

«Не волнуйтесь, даже если вы сейчас немного проголодались, вы можете есть все, что захотите, после прочтения нашей статьи.”

Основная идея CROSS JOIN заключается в том, что он возвращает декартово произведение объединенных таблиц. В следующем совете мы кратко объясним декартово произведение;

Совет: Что такое декартово произведение?

Декартово произведение — это операция умножения в теории множеств, которая генерирует все упорядоченные пары данных множеств. Предположим, что A — это набор, элементы — это {a, b}, B — это набор, а элементы — это {1,2,3}.Декартово произведение этих двух A и B обозначается AxB, и результат будет следующим.

AxB = {(a, 1), (a, 2), (a, 3), (b, 1), (b, 2), (b, 3)}

Синтаксис

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

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

Набор результатов не изменяется ни для одного из этих синтаксисов. Кроме того, мы должны отметить одну особенность CROSS JOIN.В отличие от INNER JOIN, LEFT JOIN и FULL OUTER JOIN, CROSS JOIN не требует условия соединения.

Пример SQL CROSS JOIN:

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

С помощью следующего запроса мы выполним эти два шага:

Следующий запрос присоединится к таблице Meals and Drinks с ключевым словом CROSS JOIN , и мы получим все парные комбинации названий еды и напитков.

На изображении ниже показан принцип работы CROSS JOIN.

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

Совет: Количество строк набора результатов будет равно умножению количества строк в таблицах, которые будут объединены.В примере меню завтрака количество строк таблицы Meals равно 3, а количество строк таблицы Drinks равно 3, поэтому количество строк набора результатов можно найти с помощью следующих вычислений.

3 (количество строк в таблице обедов) x 3 (количество строк в таблице напитков) = 9 (количество строк в результирующем наборе)

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

SQL CROSS JOIN и соображения производительности

SQL-запросы, содержащие ключевое слово CROSS JOIN, могут быть очень дорогостоящими. Мы пытаемся сказать, что эти запросы могут потреблять больше ресурсов и могут вызвать проблемы с производительностью.Для следующего запроса мы проанализируем план выполнения с помощью ApexSQL Plan. В сгенерированном фактическом плане выполнения мы увидим оператор вложенных циклов, и когда мы наведем указатель мыши на этот оператор, появится всплывающее окно с подробностями.

В этом окне нам на глаза бросается предупреждающее сообщение. Сообщение «Нет предиката присоединения» указывает, что этот запрос может столкнуться с проблемами производительности. По этой причине оптимизатор запросов предупреждает нас об этой потенциальной проблеме.Вкратце, когда мы решаем использовать CROSS JOIN в любом запросе, мы должны учитывать количество таблиц, которые будут объединены. Например, когда мы КРЕСТНО СОЕДИНЯЕМ две таблицы, и если первая содержит 1000 строк, а вторая содержит 1000 строк, количество строк в наборе результатов будет 1.000.000 строк.

Совет: CROSS JOIN можно реализовать только с вложенными циклами, поэтому следующие запросы вернут ошибку, если мы заставим оптимизатор запросов использовать другие типы соединений.

Заключение

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

Эсат Эркеч — специалист по SQL Server, который начал свою карьеру более 8 лет назад в качестве разработчика программного обеспечения. Он является сертифицированным экспертом по решениям Microsoft SQL Server.

Большую часть своей карьеры он посвятил администрированию и разработке баз данных SQL Server. В настоящее время он интересуется администрированием баз данных и бизнес-аналитикой. Вы можете найти его в LinkedIn.

Посмотреть все сообщения Esat Erkec

Последние сообщения Esat Erkec (посмотреть все)

SQL Cross Join — w3resource

Последнее обновление 26 февраля 2020 г. 08:07:43 (UTC / GMT +8 часов)

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

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

Если предложение WHERE используется с CROSS JOIN, оно работает как INNER JOIN.

Альтернативный способ достижения того же результата — использовать имена столбцов, разделенные запятыми, после SELECT и упоминание соответствующих имен таблиц после предложения FROM.

Синтаксис:

 ВЫБРАТЬ *
ИЗ table1
CROSS JOIN table2;
 

Графическое представление синтаксиса перекрестного соединения

Пример:

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

Образец таблицы: продукты

Образец таблицы: компания

Чтобы получить столбцы названия и единицы товара из таблицы food и названия компании, столбцы города компании из таблицы company, после ПЕРЕКРЕСТНОГО СОЕДИНЕНИЯ с этими упомянутыми таблицами можно использовать следующий оператор SQL:

Код SQL:

  ВЫБЕРИТЕ foods.item_name, foods.item_unit,
company.company_name, company.company_city
ИЗ продуктов
Компания CROSS JOIN;  

или

Код SQL:

  ВЫБЕРИТЕ продукты.item_name, foods.item_unit,
company.company_name, company.company_city
ИЗ продуктов питания, компании;  

Как произошло перекрестное объединение двух таблиц

Выход:

 ITEM_NAME ITEM_UNIT COMPANY_NAME COMPANY_CITY
--------------- ---------- --------------- ---------- -----
Chex Mix Pcs Заказать весь Бостон
Cheez-It Pcs Заказать весь Бостон
BN Biscuit Pcs Заказать весь Бостон
Mighty Munch Pcs Заказать весь Бостон
Pot Rice Pcs Заказать весь Бостон
Jaffa Cakes Pcs Заказать Весь Бостон
Соль и коктейль, шт. Заказать весь Бостон
Chex Mix Pcs Jack Hill Ltd Лондон
Cheez-It Pcs Jack Hill Ltd Лондон
BN Biscuit Pcs Jack Hill Ltd Лондон
Mighty Munch Pcs Jack Hill Ltd Лондон
Pot Rice Pcs Jack Hill Ltd Лондон
Jaffa Cakes Pcs Jack Hill Ltd Лондон
Salt n Shake Pcs Jack Hill Ltd Лондон
Chex Mix Pcs Akas Foods Дели
Cheez-It Pcs Akas Foods Дели
BN Biscuit Pcs Akas Foods Дели
Mighty Munch Pcs Акас Фудс Дели
Pot Rice Pcs Akas Foods Дели
Jaffa Cakes Pcs Akas Foods Дели
Salt n Shake Pcs Akas Foods Дели
Chex Mix Pcs Foodies.Лондон
.........
.........
 

Дополнительное представление указанной продукции:

CROSS JOINS: реляционные базы данных

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

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

Выходные данные указанного оператора SQL, показанного здесь, получены с помощью Oracle Database 10g Express Edition.

Практические упражнения по SQL

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

Предыдущая: SQL NATURAL JOIN
Следующая: SQL OUTER JOIN

Кросс-соединение SQL Server, иллюстрированное практическими примерами

Резюме : в этом руководстве вы узнаете, как использовать SQL Server CROSS JOIN для объединения двух или более несвязанных таблиц.

Ниже показан синтаксис SQL Server CROSS JOIN двух таблиц:

 
  SELECT
select_list
ИЗ
Т1
CROSS JOIN T2;  
  Язык кода: SQL (язык структурированных запросов) (sql)  

CROSS JOIN объединил каждую строку из первой таблицы (T1) с каждой строкой из второй таблицы (T2).Другими словами, перекрестное соединение возвращает декартово произведение строк из обеих таблиц.

В отличие от INNER JOIN или LEFT JOIN , перекрестное соединение не устанавливает отношения между объединенными таблицами.

Предположим, что таблица T1 содержит три строки 1, 2 и 3, а таблица T2 содержит три строки A, B и C.

CROSS JOIN получает строку из первой таблицы (T1), а затем создает новая строка для каждой строки второй таблицы (T2). Затем он делает то же самое для следующей строки в первой таблице (T1) и так далее.

На этом рисунке CROSS JOIN создает в общей сложности девять строк. В общем, если первая таблица имеет n строк, а вторая таблица имеет m строк, перекрестное соединение приведет к n x m строкам.

SQL Server

CROSS JOIN примеры

Следующая инструкция возвращает комбинации всех продуктов и магазинов. Набор результатов можно использовать для инвентаризации в конце месяца и на конец года:

 
  SELECT
    идантификационный номер продукта,
    наименование товара,
    store_id,
    0 AS количество
ИЗ
    производство.продукты
КРОСС-ПРИСОЕДИНЯЙТЕСЬ к sales.stores
СОРТИРОВАТЬ ПО
    наименование товара,
    store_id;
  
  Язык кода: SQL (язык структурированных запросов) (sql)  

Вот частичный результат:

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

 
  SELECT
    s.store_id,
    p.product_id,
    ISNULL (продажи; 0) продажи
ИЗ
    sales.stores s
CROSS JOIN production.products p
LEFT JOIN (
    ВЫБРАТЬ
        s.store_id,
        p.product_id,
        СУММ (количество * я.list_price) продажи
    ИЗ
        продажи. заказы o
    ВНУТРЕННЕЕ ПРИСОЕДИНЕНИЕ sales.order_items i ON i.order_id = o.order_id
    ВНУТРЕННЕЕ ПРИСОЕДИНЕНИЕ sales.stores к s.store_id = o.store_id
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ production.products p ON p.product_id = i.product_id
    ГРУППА ПО
        s.store_id,
        p.product_id
) c ВКЛ c.store_id = s.store_id
И c.product_id = p.product_id
ГДЕ
    продажи НУЛЬ
СОРТИРОВАТЬ ПО
    идантификационный номер продукта,
    store_id;
  
  Язык кода: SQL (язык структурированных запросов) (sql)  

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

В этом руководстве вы узнали, как использовать SQL Server CROSS JOIN для создания декартовых продуктов строк из объединенных таблиц.

MySQL CROSS JOIN ключевое слово

Ключевое слово SQL CROSS JOIN

Ключевое слово CROSS JOIN возвращает все записи
из обеих таблиц (table1 и table2).

CROSS JOIN Синтаксис

ВЫБЕРИТЕ имя_столбца
ИЗ стол1
CROSS JOIN table2 ;

Примечание: CROSS JOIN потенциально может возвращать очень большие
результирующие наборы!

Демо-база данных

В этом руководстве мы будем использовать хорошо известный образец базы данных Northwind.

Ниже представлен выбор из таблицы «Клиенты»:

И выбор из таблицы «Заказы»:

MySQL CROSS JOIN, пример

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

Пример

ВЫБЕРИТЕ клиентов.CustomerName, Orders.OrderID
ОТ клиентов
Заказы CROSS JOIN;

Попробуй сам »

Примечание. Ключевое слово CROSS JOIN возвращает все соответствие
записи из обеих таблиц независимо от того, совпадает ли другая таблица или нет. Так что если
в «Заказчиках» есть строки, не совпадающие с «Заказами», или если есть
строки в «Заказы», ​​которые не соответствуют в «Клиенты», эти строки будут
также перечислены.

Если вы добавите предложение WHERE (если table1 и
table2 имеет отношение), CROSS JOIN будет
дает тот же результат, что и предложение INNER JOIN :

Пример

ВЫБЕРИТЕ клиентов.CustomerName, Orders.OrderID
ОТ клиентов
CROSS JOIN Orders
ГДЕ Customers.CustomerID = Orders.CustomerID;

Попробуй сам »

Узнайте, как объединить данные с помощью CROSS JOIN

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

Примечание. Серия начинается со статьи «Введение в объединение баз данных». Все примеры для этого урока основаны на Microsoft SQL Server Management Studio и базе данных AdventureWorks2012. Вы можете начать использовать эти бесплатные инструменты, используя мое Руководство Начало работы с SQL Server . В этой статье мы рассмотрим внутренние соединения.

Что такое CROSS JOIN?

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

В SQL Server вы можете использовать ключевые слова CROSS JOIN для определения перекрестного соединения.

Пример простого CROSS JOIN

Ниже приведен пример простого оператора select с предложением CROSS JOIN.

 ВЫБРАТЬ список столбцов
ИЗ основного стола
       CROSS JOIN secondtable 

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

Для этого в SQL вы можете написать

 SELECT c.Цвет,
       s Размер
ОТ Цвет c
       CROSS JOIN Size s 

Вы видите, что результат содержит все комбинации цвета и размера. Поскольку есть две строки в цвете и четыре в размере, окончательный результат — восемь строк (2 x 4 = 8).

Этот комбинаторный эффект может сделать перекрестные соединения чрезвычайно опасными! Если вы ПЕРЕСЕЧИТЕ СОЕДИНЯЕТЕСЬ с таблицей, состоящей из 1000 строк, с другой таблицей из 1000, вы получите 1000000 в результате.

Теперь, когда вы знаете основы, давайте посмотрим на действительно хороший пример, где CROSS JOINS помогает добиться лучших результатов.

Использование CROSS JOIN для создания отчетов

Рассмотрим пример, в котором президент Adventure Works хотел бы знать с разбивкой по полу, сколько сотрудников занято в каждой должности, даже если в ней нет сотрудников.

Этот запрос на первый взгляд кажется достаточно простым, мы можем использовать GROUP BY и COUNT для создания результата! Вот SQL:

 SELECT JobTitle,
         Пол,
         COUNT (1) как NumberEmployees
ОТ HumanResources.Сотрудник
ГРУППА ПО НАЗВАНИЮ, ПОЛУ
ЗАКАЗАТЬ ПО JobTitle, Пол 

Но обратите внимание, что есть некоторые недостающие комбинации.Например, несмотря на то, что есть специалист по льготам для женщин (F), нет ни одного специалиста-мужчины (M).

Итак, как получить комбинации JobTitle и Gender, когда счетчик равен нулю?

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

Итак, что на самом деле делает этот запрос?

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

Мы создаем два CTE с разными значениями JobTitle и Gender из таблицы Employee. Путем объединения этих таблиц мы можем отобразить все возможные комбинации должностей и пола.

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

 WITH cteJobTitle (JobTitle)
КАК (ВЫБРАТЬ ОТЛИЧИТЕЛЬНЫЙ НАЗВАНИЕ ЗАДАНИЯ
     ОТ HumanResources.Работник),

cteGender (Пол)
КАК (ВЫБРАТЬ ОТЛИЧИТЕЛЬНЫЙ пол
    ОТ HumanResources.Сотрудник)

ВЫБЕРИТЕ J.JobTitle,
         Г.Гендер
ОТ cteJobTitle AS J
         КРЕСТНОЕ СОЕДИНЕНИЕ cteGender AS G
ЗАКАЗ J.JobTitle
 

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

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

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

Подводя итог, вот шаги, которые мы предпринимаем для получения этого результата:

1. Получите отдельный список JobTitles.Это происходит в CTE.
2. Получите четкий список полов. Это происходит в CTE.
3. Создайте все возможные комбинации должностей и пола с помощью CROSS JOIN.
4. Вычислить суммарное количество сотрудников по должностям и полу.
5. Сопоставьте вычисленное суммарное количество с отдельным списком.

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

 WITH cteJobTitle (JobTitle)
КАК (ВЫБРАТЬ ОТЛИЧИТЕЛЬНЫЙ ЗАЗВАНИЕ
ОТ HumanResources.Работник),

cteGender (Пол)
КАК (ВЫБРАТЬ ОТЛИЧИТЕЛЬНЫЙ пол
ОТ HumanResources.Сотрудник),

cteCounts (JobTitle, Gender, NumberEmployees)
КАК (ВЫБЕРИТЕ JobTitle,
Пол,
COUNT (1) AS NumberEmployees
ОТ HumanResources.Сотрудник
GROUP BY JobTitle, Gender)

ВЫБЕРИТЕ J.JobTitle,
Г.Гендер,
COALESCE (C.NumberEmployees, 0) как NumberEmployees
ОТ cteJobTitle AS J
         КРЕСТНОЕ СОЕДИНЕНИЕ cteGender AS G
LEFT OUTER JOIN cteCounts AS C
ВКЛ C.JobTitle = J.JobTitle
И C.Gender = G.Gender
ЗАКАЗ J.JobTitle, G.Gender; 

Для облегчения чтения два CTE и CROSS JOIN для создания всех комбинаций JobTitle и Gender окрашены в синий и красный цвета. CTE для обобщения данных окрашен в зеленый цвет.

Также обратите внимание, что мы используем функцию SQL COALESCE для замены NULL нулевым значением.

Вот результат запроса:

INNER JOIN as CROSS JOIN

По мере знакомства с SQL вы понимаете, что обычно существует более одного способа написать запрос.Например, с помощью предложения WHERE можно заставить CROSS JOIN вести себя как INNER JOIN.

Возьмем эти две таблицы:

Предположим, мы хотим запросить всех сотрудников и показать их дату рождения и фамилию. Для этого мы должны связать таблицу Employee с Person.

Как мы видели, мы можем написать перекрестное соединение для объединения строк, так

 SELECT P.LastName,
       E. Дата рождения
ОТ HumanResources.Сотрудник E
       CROSS JOIN Person.Person P 

Но этот запрос в данном случае не слишком полезен, поскольку он возвращает 5791880 строк!

Чтобы ограничить комбинации строк, чтобы записи о сотрудниках правильно соответствовали строкам сотрудников, мы можем использовать предложение WHERE.Вот последний запрос:

 SELECT P.LastName,
       E. Дата рождения
ОТ HumanResources.Сотрудник E
       CROSS JOIN Person.Person P
ГДЕ P.BusinessEntityID = E.BusinessEntityID 

Вот первые 14 строк из 290:

Этот запрос возвращает те же результаты, что и запрос, написанный с помощью INNER JOIN. Запрос с использованием внутреннего соединения:

 SELECT P.LastName,
       E. Дата рождения
ОТ HumanResources.Сотрудник E
       ВНУТРЕННЕЕ СОЕДИНЕНИЕ Person.Person P
       НА СТР.BusinessEntityID = E.BusinessEntityID 

Какой запрос лучше? Если вы посмотрите на планы запросов, вы увидите, что они очень похожи.

Вот план перекрестного соединения…

Вот план внутреннего соединения…

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

sql server — CROSS JOIN vs INNER JOIN в SQL

CROSS JOIN = (INNER) JOIN = запятая («,»)

TL; DR Единственное различие между SQL CROSS JOIN, (INNER) JOIN и запятой («,») (кроме запятой, имеющей более низкий приоритет для порядка оценки), состоит в том, что (INNER) JOIN имеет ON, а CROSS JOIN и запятую не т.

Re промежуточные продукты

Все три производят промежуточное концептуальное реляционное «декартово» произведение в стиле SQL, также известное как перекрестное соединение, всех возможных комбинаций строки из каждой таблицы.Это ON и / или WHERE, которые уменьшают количество строк. SQL Fiddle

Стандарт SQL определяет <запятая> через продукт (7.5 1.b.ii), <перекрестное соединение> иначе CROSS JOIN через <запятая> (7.7 1.a) и (INNER) JOIN ON <условие поиска> через < запятая> плюс ГДЕ (7.7 1.b).

Как сказано в Википедии:

Cross join
CROSS JOIN возвращает декартово произведение строк из таблиц в объединении. Другими словами, он будет создавать строки, которые объединяют каждую строку из первой таблицы с каждой строкой из второй таблицы.

Внутреннее соединение
[…] Результат соединения может быть определен как результат первого декартова произведения (или перекрестного соединения) всех записей в таблицах (объединение каждой записи в таблице A с каждой записью в таблице B ), а затем возвращает все записи, удовлетворяющие предикату соединения.

«Нотация неявного соединения» просто перечисляет таблицы для объединения в предложении FROM оператора SELECT, используя запятые для их разделения. Таким образом, он определяет перекрестное соединение

Re OUTER JOIN см. Мой ответ
В чем разница между «INNER JOIN» и «OUTER JOIN» ?.

Re OUTER JOINs и использование ON vs WHERE в них, см. Мой ответ
Условия в LEFT JOIN (OUTER JOIN) vs INNER JOIN.

Зачем сравнивать столбцы между таблицами?

Если нет повторяющихся строк:

Каждая таблица содержит строки, которые составляют истинное утверждение из определенного шаблона оператора заполнения [именованных-] пробелов. (Это делает истинное предложение из — удовлетворяет — определенному (характеристическому) предикату .)

• Базовая таблица содержит строки, которые составляют истинное утверждение из некоторого шаблона оператора, заданного администратором баз данных:

    / * строки, где
    заказчик C.CustomerID имеет возраст C.Age и ...
    * /
    ОТ клиентов C
    

• Промежуточный продукт соединения содержит строки, которые составляют истинное утверждение на основе И шаблонов его операндов:

    / * строки, где
        Клиент C.CustomerID имеет возраст C.Age и ...
    И фильм M.Movie арендует заказчик M.CustomerID и ...
    * /
    ОТ ПОКУПАТЕЛЕЙ C CROSS JOIN Movies M
    

• Условия ON и WHERE объединяются с помощью AND, чтобы получить дополнительный шаблон.Значением снова являются строки, удовлетворяющие этому шаблону:

  .

    / * строки, где
        Клиент C.CustomerID имеет возраст C.Age и ...
    И фильм M.Movie арендует заказчик M.CustomerID и ...
    И C.CustomerID = M.CustomerID
    И C. Возраст> = M. [Минимальный возраст]
    И C. Возраст = 18
    * /
    ОТ клиентов C ВНУТРЕННИЙ ПРИСОЕДИНЯЙТЕСЬ К фильмам M
    НА C.CustomerID = M.CustomerID
    И C. Возраст> = M. [Минимальный возраст]
    ГДЕ C.Age = 18
    

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

Просто напишите SQL для шаблона нужных вам строк!

О значении запросов (и таблиц в сравнении с условиями) см .:
Как получить совпадающие данные из другой таблицы SQL для двух разных столбцов: Inner Join и / или Union?
Есть ли какое-нибудь практическое правило для построения SQL-запроса из понятного человеку описания?

Перегрузка «крестовина»

К сожалению, термин «перекрестное соединение» используется для:

• Промежуточный продукт.
• КРЕСТНОЕ СОЕДИНЕНИЕ.
• (INNER) JOIN с ON или WHERE, который не сравнивает никакие столбцы из одной таблицы с любыми столбцами другой. (Так как это имеет тенденцию возвращать очень много строк промежуточного продукта.)

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

Использование CROSS JOIN против (INNER) JOIN против запятой

Общее соглашение:

• Используйте CROSS JOIN тогда и только тогда, когда вы не сравниваете столбцы между таблицами.Это показать, что отсутствие сравнений было намеренным.
• Используйте (INNER) JOIN с ON тогда и только тогда, когда вы сравниваете столбцы между таблицами. (Плюс, возможно, другие условия.)
• Не используйте запятую.

Обычно для WHERE также сохраняются условия, не относящиеся к парам таблиц. Но их, возможно, придется поместить в (n INNER) JOIN ON, чтобы получить соответствующие строки для аргумента RIGHT, LEFT или FULL (OUTER) JOIN.

Re «Не использовать запятую» Смешивание запятой с явным JOIN может ввести в заблуждение, потому что запятая имеет более низкий приоритет.Но, учитывая роль промежуточного продукта в смысле CROSS JOIN, (INNER) JOIN и запятой, аргументы в пользу приведенного выше соглашения о том, что он вообще не используется, являются шаткими. КРЕСТНОЕ СОЕДИНЕНИЕ или запятая подобны (ВНУТРЕННЕМУ) СОЕДИНЕНИЮ, которое находится в ИСТИННОМ состоянии. Промежуточный продукт ON и WHERE вводят AND в соответствующий предикат. Однако еще можно представить себе INNER JOIN ON — скажем, создание выходной строки только при нахождении пары входных строк, удовлетворяющих условию ON — тем не менее, он возвращает строки перекрестного соединения, которые удовлетворяют условию.Единственная причина, по которой ON имел для дополнения запятой в SQL, заключалась в записи OUTER JOIN. Конечно, выражение должно прояснять его значение; но то, что ясно, зависит от того, что подразумевается под этими вещами.

Re Диаграммы Венна Диаграмма Венна с двумя пересекающимися кругами может проиллюстрировать разницу между выходными строками для ВНУТРЕННИХ, ЛЕВЫХ, ПРАВЫХ и ПОЛНЫХ СОЕДИНЕНИЙ для одного и того же входа. И когда ON безусловно TRUE, результат INNER JOIN такой же, как CROSS JOIN.Также он может проиллюстрировать входные и выходные строки для ПЕРЕСЕЧЕНИЯ, СОЕДИНЕНИЯ и ИСКЛЮЧЕНИЯ. И когда оба входа имеют одинаковые столбцы, результат INTERSECT такой же, как для стандартного SQL NATURAL JOIN, а результат EXCEPT такой же, как для некоторых идиом, включающих LEFT & RIGHT JOIN. Но это , а не , иллюстрирует, как (INNER) JOIN работает в целом. То, что просто на первый взгляд кажется правдоподобным . Он может идентифицировать частей ввода и / или вывода для особых случаев ON, PK (первичные ключи), FK (внешние ключи) и / или SELECT.Все, что вам нужно сделать, чтобы увидеть это, — это определить , что именно элементы наборов, представленных кружками . (Какие запутанные презентации никогда не проясняются.) Помните, что в целом для объединений выходные строки имеют разные заголовки от входных строк . И таблицы SQL — это пакетов, , а не , наборы строк с NULL .

CROSS JOIN — Часть 3 — {coding} Прицел

CROSS JOIN в центре внимания. Эта статья завершает нашу небольшую серию публикаций, связанных с SQL JOIN.Если вы пропустили две предыдущие статьи, обратитесь к ним следующим образом:

SQL Server CROSS JOIN — самое простое из всех соединений. Он реализует комбинацию 2 таблиц без условия соединения. Если у вас есть 5 строк в одной таблице и 3 строки в другой, вы получите 15 комбинаций. Другое определение — декартово произведение.

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

Синтаксис SQL CROSS JOIN

Как и в случае с INNER JOIN, у вас может быть CROSS JOIN из 2 стандартов, SQL-92 и SQL-89.T-SQL поддерживает оба синтаксиса, но я предпочитаю SQL-92. Прочтите часть 1, посвященную INNER JOIN, если вы хотите знать, почему.

Синтаксис SQL-92

  ВЫБРАТЬ
 a.column1
, b.column2
ИЗ Таблицы 1 а
CROSS JOIN Table2 b
  

SQL-89

  ВЫБРАТЬ
 a.column1
, b.column2
ИЗ Таблицы 1 a, Таблицы 2 b
  

Очень похоже на SQL-89 — INNER JOIN без условия соединения.

5 примеров использования SQL Server CROSS JOIN

Вы можете задаться вопросом, когда можно использовать SQL CROSS JOIN.Конечно, это полезно для формирования комбинаций значений. Что еще?

1. Данные испытаний

Если вам нужен большой объем данных, CROSS JOIN поможет вам. Например, у вас есть таблица поставщиков и продуктов. Другая таблица содержит продукты, предлагаемые продавцом. Если он пуст и вам нужны данные быстро, вот что вы можете сделать:

  ВЫБРАТЬ
 P.ProductID
, v.BusinessEntityID КАК VendorID
ИЗ
Производство.Продукт p
CROSS JOIN Purchasing.Vendor v
  

В моей копии AdventureWorks было создано 52 416 записей.Этого достаточно, чтобы протестировать приложения и производительность. Однако, если вы представляете свое приложение пользователям, используйте свой источник вместо данных из нашего примера.

2. Получение результатов от пропущенных комбинаций

В предыдущей статье мы проиллюстрировали использование OUTER JOIN для получения результатов из пропущенных значений. На этот раз мы воспользуемся недостающими комбинациями. Давайте попробуем найти товары, на которых Store 294 не приносил прибыли.

  - получить список магазинов 294 («Профессиональные продажи и обслуживание») без заказов на продажу за январь 2014 г.
ВЫБРАТЬ ОТЛИЧИТЕЛЬНЫЙ
 б.Название КАК Продукт
ИЗ Продаж. Магазин
CROSS JOIN Производство.Продукт b
LEFT JOIN (ВЫБРАТЬ
c.StoreID
, a.ProductID
, СУММ (a.LineTotal) КАК OrderTotal
ОТ Sales.SalesOrderПодробнее
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Sales.SalesOrderHeader b НА a.SalesOrderID = b.SalesOrderID
ВНУТРЕННИЕ ПРИСОЕДИНЯЙТЕСЬ к Sales.Customer c ON b.CustomerID = c.CustomerID
ГДЕ c.StoreID = 294 И
б. ДАТА ЗАКАЗА МЕЖДУ 01.01.2014 И 31.01.2014.
ГРУППА ПО c.StoreID, a.ProductID) d НА a.BusinessEntityID = d.StoreID
И б.ProductID = d.ProductID
ГДЕ d.OrderTotal ЕСТЬ NULL
И a.BusinessEntityID = 294
ЗАКАЗАТЬ ПО Б.ИМЯ
  

Как видите, нам сначала нужны все комбинации товаров и магазинов — мы используем CROSS JOIN. Затем нам понадобится список проданных товаров за январь 2014 года. Наконец, примените LEFT JOIN к этому списку и используйте предложение WHERE, чтобы получить только продукты без продаж. Затем мы получаем информацию о товарах, которые не были проданы.

3. Формирование слов из сочетаний букв

Если вам нравятся словесные игры с комбинациями букв, вы можете использовать CROSS JOIN с самосоединением.Вот пример, состоящий из трех букв «D», «O» и «G».

 
DECLARE @table TABLE (буква CHAR (1) NOT NULL)

ВСТАВИТЬ В @table
ЗНАЧЕНИЯ ('D'), ('O'), ('G')

ВЫБРАТЬ
 письмо
, б. буква
, c. письмо
ИЗ @table a
CROSS JOIN @table b
CROSS JOIN @table c
ГДЕ a.letter + b.letter + c.letter КАК '% O%'
  

Аналогичный код без предложения WHERE сгенерирует 27 записей. Вышеупомянутое предложение WHERE помогло исключить комбинации из трех похожих буквенных комбинаций, таких как «DDD» или «GGG».Ниже результат.

Рисунок 1. Результат использования CROSS JOIN для формирования комбинаций из 3 букв D, O и G.

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

4. Рекомендации по питанию

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

 
ОБЪЯВЛЕНИЕ ТАБЛИЦЫ @FoodMenu (FoodItem VARCHAR (50) NOT NULL, ItemType CHAR (1) NOT NULL)

- основное блюдо
ВСТАВИТЬ @FoodMenu
ЗНАЧЕНИЯ
('Спагетти с фрикадельками', 'М'),
('Спагетти с жареной курицей', 'М'),
(«Рис с жареным цыпленком», «М»)

-- Гарнир
ВСТАВИТЬ @FoodMenu
ЗНАЧЕНИЯ
('Кукуруза и морковь в масле', 'S'),
('Картофель фри', 'S'),
('Овощной салат из капусты', 'S')

- напитки
ВСТАВИТЬ @FoodMenu
ЗНАЧЕНИЯ
("Апельсиновый сок", "Д"),
("Ананасовый сок", "Д"),
('Сода', 'Д')

ВЫБРАТЬ
 а.FoodItem AS MainCourse
, b.FoodItem как гарнир
, c.FoodItem AS Напитки
ОТ @FoodMenu a
CROSS JOIN @FoodMenu b
CROSS JOIN @FoodMenu c
ГДЕ a.ItemType = 'M' И
b.ItemType = 'S' И
c.ItemType = 'D'
  

Результат:

Рисунок 2. Комбинации продуктов с использованием CROSS JOIN и самостоятельного соединения.

Некоторые из них желательны. Некоторые говорят: «Забудь!» Это зависит от твоего вкуса.

5. Выбор дизайна футболки

Еще одно возможное использование CROSS JOIN — получение комбинаций дизайна для рубашек.Вот пример кода:

 
ОБЪЯВЛЕНИЕ ТАБЛИЦЫ @tshirts (тип атрибута CHAR (1) NOT NULL, атрибут VARCHAR (15))

--размер
ВСТАВИТЬ @tshirts
ЗНАЧЕНИЯ
('S', 'Маленький'),
('S', 'Средний'),
('S', 'Большой')

--цвет
ВСТАВИТЬ @tshirts
ЗНАЧЕНИЯ
('C', 'Красный'),
('C', 'Синий'),
('C', 'зеленый'),
('C', 'Черный'),
('C', 'Purple'),
('C', 'желтый'),
('C', 'Белый')

--дизайн
ВСТАВИТЬ @tshirts
ЗНАЧЕНИЯ
('D', 'Обычный'),
('D', 'Отпечатано')

ВЫБРАТЬ
 А. Размер атрибута
, б. Атрибут AS Color
, c. Атрибут AS Design
ОТ @tshirts a
CROSS JOIN @tshirts b
CROSS JOIN @tshirts c
Где.attributeType = 'S' И
b.attributeType = 'C' И
c.attributeType = 'D'
  

А результаты? Взгляните на его часть на рисунке 3:

Рис. 3. Первые 20 записей о комбинациях цвета, размера и дизайна с использованием CROSS JOIN.

Вы можете придумать еще несколько примеров?

Производительность CROSS JOIN для SQL Server

В чем выгода использования CROSS JOIN? Как бы то ни было, CROSS JOIN может вызвать проблемы с производительностью, если вы не будете осторожны. Самая страшная часть — это продукт из 2-х наборов.Таким образом, без ограничения результатов в предложении WHERE, Table1 с 1000 записями CROSS JOIN с Table2 с 1 000 000 записей станет 1 000 000 000 записей. Следовательно, SQL Server должен прочитать много страниц.

В качестве примера рассмотрим объединение сотрудников мужского и женского пола в AdventureWorks .

  ИСПОЛЬЗОВАТЬ AdventureWorks
ИДТИ

ВЫБРАТЬ
 P.LastName + ISNULL ('' + p.Suffix, '') + ',' + P.FirstName + ISNULL ('' + P.MiddleName, '') AS Male
, P1.LastName + ISNULL ('' + p1.Suffix, '') + ',' + P1.FirstName + ISNULL ('' + P1.MiddleName, '') AS Female
ОТ HumanResources.Employee e
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Person.Person p ON e.BusinessEntityID = P.BusinessEntityID
CROSS JOIN HumanResources.Employee e1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Person.Person p1 ON e1.BusinessEntityID = p1.BusinessEntityID
ГДЕ e.Gender = 'M'
И e1.Gender = 'F'
  

Приведенный выше код дает вам все возможные пары сотрудников мужского и женского пола. У меня всего 17 304 записи, но посмотрите на логические чтения на рис. 4:

Рисунок 4.Высокое логическое чтение после объединения сотрудников мужского и женского пола с помощью CROSS JOIN.

Вы видели логические чтения таблицы Person ? Это составляет 53 268 x 8 КБ страниц! Не говоря уже о WorkTable , логические чтения выполняются в tempdb .

Вывод? Проверьте STATISTICS IO, и если вам не удается увидеть большие логические операции чтения, выразите запрос по-другому. Дополнительные условия в предложении WHERE или подход «разделяй и властвуй» могут помочь.

Когда CROSS JOIN становится ВНУТРЕННИМ СОЕДИНЕНИЕМ

Да, верно.SQL Server может обрабатывать CROSS JOIN как INNER JOIN. Ранее мы упоминали, что когда RIGHT JOIN обрабатывается как LEFT JOIN, оно может применяться к CROSS JOIN. Взгляните на код ниже:

  ВЫБРАТЬ
 c.CustomerID
, c.AccountNumber
, P.BusinessEntityID
, P.LastName
, P.FirstName
ОТ Продажи.Клиент c
CROSS JOIN Person.Person p
ГДЕ c.PersonID = P.BusinessEntityID
  

Прежде чем мы проверим план выполнения, давайте рассмотрим эквивалент INNER JOIN.

  ВЫБРАТЬ
 c.Пользовательский ИД
, c.AccountNumber
, P.BusinessEntityID
, P.LastName
, P.FirstName
ОТ Продажи.Клиент c
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Person.Person p ON c.PersonID = P.BusinessEntityID
  

Теперь проверьте план выполнения ниже.

Рисунок 5. Два (2) плана выполнения. Один использует запрос с использованием CROSS JOIN, а другой — INNER JOIN. Наборы результатов такие же, как и планы выполнения.

Верхний план — это запрос с использованием CROSS JOIN. Нижний план — это запрос, использующий ВНУТРЕННЕЕ СОЕДИНЕНИЕ. У них тот же QueryHashPlan .

Вы обратили внимание на оператор Hash Match верхнего плана? Это ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Но мы использовали CROSS JOIN в коде. Предложение WHERE в первом запросе ( WHERE c.PersonID = P.BusinessEntityID ) заставляло результаты иметь комбинации только с одинаковыми ключами. Итак, логически это ВНУТРЕННЕЕ СОЕДИНЕНИЕ.

Какой лучше? Это ваш личный выбор. Я предпочитаю INNER JOIN, потому что цель состоит в том, чтобы объединить 2 таблицы с одинаковыми ключами. Использование INNER JOIN делает это очень понятным.Но это только я.

Заключение

CROSS JOIN хорош для предоставления вам всех возможных комбинаций значений. Однако вас предупредили, что это может вызвать некоторый «взрыв» данных. Используйте это ПРИСОЕДИНЕНИЕ осторожно. Максимально ограничивайте результаты.