SQL
JOINDRE

Introduction

Syntaxe

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

Remarques

Jointure interne explicite de base

Une jointure de base (également appelée "jointure interne") interroge les données de deux tables, leur relation étant définie dans une clause de join .

L'exemple suivant sélectionne les prénoms des employés (FName) dans la table Employees et le nom du département pour lequel ils travaillent (Nom) dans la table Departments:

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

Cela renverrait les éléments suivants de la base de données exemple :

Jointure implicite

Les jointures peuvent également être effectuées en ayant plusieurs tables dans la clause from , séparées par des virgules , et en définissant la relation entre elles dans la clause where . Cette technique s'appelle une jointure implicite (car elle ne contient pas de clause de join ).

Tous les SGBDR le supportent, mais la syntaxe est généralement déconseillée. Les raisons pour lesquelles il est déconseillé d'utiliser cette syntaxe sont les suivantes:

• Il est possible d'obtenir des jointures croisées accidentelles qui renvoient des résultats incorrects, surtout si vous avez beaucoup de jointures dans la requête.
• Si vous envisagez une jointure croisée, la syntaxe n'est pas claire (écrivez plutôt CROSS JOIN), et quelqu'un est susceptible de le modifier lors de la maintenance.

L'exemple suivant sélectionne les prénoms des employés et le nom des départements pour lesquels ils travaillent:

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

Cela renverrait les éléments suivants de la base de données exemple :

Jointure externe gauche

Une jointure externe gauche (également appelée jointure gauche ou jointure externe) est une jointure qui garantit que toutes les lignes de la table de gauche sont représentées. Si aucune ligne correspondante de la table de droite n'existe, ses champs correspondants sont NULL .

L'exemple suivant sélectionne tous les départements et le prénom des employés qui travaillent dans ce service. Les départements sans employés sont toujours renvoyés dans les résultats, mais auront NULL pour le nom de l'employé:

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

Cela renverrait les éléments suivants de la base de données exemple :

Alors, comment ça marche?

Il y a deux tables dans la clause FROM:

et

Tout d'abord, un produit cartésien est créé à partir des deux tables donnant une table intermédiaire.
Les enregistrements qui répondent aux critères de jointure ( Departments.Id = Employees.DepartmentId ) sont surlignés en gras. ceux-ci sont passés à l'étape suivante de la requête.

Comme il s'agit d'une jointure externe LEFT, tous les enregistrements sont renvoyés du côté gauche de la jointure (Departments), tandis que tous les enregistrements du côté DROIT reçoivent un marqueur NULL s'ils ne correspondent pas aux critères de jointure. Dans le tableau ci-dessous, vous verrez Tech avec NULL

Enfin, chaque expression utilisée dans la clause SELECT est évaluée pour renvoyer notre table finale:

Self Join

Une table peut être jointe à elle-même, avec différentes lignes correspondant à une condition. Dans ce cas d'utilisation, des alias doivent être utilisés pour distinguer les deux occurrences de la table.

Dans l'exemple ci-dessous, pour chaque employé dans la table Employees de la base de données exemple , un enregistrement contenant le prénom de l'employé et le prénom correspondant du responsable de l'employé est renvoyé. Les managers étant également des employés, la table est jointe à elle-même:

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

Cette requête renvoie les données suivantes:

Alors, comment ça marche?

La table d'origine contient ces enregistrements:

La première action consiste à créer un produit cartésien de tous les enregistrements des tables utilisées dans la clause FROM . Dans ce cas, c'est la table Employees à deux reprises, de sorte que la table intermédiaire ressemblera à ceci (j'ai supprimé tous les champs non utilisés dans cet exemple):

L'action suivante consiste à ne conserver que les enregistrements répondant aux critères JOIN , de sorte que tous les enregistrements pour lesquels la table e ManagerId est égale à l' Id table m ManagerId l'alias:

Ensuite, chaque expression utilisée dans la clause SELECT est évaluée pour renvoyer cette table:

Enfin, les noms de colonne e.FName et m.FName sont remplacés par leurs noms de colonne d'alias, attribués à l'opérateur AS :

