Utilisation d’un LEFT OUTER JOIN par rapport à RIGHT OUTER JOIN dans SQL

Dans cet extrait de SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL , John L. Viescas et Michael J. Hernandez discutent de LEFT OUTER JOIN par rapport à RIGHT OUTER JOIN et offrent des conseils sur la façon d’utiliser chacun de les dans les instructions SQL. Ils fournissent également divers exemples de syntaxe OUTER JOIN pour guider les administrateurs et les développeurs de bases de données sur le codage SQL requis.

Vous utiliserez généralement le formulaire OUTER JOIN qui demande toutes les lignes d’une table ou d’un ensemble de résultats et toutes les lignes correspondantes d’une deuxième table ou d’un ensemble de résultats. Pour ce faire, vous spécifiez soit un LEFT OUTER JOIN, soit un RIGHT OUTER JOIN.

Quelle est la différence entre GAUCHE et DROITE ? Rappelez-vous du chapitre précédent que pour spécifier un INNER JOIN sur deux tables, vous nommez la première table, incluez le mot-clé JOIN, puis nommez la deuxième table. Lorsque vous commencez à créer des requêtes à l’aide de OUTER JOIN, le standard SQL considère la première table que vous nommez comme celle de « gauche » et la deuxième table comme celle de « droite ». Donc, si vous voulez toutes les lignes de la première table et toutes les lignes correspondantes de la deuxième table, vous utiliserez une LEFT OUTER JOIN. Inversement, si vous voulez toutes les lignes de la deuxième table et toutes les lignes correspondantes de la première table, vous spécifierez un RIGHT OUTER JOIN.

Syntaxe

Examinons la syntaxe nécessaire pour créer une jointure externe gauche ou droite.

Utiliser des tableaux

Nous commencerons simplement par définir une OUTER JOIN à l’aide de tables. La figure 9–3 montre le diagramme de syntaxe pour créer une requête avec une OUTER JOIN sur deux tables.

Figure 9–3 Définition d’une OUTER JOIN sur deux tables.

Tout comme INNER JOIN (traité au chapitre 8), toute l’action se produit dans la clause FROM. (Nous avons laissé de côté les clauses WHERE et ORDER BY pour le moment afin de simplifier les choses.) Au lieu de spécifier un seul nom de table, vous spécifiez deux noms de table et les liez avec le mot-clé JOIN. Si vous ne spécifiez pas le type de JOIN que vous voulez, votre système de base de données suppose que vous voulez un INNER JOIN. Dans ce cas, parce que vous voulez un OUTER JOIN, vous devez indiquer explicitement que vous voulez un LEFT JOIN ou un RIGHT JOIN. Le mot clé OUTER est facultatif.

La partie critique de tout JOIN est la clause ON ou USING qui suit la deuxième table et indique à votre système de base de données comment effectuer le JOIN. Pour résoudre le JOIN, votre système de base de données combine logiquement chaque ligne de la première table avec chaque ligne de la deuxième table. (Cette combinaison de toutes les lignes d’une table avec toutes les lignes d’une seconde table est appelée un  produit cartésien .) Il applique ensuite les critères de la clause ON ou USING pour trouver les lignes correspondantes à renvoyer. Parce que vous avez demandé une OUTER JOIN, votre système de base de données renvoie également les lignes sans correspondance de la table « gauche » ou « droite ».

Vous avez appris à utiliser une condition de recherche pour former une clause WHERE au chapitre 6, Filtrage de vos données. Vous pouvez utiliser une condition de recherche dans la clause ON d’un JOIN pour spécifier un test logique qui doit être vrai afin de renvoyer deux lignes liées. Cela n’a de sens que d’écrire une condition de recherche qui compare au moins une colonne de la première table avec au moins une colonne de la deuxième table. Bien que vous puissiez écrire une condition de recherche très complexe, vous pouvez généralement spécifier un simple test de comparaison d’égalité sur les colonnes de clé primaire d’une table avec les colonnes de clé étrangère de l’autre table.

Pour garder les choses simples, commençons par les mêmes classes de recettes et exemples de recettes que nous avons utilisés dans le dernier chapitre. N’oubliez pas que dans une base de données bien conçue, vous devez répartir les noms de classification complexes dans une seconde table, puis relier les noms à la table de sujets principale via une simple valeur de clé. Dans l’exemple de base de données Recettes, les classes de recettes apparaissent dans un tableau distinct des recettes. La Figure 9–4 illustre la relation entre les tables Recipe_Classes et Recipes.

