SQL
ПРИСОЕДИНИТЬСЯ

Вступление

Синтаксис

• [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } ] JOIN

замечания

Основное явное внутреннее соединение

Базовое соединение (также называемое «внутреннее соединение») запрашивает данные из двух таблиц с их отношением, определенным в предложении join .

В следующем примере будут выбраны имена сотрудников (FName) из таблицы Employees и название отдела, в котором они работают (имя) из таблицы Departments:

SELECT Employees.FName, Departments.Name
FROM   Employees
JOIN   Departments
ON Employees.DepartmentId = Departments.Id

Это вернет из базы данных примера следующее :

Неявное присоединение

Присоединяется также может быть выполнено при наличии нескольких таблиц в from пункта, разделенных запятыми , и определения отношений между ними в where п. Этот метод называется Implicit Join (поскольку он фактически не содержит предложение join ).

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

• Можно получить случайные кросс-соединения, которые затем возвращают неверные результаты, особенно если у вас много запросов в запросе.
• Если вы намеревались перекрестное соединение, то это не ясно из синтаксиса (вместо этого выпишите CROSS JOIN), и кто-то, вероятно, изменит его во время обслуживания.

В следующем примере будут отобраны имена сотрудников и имена отделов, в которых они работают:

SELECT e.FName, d.Name
FROM   Employee e, Departments d
WHERE  e.DeptartmentId = d.Id

Это вернет из базы данных примера следующее :

Левая внешняя связь

Левая внешняя регистрация (также известная как «Левая регистрация» или «Внешняя связь») - это объединение, которое обеспечивает отображение всех строк из левой таблицы; если не существует подходящей строки из правой таблицы, соответствующие поля - NULL .

В следующем примере будут отобраны все отделы и первое имя сотрудников, работающих в этом отделе. Отделы без сотрудников все еще возвращаются в результатах, но будут иметь NULL для имени сотрудника:

SELECT          Departments.Name, Employees.FName
FROM            Departments 
LEFT OUTER JOIN Employees 
ON              Departments.Id = Employees.DepartmentId

Это вернет из базы данных примера следующее :

Так как же это работает?

В предложении FROM есть две таблицы:

а также

Сначала из двух таблиц создается декартово произведение, дающее промежуточную таблицу.
Записи, соответствующие критериям соединения ( Departments.Id = Employees.DepartmentId ) выделены жирным шрифтом; они передаются на следующий этап запроса.

Поскольку это LEFT OUTER JOIN, все записи возвращаются со стороны LEFT соединения (Departments), в то время как любые записи на стороне RIGHT имеют маркер NULL, если они не соответствуют критериям соединения. В приведенной ниже таблице возвращается Tech с NULL

Наконец, каждое выражение, используемое в предложении SELECT , вычисляется для возврата нашей итоговой таблицы:

Self Join

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

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

SELECT 
    e.FName AS "Employee", 
    m.FName AS "Manager"
FROM   
    Employees e
JOIN   
    Employees m 
    ON e.ManagerId = m.Id

Этот запрос вернет следующие данные:

Так как же это работает?

Исходная таблица содержит следующие записи:

Первое действие - создать декартово произведение всех записей в таблицах, используемых в предложении FROM . В этом случае это таблица Employees дважды, поэтому промежуточная таблица будет выглядеть так (я удалил все поля, не используемые в этом примере):

Следующее действие заключается только в том, чтобы сохранить записи, соответствующие критериям JOIN , поэтому любые записи, где aliased e table ManagerId равен Id таблицы aliased m :

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

Наконец, имена столбцов e.FName и m.FName заменяются именами их псевдонимов, назначенными оператору AS :

ПЕРЕКРЕСТНЫЙ ПРИСОЕДИНЕНИЕ

Cross join делает декартово произведение двух членов. Декартово произведение означает, что каждая строка из одной таблицы объединяется с каждой строкой второй таблицы в соединении. Например, если TABLEA имеет 20 строк и TABLEB имеет 20 строк, результатом будет 20*20 = 400 строк вывода.

Использование примерной базы данных

SELECT d.Name, e.FName
FROM   Departments d
CROSS JOIN Employees e;

Что возвращает:

Рекомендуется написать явный CROSS JOIN, если вы хотите сделать декартовое соединение, чтобы подчеркнуть, что это то, что вы хотите.

Присоединение к подзапросу

