table jointe

Une table jointe spécifie une table de résultats intermédiaire qui est le résultat d'une jointure interne ou d'une jointure externe. La table est dérivée en appliquant l'un des opérateurs de jointure: CROSS, INNER, LEFT OUTER, RIGHT OUTER ou FULL OUTER à ses opérandes.

outer

column-list

Il existe plusieurs types de jointures:

Cross join

Représente le produit croisé des tableaux, où chaque ligne de la table de gauche est combinée avec chaque ligne de la table de droite.

Inner join

Conserve uniquement les lignes pour lesquelles la condition de jointure est vraie. Les lignes de l'une ou l'autre des tables jointes pour lesquelles la condition de jointure est fausse sont exclues de la table de résultats.

Outer join

Contient les lignes pour lesquelles la condition de jointure est vraie, plus les lignes supplémentaires:

Left outer join: Inclut également les lignes de la table de gauche pour lesquelles la condition de jointure est false.
Right outer join: Inclut également les lignes de la table de droite pour lesquelles la condition de jointure est false.
Full outer join: Inclut également les lignes des tables de gauche et de droite pour lesquelles la condition de jointure est fausse.

L'ordre dans lequel plusieurs jointures sont effectuées peut affecter le résultat. Les jointures peuvent être imbriquées dans d'autres jointures. L'ordre de traitement des jointures est généralement de gauche à droite, mais en fonction de la position de la condition de jointure requise. Vous pouvez utiliser des parenthèses pour clarifier l'ordre des jointures imbriquées. Par exemple, les deux instructions suivantes sont équivalentes:

   T1 LEFT JOIN T2 ON T1.C1=T2.C1
     RIGHT JOIN T3 LEFT JOIN T4 ON T3.C1=T4.C1
       ON T1.C1=T3.C1

   (T1 LEFT JOIN T2 ON T1.C1=T2.C1)
     RIGHT JOIN (T3 LEFT JOIN T4 ON T3.C1=T4.C1)
       ON T1.C1=T3.C1

Une table jointe peut être utilisée dans n'importe quel contexte dans lequel n'importe quelle forme de l'instruction SELECT est utilisée. Une vue ou un curseur est en lecture seule si son instruction SELECT inclut une table jointe.

Les références de colonne sont résolues à l'aide des règles de résolution des qualificateurs de nom de colonne. Les mêmes règles qui s'appliquent aux prédicats s'appliquent aux conditions de jointure.

Opérations de jointure

Lorsqu'une ligne de T1 est jointe à une ligne de T2, une ligne du résultat est constituée des valeurs de cette ligne de T1 concaténées avec les valeurs de cette ligne de T2. Une opération de jointure peut entraîner la génération d'une ligne nulle. Une ligne null se compose d'une valeur null pour chaque colonne d'une table, que les valeurs null soient autorisées ou non dans les colonnes.

La liste suivante récapitule le résultat des opérations de jointure:

Le résultat de T1 CROSS JOIN T2 est constitué de toutes les paires possibles de leurs lignes.
Le résultat de T1 INNER JOIN T2 est constitué de leurs lignes appariées où la condition de jointure est vraie.
Le résultat de T1 LEFT OUTER JOIN T2 est constitué de leurs lignes appariées où la condition de jointure est vraie et, pour chaque ligne non appariée de T1, la concaténation de cette ligne avec la ligne null de T2. Les valeurs NULL sont autorisées dans toutes les colonnes dérivées de T2.
Le résultat de T1 RIGHT OUTER JOIN T2 est constitué de leurs lignes appariées où la condition de jointure est vraie et, pour chaque ligne non appariée de T2, la concaténation de cette ligne avec la ligne null de T1. Les valeurs nulles sont autorisées dans toutes les colonnes dérivées de T1.
Le résultat de T1 FULL OUTER JOIN T2 se compose de leurs lignes appariées et, pour chaque ligne non appariée de T2, de la concaténation de cette ligne avec la ligne nulle de T1 et, pour chaque ligne non appariée de T1, de la concaténation de cette ligne avec la ligne nulle de T2. Les valeurs null sont autorisées dans toutes les colonnes dérivées de T1 et T2.

Conditions de jointure

Une condition de jointure est une expression de qualification qui implique les deux tables à joindre. Elle spécifie les appariements de t1 et t2, où t1 et t2 représentent les noms des tables d'opérandes de gauche (t1) et de droite (t2) de l'opérateur JOIN. Pour toutes les combinaisons possibles de lignes de t1 et t2, une ligne de t1 est appariée avec une ligne de t2 si la condition de jointure est vraie.

Une condition de jointure est similaire à une condition de recherche, sauf que:

Elle ne peut pas inclure d'opérations de déréférencement ou la fonction DEREF, où la valeur de référence est différente de la colonne d'identificateur d'objet
Toute colonne référencée dans une expression de la condition de jointure doit être une colonne de l'une des tables d'opérandes de la jointure associée (dans la portée de la même clause de table jointe)
Toute fonction référencée dans une expression de la condition de jointure d'une jointure externe complète doit être déterministe et ne pas avoir d'action externe
Il ne peut pas inclure d'expression XMLQUERY ou XMLEXISTS

Une condition de jointure doit respecter ces règles (SQLSTATE 42972).

Résultat de la jointure

