Discussion :

[preliator]

Bonjour à tous,

Ayant commencé à apprendre les jointures avec la formule NATURAL JOIN, je me demandais quelle devait être la syntaxe d'une jointure reflexive cette dernière.

Ainsi, j'aimerais transformer cette requete qui affiche les employes et leur supérieur hiérarchique, avec une Natural join :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
select a.nom, a.prenom, b.nom as nom_superieur, b.prenom as prenom_superieur
from employe a, employe b
where (a.dependde = b.employeid)

Merci à vous.

[escartefigue]

bonjour,

Evitez les NATURAL JOIN c'est une jointure instable et donc très dangereuse : la jointure se fait automatiquement entre les colonnes de même nom des différentes tables ou vues, si les tables ou les vues utilisées sont modifiées pour y ajouter des colonnes, le résultat de la jointure peut changer. À fuir comme la peste donc.

Sinon, pour les jointures explicites, utilisez l'opérateur JOINcomme suit

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
select a.nom
     , a.prenom
     , b.nom as nom_superieur
     , b.prenom as prenom_superieur
from employe a
left join employe b
  on a.dependde = b.employeid

Ici j'ai utilisé une jointure OUTER car il est possible qu'une personne n'ait pas de supérieur (le big boss quoi )
Avec une jointure à l'ancienne telle que vous l'avez codée (jointure dans la clause WHERE) ou une jointure INNER JOIN qui est équivalente, seuls les employés qui ont un supérieur hiérarchique seront récupérés


Si vous voulez vous perfectionner sur les jointures, jetez un coup d'oeil ICI

[preliator]

Un grand merci à vous pour ces explications claires et précises

[SQLpro]

bonjour,

Evitez les NATURAL JOIN c'est une jointure instable et donc très dangereuse

Non seulement je plussois mais en sus les performances sont catastrophiques car cela provoque des jointures triangulaires quadrangulaire... qui sont totalement inoptimiSable, laissant le SGBDR faire des boucles imbriquées de boucles imbriquées de boucles imbriquées.....

Certaines SGBDR intelligent ont purement et simplement interdit le NATURAL JOIN (en particulier Microsoft SQL Server)....

A +

[pachot]

les performances sont catastrophiques

C'est une variante de syntaxe. Aucun rapport avec la performance. Le plan d'exécution sera toujours une jointure et la méthode sera choisie par l'optimiseur.

c'est une jointure instable et donc très dangereuse

Non, pas si on maitrise les projections


La question est: comment écrire la même requête avec un natural join.

La réponse est:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
select nom, prenom, nom_superieur, prenom_superieur
from (
 select nom, prenom
 ,dependde
 from employe
) natural inner join (
 select nom as nom_superieur, prenom as prenom_superieur
 ,employeid as dependde
 from employe
) superieur

On peut trouver ceci plus lisible, ou moins, mais ce n'est ni dangereux, ni instable, ni triangulaire quadrangulaire , ni catastrophique, ni moins intelligent...

[escartefigue]

Utiliser des tables dérivées pour contrer les défauts d'une jointure "naturelle" est une astuce amusante, mais je doute qu'elle corresponde à ce que l'on rencontre dans la nature
Et je n'en vois pas l'intérêt : c'est ajouter une étape supplémentaire, rendre l'écriture de la requête plus lourde et utiliser une syntaxe non supportée par tous les SGBD (NATURAL JOIN n'est pas implémenté partout)

[SQLpro]

C'est une variante de syntaxe. Aucun rapport avec la performance. Le plan d'exécution sera toujours une jointure et la méthode sera choisie par l'optimiseur.

Non, pas si on maitrise les projections

Franck, je t'aime bien, mais ce que tu dis est faux....

En effet, ce que tu affirme peut être vrai, si 3 conditions sont respectées :
1) toutes les tables sont liées par l'intégrité référentielles déclarative entre les références et les clef étrangères
2) toutes les jointures sont établies par rapport aux liens d'intégrité référentielle déclarative et non sur des colonnes "libres"
3) l'optimiseur sait tirer partie des contraintes d'intégrité référentielle déclarative (optimisation sémantique)

Dans tous les autres cas, c'est catastrophique !

A +

[pachot]

Utiliser des tables dérivées pour contrer les défauts d'une jointure "naturelle"

Il ne s'agit pas de tables dérivées. Seulement d'appliquer la projection avant la jointure. Avec cette écriture, aucun problème d'alias de tables (les 'a' et 'b' de la requête initiale qui ne sont pas très parlants car lorsqu'on doit les répeter devant toutes les colonnes, on se retrouve a prendre un nom abstrait court). Il peut être très logique et très lisible d'écrire une requête complexe de cette manière. Car on projette chaque colonne sur le résultat final au fur et à mesure qu'on déclare les tables.
Ici: je lis 'employé' en tant que subalterne dans mon résultat. Puis je lis 'employé' en tant que supérieur dans mon résultat. Et la jointure se fait toute seule.