Figure 9–4 Les classes de recettes se trouvent dans un tableau distinct des recettes.

Lorsque vous avez initialement configuré les types de recettes à enregistrer dans votre base de données, vous avez peut-être commencé par entrer toutes les classes de recettes qui vous venaient à l’esprit. Maintenant que vous avez entré un certain nombre de recettes, vous voudrez peut-être savoir quelles classes n’ont pas encore saisi de recettes. Vous pourriez également être intéressé par la liste de toutes les classes de recettes avec les noms des recettes saisies jusqu’à présent pour chaque classe. Vous pouvez résoudre l’un ou l’autre problème avec un OUTER JOIN.

« Montre-moi tous les types de recettes et toutes les recettes correspondantes dans ma base de données. »

TraductionSélectionnez la description de la classe de recettes et le titre de la recette dans le tableau des classes de recettes à gauche externe joint au tableau des recettes sur l’ID de classe de recettes dans le tableau des classes de recettes correspondant à l’ID de classe de recettes dans le tableau des recettes
NettoyerSélectionnez la description de la classe de recettes et le titre
de
la recette dans la table
des classes de recettes jointes à l’ extérieur à gauche
avec la table des recettes sur recettes_classes.ID de classe de recettes dans la table des classes de recettes matching = recettes.ID de classe de recettes dans la table des recettes

SQLSELECT Recipe_Classes.RecipeClassDescription,
Recipes.RecipeTitle 
FROM Recipe_Classes 
LEFT OUTER JOIN Recipes 
ON Recipe_Classes.RecipeClassID = 
Recipes.RecipeClassID

Lorsque vous utilisez plusieurs tables dans votre clause FROM, n’oubliez pas de qualifier entièrement chaque nom de colonne avec le nom de la table partout où vous l’utilisez afin qu’il soit absolument clair quelle colonne de quelle table vous voulez. Notez que nous avons dû qualifier le nom de RecipeClassID dans la clause ON car il existe deux colonnes nommées RecipeClassID — une dans la table Recipes et une dans la table Recipe_Classes.

Si vous exécutez notre exemple de requête dans l’exemple de base de données Recipes, vous devriez voir 16 lignes renvoyées. Étant donné que nous n’avons entré aucune recette de soupe dans la base de données, vous obtiendrez une valeur Null pour RecipeTitle dans la ligne où RecipeClassDescription est ‘Soup’. Pour trouver uniquement cette ligne, utilisez cette approche.

« Listez les classes de recettes qui n’ont pas encore de recettes. »

TraductionSélectionnez la description de la classe de recettes dans la table des classes de recettes à gauche externe jointe à la table des recettes sur l’ID de classe de recettes où l’ID de recette est vide
NettoyerSélectionnez la description
de
la classe de recettes dans la table des classes de recettes jointe extérieurement à gauche à la table des recettes sur recettes_classes. ID de classe de recette dans la table de recettes correspond à = recettes. ID de classe de recette dans la table de recettes où l’ID de recette est vide NULL



SQLSELECT Recipe_Classes.RecipeClassDescription
FROM Recipe_Classes
LEFT OUTER JOIN Recipes
ON Recipe_Classes.RecipeClassID = 
Recipes.RecipeClassID
WHERE Recipes.RecipeID IS NULL

Si vous y réfléchissez, nous venons de faire une différence ou une opération EXCEPT (voir chapitre 7) en utilisant un JOIN. C’est un peu comme dire « Montre-moi toutes les classes de recettes sauf celles qui apparaissent déjà dans le tableau des recettes ». Le diagramme d’ensemble de la Figure 9–5 devrait vous aider à visualiser ce qui se passe.

Similaire  Avantages et croissance de la gestion des données d'entreprise augmentée
Figure 9–5 Relation possible entre les classes de recettes et les recettes.

Dans la Figure 9–5, toutes les recettes ont une classe de recettes, mais certaines classes de recettes existent pour lesquelles aucune recette n’a encore été définie. Lorsque nous ajoutons le test IS NULL, nous demandons toutes les lignes du cercle extérieur plus clair qui n’ont aucune correspondance dans l’ensemble de recettes représenté par le cercle intérieur plus foncé.

Notez que le diagramme d’une OUTER JOIN sur les tables de la Figure 9–3 contient également la clause USING facultative. Si les colonnes correspondantes dans les deux tables ont le même nom et que vous souhaitez joindre uniquement sur des valeurs égales, vous pouvez utiliser la clause USING et répertorier les noms de colonne. Reprenons le problème précédent avec USING.

