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

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

Authors.AuthorID	Authors. AuthorName	Books.BookID	Books.BookName
1	Bruce Eckel	3	Modern Operating System
1	Bruce Eckel	1	Thinking in Java
1	Bruce Eckel	3	Computer Architecture
1	Bruce Eckel	4	Programming in Scala
2	Robert Lafore	3	Modern Operating System
2	Robert Lafore	1	Thinking in Java
2	Robert Lafore	3	Computer Architecture
2	Robert Lafore	4	Programming in Scala
3	Andrew Tanenbaum	3	Modern Operating System
3	Andrew Tanenbaum	1	Thinking in Java
3	Andrew Tanenbaum	3	Computer Architecture
3	Andrew Tanenbaum	4	Programming in Scala

Функция CROSSJOIN (DAX) — DAX

• 
  07/05/2020
• Чтение занимает 2 мин

В этой статье

Возвращает таблицу, содержащую декартово произведение всех строк из всех таблиц в аргументах.Returns a table that contains the Cartesian product of all rows from all tables in the arguments. Столбцы в новой таблице являются всеми столбцами во всех таблицах аргументов.The columns in the new table are all the columns in all the argument tables.

СинтаксисSyntax

CROSSJOIN(<table>, <table>[, <table>]…)  

ПараметрыParameters

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

Таблица, содержащая декартово произведение всех строк из всех таблиц в аргументах. A table that contains the Cartesian product of all rows from all tables in the arguments.

RemarksRemarks

• Имена столбцов из аргументов table должны быть разными во всех таблицах, иначе возвращается ошибка.Column names from table arguments must all be different in all tables or an error is returned.

• Общее число строк, возвращаемых функцией CROSSJOIN(), равно произведению числа строк из всех таблиц в аргументах; кроме того, общее число столбцов в таблице результатов является суммой количества столбцов во всех таблицах.The total number of rows returned by CROSSJOIN() is equal to the product of the number of rows from all tables in the arguments; also, the total number of columns in the result table is the sum of the number of columns in all tables. Например, если TableA содержит rA строк и cA столбцов, TableB содержит rB строк и cB столбцов, а TableC содержит rC строк и cC столбцов, то результирующая таблица содержит rA × rB × rC строк и cA + cB + cC столбцов.For example, if TableA has rA rows and cA columns, and TableB has rB rows and cB columns, and TableC has rC rows and cC column; then, the resulting table has rA × rb × rC rows and cA + cB + cC columns.

• Эта функция не поддерживается для использования в режиме DirectQuery при использовании в вычисляемых столбцах или правилах безопасности на уровне строк (RLS).This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

ПримерExample

В следующем примере показаны результаты применения функции CROSSJOIN() к двум таблицам: Цвета и Бланк. The following example shows the results of applying CROSSJOIN() to two tables: Colors and Stationery.

Таблица Цвета содержит цвета и узоры:The table Colors contains colors and patterns:

В таблице Бланк содержатся шрифты и их стиль отображения:The table Stationery contains fonts and presentation:

Ниже представлено выражение для создания перекрестного объединения.The expression to generate the cross join is presented below:

CROSSJOIN( Colors, Stationery)  

Если выражение выше используется везде, где ожидается табличное выражение, результаты выражения будут выглядеть следующим образом:When the above expression is used wherever a table expression is expected, the results of the expression would be as follows:

Использование 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 может упростить их решение.

DAX функции GENERATE, GENERATEALL, CROSSJOIN в Power BI и Power Pivot

 Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В этой статье мы обсудим функции GENERATE, GENERATEALL и CROSSJOIN в Power BI и Power Pivot, которые способны в коде языка DAX создавать вычисляемые таблицы на основе пересечений строк других таблиц.

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

В Power Pivot (Excel) функции GENERATE, GENERATEALL и CROSSJOIN также работают, только таблицы они там создают виртуально, во время вычисления самой формулы.

Итак, переходим к разбору функций.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

Да, и еще один момент, до 26 марта 2021 г. у Вас имеется шикарная возможность приобрести большой, пошаговый видеокурс «DAX — это просто» с огромной скидкой 60%.

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

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 60% (до 26 марта 2021 г.): узнать подробнее

DAX функции GENERATE и GENERATEALL в Power BI и Power Pivot

GENERATE () и GENERATEALL () — создают таблицу из всех пересечений строк двух таблиц, входящих в параметры функций. То есть, для каждой строки первой таблицы возвращаются (по очереди вычисляются) все строки из второй.

