Problème syntaxe - Jointure reflexive

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:

---

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.

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:

---

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

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

Citation:

---

Envoyé par escartefigue

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 +

Citation:

---

Envoyé par SQLpro

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.

Citation:

---

Envoyé par escartefigue

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:

---

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 :roll:, ni catastrophique, ni moins intelligent...

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)

Citation:

---

Envoyé par pachot

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 +

Citation:

---

Envoyé par escartefigue

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:

---

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.

Citation:

---

Envoyé par SQLpro

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


:roll: 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?

Citation:

---

Envoyé par pachot

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

Citation:

---

Envoyé par escartefigue

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

---

8O 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

Citation:

---

Envoyé par escartefigue

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.

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

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:

---

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:

---

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:

---

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

Citation:

---

Envoyé par escartefigue

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

---

La question est:

Citation:

---

Envoyé par preliator

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.

Citation:

---

Envoyé par escartefigue

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.