Объединение подзапроса часто используется, когда вы хотите получить агрегированные данные из таблицы child / details и отображать их вместе с записями из таблицы parent / header. Например, вы можете захотеть получить количество дочерних записей, среднее число числовых столбцов в дочерних записях или верхнюю или нижнюю строку на основе поля даты или числа. В этом примере используются псевдонимы, которые, возможно, упрощают чтение запросов при использовании нескольких таблиц. Вот как выглядит довольно типичное подзапрос. В этом случае мы извлекаем все строки из родительской таблицы «Заказы на поставку» и извлекаем только первую строку для каждой родительской записи дочерней таблицы PurchaseOrderLineItems.

SELECT po.Id, po.PODate, po.VendorName, po.Status, item.ItemNo, 
  item.Description, item.Cost, item.Price
FROM PurchaseOrders po
LEFT JOIN 
     (
       SELECT l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price, Min(l.id) as Id 
       FROM PurchaseOrderLineItems l
       GROUP BY l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price
     ) AS item ON item.PurchaseOrderId = po.Id

КРЕСТ ПРИМЕНЯЕТСЯ И ПОСЛЕДНЕЕ СОЕДИНЕНИЕ

Очень интересным типом JOIN является LATERAL JOIN (новый в PostgreSQL 9.3+),
который также известен как CROSS APPLY / OUTER APPLY в SQL-Server & Oracle.

Основная идея заключается в том, что для каждой присоединяемой строки применяется табличная функция (или встроенный подзапрос).

Это позволяет, например, присоединить только первую совпадающую запись в другой таблице.
Разница между нормальным и боковым соединением заключается в том, что вы можете использовать столбец, который вы ранее вложили в подзапрос, который вы «CROSS APPLY».

Синтаксис:

PostgreSQL 9.3+

левый | право | внутреннее соединение ЛАТЕРАЛЬНОГО

SQL-сервер:

CROSS | ВНЕШНИЕ ПРИМЕНЕНИЯ

INNER JOIN LATERAL - это то же самое, что и CROSS APPLY
и LEFT JOIN LATERAL - это то же самое, что и OUTER APPLY

Пример использования (PostgreSQL 9.3+):

SELECT * FROM T_Contacts 

--LEFT JOIN T_MAP_Contacts_Ref_OrganisationalUnit ON MAP_CTCOU_CT_UID = T_Contacts.CT_UID AND MAP_CTCOU_SoftDeleteStatus = 1 
--WHERE T_MAP_Contacts_Ref_OrganisationalUnit.MAP_CTCOU_UID IS NULL -- 989


LEFT JOIN LATERAL 
(
    SELECT 
         --MAP_CTCOU_UID    
         MAP_CTCOU_CT_UID   
        ,MAP_CTCOU_COU_UID  
        ,MAP_CTCOU_DateFrom 
        ,MAP_CTCOU_DateTo   
   FROM T_MAP_Contacts_Ref_OrganisationalUnit 
   WHERE MAP_CTCOU_SoftDeleteStatus = 1 
   AND MAP_CTCOU_CT_UID = T_Contacts.CT_UID 

    /*  
    AND 
    ( 
        (__in_DateFrom <= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateTo) 
        AND 
        (__in_DateTo >= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateFrom) 
    ) 
    */
   ORDER BY MAP_CTCOU_DateFrom 
   LIMIT 1 
) AS FirstOE 

И для SQL-Server

SELECT * FROM T_Contacts 

--LEFT JOIN T_MAP_Contacts_Ref_OrganisationalUnit ON MAP_CTCOU_CT_UID = T_Contacts.CT_UID AND MAP_CTCOU_SoftDeleteStatus = 1 
--WHERE T_MAP_Contacts_Ref_OrganisationalUnit.MAP_CTCOU_UID IS NULL -- 989

-- CROSS APPLY -- = INNER JOIN 
OUTER APPLY    -- = LEFT JOIN 
(
    SELECT TOP 1 
         --MAP_CTCOU_UID    
         MAP_CTCOU_CT_UID   
        ,MAP_CTCOU_COU_UID  
        ,MAP_CTCOU_DateFrom 
        ,MAP_CTCOU_DateTo   
   FROM T_MAP_Contacts_Ref_OrganisationalUnit 
   WHERE MAP_CTCOU_SoftDeleteStatus = 1 
   AND MAP_CTCOU_CT_UID = T_Contacts.CT_UID 

    /*  
    AND 
    ( 
        (@in_DateFrom <= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateTo) 
        AND 
        (@in_DateTo >= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateFrom) 
    ) 
    */
   ORDER BY MAP_CTCOU_DateFrom 
) AS FirstOE 

