Vous êtes sur le site Web historique de la documentation de 4D. Les documentations sont progressivement déplacées vers developer.4d.com

Accueil

 
Accueil
4D v20 R8
4D - Référence SQL
Utiliser le SQL dans 4D
Prise en charge des jointures

Prise en charge des jointures  


 

 

Le moteur SQL de 4D permet une prise en charge étendue des jointures.

Les jointures peuvent être internes ou externes, implicites ou explicites. Les jointures internes (INNER joins) implicites sont prises en charge via la commande SELECT. Vous pouvez également générer des jointures internes et externes explicites à l’aide du mot-clé SQL JOIN.

Note : L’implémentation actuelle des jointures dans le moteur SQL de 4D n’inclut pas :

  • les jointures naturelles.
  • le constructeur USING dans les jointures internes.
  • les jointures croisées (CROSS).

Les jointures permettent de mettre en relation les enregistrements de deux ou plusieurs tables et de combiner le résultat dans une nouvelle table, appelée jointure. 

Vous générez des jointures via des instructions SELECT définissant des conditions de jointure.

A compter de 4D v15 R4, les jointures explicites impliquant deux tables et celles impliquant trois tables ou plus sont gérées par deux implémentations différentes et ne suivent pas les mêmes règles. Veuillez vous reporter ci-dessous à la section qui correspond à vos besoins.

Note : Généralement, dans le moteur de base de données, l’ordre des tables est déterminé par l’ordre défini dans la recherche. Lorsque vous utilisez des jointures, l’ordre des tables est déterminé par la liste des tables. Dans l’exemple suivant :
SELECT * FROM T1 RIGHT OUTER JOIN T2 ON T2.depID = T1.depID;
... l’ordre des tables est T1 puis T2 (comme elles apparaissent dans la liste des tables) et non T2 puis T1 (comme elles apparaissent dans la condition de jointure).

Pour illustrer le fonctionnement des jointures, nous allons utiliser tout au long de cette section la base suivante :

  • Employees
    namedepIDcityID
    Alain1030
    Anne1139
    Bernard1033
    Fabrice1235
    Martine1530
    PhilippeNULL33
    Thomas10NULL
  • Departments
    depIDdepName
    10Program
    11Engineering
    NULLMarketing
    12Development
    13Quality
  • Cities
    cityIDcityName
    30Paris
    33New York
    NULLBerlin

Si vous le souhaitez, vous pouvez générer cette base automatiquement en exécutant le code suivant :

 Début SQL
         DROP TABLE IF EXISTS Employees;
         CREATE TABLE Employees ( depID INT32, name VARCHAR, cityID INT32);
         INSERT INTO Employees (name, depID, cityID) VALUES ('Alain', 10, 30);
         INSERT INTO Employees (name, depID, cityID) VALUES ('Anne', 11, 39);
         INSERT INTO Employees (name, depID, cityID) VALUES ('Bernard', 10, 33);
         INSERT INTO Employees (name, depID, cityID) VALUES ('Fabrice', 12, 35);
         INSERT INTO Employees (name, depID, cityID) VALUES ('Martine', 15, 30);
         INSERT INTO Employees (name, depID, cityID) VALUES ('Philippe', NULL, 33);
         INSERT INTO Employees (name, depID, cityID) VALUES ('Thomas', 10, NULL);
 
         DROP TABLE IF EXISTS Departments;
         CREATE TABLE Departments ( depID INT32, depName VARCHAR );
         INSERT INTO Departments (depID, depName) VALUES (10, 'Program');
         INSERT INTO Departments (depID, depName) VALUES (11, 'Engineering');
         INSERT INTO Departments (depID, depName) VALUES (NULL, 'Marketing');
         INSERT INTO Departments (depID, depName) VALUES (12, 'Development');
         INSERT INTO Departments (depID, depName) VALUES (13, 'Quality');
 
         DROP TABLE IF EXISTS Cities;
         CREATE TABLE Cities ( cityID INT32, cityName VARCHAR );
         INSERT INTO Cities (cityID, cityName) VALUES (30, 'Paris');
         INSERT INTO Cities (cityID, cityName) VALUES (33, 'New York');
         INSERT INTO Cities (cityID, cityName) VALUES (NULL, 'Berlin');
 Fin SQL

Une jointure interne (inner join) est une jointure basée sur une comparaison d’égalité entre deux colonnes.

Voici un exemple de jointure interne implicite :