Vous pouvez déterminer quelles colonnes sont incluses dans le résultat de la jointure en spécifiant l'une des clauses suivantes:

Activation<join-condition>

Cette clause peut spécifier toute expression de qualification qui implique les deux tables à joindre. Exemple :

SELECT * FROM t1 JOIN t2 ON t1.c1 = t2.c1 AND t1.c2 = t2.c2;

Le résultat de la jointure contient toutes les colonnes de t1 suivies de toutes les colonnes de t2.

utilisation<column-list>

Cette clause joint les tables sur les colonnes spécifiées. Chaque colonne existe dans les deux tables à joindre. Les tables sont jointes lorsque la valeur d'une colonne est la même dans les deux tables. Exemple :

SELECT * FROM t1 JOIN t2 USING (c1, c2);

Les colonnes du résultat de la jointure dépendent du type de jointure:

For an inner join or left outer join: Le résultat de la jointure contient les colonnes de jointure de t1, suivies des colonnes de non-jointure de t1, suivies des colonnes de non-jointure de t2.
For a right outer join: Le résultat de la jointure contient les colonnes de jointure de t2, suivies des colonnes de non-jointure de t1, suivies des colonnes de non-jointure de t2.
For a full outer join: Le résultat de la jointure comporte une valeur non nulle provenant des colonnes de jointure, suivie des colonnes de non-jointure provenant de t1, puis des colonnes de non-jointure provenant de t2.

Notez que les colonnes de jointure n'apparaissent qu'une seule fois dans le résultat de la jointure.

A moins qu'une liste de colonnes de projection ne soit explicitement spécifiée, l'ordre des colonnes dans la sortie est le même que dans le résultat de la jointure.

Toute référence non qualifiée ultérieure à une colonne de jointure d'une clause USING par une autre clause (telle qu'une clause WHERE, ON, ORDER BY, GROUP BY ou HAVING) est résolue en colonne à partir du résultat de la jointure. Exemple :

CREATE TABLE t1 (c1 int, c2 varchar(10), c3 numeric(4,2));
CREATE TABLE t2 (c1 bigint, c2 char(8), c4 numeric(6,3));
CREATE TABLE t3 (c3 bigint, c5 int, c6 numeric(6,3));

SELECT * FROM t1 FULL JOIN t2 USING (c1, c2) JOIN t3 ON (c1 = t3.c3);
Column projections: 
  CASE WHEN (t1.c1 IS NOT NULL) THEN t1.c1 ELSE t2.c1 END AS c1
  CASE WHEN (t1.c2 IS NOT NULL) THEN t1.c2 ELSE t2.c2 END AS c2
  t1.c3
  t2.c4
  t3.c3
  t3.c5
  t3.c6

La référence à la colonne c1 dans la clause ON se résout en l'expression CASE qui représente la colonne de jointure c1 à partir de la jointure externe complète. Ainsi, la clause ON est transformée en:

ON ((CASE WHEN (t1.c1 IS NOT NULL) THEN t1.c1 ELSE t2.c1 END) = t3.c3)

Les restrictions suivantes s'appliquent :

Les clauses ON et USING s'excluent mutuellement pour une opération de jointure particulière, c'est-à-dire qu'une seule de ces clauses peut être spécifiée lors de la jointure de deux tables. Toutefois, une seule instruction SQL peut contenir plusieurs opérations de jointure et chacune d'elles peut utiliser l'une ou l'autre de ces clauses.
Une clause ON ou USING ne peut pas être utilisée si un symbole plus (+) est utilisé comme opérateur de jointure externe.

Exemples

Supposons que les trois tables suivantes ont été créées:

CREATE TABLE t1 (c1 int, c2 varchar(10), c3 numeric(4,2));
CREATE TABLE t2 (c1 bigint, c2 char(8), c4 numeric(6,3));
CREATE TABLE t3 (c3 bigint, c5 int, c6 numeric(6,3));

Pour une jointure avec une clause ON:

SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1 AND t1.c2 = t2.c2;
Column projections: t1.c1, t1.c2, t1.c3, t2.c1, t2.c2, t2.c4

SELECT * FROM t1 FULL JOIN t2 ON t1.c1 = t2.c1 AND t1.c2 = t2.c2;
Column projections: t1.c1, t1.c2, t1.c3, t2.c1, t2.c2, t2.c4

Pour une jointure interne ou une jointure externe gauche avec une clause USING:

SELECT * FROM t1 INNER JOIN t2 USING (c1, c2);
Column projections: t1.c1, t1.c2, t1.c3, t2.c4

SELECT * FROM t1 LEFT JOIN t2 USING (c1, c2);
Column projections: t1.c1, t1.c2, t1.c3, t2.c4

Pour une jointure externe droite avec une clause USING:

SELECT * FROM t1 RIGHT JOIN t2 USING (c1, c2);
Column projections: t2.c1, t2.c2, t1.c3, t2.c4

Pour une jointure externe complète avec une clause USING:

SELECT * FROM t1 FULL JOIN t2 USING (c1, c2);
Column projections: 
  CASE WHEN (t1.c1 IS NOT NULL) THEN t1.c1 ELSE t2.c1 END AS c1
  CASE WHEN (t1.c2 IS NOT NULL) THEN t1.c2 ELSE t2.c2 END AS c2
  t1.c3
  t2.c4