CROSS JOIN

La jointure croisée fait un produit cartésien des deux membres. Un produit cartésien signifie que chaque ligne d'une table est combinée avec chaque ligne de la seconde table de la jointure. Par exemple, si TABLEA a 20 lignes et que TABLEB a 20 lignes, le résultat serait 20*20 = 400 lignes de sortie.

Utilisation de la base de données exemple

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

Qui retourne:

Il est recommandé d’écrire une jointure CROSS JOIN explicite si vous voulez faire une jointure cartésienne, pour souligner que c’est ce que vous voulez.

Rejoindre une sous-requête

La jointure d'une sous-requête est souvent utilisée lorsque vous souhaitez obtenir des données agrégées à partir d'une table enfant / détails et les afficher avec les enregistrements de la table parent / header. Par exemple, vous pouvez souhaiter obtenir un nombre d'enregistrements enfants, une moyenne de certaines colonnes numériques dans les enregistrements enfants ou la ligne supérieure ou inférieure en fonction d'une date ou d'un champ numérique. Cet exemple utilise des alias, ce qui facilite la lecture des requêtes lorsque plusieurs tables sont impliquées. Voici à quoi ressemble une jointure de sous-requête assez typique. Dans ce cas, nous récupérons toutes les lignes des commandes d'achat de la table parente et ne récupérons que la première ligne pour chaque enregistrement parent de la table enfant 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

CROSS APPLY & LATERAL JOIN

Un type très intéressant de JOIN est le LATERAL JOIN (nouveau dans PostgreSQL 9.3+),
qui est également connu sous le nom de CROSS APPLY / OUTER APPLY dans SQL-Server & Oracle.

L'idée de base est qu'une fonction de table (ou sous-requête en ligne) soit appliquée à chaque ligne que vous joignez.

Cela permet, par exemple, de ne joindre que la première entrée correspondante dans une autre table.
La différence entre une jointure normale et une jointure latérale réside dans le fait que vous pouvez utiliser une colonne que vous avez précédemment jointe à la sous - requête et que vous avez "CROSS APPLY".

Syntaxe:

PostgreSQL 9.3+

à gauche | droit | interne JOIN LATERAL

Serveur SQL:

CROSS | APPLICATION EXTERNE

INNER JOIN LATERAL est identique à CROSS APPLY
et LEFT JOIN LATERAL est identique à OUTER APPLY

Exemple d'utilisation (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 

Et pour 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 

FULL JOIN

Un type de JOIN moins connu est le FULL JOIN.
(Remarque: FULL JOIN n'est pas pris en charge par MySQL en 2016)

Un FULL OUTER JOIN renvoie toutes les lignes de la table de gauche et toutes les lignes de la table de droite.

S'il y a des lignes dans la table de gauche qui n'ont pas de correspondance dans la table de droite ou s'il existe des lignes dans la table de droite qui n'ont pas de correspondance dans la table de gauche, ces lignes seront également répertoriées.

Exemple 1 :

SELECT * FROM Table1

FULL JOIN Table2 
     ON 1 = 2 

Exemple 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 

Notez que si vous utilisez des suppressions logicielles, vous devrez vérifier à nouveau l'état de suppression logicielle dans la clause WHERE (car FULL JOIN se comporte comme un UNION);
Il est facile de négliger ce petit fait, puisque vous mettez AP_SoftDeleteStatus = 1 dans la clause de jointure.

De plus, si vous effectuez un FULL JOIN, vous devrez généralement autoriser NULL dans la clause WHERE; Si vous oubliez d'autoriser NULL sur une valeur, cela aura les mêmes effets qu'une jointure INNER, ce que vous ne voulez pas si vous faites un FULL JOIN.

Exemple:

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) 

JOIN Récursives

Les jointures récursives sont souvent utilisées pour obtenir des données parent-enfant. En SQL, ils sont implémentés avec des expressions de table communes récursives, par exemple:

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;

Différences entre les jointures intérieures / extérieures

