Время прочтения: 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 может упростить их решение.