Jointures externes Oracle dans la clause WHERE

PDFRSS

Pour des raisons de compatibilité Oracle, Amazon Redshift prend en charge l’opérateur de jointure externe Oracle (+) dans les conditions de clause WHERE. Cet opérateur doit être utilisé uniquement dans la définition de conditions de jointure externe ; n’essayez pas de l’utiliser dans d’autres contextes. Les autres utilisations de cet opérateur sont automatiquement ignorées dans la plupart des cas.

Une jointure externe renvoie toutes les lignes que la jointure interne équivalente renvoie, plus les lignes non correspondantes d’une ou de deux tables. Dans la clause FROM, vous pouvez spécifier des jointures externes gauches, droites et complètes. Dans la clause WHERE, vous ne pouvez spécifier que des jointures externes gauches et droites.

Pour les tables de jointure externes TABLE1 TABLE2 et pour renvoyer des lignes non correspondantes depuis TABLE1 (une jointure externe gauche), spécifiez-le TABLE1 LEFT OUTER JOIN TABLE2 dans la clause FROM ou appliquez l'opérateur (+) à toutes les colonnes de jointure depuis TABLE2 la clause WHERE. Pour toutes les lignes TABLE1 qui ne contiennent aucune ligne correspondante TABLE2, le résultat de la requête contient des valeurs nulles pour toutes les expressions de liste de sélection contenant des colonnes provenant de TABLE2.

Pour produire le même comportement pour toutes les lignes TABLE2 qui ne contiennent aucune ligne correspondante TABLE1, spécifiez-le TABLE1 RIGHT OUTER JOIN TABLE2 dans la clause FROM ou appliquez l'opérateur (+) à toutes les colonnes jointes depuis TABLE1 la clause WHERE.

Syntaxe de base