ПОЛНОЕ СОЕДИНЕНИЕ

Один тип JOIN, который менее известен, - это ПОЛНЫЙ ПРИСОЕДИНЕНИЕ.
(Примечание: FULL JOIN не поддерживается MySQL в соответствии с 2016)

FULL OUTER JOIN возвращает все строки из левой таблицы и все строки из правой таблицы.

Если в левой таблице есть строки, которые не имеют совпадений в правой таблице, или если в правой таблице есть строки, которые не имеют совпадений в левой таблице, то эти строки также будут перечислены.

Пример 1:

SELECT * FROM Table1

FULL JOIN Table2 
     ON 1 = 2 

Пример 2:

SELECT 
     COALESCE(T_Budget.Year, tYear.Year) AS RPT_BudgetInYear 
    ,COALESCE(T_Budget.Value, 0.0) AS RPT_Value 
FROM T_Budget 

FULL JOIN tfu_RPT_All_CreateYearInterval(@budget_year_from, @budget_year_to) AS tYear 
      ON tYear.Year = T_Budget.Year 

Обратите внимание: если вы используете soft-delete, вам нужно будет снова проверить статус soft-delete в предложении WHERE (потому что FULL JOIN ведет себя вроде как UNION);
Легко упустить этот маленький факт, поскольку вы добавляете AP_SoftDeleteStatus = 1 в предложение join.

Кроме того, если вы выполняете ПОЛНЫЙ ПРИСОЕДИНЯЙТЕСЬ, вам обычно нужно разрешить NULL в предложении WHERE; забыв позволить NULL по значению, будет иметь те же эффекты, что и INNER join, что вам не нужно, если вы выполняете ПОЛНЫЙ JOIN.

Пример:

SELECT 
     T_AccountPlan.AP_UID
    ,T_AccountPlan.AP_Code
    ,T_AccountPlan.AP_Lang_EN
    ,T_BudgetPositions.BUP_Budget
    ,T_BudgetPositions.BUP_UID 
    ,T_BudgetPositions.BUP_Jahr
FROM T_BudgetPositions    

FULL JOIN T_AccountPlan
    ON T_AccountPlan.AP_UID = T_BudgetPositions.BUP_AP_UID 
    AND T_AccountPlan.AP_SoftDeleteStatus = 1 

WHERE (1=1) 
AND (T_BudgetPositions.BUP_SoftDeleteStatus = 1 OR T_BudgetPositions.BUP_SoftDeleteStatus IS NULL) 
AND (T_AccountPlan.AP_SoftDeleteStatus = 1 OR T_AccountPlan.AP_SoftDeleteStatus IS NULL) 

Рекурсивные СОБЫТИЯ

Рекурсивные объединения часто используются для получения данных родитель-ребенка. В SQL они реализованы с использованием рекурсивных общих табличных выражений , например:

WITH RECURSIVE MyDescendants AS (
    SELECT Name
    FROM People
    WHERE Name = 'John Doe'

    UNION ALL

    SELECT People.Name
    FROM People
    JOIN MyDescendants ON People.Name = MyDescendants.Parent
)
SELECT * FROM MyDescendants;

Различия между внутренними / внешними соединениями

SQL имеет различные типы соединений, чтобы указать, включены ли в результат (не) совпадающие строки: INNER JOIN , LEFT OUTER JOIN , RIGHT OUTER JOIN и FULL OUTER JOIN (ключевые слова INNER и OUTER являются необязательными). На следующем рисунке показаны различия между этими типами объединений: синяя область представляет результаты, возвращаемые соединением, а белая область представляет результаты, которые соединение не будет возвращено.

Перекрестное знакомство с символической презентацией SQL ( ссылка ):

Ниже приведены примеры из этого ответа.

Например, существуют две таблицы:

A    B
-    -
1    3
2    4
3    5
4    6

Заметим, что (1,2) уникальны для A, (3,4) являются общими, и (5,6) являются единственными для B.

Внутреннее соединение

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

select * from a INNER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Левое внешнее соединение

Левое внешнее соединение даст все строки в A плюс любые общие строки в B:

select * from a LEFT OUTER JOIN b on a.a = b.b;

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Правое внешнее соединение

Точно так же правое внешнее соединение даст все строки в B плюс любые общие строки в A:

select * from a RIGHT OUTER JOIN b on a.a = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Полное внешнее соединение

Полное внешнее соединение даст вам объединение A и B, т. Е. Все строки в A и все строки в B. Если что-то в A не имеет соответствующей базы данных в B, то B-часть имеет значение NULL и наоборот.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

