Les jointures externes, aspects basiques
Lors d’une migration d’Oracle Database vers PostgreSQL, il faut abandonner la syntaxe propriétaire Oracle pour les jointures externes.
Dans cet exemple, nous allons utiliser la table GEANTS et la table LANCERS. Nous allons ramener la liste des géants et de leurs lancers, même si les géants n’ont jamais lancé.
Démonstration avec Oracle Database 12.2 :
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
create table geants(idg smallint, nmg character varying(16));
Table created.
create table lancers(dtl timestamp, idg smallint, perf integer);
Table created.
insert into geants(idg, nmg) values(1, 'OUMPFOR');
1 row created.
insert into geants(idg, nmg) values(2, 'KILIN');
1 row created.
insert into lancers(dtl, idg, perf) values(current_timestamp, 1, 30000);
1 row created.
insert into lancers(dtl, idg, perf) values(current_timestamp, 0, 60000);
1 row created.
commit;
SELECT nmg, perf
FROM geants, lancers
WHERE geants.idg = lancers.idg (+);
NMG PERF
---------------- ----------
OUMPFOR 30000
KILIN
Le fameux (+). "Il faut mettre le (+) du côté où il y en a le moins, où il manque des infos que je veux voir dans les résultats". Certains géants n’ont pas lancé mais je veux tous les géants donc je mets le (+) du côté de lancers.idg.
A présent, place à PostgreSQL dans sa version 9.6 :
select version();
version
---------------------------------------------------------------------------------------------------
PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit
create table geants(idg smallint, nmg character varying(16));
CREATE TABLE
create table lancers(dtl timestamp, idg smallint, perf integer);
CREATE TABLE
insert into geants(idg, nmg) values(1, 'OUMPFOR'), (2, 'KILIN');
INSERT 0 2
insert into lancers(dtl, idg, perf) values(current_timestamp, 1, 30000), (current_timestamp, 0, 60000);
INSERT 0 2
commit;
COMMIT
SELECT nmg, perf
FROM geants, lancers
WHERE geants.idg = lancers.idg (+);
ERREUR: erreur de syntaxe sur ou près de " ) "
LIGNE 3 : WHERE geants.idg = lancers.idg (+);
SELECT nmg, perf
FROM geants LEFT OUTER JOIN lancers on (geants.idg = lancers.idg);
nmg | perf
---------+-------
OUMPFOR | 30000
KILIN |
SELECT nmg, perf
FROM lancers RIGHT OUTER JOIN geants on (lancers.idg = geants.idg);
nmg | perf
---------+-------
OUMPFOR | 30000
KILIN |
Comme prévu, PostgreSQL ne veut rien savoir de la syntaxe propriétaire d’Oracle. Nous allons "ouvrir la jointure du côté où nous voulons garder les lignes".
Nous voulons ici tous les géants. Donc, si nous écrivons GEANTS puis LANCERS dans la clause FROM, il s’agira d’une jointure externe gauche (LEFT OUTER JOIN) alors que si nous écrivons LANCERS puis GEANTS dans la clause FROM il s’agira d’une jointure externe droite (RIGHT OUTER JOIN). Notez que le mot clé OUTER pourrait être omis.
Oracle Database permet toujours d’utiliser la syntaxe propriétaire avec (+) mais permet bien sûr d’utiliser la syntaxe SQL92 depuis la 9i. Démonstration, toujours avec Oracle 12.2 :
SELECT nmg, perf
FROM geants LEFT OUTER JOIN lancers on (geants.idg = lancers.idg);
NMG PERF
---------------- ----------
OUMPFOR 30000
KILIN
SELECT nmg, perf
FROM lancers RIGHT OUTER JOIN geants on (lancers.idg = geants.idg);
nmg | perf
---------+-------
OUMPFOR | 30000
KILIN |
Pas de problème. A présent, nous allons encore joindre GEANTS et LANCERS, nous voulons toujours obtenir les géants n’ayant jamais lancé mais aussi les lancers orphelins ne pouvant plus être attribués à un géant :
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SELECT nmg, perf
FROM geants, lancers
WHERE geants.idg (+) = lancers.idg (+);
ERROR at line 3:
ORA-01468: a predicate may reference only one outer-joined table
SELECT nmg, perf
FROM geants, lancers
WHERE geants.idg = lancers.idg(+)
UNION ALL
SELECT
nmg, perf
FROM geants, lancers
WHERE geants.idg (+) = lancers.idg AND geants.idg IS NULL;
NMG PERF
---------------- ----------
OUMPFOR 30000
KILIN
60000
SELECT nmg, perf
FROM lancers FULL OUTER JOIN geants on (lancers.idg = geants.idg);
NMG PERF
---------------- ----------
OUMPFOR 30000
KILIN
60000
select version();
version
---------------------------------------------------------------------------------------------------
PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit
SELECT nmg, perf
FROM lancers FULL OUTER JOIN geants on (lancers.idg = geants.idg);
nmg | perf
---------+-------
| 60000
OUMPFOR | 30000
KILIN |
La syntaxe propriétaire Oracle ne permet pas directement d’obtenir le résultat (impossible de mettre des (+) de chaque côté), ce qui rend l’écriture plus complexe.
La syntaxe standard SQL est de son côté très simple, en gros LEFT + RIGHT = FULL. Encore une fois le mot clé OUTER pourrait être omis.
Conclusion
Vous devrez abandonner la syntaxe propriétaire Oracle au passage à PostgreSQL. Des cas simples ont été proposés dans cette page, apprenez à éviter les pièges si vous souhaitez aller plus loin.
Mise à jour : 05/06/2017