Microsoft SQL Server
Joindre

Introduction

Jointure interne

Inner join renvoie uniquement les enregistrements / lignes qui correspondent / existent dans les deux tables en fonction d'une ou de plusieurs conditions (spécifiées à l'aide du mot clé ON ). C'est le type de jointure le plus courant. La syntaxe générale pour inner join est la suivante:

SELECT * 
FROM table_1
INNER JOIN table_2
  ON table_1.column_name = table_2.column_name

Il peut également être simplifié comme simplement JOIN :

SELECT * 
FROM table_1
JOIN table_2
  ON table_1.column_name = table_2.column_name

Exemple

/* Sample data. */
DECLARE @Animal table (
    AnimalId Int IDENTITY,
    Animal Varchar(20)
);

DECLARE @AnimalSound table (
    AnimalSoundId Int IDENTITY,
    AnimalId Int,
    Sound Varchar(20)
);

INSERT INTO @Animal (Animal) VALUES ('Dog');
INSERT INTO @Animal (Animal) VALUES ('Cat');
INSERT INTO @Animal (Animal) VALUES ('Elephant');

INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (1, 'Barks');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (2, 'Meows');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (3, 'Trumpets');
/* Sample data prepared. */

SELECT 
    * 
FROM 
    @Animal
    JOIN @AnimalSound
        ON @Animal.AnimalId = @AnimalSound.AnimalId;

AnimalId    Animal               AnimalSoundId AnimalId    Sound
----------- -------------------- ------------- ----------- --------------------
1           Dog                  1             1           Barks
2           Cat                  2             2           Meows
3           Elephant             3             3           Trumpets

Utiliser la jointure interne avec la jointure externe gauche (Substitute for Not existe)

Cette requête renverra des données de la table 1 où les champs correspondant à table2 avec une clé et des données ne figurant pas dans le tableau 1 lors de la comparaison avec Table2 avec une condition et une clé

select * 
  from Table1 t1
    inner join Table2 t2 on t1.ID_Column = t2.ID_Column 
    left  join Table3 t3 on t1.ID_Column = t3.ID_Column 
  where t2.column_name = column_value 
    and t3.ID_Column is null 
  order by t1.column_name;

Cross Join

A cross join est une jointure cartésienne, signifiant un produit cartésien des deux tables. Cette jointure ne nécessite aucune condition pour joindre deux tables. Chaque ligne de la table de gauche se joindra à chaque ligne de la table de droite. Syntaxe pour une jointure croisée:

SELECT * FROM table_1
CROSS JOIN table_2 

Exemple:

/* Sample data. */
DECLARE @Animal table (
    AnimalId Int IDENTITY,
    Animal Varchar(20)
);

DECLARE @AnimalSound table (
    AnimalSoundId Int IDENTITY,
    AnimalId Int,
    Sound Varchar(20)
);

INSERT INTO @Animal (Animal) VALUES ('Dog');
INSERT INTO @Animal (Animal) VALUES ('Cat');
INSERT INTO @Animal (Animal) VALUES ('Elephant');

INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (1, 'Barks');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (2, 'Meows');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (3, 'Trumpet');
/* Sample data prepared. */

SELECT 
    * 
FROM 
    @Animal 
    CROSS JOIN @AnimalSound;

Résultats:

AnimalId    Animal               AnimalSoundId AnimalId    Sound
----------- -------------------- ------------- ----------- --------------------
1           Dog                  1             1           Barks
2           Cat                  1             1           Barks
3           Elephant             1             1           Barks
1           Dog                  2             2           Meows
2           Cat                  2             2           Meows
3           Elephant             2             2           Meows
1           Dog                  3             3           Trumpet
2           Cat                  3             3           Trumpet
3           Elephant             3             3           Trumpet

Notez qu'il existe d'autres manières d'appliquer une CROSS JOIN. Il s’agit d’une jointure "ancienne" (obsolète depuis ANSI SQL-92) sans condition, ce qui entraîne une jointure croisée / cartésienne:

SELECT * 
FROM @Animal, @AnimalSound;

Cette syntaxe fonctionne également grâce à une condition de jointure "toujours vraie", mais elle n'est pas recommandée et doit être évitée, au profit de la syntaxe explicite de CROSS JOIN , dans un souci de lisibilité.

