3.2.1. Перекрестное соединение
Существуют несколько разновидностей соединения, которым соответствуют определенные ключевые слова, добавляемые к слову JOIN. Так, например, декартово произведение является операцией перекрестного соединения. В SQL-выражении для обозначения этой операции используется оператор CROSS JOIN. Результат перекрестного соединения принципиально не отличается от перечисления источников через запятую.
Пусть в базе данных имеются следующие две таблицы:
Сотрудники (Номер_сотрудника, Фамилия, Имя, Номер_отдела);
Отделы (Номер_отдела, Название).
Общим столбцом для этих таблиц является Номер_отдела. Декартово произведение этих таблиц получается с помощью следующих эквивалентных запросов:
FROM Сотрудники CROSS JOIN Отделы;
FROM Сотрудники, Отделы;
На следующих рисунках показаны примеры таблиц Сотрудники и Отделы, а также результат их декартового произведения.
Сотрудники. Номер_ отдела
Отделы. Номер_ отдела
Синтаксис CROSS JOIN всего лишь более формально выражает отношение между двумя наборами данных. Между синтаксисом CROSS JOIN и простым перечислением таблиц через запятую нет никаких функциональных различий.
В основе любого соединения наборов записей лежит операция их декартового произведения.
3.2.2. Естественное соединение
В полученном декартовом произведении интерес представляют не все записи, а только те, в которых идентичные столбцы имеют одинаковые значения (Сотрудники.Номер_отдела = Отделы.Номер_отдела). Следовательно, в команде выборки с перекрестным соединением практически всегда должна присутствовать секция WHERE, уточняющая связи между объединенными наборами данных. Кроме того, в результатной таблице не нужны оба идентичных столбца, достаточно лишь одного из них. Такая таблица и будет естественным соединением таблиц Сотрудники и Отделы. Она получается с помощью следующего запроса:
Перекрестное соединение vs внутреннее соединение в SQL Server 2008
какой из них лучше и почему я должен использовать один?
10 ответов
Cross join не объединяет строки, Если у вас есть 100 строк в каждой таблице с 1 до 1 совпадения, вы получите 10.000 результатов, Innerjoin вернет только 100 строк в той же ситуации.
эти 2 примера вернут тот же результат:
используйте последний метод
вот лучший пример перекрестного соединения и внутреннего соединения.
рассмотрим следующие таблицы
стол : Teacher
стол : Student
1. ВНУТРЕННЕЕ СОЕДИНЕНИЕ
внутреннее соединение выбирает строки, удовлетворяющие таблице.
Считайте, нам нужно найти учителей, классных руководителей и их учеников. В том условие, мы должны применить JOIN или INNER JOIN и
запрос
- SQL FIDDLE
результат
2. CROSS JOIN
Cross join выбирает все строки из первой таблицы и все строки из второй таблицы и показывает как Декартовое произведение ie, со всеми возможности
Считайте, что нам нужно найти всех учителей в школе и учеников независимо от классных учителей, нам нужно подать заявку CROSS JOIN .
запрос
- SQL FIDDLE
результат
CROSS JOIN = (INNER) JOIN = запятая («,»)
TL; DR единственное различие между SQL CROSS JOIN, (INNER) JOIN и запятой ( » ,») (кроме запятой, имеющей более низкий приоритет для порядка оценки) заключается в том, что (INNER) JOIN имеет ON, а CROSS JOIN и запятая-нет.
промежуточные продукты Re
все три производят промежуточный концептуальный реляционный «Декартовый» «перекрестный» продукт SQL-стиля возможные комбинации строк из каждой таблицы. Он включен и / или где это уменьшает количество строк. SQL Fiddle
стандарт SQL определяет через продукт (7.5 1.b.ii), через (7.7 1.a) и присоединиться к через плюс где (7.7 1.си.)
как говорит Википедия:
перекрестное соединение
CROSS JOIN возвращает Декартовое произведение строк из таблицы в соединении. Другими словами, он будет создавать строки, которые объединяют каждую строку из первой таблицы с каждой строкой из второй таблицы.
внутреннее соединение
[. ] Результат соединения может быть определен как результат первого принятия Декартового произведения (или перекрестного соединения) всех записей в таблицах (объединения каждой записи в таблице A с каждой записью в таблице B) , а затем возврата всех записей, которые удовлетворяют предикат соединения.
«неявная нотация соединения» просто перечисляет таблицы для соединения в предложении FROM инструкции SELECT, используя запятые для их разделения. Таким образом, он указывает перекрестное соединение
зачем сравнивать столбцы между таблицами?
каждый таблица содержит строки, которые делают оператор true из определенного шаблона инструкции fill-in-the-[named-]blanks. (Это делает true предложение С—отвечает—определенный (характеристика) предикат.)
базовая таблица содержит строки, которые делают оператор true из некоторого шаблона оператора DBA:
промежуточный продукт соединения содержит строки, которые делают оператор true из шаблонов и его операндов:
ON & WHERE условия ANDed в, чтобы дать дополнительный шаблон. Значение снова является строками, которые удовлетворяют этому шаблону:
в частности, сравнение столбцов для равенства между таблицами означает, что строки, сохраненные из продукта из частей соединенных таблиц шаблона, имеют одинаковое значение для этих столбцов. Это просто совпадение, что много строк обычно удалено путем сравнения равенства между таблицами-что необходимо и достаточно, чтобы охарактеризовать строки, которые вы хотите.
просто напишите SQL для шаблона для строк, которые вы хотите!
перегрузка «cross join»
к сожалению, термин «cross join» используется для:
- промежуточный продукт.
- ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ.
- (внутренний) присоединиться к ON или где это не сравнивает столбцы из одной таблицы с любыми столбцами другого. (Поскольку это имеет тенденцию возвращать так много строк промежуточного продукта.)
использование CROSS JOIN vs (INNER) JOIN против запятая
- используйте перекрестное соединение, когда и только когда вы не сравниваете столбцы между таблицами. Это должно показать, что отсутствие сравнений было преднамеренным.
- использовать (внутреннее) соединение с ON, когда и только когда вы сравниваете столбцы между таблицами (плюс, возможно, другие условия.)
- не используйте запятую.
обычно также условия не на парах таблиц хранятся для WHERE. Но они может потребоваться ввести (N внутреннее) соединение, чтобы получить соответствующие строки для аргумента в правом, левом или полном (внешнем) соединении.
эти различные значения смешиваются. (Например, как и в других ответах и комментариях здесь.)
Re «не использовать запятую» смешивание запятой с explict JOIN может ввести в заблуждение, потому что запятая имеет более низкий приоритет. Но учитывая роль промежуточного продукта в значении перекрестного соединения, (внутреннего) соединения и запятой, аргументы для конвенции выше не использование его вообще шатко. Перекрестное соединение или запятая — это как (внутреннее) соединение, которое находится в истинном состоянии. Промежуточный продукт, на и где все вводят и в соответствующем предикате. Однако еще можно подумать о внутреннем соединении-скажем, генерировании выходной строки только при нахождении пары входных строк, удовлетворяющих условию ON-тем не менее он возвращает строки перекрестного соединения, удовлетворяющие условию. Единственная причина на had дополнить запятую в SQL было написать внешний присоединяется. Конечно, выражение должно прояснить свой смысл, но то, что ясно, зависит от того, что подразумевается.
повторное диаграммы Венна диаграмма Венна с двумя пересекающимися кругами может проиллюстрировать разницу между вывод строк для внутренних, левых, правых и полных соединений для одного и того же входа. И при unconditionaly правда, внутреннее соединение, результат такой же, как перекрестное соединение. Также он может проиллюстрировать вход & выходные строки для INTERSECT, UNION & EXCEPT. И когда оба входа имеют одинаковые столбцы, результат пересечения совпадает со стандартным естественным соединением SQL. Но это не так!—21—>не проиллюстрируйте, как (внутреннее) соединение работает в целом. Вот только на первый взгляд кажется правдоподобным. Он может идентифицировать некоторые части ввода и/или вывода для особых случаях ON, PKS (первичные ключи), FKs (внешние ключи) и/или выберите. Все, что вам нужно сделать, чтобы увидеть это идентифицировать каковы именно ограничения и каковы именно элементы множеств, представленных кругами. (Что в этих особых случаях никогда не проясняется.) (Помните, что вообще выходные строки имеют разные заголовки от входных строк.)
Введение в соединения
Соединение (JOIN) — одна из самых важных операций, выполняемых реляционными системами управления базами данных (РСУБД). РСУБД используют соединения для того, чтобы сопоставить строки одной таблицы строкам другой таблицы. Например, соединения можно использовать для сопоставления продаж — клиентам или книг — авторам. Без соединений, имелись бы раздельные списки продаж и клиентов или книг и авторов, но невозможно было бы определить, какие клиенты что купили, или какой из авторов был заказан.
Можно соединить две таблицы явно, перечислив обе таблицы в предложении FROM запроса. Также можно соединить две таблицы, используя для этого всё разнообразие подзапросов. Наконец, SQL Server во время оптимизации может добавить соединение в план запроса, преследуя свои цели.
Это первая из серии статей, которые я планирую посвятить соединениям. Эту статью я собираюсь посвятить азам соединений, описав назначение логических операторов соединениё, поддерживаемых SQL Server. Вот они:
Для иллюстрации каждого соединения я буду использовать простую схему и набор данных:
Внутренние соединения
Внутренние соединения — самый распространённый тип соединений. Внутреннее соединение просто находит пары строк, которые соединяются и удовлетворяют предикату соединения. Например, показанный ниже запрос использует предикат соединения «S.Cust_Id = C.Cust_Id», позволяющий найти все продажи и сведения о клиенте с одинаковыми значениями Cust_Id:
Примечания:
Cust_Id = 3 купил два наименования, поэтому он фигурирует в двух строках результирующего набора.
Cust_Id = 1 не купил ничто и потому не появляется в результате.
Для Cust_Id = 4 тоже был продан товар, но поскольку в таблице нет такого клиента, сведения о такой продаже не появились в результате.
Внутренние соединения полностью коммутативны. «A inner join B» и «B inner join A» эквивалентны.
Внешние соединения
Предположим, что мы хотели бы увидеть список всех продаж; даже тех, которые не имеют соответствующих им записей о клиенте. Можно составить запрос с внешним соединением, которое покажет все строки в одной или обеих соединяемых таблицах, даже если не будет существовать соответствующих предикату соединения строку. Например:
Обратите внимание, что сервер возвращает вместо данных о клиенте значение NULL, поскольку для проданного товара ‘Printer’ нет соответствующей записи клиента. Обратите внимание на последнюю строку, у которой отсутствующие значения заполнены значением NULL.
Используя полное внешнее соединение, можно найти всех клиентов (независимо от того, покупали ли они что-нибудь), и все продажи (независимо от того, сопоставлен ли им имеющийся клиент):
Следующая таблица показывает, строки какой из соединяемых таблиц попадут в результирующий набор (у оставшейся таблицы возможны замены NULL), она охватывает все типы внешних соединений:
A left outer join B
A right outer join B
A full outer join B
Все строки A и B
Полные внешние соединения коммутативны. Кроме того, «A left outer join B » и «B right outer join A» является эквивалентным.
Перекрестные соединения
Перекрестное соединение выполняет полное Декартово произведение двух таблиц. То есть это соответствие каждой строки одной таблицы — каждой строке другой таблицы. Для перекрестного соединения нельзя определить предикат соединения, используя для этого предложение ON, хотя для достижения практически того же результата, что и с внутренним соединением, можно использовать предложение WHERE.
Перекрестные соединения используются довольно редко. Никогда не стоит пересекать две большие таблицы, поскольку это задействует очень дорогие операции и получится очень большой результирующий набор.
CROSS APPLY
В SQL Server 2005 мы добавили оператор CROSS APPLY, с помощью которого можно соединять таблицу с возвращающей табличное значение функцией (table valued function — TVF), причём TVF будет иметь параметр, который будет изменяться для каждой строки. Например, представленный ниже запрос возвратит тот же результат, что и показанное ранее внутреннее соединение, но с использованием TVF и CROSS APPLY:
Также можно использовать внешнее обращение — OUTER APPLY, позволяющее нам найти всех клиентов независимо от того, купили ли они что-нибудь или нет. Это будет похоже на внешнее соединение.
Полусоединение и анти-полусоединение
Полусоединение — semi-join возвращает строки только одной из соединяемых таблиц, без выполнения соединения полностью. Анти-полусоединение возвращает те строки таблицы, которые не годятся для соединения с другой таблицей; т.е. они в обычном внешнем соединении выдавали бы NULL.
В отличие от других операторов соединений, не существует явного синтаксиса для указания исполнения полусоединения, но SQL Server, в целом ряде случаев, использует в плане исполнения именно полусоединения. Например, полусоединение может использоваться в плане подзапроса с EXISTS:
В отличие от предыдущих примеров, полусоединение возвращает только данные о клиентах.
В плане запроса видно, что SQL Server действительно использует полусоединение:
|—Nested Loops(Left Semi Join, WHERE:([S].[Cust_Id]=[C].[Cust_Id]))
|—Table Scan(OBJECT:([Customers] AS [C]))
|—Table Scan(OBJECT:([Sales] AS [S]))
Существуют левые и правые полусоединения. Левое полусоединение возвращает строки левой (первой) таблицы, которые соответствуют строкам из правой (второй) таблицы, в то время как правое полусоединение возвращает строки из правой таблицы, которые соответствуют строкам из левой таблицы.
Подобным образом может использоваться анти-полусоединение для обработки подзапроса с NOT EXISTS.
Дополнение
Во всех представленных в статье примерах использовались предикаты соединения, который сравнивали, являются ли оба столбца каждой из соединяемых таблицы равными. Такой тип предикатов соединений принято называть «соединением по эквивалентности». Другие предикаты соединений (например, неравенства) тоже возможны, но соединения по эквивалентности распространены наиболее широко. В SQL Server заложено много альтернативных вариантов оптимизации соединений по эквивалентности и оптимизации соединений с более сложными предикатами.
SQL Server более гибок в выборе порядка соединения и его алгоритма при оптимизации внутренних соединений, чем при оптимизации внешних соединений и CROSS APPLY. Таким образом, если взять два запроса, которые отличаются только тем, что один использует исключительно внутренние соединения, а другой использует внешние соединения и/или CROSS APPLY, SQL Server сможет найти лучший план исполнения для запроса, который использует только внутренние соединения.
Артём Санников
Данная книга является руководством для начинающих специалистов в области анализа и обработки данных. В книге рассматривается язык SQL и его процедурное расширение PL/SQL от компании Oracle.
Перекрёстное соединение (CROSS JOIN) в SQL
Оператор CROSS JOIN в наиболее простом виде, без условия WHERE представляет собой – декартово произведение. Результатом такого соединения будет объединение каждой строки первой таблицы с каждой строкой второй таблицы.
Таблицы могут быть записаны в запросе либо через оператор CROSS JOIN , либо через запятую между ними.
Синтаксис CROSS JOIN
Практический пример
Произвести полное перекрёстное объединение таблиц Users и Sales , при помощи оператора CROSS JOIN .
Исходная таблица: Users
Исходная таблица: Sales
Результирующая таблица
В результирующей таблице, будет представлено декартово произведение двух таблиц Users и Sales , то есть каждая строка из таблицы Users , будет сопоставлена с каждой строкой второй таблицы Sales .