Иногда, на просторах Интернета я встречаю, что некоторые пользователи несколько неверно прописывают имя этих функций, добавляя второе слово: generate table. Слово table здесь не нужно.

Синтаксис:

GENERATE ('Таблица 1'; 'Таблица 2')
GENERATEALL ('Таблица 1'; 'Таблица 2')

Где: Таблица — исходная таблица или табличное выражение

! — Названия столбцов в обеих таблицах должны быть разными

Обе функции GENERATE и GENERATEALL работают практически одинаково, за исключением того, как они обрабатывают пустую вторую таблицу.

Давайте сначала разберемся как они обе функционируют, а затем рассмотрим различия в их работе.

Примеры формул на основе DAX функций GENERATE и GENERATEALL.

В Power BI Desktop имеются 2 исходные таблицы «Год» и «Месяц»:

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

Календарь = GENERATE (‘Год’;’Месяц’)

Результатом выполнения этой формулы будет перечисление всех 3 месяцев по каждому году:

Итог будет абсолютно тем же самым, если вместо GENERATE мы пропишем DAX функцию GENERATEALL:

Календарь = GENERATEALL ('Год';'Месяц')

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

Рассмотрим пример, когда вторая таблица «Месяц» полностью пустая:

При тех же формулах:

Календарь = GENERATE ('Год';'Месяц')
Календарь = GENERATEALL ('Год';'Месяц')

В этот раз результат будет разным.

GENERATE — возвратит вообще пустую таблицу:

А DAX функция GENERATEALL возвратит таблицу, состоящую из двух столбцов, где будут прописаны года, а месяца останутся пустыми:

Рассмотрим еще одну DAX функцию, похожую на GENERATE и GENERATEALL, которая также возвращает пересечение всех строк, только уже не двух, а многих таблиц.

DAX функция CROSSJOIN в Power BI и Power Pivot

CROSSJOIN () — создает таблицу из всех пересечений строк всех таблиц, входящих в параметры функции.

Синтаксис:

CROSSJOIN ('Таблица 1'; 'Таблица 2'; 'Таблица N')

Где: Таблица — исходная таблица или табличное выражение

! — Названия столбцов во всех таблицах должны быть разными

Рассмотрим пример формулы на основе DAX функции CROSSJOIN.

В Power BI Desktop имеются 3 исходные таблицы «Год», «Месяц», «День»:

Напишем формулу календаря из пересечения всех строк года, месяца и дня на основе функции CROSSJOIN:

Календарь = CROSSJOIN ('Год'; 'Месяц'; 'День')

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

Так как все эти 3 функции GENERATE, GENERATEALL и CROSSJOIN в итоге своей работы возвращают таблицы, то всех их можно легко использовать в сочетании со многими другими функциями в DAX.

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

На этом, с функциями создания таблиц в Power BI и Power Pivot из пересечений строк, в этой статье, все.

Также, напоминаю Вам, что до 26 марта 2021 г. у Вас имеется шикарная возможность приобрести большой, пошаговый видеокурс «DAX — это просто» с огромной скидкой 60%.

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

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 60% (до 26 марта 2021 г.): узнать подробнее

Пожалуйста, оцените статью:

1. 5
2. 4
3. 3
4. 2
5. 1

(13 голосов, в среднем: 5 из 5 баллов)
 
 
Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»
 
 
 
 

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

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

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.

Полное соединение · Loginom Help

Перекрестное соединение (SQL-аналог: CROSS JOIN)

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

Пример:

Для примера возьмем две таблицы. Главная таблица — Персона, присоединяемая — Город.

Главная таблица:

Присоединяемая таблица:

Рисунок 1. Связь

Результирующая таблица:

Полное соединение (SQL-аналог: FULL JOIN)

При полном соединении (FULL JOIN) производится полное внешнее соединение двух наборов. В результирующий набор добавляются следующие записи:

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

При таком соединении необходимо сопоставление ключевых полей.

Пример:

Для примера возьмем две таблицы. Главная таблица — Персона, присоединяемая — Город.

Главная таблица:

Присоединяемая таблица:

Рисунок 2. Связь

Результирующая таблица:

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