SELECT *  
        FROM employees, departments
        WHERE employees.DepID = departments.DepID;

Dans 4D, vous pouvez également utiliser le mot-clé JOIN afin de définir une jointure interne explicite :

SELECT *
        FROM employees
        INNER JOIN departments
            ON employees.DepID = departments.DepID;

Cette requête peut être insérée dans le code 4D de la manière suivante :

 TABLEAU TEXTE(aName;0)
 TABLEAU TEXTE(aDepName;0)
 TABLEAU ENTIER(aEmpDepID;0)
 TABLEAU ENTIER(aDepID;0)
 Debut SQL
        SELECT Employees.name, Employees.depID, Departments.depID, Departments.depName
        FROM Employees
                INNER JOIN Departments
                    ON Employees.depID = Departments.depID
                INTO :aName, :aEmpDepID, :aDepID, :aDepName;
 Fin SQL

Résultat de cette jointure :

aNameaEmpDepIDaDepIDaDepName
Alain1010Program
Anne1111Engineering
Bernard1010Program
Fabrice1212Development
Thomas1010Program

A noter que ni les employés Philippe et Martine ni les départements Marketing et Quality n’apparaissent dans la jointure résultante car :

  • Philippe n’a pas de département associé (NULL),
  • l’ID de département de Martine n’existe pas dans la table Departments,
  • il n’y a pas d’employé associé au département d’ID 13 (Quality),
  • le département Marketing n’a pas d’ID associé (NULL).

4D vous permet de générer des jointures externes (OUTER JOINs). Dans une jointure externe, il n’est pas nécessaire qu’il existe une correspondance entre les lignes des tables jointes. La table résultante contient toutes les lignes des tables (ou d’au moins une des tables de la jointure) même s’il n’y a pas de ligne correspondante. Ce principe permet de s’assurer que toutes les informations d’une table sont exploitées, même si des lignes ne sont pas renseignées entre les différentes tables jointes. 

Il existe trois types de jointures externes, définies par les mots-clés LEFT, RIGHT et FULL. LEFT et RIGHT permettent de désigner la table (située à gauche ou à droite du mot-clé) dont la totalité des données devra être traitée. FULL indique une jointure externe bilatérale.

Note : Seules les jointures externes explicites sont prises en charge par 4D.

Dans les jointures externes à deux tables, les conditions peuvent être complexes mais elles doivent toujours être basées sur un test d’égalité entre des colonnes incluses dans la jointure. Par exemple, il n’est pas possible d’utiliser l’opérateur >= dans une condition de jointure explicite. Les jointures implictes autorisent tout type de comparaison. En interne, les comparaisons d’égalité sont effectuées directement par le moteur de 4D, ce qui leur assure une grande rapidité d’exécution.

Le résultat d’une jointure externe gauche (ou jointure gauche) contient toujours tous les enregistrements de la table située à gauche du mot-clé même si la condition de jointure ne trouve pas d’enregistrement correspondant dans la table de droite. Cela signifie que si pour une ligne de la table gauche la requête trouve zéro ligne correspondant dans la table droite, la jointure contiendra la ligne avec la valeur NULL pour chaque colonne de la table de droite. Autrement dit, une jointure externe gauche retourne toutes les lignes de la table gauche plus celles de la table droite qui correspondent à la condition de jointure (ou NULL si aucune ne correspond). A noter que si la table la droite contient plus d’une ligne correspondant au prédicat de la jointure pour une ligne de la table gauche, les valeurs de la table gauche seront répétées pour chaque ligne distincte de la table droite. 

Voici un exemple de code 4D effectuant une jointure externe gauche :

 TABLEAU TEXTE(aName;0)
 TABLEAU TEXTE(aDepName;0)
 TABLEAU ENTIER(aEmpDepID;0)
 TABLEAU ENTIER(aDepID;0)
 Debut SQL
        SELECT Employees.name, Employees.depID, Departments.depID, Departments.depName
            FROM Employees
            LEFT OUTER JOIN Departments
                ON Employees.DepID = Departments.DepID
                INTO :aName, :aEmpDepID, :aDepID, :aDepName;
 Fin SQL

Résultat de cette jointure avec notre base d’exemple (les lignes additionnelles sont en rouge) :

aNameaEmpDepIDaDepIDaDepName
Alain1010Program
Anne1111Engineering
Bernard1010Program
Fabrice1212Development
Thomas1010Program
Martine15NULLNULL
PhilippeNULLNULLNULL