« Afficher les classes de recettes qui n’ont pas encore de recettes. »

TraductionSélectionnez la description de la classe de recette dans la table des classes de recette à gauche externe jointe à la table des recettes à l’aide de l’ID de classe de recette où l’ID de recette est vide
NettoyerSélectionnez la description
de
la classe de recettes dans la table des classes de recettes jointe extérieurement à gauche avec la table des recettes à l’aide de l’ ID de classe de recettes où l’ID de recette est vide NULL

SQLSELECT Recipe_Classes.RecipeClassDescription
FROM Recipe_Classes
LEFT OUTER JOIN Recipes
USING (RecipeClassID)
WHERE Recipes.RecipeID IS NULL

La syntaxe USING est beaucoup plus simple, n’est-ce pas ? Il y a un petit hic : n’importe quelle colonne dans la clause USING perd son identité de table parce que la norme SQL dicte que le système de base de données doit « fusionner » les deux colonnes en une seule colonne. Dans cet exemple, il n’y a qu’une seule colonne RecipeClassID en conséquence, vous ne pouvez donc pas référencer Recipes.RecipeClassID ou Recipe_Classes.RecipeClassID dans la clause SELECT ou toute autre clause.

Sachez que certains systèmes de bases de données ne prennent pas encore en charge USING. Si vous trouvez que vous ne pouvez pas utiliser USING avec votre base de données, vous pouvez toujours obtenir le même résultat avec une clause ON et une comparaison égale.


Intégration d’une instruction SELECT

Comme vous vous en souvenez au chapitre 8, la plupart des implémentations SQL vous permettent de substituer une instruction SELECT entière à n’importe quel nom de table dans votre clause FROM. Bien sûr, vous devez ensuite attribuer un nom de corrélation (voir la section sur l’attribution de noms d’alias au chapitre 8) afin que le résultat de l’évaluation de votre requête intégrée ait un nom. La Figure 9–6 montre comment assembler une clause OUTER JOIN à l’aide d’instructions SELECT imbriquées.

Figure 9–6 OUTER JOIN utilisant des instructions SELECT.

Notez qu’une instruction SELECT peut inclure toutes les clauses de requête à l’exception d’une clause ORDER BY. En outre, vous pouvez mélanger et faire correspondre des instructions SELECT avec des noms de table de part et d’autre des mots clés OUTER JOIN.

Examinons à nouveau les tables Recipes et Recipe_Classes. Pour cet exemple, supposons également que vous ne vous intéressez qu’aux classes Salades, Soupes et Plats principaux. Voici la requête avec la table Recipe_Classes filtrée dans une instruction SELECT qui participe à un LEFT OUTER JOIN avec la table Recipes.

SQL
SELECT RCFiltered.ClassName, R.RecipeTitle
FROM
(SELECT RecipeClassID,
RecipeClassDescription AS ClassName
FROM Recipe_Classes AS RC
WHERE RC.ClassName= »Salades »
OR RC.ClassName= »Soup »
OR RC.ClassName= »Plat principal »)
AS RCFiltered
LEFT OUTER JOIN Recettes AS R
ON RCFiltered.RecipeClassID = R.RecipeClassID

Vous devez être prudent lorsque vous utilisez une instruction SELECT dans une clause FROM. Tout d’abord, lorsque vous décidez de substituer une instruction SELECT à un nom de table, vous devez vous assurer d’inclure non seulement les colonnes que vous souhaitez voir apparaître dans le résultat final, mais également toutes les colonnes de liaison dont vous avez besoin pour effectuer la JOINTURE. C’est pourquoi vous voyez à la fois RecipeClassID et RecipeClassDescription dans l’instruction intégrée. Juste pour le plaisir, nous avons donné à RecipeClassDescription un nom d’alias de ClassName dans l’instruction intégrée. Par conséquent, la clause SELECT demande ClassName plutôt que RecipeClassDescription. Notez que la clause ON fait désormais référence au nom de corrélation (RCFiltered) de l’instruction SELECT intégrée plutôt qu’au nom d’origine de la table ou au nom de corrélation que nous avons attribué à la table dans l’instruction SELECT intégrée.

Comme la requête est indiquée pour l’exemple de base de données Recipes réel, vous voyez une ligne avec RecipeClassDescription of Soup avec une valeur Null renvoyée pour RecipeTitle car il n’y a pas de recettes de soupe dans l’exemple de base de données. Nous aurions tout aussi bien pu créer une instruction SELECT sur la table Recipes du côté droit de OUTER JOIN. Par exemple, nous aurions pu demander des recettes qui contiennent le mot « bœuf » dans leur titre, comme dans l’énoncé suivant.