При любом соединении (JOIN) в результирующий набор данных включаются ключевые поля только главной таблицы. Чтобы включить ключевые поля присоединяемой таблицы в результирующий набор данных, необходимо установить флаг Добавлять присоединяемые ключевые поля. Если флаг установлен, то результрующая таблица из предыдущего примера будет выглядеть следующим образом:

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 .

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

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

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

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

Если в предложении 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 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.

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

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

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

Код SQL:

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

или

Код SQL:

  ВЫБЕРИТЕ foods.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 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 (посмотреть все)

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

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

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

Что такое CROSS JOIN?

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

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

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

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

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

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

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

 ВЫБРАТЬ c.Color,
       s Размер
ОТ Цвет c
       КРЕСТОВОЕ СОЕДИНЕНИЕ Размер s 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 С 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. Сопоставьте вычисленное суммарное количество с отдельным списком.

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

 С 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.

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

 ВЫБЕРИТЕ P.LastName,
       E. Дата рождения
ОТ HumanResources.Сотрудник E
       CROSS JOIN Person.Человек P 

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

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

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

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

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

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

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

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

Вот план внутреннего стыка…

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

Функция CROSSJOIN (DAX) — DAX

• 05.07.2020
• 2 минуты на чтение

В этой статье

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

Синтаксис

  CROSSJOIN (<таблица>, <таблица> [, <таблица>]…)
  

Параметры

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

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

Замечания

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

• Общее количество строк, возвращаемых CROSSJOIN (), равно произведению количества строк из всех таблиц в аргументах; Кроме того, общее количество столбцов в таблице результатов является суммой количества столбцов во всех таблицах. Например, если TableA имеет строк rA и cA столбцов, а TableB имеет rB строк и cB столбцов, а TableC имеет rC строк и cC столбцов; тогда результирующая таблица имеет строк rA × rb × rC и столбцов cA + cB + cC .

• Эта функция не поддерживается для использования в режиме DirectQuery при использовании в вычисляемых столбцах или правилах безопасности на уровне строк (RLS).

Пример

В следующем примере показаны результаты применения CROSSJOIN () к двум таблицам: Colors и Stationery .

Таблица Цвета содержит цвета и узоры:

Таблица Канцелярские товары содержит шрифты и презентацию:

Выражение для генерации перекрестного соединения представлено ниже:

  CROSSJOIN (Цвета, канцелярские товары)
  

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

ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ SQL — javatpoint

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

ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ можно указать двумя способами:

1. Использование синтаксиса JOIN.
2. таблица в предложении FROM без использования предложения WHERE.

СИНТАКСИС перекрестного соединения SQL

ВЫБРАТЬ * ИЗ [ТАБЛИЦА1] ПЕРЕКРЕСТИТЬ СОЕДИНЕНИЕ [ТАБЛИЦА2]
ИЛИ ЖЕ
ВЫБРАТЬ * ИЗ [ТАБЛИЦА1], [ТАБЛИЦА2]

Возьмем для примера две таблицы,

Таблица1 — MatchScore

3

Таблица2 — Отделы

34 Заявление SQL:

34

ВЫБРАТЬ * ИЗ MatchScore ПЕРЕКРЕСТИТЬ ПРИСОЕДИНЯТЬСЯ к отделам

После выполнения этого запроса вы получите следующий результат:

9014 9014 9014 9014 9014 9014 9014 9014 9014 9014 9014 9014 9014 Франк 2

Oracle CROSS JOIN на практических примерах

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

Введение в Oracle

CROSS JOIN clause

В математике для двух наборов A и B декартово произведение A x B представляет собой набор всех упорядоченных пар (a, b), которые a принадлежит A и b принадлежит B .

Чтобы создать декартово произведение таблиц в Oracle, вы используете предложение CROSS JOIN . Ниже показан синтаксис предложения CROSS JOIN :

 
  SELECT
    column_list
ИЗ
    Т1
КРЕСТНОЕ СОЕДИНЕНИЕ T2;
  
 

В отличие от других соединений, таких как INNER JOIN или LEFT JOIN , CROSS JOIN не имеет предложения ON с предикатом соединения.

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

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

Пример Oracle Cross Join

См. Следующую таблицу инвентаризации в образце базы данных.

Каждая строка в таблице запасов требует данных для product_id , inventory_id и количества .

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

 
  SELECT
    идантификационный номер продукта,
    склад_id,
    ОКРУГЛ (dbms_random.value (10, 100)) количество
ИЗ
    товары
CROSS JOIN склады;
  
 

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