SQL a différents types de jointure pour spécifier si des lignes correspondantes (non) sont incluses dans le résultat: INNER JOIN , LEFT OUTER JOIN , RIGHT OUTER JOIN et FULL OUTER JOIN (les mots-clés INNER et OUTER sont facultatifs). La figure ci-dessous souligne les différences entre ces types de jointures: la zone bleue représente les résultats renvoyés par la jointure et la zone blanche représente les résultats que la jointure ne renverra pas.

Cross Join SQL Présentation graphique ( référence ):

Vous trouverez ci-dessous des exemples de cette réponse.

Par exemple, il y a deux tableaux comme ci-dessous:

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

Notez que (1,2) sont uniques à A, (3,4) sont communs et (5,6) sont uniques à B.

Jointure interne

Une jointure interne utilisant l'une des requêtes équivalentes donne l'intersection des deux tables, c'est-à-dire les deux lignes qu'elles ont en commun:

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

Jointure externe gauche

Une jointure externe gauche donnera toutes les lignes dans A, plus toutes les lignes communes dans B:

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

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

Jointure externe droite

De même, une jointure externe droite donnera toutes les lignes de B, plus toutes les lignes communes de A:

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

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

Jointure externe complète

Une jointure externe complète vous donnera l'union de A et B, c'est-à-dire toutes les lignes de A et toutes les lignes de B. Si quelque chose dans A n'a pas de donnée correspondante dans B, alors la partie B est nulle et vice versa.

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

Terminologie JOIN: Intérieur, Extérieur, Semi, Anti ...

Disons que nous avons deux tables (A et B) et que certaines de leurs lignes correspondent (par rapport à la condition JOIN donnée, quel que soit le cas):

Nous pouvons utiliser différents types de jointure pour inclure ou exclure les lignes correspondantes ou non de chaque côté, et nommer correctement la jointure en sélectionnant les termes correspondants dans le diagramme ci-dessus.

Les exemples ci-dessous utilisent les données de test suivantes:

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');

Jointure interne

Combine les lignes gauche et droite qui correspondent.

SELECT * FROM A JOIN B ON X = Y;

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

Jointure externe gauche

Parfois abrégé en "gauche rejoindre". Combine les lignes gauche et droite qui correspondent et inclut les lignes gauche non correspondantes.

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

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

Right Outer Join

Parfois abrégé en "droit de rejoindre". Combine les lignes gauche et droite qui correspondent et inclut les lignes droites non correspondantes.

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

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

Full Outer Join

Parfois abrégé en "full join". Union de gauche et de droite se rejoignent.

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

Gauche Semi Rejoindre

Inclut les lignes de gauche qui correspondent aux lignes droites.

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

X
-----
Lisa
Marco
Phil

Right Semi Join

Inclut les lignes droites qui correspondent aux lignes de gauche.

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

Y
-----
Lisa
Marco
Phil

Comme vous pouvez le voir, il n'y a pas de syntaxe IN dédiée pour la semi-jointure gauche / droite - nous obtenons l'effet simplement en changeant les positions de la table dans le texte SQL.

Gauche anti semi rejoindre

Inclut les lignes de gauche qui ne correspondent pas aux lignes droites.

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

X
----
Amy
John

ATTENTION: soyez prudent si vous utilisez NOT IN sur une colonne NULL-able! Plus de détails ici .

Right Anti Semi Join

Inclut les lignes droites qui ne correspondent pas aux lignes de gauche.

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

Y
-------
Tim
Vincent

Comme vous pouvez le voir, il n'y a pas de syntaxe NOT IN dédiée pour la jointure anti-semi gauche et droite - nous obtenons l'effet simplement en changeant les positions de la table dans le texte SQL.

Cross Join

Un produit cartésien de tous laissé avec toutes les bonnes lignes.

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

La jointure croisée équivaut à une jointure interne avec une condition de jointure qui correspond toujours, de sorte que la requête suivante aurait renvoyé le même résultat:

SELECT * FROM A JOIN B ON 1 = 1;

Self-Join

Cela dénote simplement une table se joignant à elle-même. Une auto-jointure peut être l'un des types de jointure décrits ci-dessus. Par exemple, ceci est une auto-jointure interne:

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