JOIN Терминология: Внутренняя, Наружная, Полу, Анти ...

Допустим, у нас есть две таблицы (A и B), и некоторые из их строк соответствуют (относительно заданного условия JOIN, что бы это ни было в конкретном случае):

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

В приведенных ниже примерах используются следующие тестовые данные:

CREATE TABLE A (
    X varchar(255) PRIMARY KEY
);

CREATE TABLE B (
    Y varchar(255) PRIMARY KEY
);

INSERT INTO A VALUES
    ('Amy'),
    ('John'),
    ('Lisa'),
    ('Marco'),
    ('Phil');

INSERT INTO B VALUES
    ('Lisa'),
    ('Marco'),
    ('Phil'),
    ('Tim'),
    ('Vincent');

Внутреннее соединение

Объединяет левую и правую строки, которые соответствуют.

SELECT * FROM A JOIN B ON X = Y;

X      Y
------ -----
Lisa   Lisa
Marco  Marco
Phil   Phil

Левая внешняя связь

Иногда сокращается до «left join». Объединяет левую и правую строки, которые соответствуют, и включает несогласованные левые строки.

SELECT * FROM A LEFT JOIN B ON X = Y;

X      Y
-----  -----
Amy    NULL
John   NULL
Lisa   Lisa
Marco  Marco
Phil   Phil

Правостороннее соединение

Иногда сокращается «правое соединение». Объединяет левую и правую строки, которые соответствуют, и включает несогласованные правые строки.

SELECT * FROM A RIGHT JOIN B ON X = Y;

X      Y
-----  -------
Lisa   Lisa
Marco  Marco
Phil   Phil
NULL   Tim
NULL   Vincent

Полная внешняя связь

Иногда сокращается до «полного соединения». Союз левого и правого внешнего соединения.

SELECT * FROM A FULL JOIN B ON X = Y;

X      Y
-----  -------
Amy    NULL
John   NULL
Lisa   Lisa
Marco  Marco
Phil   Phil
NULL   Tim
NULL   Vincent

Левая полуось

Включает левые строки, соответствующие строкам справа.

SELECT * FROM A WHERE X IN (SELECT Y FROM B);

X
-----
Lisa
Marco
Phil

Правая полупрофессионал

Включает правые строки, которые соответствуют левым строкам.

SELECT * FROM B WHERE Y IN (SELECT X FROM A);

Y
-----
Lisa
Marco
Phil

Как вы можете видеть, нет специального синтаксиса IN для левого и правого полусоединения - мы достигаем эффекта просто путем переключения позиций таблицы в тексте SQL.

Left Anti Semi Join

Включает левые строки, которые не соответствуют строкам справа.

SELECT * FROM A WHERE X NOT IN (SELECT Y FROM B);

X
----
Amy
John

ПРЕДУПРЕЖДЕНИЕ. Будьте осторожны, если вы используете NOT IN в столбце NULL! Подробнее здесь .

Right Anti Semi Join

Включает правые строки, которые не соответствуют левым строкам.

SELECT * FROM B WHERE Y NOT IN (SELECT X FROM A);

Y
-------
Tim
Vincent

Как вы можете видеть, нет специального синтаксиса NOT IN для левого и правого анти-полусоединения - мы достигаем эффекта просто путем переключения позиций таблицы в тексте SQL.

Крест

Декартово произведение всех левых со всеми правыми строками.

SELECT * FROM A CROSS JOIN B;

X      Y
-----  -------
Amy    Lisa
John   Lisa
Lisa   Lisa
Marco  Lisa
Phil   Lisa
Amy    Marco
John   Marco
Lisa   Marco
Marco  Marco
Phil   Marco
Amy    Phil
John   Phil
Lisa   Phil
Marco  Phil
Phil   Phil
Amy    Tim
John   Tim
Lisa   Tim
Marco  Tim
Phil   Tim
Amy    Vincent
John   Vincent
Lisa   Vincent
Marco  Vincent
Phil   Vincent

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

SELECT * FROM A JOIN B ON 1 = 1;

Автообъединение

Это просто означает, что таблица соединяется сама с собой. Самосоединением может быть любой из типов соединений, рассмотренных выше. Например, это внутреннее самосоединение:

SELECT * FROM A A1 JOIN A A2 ON LEN(A1.X) < LEN(A2.X);

X     X
----  -----
Amy   John
Amy   Lisa
Amy   Marco
John  Marco
Lisa  Marco
Phil  Marco
Amy   Phil