SQL
SELECT RCFiltered.ClassName, R.RecipeTitle
FROM
(SELECT RecipeClassID,
RecipeClassDescription AS ClassName
FROM Recipe_Classes AS RC
WHERE RC.ClassName= »Salades »
OR RC.ClassName= »Soup »
OR RC.ClassName= »Plat principal »)
AS RCFiltered
LEFT OUTER JOIN
(SELECT Recipes.RecipeClassID, Recipes.Recipe
Title
FROM Recipes
WHERE Recipes.RecipeTitle LIKE ‘%beef%’)
AS R
ON RCFiltered.RecipeClassID = R.RecipeClassID

Gardez à l’esprit que le LEFT OUTER JOIN demande toutes les lignes du jeu de résultats ou de la table sur le côté gauche du JOIN, qu’il existe ou non des lignes correspondantes sur le côté droit. La requête précédente renvoie non seulement une ligne Soup avec un Null RecipeTitle (car il n’y a pas du tout de soupes dans la base de données), mais également une ligne Salad avec un Null. Vous pourriez conclure qu’il n’y a pas de recettes de salades dans la base de données. En fait, il y a des salades dans la base de données mais pas de salades avec « boeuf » dans le titre de la recette !

Intégrer des JOIN dans des JOIN

Bien que vous puissiez résoudre de nombreux problèmes en liant seulement deux tables, vous devrez souvent lier trois, quatre tables ou plus pour obtenir toutes les données nécessaires à la résolution de votre requête. Par exemple, vous souhaiterez peut-être récupérer toutes les informations pertinentes sur les recettes (le type de recette, le nom de la recette et tous les ingrédients de la recette) en une seule requête. Maintenant que vous comprenez ce que vous pouvez faire avec une OUTER JOIN, vous voudrez peut-être également répertorier toutes les classes de recettes – même celles qui n’ont pas encore de recettes définies – et tous les détails sur les recettes et leurs ingrédients. La Figure 9–7 montre toutes les tables nécessaires pour répondre à cette requête.

Figure 9–7 Les tables dont vous avez besoin dans l’exemple de base de données Recettes pour récupérer toutes les informations sur les recettes.

Il semble que vous ayez besoin de données provenant de cinq tables différentes ! Tout comme au chapitre 8, vous pouvez le faire en construisant une clause FROM plus complexe, en incorporant des clauses JOIN dans des clauses JOIN. Voici l’astuce : partout où vous pouvez spécifier un nom de table, vous pouvez également spécifier une clause JOIN entière entourée de parenthèses. La Figure 9–8 montre une version simplifiée de la jointure de deux tables. (Nous avons laissé de côté les clauses de nom de corrélation et choisi la clause ON pour former une simple INNER ou OUTER JOIN de deux tables.)

Figure 9–8 Simple JOIN de deux tables.

Pour ajouter une troisième table au mélange, placez simplement une parenthèse ouvrante avant le premier nom de table, ajoutez une parenthèse fermante après la condition de recherche, puis insérez un autre JOIN, un nom de table, le mot-clé ON et une autre condition de recherche. La Figure 9–9 (à la page 306) montre comment procéder.

Si vous y réfléchissez, le JOIN de deux tables à l’intérieur des parenthèses forme une table logique ou un ensemble de résultats interne. Cet ensemble de résultats remplace désormais le premier nom de table simple dans la Figure 9–8. Vous pouvez continuer ce processus en plaçant une clause JOIN entière entre parenthèses, puis en ajoutant un autre mot clé JOIN, un nom de table, un mot clé ON et une condition de recherche jusqu’à ce que vous ayez tous les jeux de résultats dont vous avez besoin. Faisons une requête qui nécessite des données de toutes les tables présentées dans la figure 9–7 et voyons comment cela se passe. (Vous pouvez utiliser ce type de demande pour un rapport qui répertorie tous les types de recettes avec des détails sur les recettes de chaque type.)

Similaire  Défis courants liés aux lacs de données et comment les surmonter
Figure 9–9 Une jointure simple de trois tables.

« J’ai besoin de tous les types de recettes, puis des noms de recettes correspondants, des instructions de préparation, des noms d’ingrédients, des numéros d’étape des ingrédients, des quantités d’ingrédients et des mesures d’ingrédients de ma base de données de recettes, triés par titre de recette et séquence de numéros d’étape. »

