Quand on fait une jointure externe à gauche et qu’on pose une restriction (dans le WHERE) sur la table de droite, c’est comme si on faisait une jointure interne !
Explication avec un exemple simple…
Soit les tables suivantes :
Table A
idA, valA
1, ‘toto’
2, ‘titi’
3, ‘tata’
Table B
idB, idA, valB, booleen
1, 2, ‘riri’, 0
2, 3, ‘fifi’, 1
Faisons une jointure externe à gauche sans condition :
=> Code :
1 2 3 | SELECT A.idA, A.valA, B.idB, B.valB, B.booleen FROM A LEFT OUTER JOIN B ON B.idA = A.idA |
=> Résultat :
idA, valA, idB, valB, booleen
1, ‘toto’, NULL, NULL, NULL
2, ‘titi’, 1, ‘riri’, 0
3, ‘tata’, 2, ‘fifi’, 1
Ajoutons une restriction dans le WHERE sur la table B :
=> Code :
1 2 3 4 | SELECT A.idA, A.valA, B.idB, B.valB, B.booleen FROM A LEFT OUTER JOIN B ON B.idA = A.idA WHERE B.booleen = 1 |
=> Résultat :
idA, valA, idB, valB, booleen
3, ‘tata’, 2, ‘fifi’, 1
La seule ligne qui répond à la condition est celle de fifi. On n’a plus toutes les lignes de A.
J’aurais obtenu le même résultat avec une jointure interne. Démonstration…
1ère requête sans WHERE
=> Code :
1 2 3 | SELECT A.idA, A.valA, B.idB, B.valB, B.booleen FROM A INNER JOIN B ON B.idA = A.idA |
=> Résultat :
idA, valA, idB, valB, booleen
2, ‘titi’, 1, ‘riri’, 0
3, ‘tata’, 2, ‘fifi’, 1
2ème requête avec le WHERE :
=> Code :
1 2 3 4 | SELECT A.idA, A.valA, B.idB, B.valB, B.booleen FROM A INNER JOIN B ON B.idA = A.idA WHERE B.booleen = 1 |
=> Résultat :
idA, valA, idB, valB, booleen
3, ‘tata’, 2, ‘fifi’, 1
Reprenons maintenant la jointure externe et mettons la restriction dans la clause de jointure :
=> Code :
1 2 3 4 | SELECT A.idA, A.valA, B.idB, B.valB, B.booleen FROM A LEFT OUTER JOIN B ON B.idA = A.idA AND B.booleen = 1 |
=> Résultat :
idA, valA, idB, valB, booleen
1, ‘toto’, NULL, NULL, NULL
2, ‘titi’, NULL, NULL, NULL
3, ‘tata’, 2, ‘fifi’, 1
La jointure est faite sur les données restreintes au booleen = 1, c’est à dire seulement fifi mais toutes les lignes de A sont affichées, avec NULL dans les colonnes de B quand il n’y a pas de correspondance.
Conclusion :
Avec une jointure externe, il faut déplacer les conditions de restriction sur la table externe du WHERE vers la condition de jointure. Sinon c’est l’équivalent d’une jointure interne.
Vous écrivez :
— Début de citation —
Reprenons maintenant la jointure externe et mettons la restriction dans la clause de jointure :
Code :
SELECT A.idA, A.valA, B.idB, B.valB, B.booleen
FROM A
LEFT OUTER JOIN B ON B.idA = A.idA AND B.booleen = 1
Résultat :
idA, valA, idB, valB, booleen
1, ‘toto’, NULL, NULL, NULL
2, ‘titi’, NULL, NULL, NULL
3, ‘tata’, 2, ‘fifi’, 1
La jointure est faite sur les données restreintes au booleen = 1, c’est à dire seulement fifi mais toutes les lignes de A sont affichées, avec NULL dans les colonnes de B quand il n’y a pas de correspondance.
— Fin de citation —
En donnant la possibilité de déplacer la condition de restriction « B.booleen = 1 », la norme SQL et votre SGBD vous permettent de violer l’esprit et la lettre de la jointure externe telle que l’a définie Chris Date de façon très rigoureuse (cf. http://www.developpez.net/forums/d1027725/bases-donnees/langage-sql/probleme-requete-sql-complique-mysql/#post5729264), soit. Mais, en tant qu’utilisateur, quelle interprétation en français dois-je donner de NULL tel qu’il figure dans le résultat : « Inconnu » ? « Sans objet » ? « N’existe pas » ? « Vide » ? … ?
En l’occurrence, puisque NULL marque ici l’absence de ligne dans B satisfaisant la condition de jointure et sa restriction, je dirais plutôt « N’existe pas » : Il n’existe pas de ligne dans B pour Toto et Titi avec booleen = 1
Je voulais dire à l’intersection de chaque paire ligne, colonne. Par exemple, comment interpréter NULL pour la colonne idB (ligne 1), pour la colonne idB (ligne 2), pour la colonne valB (ligne 1), pour la colonne valB (ligne 2), pour la colonne booleen (ligne 1), pour la colonne booleen (ligne 2) ?
Où veux-tu en venir ?
Il me semble que c’est le comportement normal d’un SGBDR basé sur le langage SQL d’afficher NULL pour toutes les colonnes de la table externe quand il n’y a pas de correspondance satisfaisant la condition de jointure, éventuellement augmentée d’une condition de restriction comme c’est le cas ici.
Je pense donc qu’il ne faut pas chercher un sens à chaque NULL affiché mais à l’ensemble des NULL d’une ligne de résultat pour l’ensemble des colonnes issues de la table externe.
D’ailleurs, si on cherche justement les lignes de la table interne (ici A) qui n’ont pas de correspondance dans la table externe (ici B) on peut faire une jointure externe et restreindre aux NULL sur l’une des colonnes de la table externe… ou faire une requête avec une condition WHERE NOT EXISTS qui signifie bien « il n’existe pas » !
Chercher un sens global, unique, pour l’ensemble des NULL d’une ligne du résultat ? (c’est-à -dire pour l’ensemble des colonnes issues de la table externe) : pourquoi pas, mais à condition de remplacer NULL par quelque chose de compréhensible par l’utilisateur, et qui traduise de façon claire l’alternative :
— Ou bien la condition A.idA = B.idA n’est pas vérifiée (cas de la ligne 1, celle qui correspond à toto), c’est-à -dire que la jointure externe a joué son rôle consistant à récupérer les lignes qui autrement seraient ignorées par la jointure interne ;
— Ou bien la condition B.booleen = 1 n’est pas vérifiée (cas de la ligne 2, celle qui correspond à titi), c’est-à -dire que la restriction a joué son rôle à son tour.
Un peu plus loin, je propose un exemple en ce sens. Cela dit, concernant le résultat :
idA, valA, idB, valB, booleen
1, ‘toto’, NULL, NULL, NULL
2, ‘titi’, NULL, NULL, NULL
3, ‘tata’, 2, ‘fifi’, 1
J’estime que c’est le fruit du mariage de la carpe et du lapin et qu’il relève donc de la tératologie. En effet, on met dans le même sac les résultats de deux besoins distincts. Pourquoi ne pas les isoler ? Pour prendre un exemple concret, supposons que la table A représente les clients et la table B les commandes, la colonne booleen permettant par exemple de savoir si une commande a été satisfaite (booleen = 1).
1) Pour répondre au 1er besoin : « Qui sont les clients ? » il suffit de coder une 1re requête :
SELECT idA, valA
FROM A ;
=>
idA, valA
1, ‘toto’
2, ‘titi’
3, ‘tata’
2) Pour répondre au 2e besoin : « Quels sont les fournisseurs dont les commandes sont satisfaites et quelles sont ces commandes ? » :
SELECT DISTINCT idA, valA, idB, valB
FROM A NATURAL JOIN B
WHERE B. booleen = 1 ;
Ou bien, si le SGBD ne connaît ni la forme « NATURAL JOIN » ni la forme « USING » de la jointure, on peut se rabattre sur la forme « ON » (mais en respectant l’esprit du relationnel, pas d’amalgame) :
SELECT DISTINCT idA, valA, idB, valB
FROM A INNER JOIN B ON A.idA = B.idA
WHERE B. booleen = 1 ;
=>
idA, valA, idB, valB
3, ‘tata’, 2, ‘fifi’, 1
On devrait en rester là . Mais si l’on tient à tout prix à marier la carpe et le lapin (procéder littéralement à leur union…), on peut s’en tenir aux opérateurs de l’algèbre relationnelle (dont la jointure externe ne fait bien sûr pas partie, contrairement à l’union !), car on est alors en terrain connu, théorémisé, non miné. On évitera les pustules NULL, histoire de rendre le résultat un peu moins monstrueux :
SELECT A.idA, A.valA, B.idB, B.valB, B.booleen
FROM A INNER JOIN B ON A.IdA = B.IdA
WHERE B.booleen = 1
UNION
SELECT x.idA, x.valA, ‘néant’, ‘néant’, ‘néant’
FROM
(SELECT A.idA, A.valA
FROM A
EXCEPT
SELECT A.idA, A.valA
FROM A INNER JOIN B ON A.IdA = B.IdA
WHERE B.booleen = 1) AS x ;
=>
idA, valA, idB, valB, booleen
1, ‘toto’, néant, néant, néant
2, ‘titi’, néant, néant, néant
3, ‘tata’, 2, ‘fifi’, 1
Mais pour aider l’utilisateur dans la compréhension du résultat, on précise le motif de l’absence d’information, ce qui donne lieu à quelque chose comme ceci :
SELECT A.idA, A.valA, B.idB, B.valB, B.booleen
FROM A INNER JOIN B ON A.IdA = B.IdA
WHERE B.booleen = 1
UNION
SELECT A.idA, A.valA, ‘commande non satisfaite’, ‘commande non satisfaite’, ‘commande non satisfaite’
FROM A INNER JOIN B ON A.IdA = B.IdA
WHERE B.booleen = 0
UNION
SELECT A.idA, A.valA, ‘pas de commande’, ‘pas de commande’, ‘pas de commande’
FROM A
EXCEPT
SELECT A.idA, A.valA, ‘pas de commande’, ‘pas de commande’, ‘pas de commande’
FROM A INNER JOIN B ON A.IdA = B.IdA ;
=>
idA, valA, idB, valB, booleen
1, ‘toto’, ‘pas de commande’, ‘pas de commande’, ‘pas de commande’
2, ‘titi’, ‘commande non satisfaite’, ‘commande non satisfaite’, ‘commande non satisfaite’
3, ‘tata’, 2, ‘fifi’, 1
Pour en revenir à la migration de la condition « booleen = 1 », je répète qu’il y a là un détournement à ne pas recommander. Chris Date (en 1986) a pris la peine de définir très soigneusement l’opérateur OUTER JOIN pour SQL, tout en interdisant ce genre de chose ; on doit comparer les colonnes des tables A et B, et c’est tout (comme le fit Codd en 1979, s’inspirant lui-même des travaux de Heath portant sur l’OUTER JOIN déjà en 1971). En reprenant à leur compte la version Date de JOIN (INNER aussi bien qu’OUTER) mais en l’altérant, les auteurs de la norme SQL n’ont pas pu s’empêcher de faire fi de ses mises en garde, ces gens sont décidemment indécrottables. Date avait pourtant mis des garde-fous après avoir observé minutieusement le comportement des divers SGBD (Oracle, Informix, Sybase, etc.) qui s’étaient lancé dans la mise en Å“uvre d’OUTER JOIN, avec des résultats partiellement satisfaisants. A noter que d’autres théoriciens (Chamberlin par exemple, responsable de l’arrivée officielle de NULL dans SEQUEL 2 en 1976) avaient eux aussi théorisé, mais pour un résultat trop compliqué.
Je note à cette occasion qu’avec MS SQL Server (et sans doute les autres…), on peut coder n’importe quoi avec l’opérateur JOIN (INNER aussi bien qu’OUTER). Par exemple :
SELECT A.idA, A.valA, B.idB, B.valB, B.booleen
FROM A INNER JOIN B ON B.booleen = 1
Ou
SELECT A.idA, A.valA, B.idB, B.valB, B.booleen
FROM A LEFT OUTER JOIN B ON B.booleen = 1
Ou encore (pour effectuer un produit cartésien ?)
SELECT A.idA, A.valA, B.idB, B.valB, B.booleen
FROM A INNER JOIN B ON ‘1’ < 'A'
Et j’en passe. L’esprit de l’opérateur est complètement perverti, ça ne fait vraiment pas sérieux…
Mais je m'égare. Ce à quoi je veux donc en venir : il faut utiliser les opérateurs de façon régulière, c'est-à -dire dans l’esprit du relationnel, sans chercher à faire du bricolage même si par laxisme la norme SQL laisse la porte ouverte.
Pour reprendre votre conclusion :
« Avec une jointure externe, il faut déplacer les conditions de restriction sur la table externe du WHERE vers la condition de jointure. Sinon c'est l'équivalent d'une jointure interne. »
Dans le cas général, je conteste ce procédé qui ne pourrait se justifier que si un prototypage des performances prouvait que le jeu en vaut vraiment la chandelle. Si la norme SQL était restée fidèle à l’esprit du Modèle Relationnel de Données et avait suivi Chris Date, vous n’auriez pas pu déplacer la condition de restriction, vous auriez été obligé de procéder autrement, mais il est évident que ça n’eut pas été un problème pour vous.
Un mot sur NOT EXISTS puisque vous l’évoquez : grâce à lui, SQL avait pu (généreusement) être qualifié d’algébriquement complet, du temps où il ne proposait pas les opérateurs MINUS (EXCEPT en SQL) et INTERSECT, puisqu’il permet de les simuler (comme il permet de simuler l’OUTER JOIN…) Autant dire qu’aux temps héroïques on l’utilisait à tour de bras (et il m’arrive de continuer à m’en servir car sur le plan de la performance ce brave NOT EXISTS percute, au moins avec mon SGBD, DB2, que j’utilise encore à l’occasion et la performance est un sujet qui ne laisse pas la Production indifférente, loin de nos discussions qui à vrai dire ne la concernent pas).
Si je résume ton long commentaire, tu préconiserais ceci :
« Si on doit poser une condition de restriction sur la table externe d’une jointure externe, il est préférable de faire une union qui précise quelle condition (de jointure ou de restriction) n’est pas satisfaite pour certaines lignes de la table externe. »
Associé à ton exemple de code, assez parlant pour qu’il se passe d’explications :
2
3
4
5
6
7
8
9
10
11
12
13
FROM A INNER JOIN B ON A.IdA = B.IdA
WHERE B.booleen = 1
UNION
SELECT A.idA, A.valA, ‘commande non satisfaite’, ‘commande non satisfaite’, ‘commande non satisfaite’
FROM A INNER JOIN B ON A.IdA = B.IdA
WHERE B.booleen = 0
UNION
SELECT A.idA, A.valA, ‘pas de commande’, ‘pas de commande’, ‘pas de commande’
FROM A
EXCEPT
SELECT A.idA, A.valA, ‘pas de commande’, ‘pas de commande’, ‘pas de commande’
FROM A INNER JOIN B ON A.IdA = B.IdA ;
Ce qui donne pour résultat :
OK pour la clarté du résultat présenté mais il n’est pas toujours indispensable à l’application et ta requête est quand même plus compliquée que la mienne. Et je ne suis pas loin de penser qu’elle serait également moins performante avec un bon gros paquet de données mais ça demande à être testé. Aurais-tu une anecdote savoureuse tirée de ta grande expérience qui irait dans un sens ou dans l’autre ?
Quant à ta remarque sur NOT EXISTS, je l’emploie moi aussi et SQLPro recommande de l’utiliser en remplacement d’une jointure externe avec recherche des NULL sur la table externe. [NOT] EXISTS a aussi un avantage sur le plan de la clarté de la requête en disant précisément « Sélectionne les lignes de la table A pour lesquelles il (n’)existe (pas) de lignes correspondantes dans la table B qui répondent à telle condition ».
C’est toujours un plaisir de discuter avec toi et j’espère bien qu’un jour nous arriverons à le faire de vive voix.
A+
Bonjour,
vos commentaires sont très intéressant.
J’aimerais néanmoins rebondir sur la proposition de fsmrel de proposer de mettre une chaine de caractère dans le champs attendu. Je ne suis pas sur que cela soit une bonne pratique principalement pour l’application :
-si on utilise un ORM le mapping défini deviendrait donc un string et il conviendrait de gérer le type de la donnée… avec toute la lourdeur qui cela implique.
Je pense que l’on aurait tout de même pu rebondir ta proposition en rajouant une hypothèse à l’énoncé en précisant que les colonnes des tables sont toutes NON NULL. Dès lors, avec la jointure externe on aurait implicitement su que ce NULL était dû à la jointure.
Bien cordialement
<>
Si on utilise un quoi ? Un Objet Radicalement Merdique ?
Normalement, une applicatipon ne devrait accéder à une base de données que via des vues et des procédures stockées.
Certes, mais des ORM mappent aussi les procédures stockées donc l’argument restent assez valable:o. La chaine me semblera toujours plus complexe à analyser lors du traitement
Ta remarque sur la normalité me semble justifiée mais cela m’interroge tout de même sur cette « normalité ». Sur 1000 applications de gestion « pro », combien sont effectivement architecturées de cette manière ? (trop peu à mon avis)
Ces remarques viennent d’un débutant curieux :-)…
Bonne continuation