SELECT * 
FROM 
    @Animal 
    JOIN @AnimalSound 
        ON 1=1

Jointure externe

Jointure externe gauche

LEFT JOIN renvoie toutes les lignes de la table de gauche, correspondant aux lignes de la table de droite où les conditions de la clause ON sont remplies. Les lignes dans lesquelles la clause ON n'est pas remplie ont NULL dans toutes les colonnes de la table de droite. La syntaxe d'une LEFT JOIN est la suivante:

SELECT * FROM table_1 AS t1
LEFT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column 

Right Outer Join

RIGHT JOIN renvoie toutes les lignes de la table de droite, correspondant aux lignes de la table de gauche où les conditions de la clause ON sont remplies. Les lignes dans lesquelles la clause ON n'est pas remplie ont la NULL dans toutes les colonnes de la table de gauche. La syntaxe de RIGHT JOIN est la suivante:

SELECT * FROM table_1 AS t1
RIGHT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column 

Full Outer Join

FULL JOIN combine LEFT JOIN et RIGHT JOIN . Toutes les lignes sont renvoyées des deux tables, que les conditions de la clause ON soient remplies ou non. Les lignes qui ne satisfont pas à la clause ON sont renvoyées avec NULL dans toutes les colonnes de la table opposée (c'est-à-dire que pour une ligne de la table de gauche, toutes les colonnes de la table de droite contiendront NULL et inversement). La syntaxe d'un FULL JOIN est la suivante:

SELECT * FROM table_1 AS t1
FULL JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column  

Exemples

/* Sample test data. */
DECLARE @Animal table (
    AnimalId Int IDENTITY,
    Animal Varchar(20)
);

DECLARE @AnimalSound table (
    AnimalSoundId Int IDENTITY,
    AnimalId Int,
    Sound Varchar(20)
);

INSERT INTO @Animal (Animal) VALUES ('Dog');
INSERT INTO @Animal (Animal) VALUES ('Cat');
INSERT INTO @Animal (Animal) VALUES ('Elephant');
INSERT INTO @Animal (Animal) VALUES ('Frog');

INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (1, 'Barks');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (2, 'Meows');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (3, 'Trumpet');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (5, 'Roars');
/* Sample data prepared. */

JOINTURE EXTERNE GAUCHE

SELECT * 
FROM @Animal As t1 
LEFT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;

Résultats pour LEFT JOIN

AnimalId    Animal               AnimalSoundId AnimalId    Sound
----------- -------------------- ------------- ----------- --------------------
1           Dog                  1             1           Barks
2           Cat                  2             2           Meows
3           Elephant             3             3           Trumpet
4           Frog                 NULL          NULL        NULL

DROIT EXTERIEUR

SELECT * 
FROM @Animal As t1 
RIGHT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;

Résultats pour RIGHT JOIN

AnimalId    Animal               AnimalSoundId AnimalId    Sound
----------- -------------------- ------------- ----------- --------------------
1           Dog                  1             1           Barks
2           Cat                  2             2           Meows
3           Elephant             3             3           Trumpet
NULL        NULL                 4             5           Roars

FULL OUTER JOIN

SELECT * 
FROM @Animal As t1 
FULL JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;

Résultats pour FULL JOIN

AnimalId    Animal               AnimalSoundId AnimalId    Sound
----------- -------------------- ------------- ----------- --------------------
1           Dog                  1             1           Barks
2           Cat                  2             2           Meows
3           Elephant             3             3           Trumpet
4           Frog                 NULL          NULL        NULL
NULL        NULL                 4             5           Roars

Utiliser la jointure dans une mise à jour

Les jointures peuvent également être utilisées dans une instruction UPDATE :

CREATE TABLE Users (
    UserId int NOT NULL,
    AccountId int NOT NULL,
    RealName nvarchar(200) NOT NULL
)

CREATE TABLE Preferences (
    UserId int NOT NULL,
    SomeSetting bit NOT NULL
)

Mettez à jour la colonne SomeSetting du filtrage des tables de Preferences par un prédicat de la table Users comme suit:

UPDATE p
SET p.SomeSetting = 1
FROM Users u
JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234

p est un alias de Preferences défini dans la clause FROM de l'instruction. Seules les lignes avec un AccountId correspondant de la table Users seront mises à jour.

Mise à jour avec les instructions de jointure externes à gauche

Update t 
SET  t.Column1=100
FROM Table1 t LEFT JOIN Table12 t2 
ON t2.ID=t.ID

Mise à jour des tables avec jointure interne et fonction d'agrégation

UPDATE t1
SET t1.field1 = t2.field2Sum
FROM table1 t1
INNER JOIN (select field3, sum(field2) as field2Sum
from table2
group by field3) as t2
on t2.field3 = t1.field3  

Rejoindre une sous-requête

La jointure sur une sous-requête est souvent utilisée lorsque vous souhaitez obtenir des données agrégées (telles que Count, Avg, Max ou Min) à partir d'une table enfant / détails et l'afficher avec les enregistrements de la table parent / header. Par exemple, vous souhaiterez peut-être récupérer la ligne supérieure / première enfant en fonction de la date ou de l'ID ou peut-être souhaitez-vous un nombre total de lignes enfants ou une moyenne.

Cet exemple utilise des alias qui facilitent la lecture des requêtes lorsque plusieurs tables sont impliquées. Dans ce cas, nous récupérons toutes les lignes des commandes d'achat de la table parente et récupérons uniquement la dernière ligne enfant (ou la plus récente) de la table enfant PurchaseOrderLineItems. Cet exemple suppose que la table enfant utilise des identifiants numériques incrémentiels.

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, Max(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

Self Join

Une table peut être jointe sur ce que l'on appelle une auto-jointure, en combinant des enregistrements de la table avec d'autres enregistrements de la même table. Les auto-jointures sont généralement utilisées dans les requêtes où une hiérarchie est définie dans les colonnes de la table.

Considérez les exemples de données dans une table appelée Employees :

Le Boss_ID chaque employé correspond à l' ID un autre employé. Pour récupérer une liste d'employés avec le nom de leur patron respectif, la table peut être jointe sur elle-même à l'aide de ce mappage. Notez que joindre une table de cette manière nécessite l'utilisation d'un alias ( Bosses dans ce cas) sur la deuxième référence à la table pour se distinguer de la table d'origine.

SELECT Employees.Name,
    Bosses.Name AS Boss
FROM Employees
INNER JOIN Employees AS Bosses 
    ON Employees.Boss_ID = Bosses.ID

L'exécution de cette requête génère les résultats suivants:

Supprimer en utilisant Join

Les jointures peuvent également être utilisées dans une instruction DELETE . Étant donné un schéma comme suit:

CREATE TABLE Users (
    UserId int NOT NULL,
    AccountId int NOT NULL,
    RealName nvarchar(200) NOT NULL
)

CREATE TABLE Preferences (
    UserId int NOT NULL,
    SomeSetting bit NOT NULL
)

Nous pouvons supprimer des lignes de la table Preferences , en les filtrant par un prédicat de la table Users comme suit:

DELETE p
FROM Users u
INNER JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234

Ici, p est un alias de Preferences défini dans la clause FROM de l'instruction et nous AccountId uniquement les lignes qui ont un AccountId correspondant de la table Users .

Tourner accidentellement une jointure externe en une jointure interne

Les jointures externes renvoient toutes les lignes d'une ou des deux tables, ainsi que les lignes correspondantes.

Table People
PersonID FirstName
       1 Alice
       2 Bob
       3 Eve

Table Scores
PersonID Subject Score
       1 Math    100
       2 Math     54
       2 Science  98

Gauche rejoignant les tables:

Select * from People a
left join Scores b
on a.PersonID = b.PersonID

Résultats:

PersonID FirstName PersonID Subject Score
       1 Alice            1 Math    100
       2 Bob              2 Math     54
       2 Bob              2 Science  98
       3 Eve           NULL NULL   NULL

Si vous vouliez renvoyer toutes les personnes, avec tous les scores de mathématiques applicables, une erreur commune est d'écrire:

Select * from People a
left join Scores b
on a.PersonID = b.PersonID
where Subject = 'Math'

Cela supprimerait Eve de vos résultats, en plus de supprimer le score scientifique de Bob, puisque Subject est NULL pour elle.

La syntaxe correcte pour supprimer des enregistrements non-Math en conservant tous les individus dans la table People serait:

Select * from People a
left join Scores b
on a.PersonID = b.PersonID
and b.Subject = 'Math'