[SQL Server] Jointures : concepts généraux

Ce billet présente les jointures sous SQL Server.

Qu’est-ce qu’une jointure ?

Une jointure est une opération qui permet de traiter des données issues d’une ou plusieurs tables, en se basant sur des relations logiques inter-tables. Il s’agit, pour SQL Server, de savoir comment utiliser des données d’une table pour sélectionner les lignes d’une autre table.

Les différents types de jointures

Afin de mieux étayer les explications à venir, créons deux tables (au sein d’une base de test, par exemple) MaTable1 et MaTable2 et insérons-y quelques enregistrements :

-- Création des tables
CREATE TABLE MaTable1 (MaTable1_ID INT, MaTable1_Nom VARCHAR(20))
GO
CREATE TABLE MaTable2 (MaTable2_ID INT, MaTable2_Nom VARCHAR(20))
GO
-- Insertions
INSERT INTO MaTable1(MaTable1_ID,MaTable1_Nom) VALUES(1,'Paris')
INSERT INTO MaTable1(MaTable1_ID,MaTable1_Nom) VALUES(2,'Rome')
INSERT INTO MaTable1(MaTable1_ID,MaTable1_Nom) VALUES(4,'Ottawa')
INSERT INTO MaTable1(MaTable1_ID,MaTable1_Nom) VALUES(5,'Tokyo')
INSERT INTO MaTable1(MaTable1_ID,MaTable1_Nom) VALUES(7,'Camberra')
GO
INSERT INTO MaTable2(MaTable2_ID,MaTable2_Nom) VALUES(1,'Paris')
INSERT INTO MaTable2(MaTable2_ID,MaTable2_Nom) VALUES(3,'Montevideo')
INSERT INTO MaTable2(MaTable2_ID,MaTable2_Nom) VALUES(5,'Tokyo')
INSERT INTO MaTable2(MaTable2_ID,MaTable2_Nom) VALUES(6,'Kampala')
GO
-- Consultation des tables
SELECT *FROM MaTable1
GO
SELECT * FROM MaTable2
GO

Les jointures normalisées

La jointure interne : INNER JOIN

Ce type de jointure retourne les lignes d’enregistrements qui sont présentes dans les deux tables jointes.

Soit d’un point de vue booléen : MaTable1 AND MaTable2.

  • En (T-)SQL:
SELECT *
FROM MaTable1 mt1 INNER JOIN MaTable2 mt2
 ON mt1.MaTable1_ID=mt2.MaTable2_ID
GO

Notons que par défaut, quand le mot-clé JOIN est utilisé seul, c’est INNER JOIN qui est implicitement employé par SQL Server. En d’autres termes, l’utilisation du mot-clé INNER est facultative.

La jointure externe : OUTER JOIN

        LEFT [OUTER] JOIN

Ce type de jointure retourne toutes les lignes d’enregistrements de la table de gauche, qu’il y ait ou non une correspondance avec la table de droite. Les enregistrements de la table de droite qui sont présents dans la table de gauche sont également retournés. En revanche, les enregistrements de la table de droite non-présents dans celle de gauche seront remplacés par NULL.

  • Graphiquement :


  • En (T-)SQL
SELECT *
FROM MaTable1 mt1 LEFT OUTER JOIN MaTable2 mt2
 ON mt1.MaTable1_ID=mt2.MaTable2_ID
GO
L’utilisation du mot-clé OUTER est facultative.

Le résultat est le suivant :


Les enregistrements 3 (Montevideo) et 6 (Kampala) de la table de droite (MaTable2) ont été remplacés par NULL car non-présents au sein de la table de gauche (MaTable1).

Si l’on souhaite n’afficher que les enregistrements de la table de gauche non-présents dans celle de droite, il suffit de préciser dans une clause WHERE que la table de droite est « nulle » :

SELECT *
FROM MaTable1 mt1 LEFT JOIN MaTable2 mt2
 ON mt1.MaTable1_ID=mt2.MaTable2_ID
WHERE mt2.MaTable2_ID IS NULL
GO

Graphiquement, cela donnerait :


Soit d’un point de vue booléen : MaTable1 NOT MaTable2.


RIGHT [OUTER] JOIN

Ce type de jointure est l’inverse de la jointure externe de gauche. Il retourne toutes les lignes d’enregistrements de la table de droite, qu’il y ait ou non une correspondance avec la table de gauche. Les enregistrements de la table de gauche qui sont présents dans la table de droite sont également retournés. Par contre, les enregistrements de la table de gauche non-présents dans celle de droite seront remplacés par NULL.

  • Graphiquement :


  • En (T-)SQL
SELECT *
FROM MaTable1 mt1 RIGHT OUTER JOIN MaTable2 mt2
 ON mt1.MaTable1_ID=mt2.MaTable2_ID
GO
L’utilisation du mot-clé OUTER est facultative.

Le résultat de la requête est le suivant :


Les enregistrements 2 (Rome) et
7 (Camberra) de la table de gauche (MaTable1) ont été remplacés par NULL car non-présents au sein de la table de droite (MaTable2).

Si l’on souhaite n’afficher que les enregistrements de la table de droite non-présents dans celle de gauche, il suffit de préciser dans une clause WHERE que la table de gauche est « nulle » :

SELECT *
FROM MaTable1 mt1 RIGHT JOIN MaTable2 mt2
 ON mt1.MaTable1_ID=mt2.MaTable2_ID
WHERE mt1.MaTable1_ID IS NULL
GO

Graphiquement, cela donnerait :


Soit d’un point de vue booléen : MaTable2 NOT MaTable1.

      

FULL [OUTER] JOIN

Ce type de jointure retourne les lignes d’enregistrements de la table de gauche qui ne sont pas présentes dans la table de droite et vice-versa. Toutes les lignes

  • Graphiquement :