TraductionSélectionnez la description de la classe de recette, le titre de la recette, les instructions de préparation, le nom de l’ingrédient, le numéro de séquence de la recette, la quantité et la description de la mesure dans le tableau des classes de recette à gauche externe joint au tableau des recettes sur l’ID de classe de recette dans le tableau des classes de recette correspondant à l’ID de recette dans le la table des recettes, puis jointe à la table des ingrédients de la recette sur l’ID de la recette dans la table des recettes correspondant à l’ID de la recette dans la table des ingrédients de la recette, puis jointe à la table des ingrédients sur l’ID de l’ingrédient dans la table des ingrédients correspondant à l’ID de l’ingrédient dans la table des ingrédients de la recette, puis enfin joint au tableau des mesures sur l’ID de la quantité de mesure dans le tableau des mesures correspondant à l’ID de la quantité de mesure dans le tableau des ingrédients de la recette, trier par titre de recette et numéro de séquence de recette
NettoyerSélectionnez la description de la classe de recette, le titre de la recette, les instructions de préparation , le nom de l’ingrédient, le numéro de séquence de la recette, la quantité et la description de la mesure dans la table des classes de recette jointe à l’ extérieur à gauche avec la table des recettes sur les classes_recette . recettes .recipe classe ID dans la table des recettes, puis jointure interne avec la table des ingrédients de la recette sur recettes.recipe ID dans la table des recettes matching = recette_ingredients.recipe ID dans la table des ingrédients de la recette, puis une jointure interne avec la table des ingrédients sur ingredients.ingredient ID dans la table des ingrédients matching = recette_ingredients.ingredient ID dans la table des ingrédients de la recette, puis enfin une jointure interne avec la table des mesures sur les mesures.quantité de mesure ID dans le tableau des mesures correspondant à la recette_ingrédients.quantité de mesure ID dans le tableau des ingrédients de la recette, ordre par titre de la recette et numéro de séquence de la recette
SQLSELECT Recipe_Classes.RecipeClassDescription,
Recipes.RecipeTitle, Recipes.Preparation,
Ingredients.IngredientName,
Recipe_Ingredients.RecipeSeqNo,
Recipe_Ingredients.Amount,
Measurements.MeasurementDescription
FROM (((Recipe_Classes
LEFT OUTER JOIN Recipes
ON Recipe_Classes.RecipeClassID =
Recipes.RecipeClassID)
INNER JOIN Recipe_Ingredients
ON Recipes.RecipeID = 
Recipe_Ingredients.RecipeID)
INNER JOIN Ingredients
ON Ingredients.IngredientID =
Recipe_Ingredients.IngredientID)
INNER JOIN Measurements 
ON Measurements.MeasureAmountID = 
Recipe_Ingredients.MeasureAmountID
ORDER BY RecipeTitle, RecipeSeqNo

En vérité, vous pouvez substituer une jointure entière de deux tables partout où vous ne placeriez autrement qu’un nom de table. Dans la Figure 9–9, nous impliquons que vous devez d’abord joindre la première table à la deuxième table, puis joindre ce résultat à la troisième table. Vous pouvez également joindre d’abord les deuxième et troisième tables (tant que la troisième table est, en fait, liée à la deuxième table et non à la première), puis effectuer le JOIN final avec la première table. La Figure 9–10 (à la page 308) vous montre cette autre méthode.

Figure 9–10 Joindre plus de deux tables dans une séquence alternée.

Pour résoudre la requête que nous venons de vous montrer en utilisant cinq tables, nous aurions également pu énoncer le SQL comme suit.

SQL
SELECT Recipe_Classes.RecipeClassDescription,
 Recipes.RecipeTitle, Recipes.Preparation, 
 Ingredients.IngredientName, 
 Recipe_Ingredients.RecipeSeqNo, 
 Recipe_Ingredients.Amount, 
 Measurements.MeasurementDescription
 FROM Recipe_Classes 
 LEFT OUTER JOIN 
 (((Recipes 
 INNER JOIN Recipe_Ingredients 
 ON Recipes.RecipeID = Recipe_Ingredients.RecipeID) 
 INNER JOIN Ingredients 
 ON Ingredients.IngredientID = 
 Recipe_Ingredients.IngredientID) 
 INNER JOIN Mesures 
 ON Measurements.MeasureAmountID =
 Recipe_Ingredients.MeasureAmountID) 
 ON Recipe_Classes.RecipeClassID = 
 Recipes.RecipeClassID
 ORDRE PAR TitreRecette, NuméroSeqRecette

