CROSS JOIN против INNER JOIN в SQL Server 2008
В чем разница между CROSS JOIN а также INNER JOIN?
CROSS JOIN:
SELECT
Movies.CustomerID, Movies.Movie, Customers.Age,
Customers.Gender, Customers.[Education Level],
Customers.[Internet Connection], Customers.[Marital Status],
FROM
Customers
CROSS JOIN
Movies
ВНУТРЕННЕЕ СОЕДИНЕНИЕ:
SELECT
Movies.CustomerID, Movies.Movie, Customers.Age,
Customers.Gender, Customers.[Education Level],
Customers.[Internet Connection], Customers.[Marital Status]
FROM
Customers
INNER JOIN
Movies ON Customers.CustomerID = Movies.CustomerID
Какой из них лучше, и почему я должен использовать любой из них?
9 ответов
Перекрестное объединение не объединяет строки, если в каждой таблице 100 строк с соответствием 1 к 1, вы получите 10.000 результатов, Innerjoin вернет только 100 строк в той же ситуации.
Эти 2 примера вернут один и тот же результат:
Перекрестное соединение
select * from table1 cross join table2 where table1.id = table2.fk_id
Внутреннее соединение
select * from table1 join table2 on table1.id = table2.fk_id
Используйте последний метод
Вот лучший пример Cross Join и Inner Join.
Рассмотрим следующие таблицы
ТАБЛИЦА:Teacher
x------------------------x
| TchrId | TeacherName |
x----------|-------------x
| T1 | Mary |
| T2 | Jim |
x------------------------x
ТАБЛИЦА:Student
x--------------------------------------x
| StudId | TchrId | StudentName |
x----------|-------------|-------------x
| S1 | T1 | Vineeth |
| S2 | T1 | Unni |
x--------------------------------------x
1. ВНУТРЕННЕЕ СОЕДИНЕНИЕ
Внутреннее соединение выбирает строки, которые удовлетворяют обеим таблицам.
Представьте, что нам нужно найти учителей, которые являются классными учителями, и их соответствующих учеников. В этом состоянии мы должны применить JOIN или же INNER JOIN и воля
запрос
SELECT T.TchrId,T.TeacherName,S.StudentName
FROM #Teacher T
INNER JOIN #Student S ON T.TchrId = S.TchrId
Результат
x--------------------------------------x
| TchrId | TeacherName | StudentName |
x----------|-------------|-------------x
| T1 | Mary | Vineeth |
| T1 | Mary | Unni |
x--------------------------------------x
2. CROSS JOIN
Перекрестное соединение выбирает все строки из первой таблицы и все строки из второй таблицы и отображается как декартово произведение, т. Е. Со всеми возможностями.
Учтите, что нам нужно найти всех учителей в школе и учеников независимо от классных учителей, нам нужно подать заявку CROSS JOIN,
запрос
SELECT T.TchrId,T.TeacherName,S.StudentName
FROM #Teacher T
CROSS JOIN #Student S
Результат
x--------------------------------------x
| TchrId | TeacherName | StudentName |
x----------|-------------|-------------x
| T2 | Jim | Vineeth |
| T2 | Jim | Unni |
| T1 | Mary | Vineeth |
| T1 | Mary | Unni |
x--------------------------------------x
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 с использованием запятых для их разделения. Таким образом, он определяет перекрестное соединение
ВНЕШНЕЕ СОЕДИНЕНИЕ И ВКЛ против ГДЕ в них см. Условия в СЛЕДУЮЩЕМ СОЕДИНЕНИИ (НАРУЖНОЕ СОЕДИНЕНИЕ) против ВНУТРЕННЕГО СОЕДИНЕНИЯ.
Зачем сравнивать столбцы между таблицами?
Каждая таблица содержит строки, которые составляют истинный оператор из определенного шаблона оператора fill-in-[named-]blank. (Это делает истинноепредложение из - удовлетворяет - определенный(характерный) предикат.)
Базовая таблица содержит строки, которые делают истинный оператор из некоторого заданного 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 MON & 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 CROSS JOIN Movies M ON C.CustomerID = M.CustomerID AND C.Age >= M.[Minimum Age] WHERE C.Age = 18
В частности, сравнение столбцов на равенство между таблицами означает, что строки, хранящиеся в продукте из частей шаблона объединенных таблиц, имеют одинаковое значение для этих столбцов. Просто случайно, что многие строки обычно удаляются сравнением равенства между таблицами - что необходимо и достаточно, чтобы охарактеризовать нужные вам строки.
Просто напишите 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 JOIN. Конечно, выражение должно прояснить его значение; но то, что ясно, зависит от того, что вещи означают.
Диаграммы Ре Венна Диаграмма Венна с двумя пересекающимися кругами может иллюстрировать разницу междувыходными строками для INNER, LEFT, RIGHT & FULL JOIN для одного и того же входа. И когда ВКЛ безоговорочно TRUE, результат INNER JOIN совпадает с CROSS JOIN. Также он может иллюстрировать строки ввода и вывода для INTERSECT, UNION и EXCEPT. И когда оба входа имеют одинаковые столбцы, результат INTERSECT совпадает со стандартным SQL NATURAL JOIN. Но это не иллюстрирует, как (INNER) JOIN работает в целом. На первый взгляд это кажется правдоподобным. Он может идентифицировать некоторые части ввода и / или вывода для особых случаев ON, PK (первичные ключи), FK (внешние ключи) и / или SELECT. Все, что вам нужно сделать, чтобы увидеть это, - это определить, какие именно существуют ограничения и какие именно элементы наборов представлены кружками. (Какие в этих особых случаях детали никогда не разъясняются.) (Помните, что обычно выходные строки имеют разные заголовки от входных строк.)
Внутреннее соединение
Объединение, в котором отображаются только строки, совпадающие в обеих объединенных таблицах, называется внутренним объединением. Это объединение по умолчанию в запросе и просмотр конструктора.
Синтаксис для внутреннего соединения
SELECT t1.column_name,t2.column_name
FROM table_name1 t1
INNER JOIN table_name2 t2
ON t1.column_name=t2.column_name
Перекрестное соединение
Перекрестное соединение, которое производит декартово произведение таблиц, которые участвуют в объединении. Размер декартового произведения - это число строк в первой таблице, умноженное на количество строк во второй таблице.
Синтаксис для перекрестного соединения
SELECT * FROM table_name1
CROSS JOIN table_name2
Или мы можем написать это по-другому также
SELECT * FROM table_name1,table_name2
Теперь проверьте запрос ниже для перекрестного соединения
пример
SELECT * FROM UserDetails
CROSS JOIN OrderDetails
Или же
SELECT * FROM UserDetails, OrderDetails
КРЕСТНОЕ СОЕДИНЕНИЕ
Как я объяснил в этой статье, CROSS JOIN предназначен для создания декартова произведения.
Декартово произведение берет два набора A и B и генерирует все возможные перестановки парных записей из двух заданных наборов данных.
Например, если у вас есть следующие ranks а также suits таблицы базы данных:
И ranks имеет следующие строки:
| name | symbol | rank_value |
|-------|--------|------------|
| Ace | A | 14 |
| King | K | 13 |
| Queen | Q | 12 |
| Jack | J | 11 |
| Ten | 10 | 10 |
| Nine | 9 | 9 |
В то время как suits таблица содержит следующие записи:
| name | symbol |
|---------|--------|
| Club | ♣ |
| Diamond | ♦ |
| Heart | ♥ |
| Spade | ♠ |
Как запрос CROSS JOIN, подобный следующему:
SELECT
r.symbol AS card_rank,
s.symbol AS card_suit
FROM
ranks r
CROSS JOIN
suits s
сгенерирует все возможные перестановки ranks а также suites пары:
| card_rank | card_suit |
|-----------|-----------|
| A | ♣ |
| A | ♦ |
| A | ♥ |
| A | ♠ |
| K | ♣ |
| K | ♦ |
| K | ♥ |
| K | ♠ |
| Q | ♣ |
| Q | ♦ |
| Q | ♥ |
| Q | ♠ |
| J | ♣ |
| J | ♦ |
| J | ♥ |
| J | ♠ |
| 10 | ♣ |
| 10 | ♦ |
| 10 | ♥ |
| 10 | ♠ |
| 9 | ♣ |
| 9 | ♦ |
| 9 | ♥ |
| 9 | ♠ |
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
С другой стороны, INNER JOIN не возвращает декартово произведение двух соединяющихся наборов данных.
Вместо этого INNER JOIN берет все элементы из левой таблицы и сопоставляет их с записями в правой таблице, чтобы:
- если в правой таблице не найдено совпадений, левая строка отфильтровывается из набора результатов
- для любой совпадающей записи в правой таблице левая строка повторяется, как если бы между этой записью и всеми связанными с ней дочерними записями в правой таблице было декартово произведение.
Например, предполагая, что у нас есть связь таблицы один-ко-многим между родительскимpost и ребенок post_comment таблицы, которые выглядят следующим образом:
Теперь, если post В таблице есть следующие записи:
| id | title |
|----|-----------|
| 1 | Java |
| 2 | Hibernate |
| 3 | JPA |
и post_comments таблица имеет эти строки:
| id | review | post_id |
|----|-----------|---------|
| 1 | Good | 1 |
| 2 | Excellent | 1 |
| 3 | Awesome | 2 |
Запрос INNER JOIN, подобный следующему:
SELECT
p.id AS post_id,
p.title AS post_title,
pc.review AS review
FROM post p
INNER JOIN post_comment pc ON pc.post_id = p.id
будет включать все post записи вместе со всеми связанными с ними post_comments:
| post_id | post_title | review |
|---------|------------|-----------|
| 1 | Java | Good |
| 1 | Java | Excellent |
| 2 | Hibernate | Awesome |
В принципе, вы можете думать о INNER JOIN как о фильтрованном CROSS JOIN, где в окончательном наборе результатов хранятся только соответствующие записи.
Чтобы узнать больше о том, как работает INNER JOIN, ознакомьтесь также с этой статьей.
Помните, что если добавлено предложение WHERE, перекрестное соединение ведет себя как внутреннее соединение. Например, следующие запросы Transact-SQL дают одинаковый набор результатов. Пожалуйста, обратитесь к http://technet.microsoft.com/en-us/library/ms190690(v=sql.105).aspx
SQL Server также принимает более простую запись:
SELECT A.F,
B.G,
C.H
FROM TABLE_A A,
TABLE_B B,
TABLE_C C
WHERE A.X = B.X
AND B.Y = C.Y
Используя эту более простую нотацию, не нужно беспокоиться о разнице между внутренними и перекрестными соединениями. Вместо двух предложений "ON" есть одно предложение "WHERE", которое выполняет эту работу. Если у вас возникнут трудности с определением, куда и куда идут предложения "JOIN", "ON", оставьте нотацию "JOIN" и используйте более простую приведенную выше.
Это не обман.
При написании запросов с использованием внутренних объединений записи будут извлекаться из обеих таблиц, если условие выполняется в обеих таблицах, т.е. точное совпадение общего столбца в обеих таблицах.
При написании запроса с использованием кросс-соединения результат выглядит как декартово произведение числа записей в обеих таблицах. Например, если table1 содержит 2 записи, а table2 содержит 3 записи, результат запроса будет 2*3 = 6 записей.
Так что не переходите на перекрестное соединение, пока вам это не понадобится.
Внутреннее соединение даст результат совпавших записей между двумя таблицами, где в качестве перекрестного соединения вы можете найти возможные комбинации между двумя таблицами.
Перекрестное и внутреннее объединение одинаковы с той лишь разницей, что при внутреннем объединении мы логически фильтруем некоторые результаты декартового произведения.
table1
x--------------------------------------x
| fieldA | fieldB | fieldC |
x----------|-------------|-------------x
| A | B | option1 |
| A | B1 | option2 |
x--------------------------------------x
table2
x--------------------------------------x
| fieldA | fieldB | fieldC |
x----------|-------------|-------------x
| A | B | optionB1 |
| A1 | B1 | optionB2 |
x--------------------------------------x
cross join
A,B,option1,A,B,optionB1
A,B,option1,A1,B1,optionB2
A,B1,option2,A,B,optionB1
A,B1,option2,A1,B1,optionB2
inner join on field1 (only with the value is the same in both tables)
A,B,option1,A,B,optionB1
A,B1,option2,A,B,optionB1
inner join on field1
A,B,option1,A,B,optionB1
Именно на основе наших данных мы решаем, что существует только один случай поля, которое мы используем для объединения. Объединять только перекрестные объединения обеих таблиц и получать только строки, выполняющие специальное логическое выражение.
Обратите внимание, что если поля, в которых мы выполняем наши объединения, будут нулевыми в обеих таблицах, мы пропустим фильтр. Это зависит от нас или производителя базы данных, чтобы добавить дополнительные правила, чтобы избежать или разрешить нулевые значения. Придерживаясь основ, это просто перекрестное соединение, за которым следует фильтр.
Это зависит от ожидаемого результата.
Перекрестное соединение сопоставляет все строки в одной таблице со всеми строками в другой таблице. Внутреннее соединение соответствует полю или полям. Если у вас есть одна таблица с 10 строками, а другая с 10 строками, то два соединения будут вести себя по-разному.
В результате перекрестного соединения будет возвращено 100 строк, и они не будут связаны друг с другом, это будет то, что называется декартовым произведением. Внутреннее соединение будет сопоставлять записи друг с другом. Предполагая, что у одного есть первичный ключ, а в другом это внешний ключ, вы получите 10 строк.
Перекрестное соединение имеет ограниченную общую полезность, но существует для полноты и описывает результат соединения таблиц без каких-либо отношений, добавленных к запросу. Вы можете использовать перекрестное соединение для составления списков комбинаций слов или чего-то подобного. С другой стороны, внутреннее соединение является наиболее распространенным соединением.
SQL CROSS JOIN создает результирующий набор, который представляет собой число строк в первой таблице, умноженное на количество строк во второй таблице, если вместе с CROSS JOIN не используется предложение WHERE. Этот вид результата называется декартовым продуктом.
Если предложение WHERE используется с CROSS JOIN, оно работает как INNER JOIN.
A = {1,5,3,4,6,7,9,8} B = {2,8,5,4,3,6,9}
cross join действует как декартово произведение A ✖ B = {1,2}, {1,8}...,{5,2}, {5,8},{5,5}.....{3,3}...,{6,6}....{8,9} и вернул этот длинный набор результатов..
при обработке inner join это делается через декартово произведение и выбирает совпадающие пары... если подумайте, что эти упорядоченные пары как первичные ключи двух таблиц и поиск по предложению A = B, тогда inner join выберите {5,5}, {4,4}, {6,6}, {9,9} и вернул запрашиваемый столбец в предложении выбора, относящемся к этим идентификаторам.
если cross join на a = b, то результат тот же результат, что и inner join. в этом случае также используйтеinner join.