Soit d’un point de vue booléen : MaTable1 OR MaTable2.

  • En (T-)SQL :
SELECT *
FROM MaTable1 mt1 FULL OUTER JOIN MaTable2 mt2
 ON mt1.MaTable1_ID=mt2.MaTable2_ID
GO
L’utilisation du mot-clé OUTER est facultative.

Voici le résultat de la requête :


Comme on peut le noter, il s’agit d’un « mix » entre LEFT JOIN et RIGHT JOIN.

Si l’on souhaite n’afficher que les enregistrements de la table de droite non-présents dans celle de gauche et vice-versa, il suffit de préciser dans une clause WHERE que les tables de gauche et de droite sont « nulles » :

SELECT *
FROM MaTable1 mt1 FULL JOIN MaTable2 mt2
 ON mt1.MaTable1_ID=mt2.MaTable2_ID
WHERE mt1.MaTable1_ID IS NULL
 OR mt2.MaTable2_ID IS NULL
GO

Graphiquement, cela donnerait :


Soit d’un point de vue booléen : NOT (MaTable2 AND MaTable1).

La jointure croisée : CROSS JOIN

Il s’agit d’un type de jointure cartésien. Concrètement, un CROSS JOIN est le produit cartésien entre les 2 tables concernées, retournant toutes les combinaisons possibles de toutes les lignes de chacune de ces tables. Toutes les lignes d’enregistrements d’une table étant mises en correspondance avec chaque ligne de la table à joindre, aucune condition de jointure n’est nécessaire.

En T-SQL, cela donne :

SELECT *
FROM MaTable1 mt1 CROSS JOIN MaTable2 mt2
GO

Avec le résultat suivant :


Ou pour être exact :


Soit 5 enregistrements de MaTable1 multipliés par 4 enregistrements de MaTable2 donnant ainsi 20 enregistrements issus du CROSS JOIN.

L’auto-jointure (ou self-join)

Ce type de jointure permet d’effectuer une jointure d’une table à elle-même. Cela peut être utile dans le cas de l’établissement de hiérarchies.

Pour mieux étayer notre exemple, prenons l’exemple classique de l’employé et de son manager :

-- Création de la table
CREATE TABLE Employé (Emp_ID INT, Emp_Nom VARCHAR(30), Manager_ID INT)
-- Insertion d'enregistrements
INSERT INTO Employé (Emp_ID, Emp_Nom, Manager_ID) VALUES (1, 'Durand', NULL)
INSERT INTO Employé (Emp_ID, Emp_Nom, Manager_ID) VALUES (2, 'Dupont', 1)
INSERT INTO Employé (Emp_ID, Emp_Nom, Manager_ID) VALUES (3, 'Dumont', 4)
INSERT INTO Employé (Emp_ID, Emp_Nom, Manager_ID) VALUES (4, 'Dubois', 2)
GO
-- Consultation de la table
SELECT * FROM Employé
GO

Voici un exemple d’utilisation d’une auto-jointure qui permet d’afficher les employés et leur manager correspondant :

SELECT e1.Emp_ID,
 e1.Emp_Nom,
 e2.Emp_Nom AS 'Manager'
FROM Employé e1 INNER JOIN Employé e2
 ON e1.Emp_ID=e2.Manager_ID
GO

L’emploi d’alias est recommandé, voire obligatoire, pour ne pas pousser l’optimiseur de requêtes à la confusion.

La jointure hétérogène

Ce type de jointure permet de joindre une table (ou vue) issue d’une source de données différente. Il peut s’agir d’une autre base SQL Server, d’une base hétérogène (Oracle, Sybase, MySQL, DB2,…), d’un fichier,…

Vous pouvez jeter un coup d’oeil sur le lien suivant pour avoir un exemple de jointure hétérogène (SQL Server-Active Directory): http://msdn.microsoft.com/en-us/library/windows/desktop/aa772367.aspx.

Nous aborderons dans le futur les linked servers.

Les conditions de jointure

Il existe 2 conditions de jointures :

  • L’équijointure, qui consiste à relier 2 tables en se basant sur un critère d’équivalence entre 2 colonnes utilisées pour la jointure (via l’opérateur d’égalité =). Par exemple
SELECT *
FROM MaTable1 mt1 INNER JOIN MaTable2 mt2
 ON mt1.MaTable1_ID=mt2.MaTable2_ID
GO
  • La non-équijointure, qui consiste à relier 2 tables en se basant sur un critère autre que l’équivalence entre 2 colonnes utilisées pour la jointure (opérateurs <, >, <=, >=, <>, BETWEEN, IN, LIKE, EXISTS). Par exemple :
SELECT *
FROM MaTable1 mt1 INNER JOIN MaTable2 mt2
 ON mt1.MaTable1_ID<>mt2.MaTable2_ID
GO

Pour finir…

SQL Server dispose d’un bon panel de jointures permettant à une requête de travailler sur plusieurs tables. Si vous souhaitez approfondir le sujet, vous pouvez également jeter un coup d’œil sur http://msdn.microsoft.com/en-us/library/ms191517.aspx et http://msdn.microsoft.com/fr-fr/library/ms191472.aspx.

Notons que d’autres types de jointures existent mais ne sont pas supportées par SQL Server, à savoir :

  • La jointure naturelle (NATURAL JOIN), qui permet de joindre 2 tables en se basant sur les colonnes de même nom et de même type (avec utilisation facultative de la clause USING pour mieux cibler les colonnes).
  • La jointure d’union (UNION JOIN) qui permet de joindre 2 tables peu importe leur structure. Ce type de jointure peut être remplacé par un FULL [OUTER] JOIN.
Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s