N’oubliez pas que les optimiseurs de certains systèmes de base de données sont sensibles à la séquence des définitions JOIN. Si votre requête avec de nombreux JOIN prend beaucoup de temps à s’exécuter sur une grande base de données, elle peut s’exécuter plus rapidement si vous modifiez la séquence des JOIN dans votre instruction SQL.

Vous avez peut-être remarqué que nous n’avons utilisé qu’une seule OUTER JOIN dans les exemples précédents de multiples JOIN. Vous vous demandez probablement s’il est possible ou même logique d’utiliser plus d’un OUTER JOIN dans un complexe JOIN. Supposons qu’il existe non seulement certaines classes de recettes qui n’ont pas de lignes de recettes correspondantes, mais également certaines recettes qui n’ont pas encore défini d’ingrédients. Dans l’exemple précédent, vous ne verrez aucune ligne de la table Recipes qui n’a pas de lignes correspondantes dans la table Recipe_Ingredients car INNER JOIN les élimine. Demandons également toutes les recettes.

« J’ai besoin de tous les types de recettes, puis de tous les noms de recettes et instructions de préparation, puis de tous les noms d’ingrédients, numéros d’étapes d’ingrédients, quantités d’ingrédients et mesures d’ingrédients correspondants de ma base de données de recettes, triés par titre de recette et séquence de numéros d’étape. »

TraductionSélectionnez la description de la classe de recette, le titre de la recette, les instructions de préparation, le nom de l’ingrédient, le numéro de séquence de la recette, la quantité et la description de la mesure dans le tableau des classes de recette à gauche joint au tableau des recettes sur l’ID de classe de recette dans le tableau des classes de recette correspondant à l’ID de classe de recette dans la table des recettes, puis jointe extérieure gauche avec la table des ingrédients de la recette sur l’ID de la recette dans la table des recettes correspondant à l’ID de la recette dans la table des ingrédients de la recette, puis jointe à la table des ingrédients sur l’ID de l’ingrédient dans la table des ingrédients correspondant à l’ID de l’ingrédient dans la table des ingrédients de la recette , puis enfin rejoint le tableau des mesures sur l’ID de la quantité de mesure dans le tableau des mesures correspondant à l’ID de la quantité de mesure dans le tableau des ingrédients de la recette, trier par titre de recette et numéro de séquence de recette
NettoyerSélectionnez la description de la classe de recette, le titre de la recette, les instructions de préparation , le nom de l’ingrédient, le numéro de séquence de la recette, la quantité et la description de la mesure dans la table des classes de recette jointe à l’ extérieur à gauche avec la table des recettes sur la recette_classes. ID de classe de recette dans la table des classes de recette matching = recettes.recipe classe ID dans la table des recettes, puis joint externe gauche avec la table des ingrédients de la recette sur recettes.recipe ID dans la table des recettes matching = recette_ingredients.recipe ID dans la table des ingrédients de la recette, puis une jointure interne avec la table des ingrédients sur ingredients.ingredient ID dans la table des ingrédients matching = recette_ingredients.ingredient ID dans la table des ingrédients de la recette, puis enfin une jointure interne avec la table des mesures sur measure.measurement amount ID dans le tableau des mesures matching = recette_ingrédients.quantité de mesure ID dans le tableau des ingrédients de la recette, trier par titre de recette et numéro de séquence de recette
SQLSELECT Recipe_Classes.RecipeClassDescription,
Recipes.RecipeTitle, Recipes.Preparation, 
Ingredients.IngredientName, 
Recipe_Ingredients.RecipeSeqNo, 
Recipe_Ingredients.Amount, 
Measurements.MeasurementDescription
FROM (((Recipe_Classes 
LEFT OUTER JOIN Recipes 
ON Recipe_Classes.RecipeClassID = 
Recipes.RecipeClassID) 
LEFT OUTER JOIN Recipe_Ingredients 
ON Recipes.RecipeID = 
Recipe_Ingredients.RecipeID) 
INNER JOIN Ingredients 
ON Ingredients.IngredientID = 
Recipe_Ingredients.IngredientID) 
INNER JOIN Measurements 
ON Measurements.MeasureAmountID = 
Recipe_Ingredients.MeasureAmountID
ORDER BY RecipeTitle, RecipeSeqNo

Fais attention! Ce type de OUTER JOIN multiple fonctionne comme prévu uniquement si vous suivez un chemin de relations un-à-plusieurs. Examinons à nouveau les relations entre Recipe_Classes, Recipes et Recipe_Ingredients, comme illustré à la Figure 9–11.