A l’exact opposé de la jointure externe gauche, le résultat d’une jointure externe droite contient toujours tous les enregistrements de la table située à droite du mot-clé même si la condition de jointure ne trouve pas d’enregistrement correspondant dans la table gauche. 

Voici un exemple de code 4D effectuant une jointure externe droite :

 TABLEAU TEXTE(aName;0)
 TABLEAU TEXTE(aDepName;0)
 TABLEAU ENTIER(aEmpDepID;0)
 TABLEAU ENTIER(aDepID;0)
 Debut SQL
        SELECT Employees.name, Employees.depID, Departments.depID, Departments.depName
            FROM Employees
            RIGHT OUTER JOIN Departments
                ON Employees.DepID = Departments.DepID
                INTO :aName, :aEmpDepID, :aDepID, :aDepName;
 Fin SQL

Résultat de cette jointure avec notre base d’exemple (les lignes additionnelles sont en rouge) :

aNameaEmpDepIDaDepIDaDepName
Alain1010Program
Anne1111Engineering
Bernard1010Program
Fabrice1212Development
Thomas1010Program
NULLNULLNULLMarketing
NULLNULL13Quality

La jointure externe bilatérale combine simplement les résultats d’une jointure externe gauche et d’une jointure externe droite. La table jointure résultante contient tous les enregistrements des tables gauche et droite et remplit les champs manquants de chaque côté avec des valeurs NULL. 

Voici un exemple de code 4D effectuant une jointure externe bilatérale :

 TABLEAU TEXTE(aName;0)
 TABLEAU TEXTE(aDepName;0)
 TABLEAU ENTIER(aEmpDepID;0)
 TABLEAU ENTIER(aDepID;0)
 Debut SQL
        SELECT Employees.name, Employees.depID, Departments.depID, Departments.depName
            FROM Employees
            FULL OUTER JOIN Departments
                ON Employees.DepID = Departments.DepID
                INTO :aName, :aEmpDepID, :aDepID, :aDepName;
 Fin SQL

Résultat de cette jointure avec notre base d’exemple (les lignes additionnelles sont en rouge) :

aNameaEmpDepIDaDepIDaDepName
Alain1010Program
Anne1111Engineering
Bernard1010Program
Fabrice1212Development
Thomas1010Program
Martine15NULLNULL
PhilippeNULLNULLNULL
NULLNULLNULLMarketing
NULLNULL13Quality

A compter de 4D v15 R4, le serveur SQL intégré de 4D étend la prise en charge des jointures SQL externes aux requêtes impliquant trois tables ou plus. Cette implémentation particulière inclut ses propres règles et limitations, qui sont décrites dans cette section. 

Comme les jointures à deux tables, les jointures externes impliquant trois tables ou plus peuvent être LEFT, RIGHT ou FULL. Pour des informations générales sur les jointures externes, veuillez vous référer ci-dessus au paragraphe Jointures externes entre deux tables.

A la différence des jointures externes à deux tables, les jointures externes impliquant trois tables ou plus acceptent plusieurs opérateurs de comparaison, en plus de l'égalité (=) : <, >, >=, ou <=. Ces opérateurs peuvent être combinés dans les clauses ON.

  • Chaque clause ON de jointure externe explicite doit référencer exactement deux tables, ni plus ni moins. Chaque table de la jointure doit être référencée au moins une fois parmi les clauses ON.
  • Une des tables doit provenir de la partie gauche de la clause JOIN et l'autre table, de la partie droite.

Par exemple, la requête suivante sera exécutée avec succès:

SELECT * FROM T1
LEFT JOIN
(T2 LEFT JOIN T3 ON T2.ID=T3.ID) -- ici T2 est juste à gauche et T3 est juste à droite
ON T1.ID=T3.ID -- ici T1 est juste à gauche et T3 est juste à droite