Encore une fois, c'est au choix. Mais il y en a qui trouveront ceci beaucoup plus lisible et maintenable ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
with
subordonne as ( -- ceci est ma liste d'employés en tant que subordonné
 select nom, prenom
 , dependde as employeid_superieur
 from employe
),
superieur as ( -- ceci est ma liste d'employés en tant que supérieur
 select nom as nom_superieur, prenom as prenom_superieur
 ,employeid as employeid_superieur
 from employe
)
select nom, prenom, nom_superieur, prenom_superieur
from subordonne natural inner join superieur

Et même si l'écriture est différente, c'est exactement la même chose à l'exécution.

...contraintes d'intégrité référentielle déclarative...optimisation sémantique...

Il suffit de lire la doc (NATURAL is a shorthand form of USING) et tester (pour voir que le plan d'exécution est exactement le même):
https://dbfiddle.uk/?rdbms=postgres_...d07a4a1a87cad6


Il ne s'agit que de syntaxe différent pour exprimer la même sémantique. Bien avant une quelconque optimisation.


La question originale était d'écrire la requête avec un Natural Join. Pourquoi ne pas juste répondre à la question et laissez l'auteur juger si c'est plus lisible, ou plus dangereux. Au lieu de l'induire en erreur avec des opinions non vérifiées?

[escartefigue]

Au lieu de l'induire en erreur avec des opinions non vérifiées?

Oui et non, je n'ai pas PG sous la main, mais je l'ai revérifié ce matin sur SQLITE et le résultat n'est pas du tout le même dès qu'on modifie l'une des tables (si une des colonnes devient commune alors qu'elle ne l'était pas ou l'inverse)
J'avais fait l'expérience sur d'autres SGBD par le passé et pareil, dès que les noms des colonnes changent dans l'une des tables, le résultat en est modifié.
Je veux bien croire que PG fonctionne différemment, mais ce serait très surprenant

[pachot]

Oui et non, je n'ai pas PG sous la main

Mon lien, sur db<>fiddle permet de faire tous les tests online sur tous les SGBD...
Un clic et on a la même chose pour SQLLite: https://dbfiddle.uk/?rdbms=sqlite_3....d07a4a1a87cad6

les noms des colonnes changent dans l'une des tables, le résultat en est modifié.

Dans l'exemple que j'ai donné je liste les colonnes que j'utilise. Donc rien ne changera si on modifie la table. C'est nécessaire en utilisant natural join. On liste les colonnes de jointures dans toutes les syntaxes de jointures: dans le sous-select, ou dans le USING ou dans le ON, ou dans le WHERE... Ne pas nommer les colonnes c'est comme faire un "select *" ou ne pas préfixer les colonnes par l'alias de table: ça va pour une requête ad-hoc où on connait les colonnes. Mais pas pour une requête dans un programme car le modèle peut évoluer.

[escartefigue]

Génial ce lien, je le conserve précieusement, merci

Je pense que nous sommes d'accord, mais tu as mal lu mon intervention précédente que je reformule : sauf à utiliser un artifice pour s'affranchir des noms de colonnes, NATURAL JOIN produit un résultat hasardeux.

Or, si tu reprends le post initial, tu constates que la jointure est effectuée directement, le résultat est donc instable.

Voici (en utilisant ton lien) un résultat avec PG 10

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
with t1 (C1, C2) as
    (select 1, 123
     union all
     select 2, 444
     union all
     select 3, 080
     union all
     select 4, 222
    )
   , T2 (C1, CX) as
    (select 1, 123
     union all
     select 1, 006
     union all
     select 2, 555
     union all
     select 3, 120
     union all
     select 3, 250
    )
select *
from t1
natural join T2
;

Résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
c1	c2
1	123

La même requête NATURAL JOIN en modifiant le nom de la colonne dans T2 : C2 devient CX

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
c1	c2	cx
1	123	123
1	123	6
2	444	555
3	80	120
3	80	250

on est donc bien contraint de contourner la faille du NATURAL JOIN pour éviter ce phénomène

[pachot]

Or, si tu reprends le post initial, tu constates que la jointure est effectuée directement

La question est:

j'aimerais transformer cette requete qui affiche les employes et leur supérieur hiérarchique, avec une Natural join

La réponse est: avec projection avant la jointure.

sauf à utiliser un artifice pour s'affranchir des noms de colonnes, NATURAL JOIN produit un résultat hasardeux.

On est sur une jointure avec la même table donc bien sûr toutes les colonnes ont le même nom mais pas le même rôle. Ce n'est pas un artifice ni un contournement: il faut projeter les noms de colonnes sur leur role. Soit en préfixant par l'alias de table dans le prédicat du JOIN ... ON, soit par alias de colonne et NATURAL JOIN.