Similaire  Qu'est-ce qu'une procédure stockée ?
Figure 9–11 Les relations entre les tables Recipe_Classes, Recipes et Recipe_Ingredients.

Vous pouvez voir une relation un-à-plusieurs parfois appelée relation parent-enfant. Chaque ligne parent (du côté « un » de la relation) peut avoir zéro ou plusieurs lignes enfants (du côté « plusieurs » de la relation). Sauf si vous avez des lignes orphelines du côté « plusieurs » (par exemple, une ligne dans Recipes qui a un Null dans sa colonne RecipeClassID), chaque ligne de la table enfant doit avoir une ligne correspondante dans la table parent. Il est donc logique de dire Recipe_Classes LEFT JOIN Recipes pour récupérer toutes les lignes parentes dans Recipe_Classes qui n’ont pas encore d’enfants dans Recipes. Recipe_Classes RIGHT JOIN Les recettes devraient (à l’exception des lignes orphelines) vous donner le même résultat qu’un INNER JOIN.

De même, il est logique de demander Recettes LEFT JOIN Recette_Ingrédients car vous pourriez avoir des recettes pour lesquelles aucun ingrédient n’a encore été saisi. Recipes RIGHT JOIN Recipe_Ingredients ne fonctionne pas car la colonne de liaison (RecipeID) dans Recipe_Ingredients fait également partie de la clé primaire composée de cette table. Par conséquent, vous êtes assuré de n’avoir aucune ligne orpheline dans Recipe_Ingredients car aucune colonne d’une clé primaire ne peut contenir une valeur Null.

Maintenant, allons un peu plus loin et demandons tous les ingrédients, y compris ceux qui ne sont pas encore inclus dans les recettes. Tout d’abord, examinez attentivement les relations entre les tables, y compris la table Ingrédients, comme illustré à la Figure 9–12.

Figure 9–12 Les relations entre les tables Recipe_Classes, Recipes, Recipe_Ingredients et Ingredients.

Essayons cette requête. (Attention : il y a un piège ici !)

« J’ai besoin de tous les types de recettes, puis de tous les noms de recettes et des instructions de préparation, puis de tous les numéros d’étape d’ingrédients correspondants, des quantités d’ingrédients et des mesures d’ingrédients, et enfin de tous les noms d’ingrédients de ma base de données de recettes, triés par titre de recette et numéro d’étape séquence. »

TraductionSélectionnez la description de la classe de recette, le titre de la recette, les instructions de préparation, le nom de l’ingrédient, le numéro de séquence de la recette, la quantité et la description de la mesure dans le tableau des classes de recette à gauche joint au tableau des recettes sur l’ID de classe de recette dans le tableau des classes de recette correspond à l’ID de classe dans le le tableau des recettes, puis l’extérieur gauche joint au tableau des ingrédients de la recette sur l’ID de la recette dans le tableau des recettes correspond à l’ID de la recette dans le tableau des ingrédients de la recette, puis joint au tableau des mesures sur l’ID de la quantité de mesure dans le tableau des mesures correspond à l’ID de la quantité de mesure dans le tableau des mesures , puis enfin l’extérieur droit joint à la table des ingrédients sur l’ID de l’ingrédient dans la table des ingrédients correspond à l’ID de l’ingrédient dans la table des ingrédients de la recette, trier par titre de recette et numéro de séquence de recette
NettoyerSélectionnez la description de la classe de recette, le titre de la recette, les instructions de préparation , le nom de l’ingrédient, le numéro de séquence de la recette, la quantité et la description de la mesure dans la table des classes de recette jointe à l’ extérieur à gauche avec la table des recettes sur les classes_recette . recettes .ID de classe dans la table des recettes, puis joint externe gauche avec la table des ingrédients de la recette sur recettes.ID de la recette dans la table des recettes correspond à = recette_ingrédients.ID de la recette dans la table des ingrédients de la recette, puis joint interne avec le tableau des mesures sur les mesures.ID de la quantité de mesure dans le tableau des mesures correspond à = mesures.ID de la quantité de mesure dans le tableau des mesures, puis enfin joint externe droit avec le tableau des ingrédients sur ingredients.ID de l’ingrédient dans le tableau des ingrédients matches = recette_ingredients.ingredient ID dans la table des ingrédients de la recette, ordre par titre de recette et numéro de séquence de recette
SQLSELECT Recipe_Classes.RecipeClassDescription,
Recipes.RecipeTitle, Recipes.Preparation, 
Ingredients.IngredientName, 
Recipe_Ingredients.RecipeSeqNo, 
Recipe_Ingredients.Amount, 
Measurements.MeasurementDescription
FROM (((Recipe_Classes 
LEFT OUTER JOIN Recipes 
ON Recipe_Classes.RecipeClassID = 
Recipes.RecipeClassID) 
LEFT OUTER JOIN Recipe_Ingredients 
ON Recipes.RecipeID = 
Recipe_Ingredients.RecipeID) 
INNER JOIN Measurements 
ON Measurements.MeasureAmountID =
Recipe_Ingredients.MeasureAmountID) 
RIGHT OUTER JOIN Ingredients 
ON Ingredients.IngredientID = 
Recipe_Ingredients.IngredientID 
ORDER BY RecipeTitle, RecipeSeqNo