Avec nos trois tables, cette requête pourrait être par exemple :

 TABLEAU TEXTE(aName;0)
 TABLEAU TEXTE(aDepName;0)
 TABLEAU TEXTE(aCityName;0)
 TABLEAU ENTIER(aEmpDepID;0)
 TABLEAU ENTIER(aEmpCityID;0
 TABLEAU ENTIER(aDepID;0)
 TABLEAU ENTIER(aCityID;0)
 Debut SQL
        SELECT Employees.name, Employees.depID, Employees.cityID, Departments.depID, Departments.depName, Cities.cityID, Cities.cityName
            FROM Departments
                LEFT JOIN
                (Employees LEFT JOIN Cities ON Employees.cityID=Cities.cityID)
                ON Departments.depID=Employees.depID
                INTO :aName, :aEmpDepID, :aEmpCityID, :aDepID, :aDepName, :aCityID, :aCityName;
 Fin SQL

Les résultats sont alors :

aNameaEmpDepIDaEmpCityIDaDepIDaDepNameaCityIDaCityName
Alan103010ProgramNULLNULL
Bernard103310Program30Paris
Anne113911Engineering33New York
Fabrice123512DevelopmentNULLNULL
Thomas10NULL10ProgramNULLNULL
NULLNULLNULLNULLMarketingNULLNULL
NULLNULLNULL13QualityNULLNULL

D'un autre côté, les trois requêtes suivantes seront rejetées car elles violent certaines règles :

SELECT * FROM T1
LEFT JOIN
(T2 LEFT JOIN T3 ON T2.ID=T1.ID) -- ici T2 est bien est juste à gauche mais T1 n'est pas présent juste à droite
ON T1.ID=T3.ID

SELECT * FROM
(T1 LEFT JOIN T2 ON T1.ID=T2.ID)
LEFT JOIN
(T3 LEFT JOIN T4 ON T3.ID=T4.ID)
ON T3.Name=T4.Name -- ici T3 et T4 proviennent de la partie droite de la clause JOIN et aucune table ne provient de la gauche

SELECT * FROM T1
LEFT JOIN
(T2 LEFT JOIN T3 ON T2.ID=T3.ID)
ON T1.ID=T3.ID AND T1.ID=T2.ID -- ici plus de deux tables sont utilisées dans la clause ON : T1, T2 et T3

En général, si les tables (Tx1, Tx2..., Txn) à la gauche de la clause JOIN et les tables (Ty1, Ty2..., Tyn) à sa droite sont jointes, alors l'expression ON doit référencer exactement une table gauche Txa et une table droite Tyb.

Non accepté dans la clause ONAccepté dans la clause ON
Opérations booéennesORAND et NOT
Prédicats et fonctionsIS NULL, COALESCETous les autres prédicats et fonctions intégrées (toutes combinaisons)
Références de variables 4D-Prise en charge sans restriction
Appels de méthodes 4DLorsque soit la partie gauche soit la partie droite de la clause JOIN courante est une jointure externe expliciteTous les autres cas (voir exemples ci-dessous

L'exemple suivant avec un appel de méthode 4D est pris en charge car il n'y a pas de sous-jointures non internes dans la jointure :

SELECT * FROM T1
LEFT JOIN T2
ON T1.ID={FN My4DCall (T2.ID) AS INT32}

Par contre, cet exemple d'appel de méthode 4D n'est pas pris en charge car des sous-jointures non internes sont présentes dans la jointure :

SELECT * FROM
(T1 LEFT JOIN T2 ON T1.ID=T2.ID)
LEFT JOIN -- Les deux parties gauche et droite de cette clause de jointure contiennent des jointures LEFT explicites
(T3 LEFT JOIN T4 ON T3.ID=T4.ID)
ON T1.ID={FN My4DCall (T4.ID) AS INT32} -- jointure avec des sous-jointures non internes

  • Les références aux Vues SQL ne sont pas autorisées dans les déclarations de jointures explicites.
  • Les sous-requêtes qui utilisent des jointures externes ne sont pas prises en charge. La requête suivante sera rejetée :
    SELECT T2.ID FROM T2
    WHERE T2.ID=(
    SELECT COUNT ( * ) FROM
    (T1 LEFT JOIN T3 ON T1.ID=T3.ID)
    RIGHT JOIN T4 ON T3.ID=T4.ID)

Page préc.
Page suiv.

 
PROPRIÉTÉS 

Produit : 4D
Thème : Utiliser le SQL dans 4D
Nom intl. : Support of joins

 
PAGE CONTENTS 
Présentation
Base d'exemple
Jointures internes explicites
Jointures externes entre deux tables
Jointures externes gauches (LEFT OUTER JOIN)
Jointures externes droites (RIGHT OUTER JOIN)
Jointures externes bilatérales (FULL OUTER JOIN)
Jointures externes entre trois tables ou plus
Règles de base
Prise en charge dans la condition ON
Limitations générales
 
HISTORIQUE 

 
UTILISATION DE L'ARTICLE

4D - Référence SQL ( 4D v20 R8)