[ WHERE {
[ table1.column1 = table2.column1(+) ]
[ table1.column1(+) = table2.column1 ]
}

La première condition est équivalente à :

from table1 left outer join table2
on table1.column1=table2.column1

La deuxième condition est équivalente à :

from table1 right outer join table2
on table1.column1=table2.column1

Note

La syntaxe présentée ici couvre le cas simple d’une équijointure sur une paire de colonnes de jointure. Cependant, d’autres types de conditions de comparaison et plusieurs paires de colonnes de jointure sont également valides.

Par exemple, la clause WHERE suivante définit une jointure externe sur deux paires de colonnes. L’opérateur (+) doit être associé à la même table dans les deux conditions :

where table1.col1 > table2.col1(+)
and table1.col2 = table2.col2(+)

Notes d’utilisation

Si possible, utilisez la syntaxe OUTER JOIN de la clause standard FROM au lieu de l’opérateur (+) dans la clause WHERE. Les requêtes qui contiennent l’opérateur (+) sont soumises aux règles suivantes :

• Vous ne pouvez utiliser que l’opérateur (+) dans la clause WHERE, et uniquement en référence aux colonnes des tables ou des vues.

• Vous ne pouvez pas appliquer l’opérateur (+) aux expressions. Cependant, une expression peut contenir des colonnes qui utilisent l’opérateur (+). Par exemple, la condition de jointure suivante renvoie une erreur de syntaxe :

  event.eventid*10(+)=category.catid

  Cependant, la condition de jointure suivante est valide :

  event.eventid(+)*10=category.catid

• Vous ne pouvez pas utiliser l’opérateur (+) dans un bloc de requête qui contient également la syntaxe de jointure de la clause FROM.

• Si deux tables sont jointes sur plusieurs conditions de jointure, vous devez utiliser l’opérateur (+) dans la totalité de ces conditions ou dans aucune d’entre elles. Une jointure avec des styles de syntaxe mixtes est exécutée comme jointure interne, sans avertissement.

• L’opérateur (+) ne génère pas une jointure externe si vous joignez une table de la requête externe à une table qui résulte d’une requête interne.

• Pour utiliser l’opérateur (+) et créer une jointure externe d’une table avec elle-même, vous devez définir les alias de table dans la clause FROM et les référencer dans la condition de jointure :

  select count(*)
  from event a, event b
  where a.eventid(+)=b.catid;
  
  count
  -------
  8798
  (1 row)

• Vous ne pouvez pas associer une condition de jointure contenant l’opérateur (+) avec une condition OR ou une condition IN. Par exemple :

  select count(*) from sales, listing
  where sales.listid(+)=listing.listid or sales.salesid=0;
  ERROR:  Outer join operator (+) not allowed in operand of OR or IN.

• Dans une clause WHERE qui crée une jointure externe avec plus de deux tables, l’opérateur (+) ne peut être appliqué qu’une seule fois à une table donnée. Dans l’exemple suivant, la table SALES ne peut pas être référencée par l’opérateur (+) dans deux jointures successives.

  select count(*) from sales, listing, event
  where sales.listid(+)=listing.listid and sales.dateid(+)=date.dateid;
  ERROR:  A table may be outer joined to at most one other table.

• Si la condition de jointure externe de la clause WHERE compare une colonne à TABLE2 une constante, appliquez l'opérateur (+) à la colonne. Si vous n'incluez pas l'opérateur, les lignes jointes à l'extérieur de TABLE1, qui contiennent des valeurs nulles pour la colonne restreinte, sont éliminées. Consultez la section Exemples ci-dessous.

Exemples

La requête de jointure suivante spécifie une jointure externe gauche des tables SALES et LISTING, sur leurs colonnes LISTID :

select count(*)
from sales, listing
where sales.listid = listing.listid(+);

count
--------
172456
(1 row)

La requête équivalente suivante produit le même résultat, mais utilise la syntaxe de jointure de la clause FROM :

select count(*)
from sales left outer join listing on sales.listid = listing.listid;

count
--------
172456
(1 row)

La table SALES ne contient pas d’enregistrements pour toutes les listes de la table LISTING, car certaines listes ne se traduisent pas par des ventes. La requête suivante crée une jointure externe de SALES et de LISTING, et renvoie les lignes de LISTING même lorsque la table SALES ne rapporte aucune vente pour un ID de liste donné. Les colonnes PRICE et COMM, dérivées de la table SALES, contiennent des valeurs null dans le jeu de résultats pour ces lignes sans correspondance.

select listing.listid, sum(pricepaid) as price,
sum(commission) as comm
from listing, sales
where sales.listid(+) = listing.listid and listing.listid between 1 and 5
group by 1 order by 1;

listid | price  |  comm
--------+--------+--------
1 | 728.00 | 109.20
2 |        |
3 |        |
4 |  76.00 |  11.40
5 | 525.00 |  78.75
(5 rows)

Notez que lorsque l’opérateur de jointure de la clause WHERE est utilisé, l’ordre des tables dans la clause FROM n’importe pas.

L’exemple d’une condition de jointure externe plus complexe dans la clause WHERE est celui où la condition se compose d’une comparaison entre deux Colonnes de la table et d’une comparaison avec une constante :

where category.catid=event.catid(+) and eventid(+)=796;

Notez que l’opérateur (+) est utilisé à deux emplacements : d’abord dans la comparaison d’égalité entre les tables et ensuite dans la condition de comparaison pour la colonne EVENTID. Le résultat de cette syntaxe est la conservation des lignes de jointure externe lors de l’évaluation de la restriction sur EVENTID. Si vous supprimez l’opérateur (+) de la restriction EVENTID, la requête traite cette restriction comme filtre, pas dans le cadre de la condition de jointure externe. A leur tour, les lignes de jointure externe qui contiennent des valeurs null pour EVENTID sont éliminées du jeu de résultats.

Voici une requête complète qui illustre ce comportement :

select catname, catgroup, eventid
from category, event
where category.catid=event.catid(+) and eventid(+)=796;

catname | catgroup | eventid
-----------+----------+---------
Classical | Concerts |
Jazz | Concerts |
MLB | Sports   |
MLS | Sports   |
Musicals | Shows    | 796
NBA | Sports   |
NFL | Sports   |
NHL | Sports   |
Opera | Shows    |
Plays | Shows    |
Pop | Concerts |
(11 rows)

La requête équivalente à l’aide de la syntaxe de la clause FROM est la suivante :

select catname, catgroup, eventid
from category left join event
on category.catid=event.catid and eventid=796;

Si vous supprimez le deuxième opérateur (+) de la version de la clause WHERE de cette requête, elle renvoie uniquement 1 ligne (celle où eventid=796).

select catname, catgroup, eventid
from category, event
where category.catid=event.catid(+) and eventid=796;

catname | catgroup | eventid
-----------+----------+---------
Musicals | Shows    | 796
(1 row)