В этом примере перекрестное соединение сделало декартово product_id и inventory_id из продуктов и складов таблиц.Таблица товаров 288 строк и таблица складов имеет 9 строк, поэтому перекрестное соединение этих таблиц возвращает 2592 строки (288 x 9).

Обратите внимание, что мы использовали dbms_random. value () функция для получения случайного числа от 10 до 100 и функция ROUND () для получения целых значений из случайного числа.

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

• Было ли это руководство полезным?
• Да Нет

Иллюстрированное руководство по SQL CROSS JOIN

Что такое инструкция SQL CROSS JOIN? Когда его использовать? Когда нельзя его использовать? Этот пост расскажет вам, что вам нужно знать о CROSS JOIN.

Вы уже знаете, что с помощью оператора SQL JOIN можно объединить одну или несколько таблиц, совместно использующих совпадающую запись. И если вы читали статью LearnSQL «Изучение SQL-соединений с использованием реальных жизненных ситуаций», вы знаете, что существует много типов JOIN s.Какой из них вы выберете, будет зависеть от того, что вы от него хотите.

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

Что такое ПРИСОЕДИНЕНИЕ?

Во-первых, давайте рассмотрим, что делает JOIN в SQL: позволяет объединять данные (то есть записи) из нескольких таблиц. JOIN Операторы позволяют объединять записи определенными способами, например только те записи, которые совпадают в обеих таблицах.

Когда мы начнем изучать CROSS JOIN , мы начнем с двух таблиц: « цвет » и « футболка ».

Таблица « цвет », в которой хранятся названия доступных цветов футболок, выглядит так:

Таблица « tshirt », в которой хранятся размеры различных футболок, выглядит так:

Люди носят футболки всех сочетаний размеров и цветов.Итак, мы объединим данные из этих таблиц.

Что такое CROSS JOIN?

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

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

 ВЫБРАТЬ * ИЗ  футболка 
  CROSS JOIN цвет; 
 

Обратите внимание, что одна таблица указана после предложения FROM ; имя другой таблицы следует за CROSS JOIN Неважно, какая таблица указана после начала, а какая после CROSS JOIN .Результат будет тот же: все возможные комбинации записей из всех таблиц.

Вот результат этой операции:

Мы выбрали все записи из таблицы « tshirt » и присоединили к каждой записи все записи из таблицы « цвет ».Другими словами, у нас есть комбинация каждой футболки каждого размера и каждого цвета. График ниже наглядно объясняет это:

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

Есть другой способ получить запрос на возврат декартова произведения без использования CROSS JOIN :

 SELECT * FROM  футболка, цветная; 
 

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

Использование CROSS JOIN в умножении

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

«t1»

«t2»

Обе таблицы хранят одни и те же числа: 11, 12, 13. Наша цель — умножить эти числа сами на себя, создав своего рода таблицу умножения.Для этого мы можем использовать CROSS JOIN . Посмотрите на запрос ниже:

 ВЫБЕРИТЕ t1.n, t2.n, t1.n * t2.n КАК результат ИЗ  t1 
  CROSS JOIN t2; 
 

SELECT перечисляет три столбца: числа из первой таблицы (t1.n) , числа из второй таблицы (t2.n) и умножение, которое хранится в столбце результата (t1.n * t2.n AS результат) . В таблице ниже показан результат:

Использование CROSS JOIN со многими таблицами

Мы можем использовать более двух таблиц в CROSS JOIN .Представьте себе, что компания производит футболки трех размеров (S, M, L) и из двух тканей (хлопок и лен). Каждая футболка доступна в двух цветах: розовом или синем. Взгляните на следующие таблицы:

«цвет»

«футболка»

«ткань»

Теперь предположим, что вы хотите увидеть все возможные футболки: все комбинации размера, цвета и ткани. Приведенный ниже запрос извлекает эту информацию с помощью CROSS JOIN на трех таблицах:

 Футболка SELECT.размер как размер, название цвета как цвет, название ткани как ткань
ОТ футболки
CROSS JOIN ткань
CROSS JOIN цвет;
 

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

Есть двенадцать возможных футболок.Умножение двух цветов на три размера и снова на две ткани дает 12.

Узнать больше

Об использовании CROSS JOIN нужно знать больше, чем мы рассмотрели в этом иллюстрированном введении. Если вы хотите узнать больше о JOIN s, посетите курс LearnSQL по основам SQL.

.