Pensez-vous que cela fonctionnera? En fait, la réponse est un NON retentissant ! La plupart des systèmes de base de données analysent l’intégralité de la clause FROM, puis tentent de déterminer la manière la plus efficace d’assembler les liens de table. Supposons, cependant, que la base de données décide de respecter pleinement la manière dont nous avons regroupé les JOIN entre parenthèses. Cela signifie que le système de base de données fonctionnera d’abord à partir du JOIN le plus interne (Recipe_Classes joint à Recipes), puis travaillera vers l’extérieur.

Étant donné que certaines lignes de Recipe_Classes peuvent ne pas avoir de lignes correspondantes dans Recipes, ce premier JOIN renvoie les lignes qui ont une valeur Null dans RecipeClassID. En regardant la Figure 9–12, vous pouvez voir qu’il existe une relation un-à-plusieurs entre Recipe_Classes et Recipes. À moins que certaines recettes n’aient été assignées à une classe de recettes, nous devrions de toute façon obtenir toutes les lignes de la table Recettes ! Le prochain JOIN avec la table Recipe_Ingredients demande également un LEFT OUTER JOIN. Nous voulons toutes les lignes, quelles que soient les valeurs Null, du JOIN précédent (de Recipe_Classes avec Recipes) et toutes les lignes correspondantes dans Recipe_Ingredients. Encore une fois, étant donné que certaines lignes de Recipe_Classes peuvent ne pas avoir de lignes correspondantes dans Recipes ou que certaines lignes de Recipes peuvent ne pas avoir de lignes correspondantes dans Recipe_Ingredients, plusieurs lignes peuvent avoir une valeur Null dans la colonne IngredientID de la table Recipe_Ingredients. Ce que nous faisons avec les deux JOIN, c’est « parcourir » les relations un-à-plusieurs de Recipe_Classes à Recipes, puis de Recipes à Recipe_Ingredients. Jusqu’ici tout va bien. (Au fait, le INNER JOIN final avec les mesures est sans conséquence – nous savons que tous les ingrédients ont un MeasureAmountID valide.)

Maintenant, les ennuis commencent. Le RIGHT OUTER JOIN final demande toutes les lignes d’Ingredients et toutes les lignes correspondantes du résultat des JOIN précédents. Rappelez-vous du chapitre 5 qu’un Null est une valeur très spéciale – il ne peut être égal à aucune autre valeur, pas même à un autre Null. Lorsque nous demandons toutes les lignes dans Ingredients, l’IngredientID dans toutes ces lignes a une valeur non nulle. Aucune des lignes du JOIN précédent qui ont un Null dans IngredientID ne correspondra du tout, donc le JOIN final les jette ! Vous verrez tous les ingrédients qui ne sont pas encore utilisés dans une recette, mais vous ne verrez pas les classes de recettes qui n’ont pas de recettes ou les recettes qui n’ont pas d’ingrédients.

Si votre système de base de données décide de résoudre la requête en exécutant les JOIN dans un ordre différent, vous verrez peut-être des classes de recettes sans recettes et des recettes sans ingrédients, mais vous ne verrez pas d’ingrédients non encore utilisés dans une recette en raison de la Problème de correspondance nulle. Certains systèmes de base de données peuvent reconnaître ce problème de logique et refuser de résoudre votre requête – vous verrez quelque chose comme un message d’erreur « ambiguous OUTER JOINs ». Le problème que nous rencontrons actuellement résulte de la tentative de « remonter » une relation plusieurs-à-un avec une OUTER JOIN allant dans l’autre sens. Descendre la colline est facile, mais remonter de l’autre côté nécessite des outils spéciaux. Quelle est la solution à ce problème ? Lisez la section suivante pour le savoir !