4D Doc Center : Prise en charge des jointures

4D v14

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.

Présentation

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. Avec les jointures explicites, 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.

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).

Jointures internes explicites

Une jointure interne (inner join) est une jointure basée sur une comparaison d’égalité entre deux colonnes. Considérons par exemple les trois tables suivantes :

Employees
name depID cityID
Alain 10 30
Anne 11 39
Bernard 10 33
Fabrice 12 35
Martine 15 30
Philippe NULL 33
Thomas 10 NULL

Departments
depID depName
10 Program
11 Engineering
NULL Marketing
12 Development
13 Quality

Cities
cityID cityName
30 Paris
33 New York
NULL Berlin

Note : Cette structure d’exemple sera utilisée tout au long de ce chapitre.

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 *

        FROM employees

                INNER JOIN departments

                    ON employees.depID = departments.depID

                INTO :aName, :aEmpDepID, :aDepID, :aDepName;

 Fin SQL

Résultat de cette jointure :

aName	aEmpDepID	aDepID	aDepName
Alain	10	10	Program
Anne	11	11	Engineering
Bernard	10	10	Program
Fabrice	12	12	Development
Thomas	10	10	Program

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).

Jointures croisées (CROSS)

Une jointure interne dans laquelle la clause WHERE et la clause ON sont omises est appelée jointure croisée ou cartésienne. Effectuer une jointure croisée consiste à associer chaque ligne d’une table à chaque ligne d’une autre table.
Le résultat d’une jointure croisée est le produit cartésien des tables, contenant m x n lignes, où m est le nombre de lignes de la première table et n est le nombre de lignes de la seconde table. Ce produit représente l’ensemble des combinaisons possibles formées par la concaténation des lignes des tables.

Les syntaxes suivantes sont équivalentes :

    SELECT * FROM T1 INNER JOIN T2
    SELECT * FROM T1, T2
    SELECT * FROM T1 CROSS JOIN T2;

Voici un exemple de code 4D intégrant une jointure croisée :

 TABLEAU TEXTE(aName;0)

 TABLEAU TEXTE(aDepName;0)

 TABLEAU ENTIER(aEmpDepID;0

 TABLEAU ENTIER(aDepID;0)

 Debut SQL

        SELECT *

        FROM employees CROSS JOIN departments

                INTO :aName, :aEmpDepID, :aDepID, :aDepName;

 Fin SQL

Résultat de cette jointure avec notre base d’exemple :

aName	aEmpDepID	aDepID	aDepName
Alain	10	10	Program
Anne	11	10	Program
Bernard	10	10	Program
Fabrice	12	10	Program
Martine	15	10	Program
Philippe	NULL	10	Program
Thomas	10	10	Program
Alain	10	11	Engineering
Anne	11	11	Engineering
Bernard	10	11	Engineering
Fabrice	12	11	Engineering
Martine	15	11	Engineering
Philippe	NULL	11	Engineering
Thomas	10	11	Engineering
Alain	10	NULL	Marketing
Anne	11	NULL	Marketing
Bernard	10	NULL	Marketing
Fabrice	12	NULL	Marketing
Martine	15	NULL	Marketing
Philippe	NULL	NULL	Marketing
Thomas	10	NULL	Marketing
Alain	10	12	Development
Anne	11	12	Development
Bernard	10	12	Development
Fabrice	12	12	Development
Martine	15	12	Development
Philippe	NULL	12	Development
Thomas	10	12	Development
Alain	10	13	Quality
Anne	11	13	Quality
Bernard	10	13	Quality
Fabrice	12	13	Quality
Martine	15	13	Quality
Philippe	NULL	13	Quality
Thomas	10	13	Quality

Note : Pour des raisons de performances, les jointures croisées sont à utiliser avec précaution.

Jointures externes

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.

Jointures externes gauches (LEFT OUTER JOIN)

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 *

            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) :

aName	aEmpDepID	aDepID	aDepName
Alain	10	10	Program
Anne	11	11	Engineering
Bernard	10	10	Program
Fabrice	12	12	Development
Thomas	10	10	Program
Martine	15	NULL	NULL
Philippe	NULL	NULL	NULL

Jointures externes droites (RIGHT OUTER JOIN)

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 *

            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) :

aName	aEmpDepID	aDepID	aDepName
Alain	10	10	Program
Anne	11	11	Engineering
Bernard	10	10	Program
Fabrice	12	12	Development
Thomas	10	10	Program
NULL	NULL	NULL	Marketing
NULL	NULL	13	Quality

Jointures externes bilatérales (FULL OUTER JOIN)

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 *

            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) :

aName	aEmpDepID	aDepID	aDepName
Alain	10	10	Program
Anne	11	11	Engineering
Bernard	10	10	Program
Fabrice	12	12	Development
Thomas	10	10	Program
Martine	15	NULL	NULL
Philippe	NULL	NULL	NULL
NULL	NULL	NULL	Marketing
NULL	NULL	13	Quality

Jointures multiples dans une seule requête

Il est possible de combiner plusieurs jointures dans une même instruction SELECT. Il est également possible de mixer des jointures internes implicites ou explicites et des jointures externes explicites.

Voici un exemple de code 4D effectuant des jointures multiples :

 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 *

            FROM (employees RIGHT OUTER JOIN departments

                        ON employees.depID = departments.depID)

                        LEFT OUTER JOIN cities

                        ON employees.cityID = cities.cityID

            INTO :aName, :aEmpDepID, :aEmpCityID, :aDepID, :aDepName, :aCityID, :aCityName;

 Fin SQL

Résultat de cette jointure avec notre base d’exemple :

aName	aEmpDepID	aEmpCityID	aDepID	aDepName	aCityID	aCityName
Alain	10	30	10	Program	30	Paris
Anne	11	39	11	Engineering	0
Bernard	10	33	10	Program	33	New York
Fabrice	12	35	12	Development	0
Thomas	10	NULL	10	Program	0