Cross join sql: Оператор SQL CROSS JOIN: примеры, синтаксис
Содержание
Оператор SQL CROSS JOIN: примеры, синтаксис
Оператор SQL CROSS JOIN формирует таблицу перекрестным соединением (декартовым произведением) двух таблиц. При использовании оператора SQL CROSS JOIN каждая строка левой таблицы сцепляется с каждой строкой правой таблицы. В результате получается таблица со всеми возможными сочетаниями строк обеих таблиц.
Оператор SQL CROSS JOIN имеет следующий синтаксис:
SELECT
column_names [,... n]
FROM
Table_1 CROSS JOIN Table_2
Обратите внимание, что в операторе за ненадобностью отсутствует условие (ON).
Примеры оператора SQL CROSS JOIN. Имеются две таблицы:
Authors — содержит в себе информацию об авторах книг:
| AuthorID | AuthorName |
| 1 | Bruce Eckel |
| 2 | Robert Lafore |
| 3 | Andrew Tanenbaum |
Books — содержит в себе информацию о названии книг:
| BookID | BookName |
| 3 | Modern Operating System |
| 1 | Thinking in Java |
| 3 | Computer Architecture |
| 4 | Programming in Scala |
В таблице 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 |
Использование 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;
lname as c_lname
, CASE
WHEN underwriter.lname_id = client.lname_id
OR underwriter.lname_stem = client.lname_stem
THEN 1
ELSE 0
END as is_equal_lnames
FROM (-- Андеррайтер "Петрова Екатерина Павловна"
SELECT fh.person_id, fh.fio_id, ln.lname_id, ln.lname
, regexp_replace(upper(ln.lname), '(А|ИЙ|АЯ)$', '')) as lname_stem
FROM fio_history fh
INNER JOIN fio f ON fh.fio_id = f.fio_id
INNER JOIN lname ln ON f.lname_id = ln.lname_id
WHERE fh.person_id = 1) underwriter
CROSS JOIN (-- Клиент "Сидоров Степан Павлович"
SELECT fh.person_id, fh.fio_id, ln.lname_id, ln.lname
, regexp_replace(upper(ln.lname), '(А|ИЙ|АЯ)$', '')) as lname_stem
FROM fio_history fh
INNER JOIN fio f ON fh.fio_id = f.
fio_id
INNER JOIN lname ln ON f.lname_id = ln.lname_id
WHERE fh.person_id = 5) client) results
WHERE results.is_equal_lnames = 1;
В результате было найдено одно пересечение, что может указывать на родственные связи по Фамилии.
Данный пример является достаточно простым в проверке, т.к. родственные связи находятся в пределах одного поколения.
Но, что если андеррайтер не должен обрабатывать заявки не только своих братьев, сестер, родителей и детей, но также членов семей братьев и сестер своих супругов.
В данной ситуации подход остается тот же, увеличивается только лишь количество проверяемых субъектов.
Пусть будет ситуация аналогичная предыдущей: клиент Иванов Алексей Николаевич оформляет заявку на кредит, которая впоследствии распределяется на андеррайтера Петров Юрий Александрович.
На первый взгляд, очевидно, что люди не являются родственниками.
Нам необходимо проверить всех ближайших родственников клиента со всеми ближайшими родственниками андеррайтера (предполагается, что родственные связи записаны в других справочниках; для простоты представления примера идентификаторы person_id указаны явным образом).
SELECT fh.fio_history_id
, fh.fio_id
, fh.person_id
, ln.lname, fn.fname, mn.mname
, fh.actual
FROM fio_history fh
INNER JOIN fio f ON fh.fio_id = f.fio_id
INNER JOIN lname ln ON f.lname_id = ln.lname_id
INNER JOIN fname fn ON f.fname_id = fn.fname_id
INNER JOIN mname mn ON f.mname_id = mn.mname_id
WHERE fh.person_id IN (1, 2, 3, 4)
ORDER BY actual DESC;
Чтобы обнаружить родственные связи между двумя разными семьями необходимо выполнить рекурсивное декартова перемножение множеств имен каждого из супругов.
WITH people_info AS (
SELECT person_id
, fio_id
, NULL as relative_person_id
, NULL as relative_fio_id
FROM fio_history
UNION ALL
SELECT pinf.person_id
, pinf.fio_id
, fh.person_id as other_person_id
, fh.fio_id as other_fio_id
FROM fio_history fh
CROSS JOIN people_info pinf
WHERE pinf.relative_person_id IS NULL
)
SELECT results.
*
FROM (SELECT underwriter.person_id as u_person_id
, underwriter.fio_id as u_fio_id
, underwriter.relative_person_id as u_relative_person_id
, underwriter.relative_fio_id as u_relative_fio_id
, underwriter.lname as u_lname
, client.person_id as c_person_id
, client.fio_id as c_fio_id
, client.relative_person_id as c_relative_person_id
, client.relative_fio_id as c_relative_fio_id
, client.lname as c_lname
, CASE
WHEN underwriter.lname_id = client.lname_id
OR underwriter.lname_stem = client.lname_stem
THEN 1
ELSE 0
END as is_equal_lnames
FROM (-- Андеррайтер "Петров Юрий Александрович"
SELECT pinf.person_id, pinf.fio_id
, pinf.relative_person_id, pinf.relative_fio_id, ln.lname_id, ln.lname
, regexp_replace(upper(ln.
lname), '(А|ИЙ|АЯ)$', '')) as lname_stem
FROM people_info pinf
INNER JOIN fio f ON pinf.relative_fio_id = f.fio_id
INNER JOIN lname ln ON f.lname_id = ln.lname_id
WHERE pinf.relative_person_id IS NOT NULL
AND pinf.person_id IN (4)
AND pinf.relative_person_id IN (1, 4)) underwriter
CROSS JOIN (-- Клиент "Иванов Алексей Николаевич"
SELECT pinf.person_id, pinf.fio_id
, pinf.relative_person_id, pinf.relative_fio_id, ln.lname_id, ln.lname
, regexp_replace(upper(ln.lname), '(А|ИЙ|АЯ)$', '')) as lname_stem
FROM people_info pinf
INNER JOIN fio f ON pinf.relative_fio_id = f.fio_id
INNER JOIN lname ln ON f.lname_id = ln.lname_id
WHERE pinf.relative_person_id IS NOT NULL
AND pinf.person_id IN (3)
AND pinf.
relative_person_id IN (2, 3)) client) results
WHERE results.is_equal_lnames = 1;
Таким образом, родственные связи клиента и андеррайтера были обнаружены между их женами, которые являются родными сестрами.
Задачи, где может потребоваться проверка исторических данных, могут быть самыми разнообразными, и во многих случаях CROSS JOIN может упростить их решение.
SQL Server Cross Join иллюстрируется практическими примерами
Резюме : в этом руководстве вы узнаете, как использовать SQL Server CROSS JOIN для объединения двух или более несвязанных таблиц.
Ниже показан синтаксис SQL Server CROSS JOIN двух таблиц:
Язык кода: SQL (язык структурированных запросов) (sql)
SELECT select_list ОТ Т1 ПОПЕРЕЧНОЕ СОЕДИНЕНИЕ T2;
CROSS JOIN соединяет каждую строку из первой таблицы (T1) с каждой строкой из второй таблицы (T2).
Другими словами, перекрестное соединение возвращает декартово произведение строк из обеих таблиц.
В отличие от INNER JOIN или LEFT JOIN , перекрестное соединение не устанавливает связи между соединяемыми таблицами.
Предположим, что таблица T1 содержит три строки 1, 2 и 3, а таблица T2 содержит три строки A, B и C.
CROSS JOIN получает строку из первой таблицы (T1), а затем создает новая строка для каждой строки во второй таблице (T2). Затем он делает то же самое для следующей строки в первой таблице (T1) и так далее.
На этом рисунке CROSS JOIN создает всего девять строк. В общем, если в первой таблице n строк, а во второй таблице m строк, результатом перекрестного соединения будет n x m строк.
Следующий оператор возвращает комбинации всех продуктов и магазинов. Набор результатов можно использовать для процедуры инвентаризации при закрытии месяца и года:
Язык кода: SQL (язык структурированных запросов) (sql)
SELECT Код товара, наименование товара, store_id, 0 АС количество ОТ производство.
продукция
ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ
СОРТИРОВАТЬ ПО
наименование товара,
store_id;
Вот частичный вывод:
Следующий оператор находит товары, которые не продаются в магазинах:
Язык кода: SQL (язык структурированных запросов) (sql)
SELECT s.store_id, p.product_id, ISNULL(продажи, 0) продажи ОТ продажи.магазины с ПОПЕРЕЧНОЕ СОЕДИНЕНИЕ производство.продукция p ЛЕВОЕ СОЕДИНЕНИЕ ( ВЫБРАТЬ s.store_id, p.product_id, СУММА (количество * i.list_price) продаж ОТ продажи.заказы o ВНУТРЕННЕЕ СОЕДИНЕНИЕ sales.order_items i ON i.order_id = o.order_id ВНУТРЕННЕЕ СОЕДИНЕНИЕ sales.stores s ON s.store_id = o.store_id ВНУТРЕННЕЕ СОЕДИНЕНИЕ production.products p ON p.product_id = i.product_id ГРУППА ПО s.
store_id,
p.product_id
) c ON c.store_id = s.store_id
И c.product_id = p.product_id
КУДА
продажи НЕ НУЛЕВЫЕ
СОРТИРОВАТЬ ПО
Код товара,
store_id;
На следующем рисунке показан частичный набор результатов:
В этом руководстве вы узнали, как использовать SQL Server CROSS JOIN для создания декартовых произведений. строк из соединяемых таблиц.
SQL CROSS JOIN — javatpoint
следующий →
SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1 CROSS JOIN TableName2 ON TableName1.ColumnName = TableName2.ColumnName; Теперь давайте более подробно рассмотрим перекрестное соединение в SQL с помощью примеров. Все запросы в примерах будут написаны с использованием базы данных MySQL. Допустим, у нас есть следующие таблицы с заданными данными: Таблица 1: MatchScore
Таблица 2: Отделы
Таблица 3: сотрудник
Таблица 4: отдел
Таблица 5: кредит
Таблица 6: заемщик
Таблица 7: клиент
Таблица 8: заказы
Пример 1:Напишите запрос для выполнения операции перекрестного объединения, рассматривая таблицу MatchScore как левую таблицу, а таблицу Departments как правую. Запрос: SELECT * FROM MatchScore CROSS JOIN Departments; Мы использовали команду SELECT со звездочкой, чтобы получить все столбцы, присутствующие в таблице MatchScore и Departments. Затем мы использовали ключевое слово CROSS JOIN для выполнения операции перекрестного соединения в таблице MatchScore и Departments. Так как в таблице MatchScore 4 записи, а в таблице Departments 3 записи, после выполнения операции перекрестного соединения мы получим 12 строк. После выполнения этого запроса вы найдете следующий результат:
Каждая строка таблицы MatchScore объединяется с каждой строкой таблицы Departments. Пример 2:Напишите запрос для выполнения операции перекрестного соединения, рассматривая таблицу сотрудников как левую таблицу, а таблицу отделов как правую. Запрос: mysql> ВЫБРАТЬ *FROM сотрудника CROSS JOIN отдела; Мы использовали команду SELECT со звездочкой для получения всех столбцов, присутствующих в таблице сотрудников и отделов. Затем мы использовали ключевое слово CROSS JOIN для выполнения операции перекрестного соединения в таблице сотрудников и отделов. Так как в таблице сотрудников пять записей, а в таблице отделов четыре записи, после выполнения операции перекрестного соединения мы получим 20 строк. После выполнения этого запроса вы найдете следующий результат:
Каждая строка таблицы сотрудников объединяется с каждой строкой таблицы отдела. Пример 3: Напишите запрос для выполнения операции перекрестного соединения, рассматривая таблицу ссуд как левую таблицу, а таблицу заемщиков как правую. Запрос: mysql> ВЫБЕРИТЕ * ИЗ кредита CROSS JOIN заемщик; Мы использовали команду SELECT со звездочкой для извлечения всех столбцов, присутствующих в таблице займа и заемщика. Затем мы использовали ключевое слово CROSS JOIN для выполнения операции перекрестного соединения ссуды и таблицы заемщика. Так как в таблице кредитов четыре записи, а в таблице заемщиков четыре записи, после выполнения операции перекрестного соединения мы получим 16 строк. После выполнения этого запроса вы найдете следующий результат:
Каждая строка из таблицы займов объединяется с каждой строкой таблицы заемщиков. Пример 4:Напишите запрос для выполнения операции перекрестного соединения, считая таблицу клиентов левой и таблицу заказов правой. Запрос: mysql> SELECT *FROM заказ CROSS JOIN клиента; Мы использовали команду SELECT со звездочкой для извлечения всех столбцов, присутствующих в таблице клиентов и заказов. Затем мы использовали ключевое слово CROSS JOIN для выполнения операции перекрестного соединения для таблицы клиентов и таблицы заказов. Так как в таблице кредитов три записи, а в таблице заказов три записи, после выполнения операции перекрестного соединения мы получим 9ряды. После выполнения этого запроса вы найдете следующий результат:
|
Поскольку в таблице MatchScore четыре записи, а в таблице Departments три записи, после выполнения операции перекрестного соединения мы получили 12 строк в итоговой таблице.
Поскольку в таблице сотрудников пять записей, а в таблице отделов четыре записи, после выполнения операции перекрестного соединения мы получили 20 строк в итоговой таблице.
Поскольку в таблице займов четыре записи, а в таблице заемщиков четыре записи, после выполнения операции перекрестного соединения мы получили 16 строк.
