Л ReciPE Classes

RecipeCiaeslO РК

ReopeCla&sDMCriptioni

ReCtPES I

RecipelD RecfpeTttte I

RecjpeCiadiO FK \ Preparation

Notes

.i.V.A4.WA4i*,-...V.V.iA.-A .V.>.,.i . ..>. .-.>i4A-,..i....A*

ReClPEjNGREDIENTS

IngredientlD FK

MeasureAmourtID fK

iNGAEDIENTS I

tngredientlD PK \ fbj IncjreclinlNttrr.

tnc}rQdientCU$siD FK MaaufoAmountlD W

Рис. 9.12. Отношение между таблицами Recipe Classes, Recipes,

Recipe Ingredients и Ingredients

Подобным образом имеет смысл запросить Recipes LEFT JOIN Recipe Ingredients, потому что могут быть рецепты, для которых пока епде не указаны компоненты. Recipes RIGHT JOIN Recipe Ingredients не работает, потому что связывающий столбец (RecipelD) в Recipe Ingredients также является частью первичного ключа этой таблицы; поэтому гарантируется, что в Recipe Ingredients отсутствуют зависшие строки, поскольку ни один столбец в первичном ключе не может содержать значение Null.

Продвинемся еще на шаг вперед и запросим все компоненты, включая те, которые еще не введены ни в один рецепт. Вначале посмотрим внимательно на отношения между таблицами, включая таблицу Ingredients, показанные на рис. 9.12.

Попробуем записать следующий запрос (осторожно: здесь есть ловушка!):

7 need all of the recipe types, and then all of the recipe names, preparations, and instructions, and then any matching ingredient step number, ingredient quantities, and ingredient measurements and finally all ingredient names from my Recipes database sorted in step number sequence .

( Нужны все виды рецептов, все названия рецептов, приготовления и указания, и затем все сопоставленные им номера этапа компонента, количество компонента и единица измерения компонента и, наконец, названия всех компонентов из базы данных Рецепты , отсортированные в последовательности этапов .)

Преобразование:

Select the recipe class description, recipe title, preparation instructions, ingredient name, recipe sequence number, amount, and measurement description from the recipe classes table left outer joined with the recipes table on recipe class ID, then left outer joined with the recipe ingredients table on recipe ID, then joined with the measurements table on measurement amount ID, and then finally right outer joined with the ingredients table on ingredient ID, order by recipe title and recipe sequence number

(Выбрать описание вида рецепта, заголовок рецепта, указания по приготовлению, название компонента, порядковый номер рецепта, количество и описание

единиц измерения из таблицы Виды рецепта

с левым внешним соединением с таблицей Рецепты

по идентификатору вида рецепта, затем с левым внешним

соединением с таблицей Компоненты рецепта

по идентификатору рецепта, соединенной с таблицей

Единицы измерения по идентификатору единиц

измерения количества, и, наконец, соединенной с правым

внешним соединением с таблицей Компоненты

по идентификатору компонента, упорядоченные

по заголовку рецепта и порядковому номеру рецепта)

Уточнение: Select the recipe class description, recipe title, preparation

instructions, ingredient name, recipe sequence number, amount, шё measurement description from the recipe classes table left outer joined with the recipes table on recipe class ID, thett left outer joined with the recipe ingredients table on recipe ID, then joined with the measurements table on measurement amount ID, and then finally right outer joined with the ingredients table on ingredient ID, order by recipe title atie recipe sequence number

(Выбрать описание вида рецепта, заголовок рецепта, приготовление, название компонента, порядковый номер рецепта, количество, описание единиц измерения из Виды рецепта с левым внешним соединением с Рецепты по идентификатору вида рецепта, затем с левым внешним соединением с Компоненты рецепта по идентификатору рецепта, соединенной с Единицы измерения по идентификатору единиц измерения количества, соединенной правым внешним соединением с Компоненты по идентификатору компонента, упорядоченные по заголовку рецепта, порядковому номеру рецепта)

SQL SELECT Recipe Classes.RecipeClassDescription,

Recipes.RecipeTitle, Recipes.Preparation, Ingredients.IngredientName, Recipe Ingredients.RecipeSeqNo, Recipe Ing redients.Amount, Measu rements.Measu rementDesc ription

FROM (((Recipe Classes

LEFT OUTER JOIN Recipes

ON Recipe Classe3.RecipeClassID = Recipes.RecipeClassID)

LEFT OUTER JOIN Recipe.Ingredients ON Recipes.RecipelD =

Recipe.Ingredients.RecipelD) INNER JOIN Measurements ON Measurements.MeasureAmountID =

RecipeIngredients.MeasureAmountID) RIGHT OUTER JOIN Ingredients ON Ingredients.IngredientID =

Recipe Ingredients. IngredientID ORDER BY RecipeTitle, RecipeSeqNo

Думаете, это будет работать? Ответ ошеломляющий: НЕТ! Большинство СУБД выполняет анализ всего условия FROM целиком, а затем пытается определить наиболее эффективный способ сбора связей таблиц. Предположим, однако, что база данных решит полностью сохранить наш порядок группирования JOIN в скобках. Это означает, что СУБД будет выполнять операции, начиная от самого внутреннего JOIN (Recipe Classes, соединенная с Recipes), а затем займется внешними операциями.

Поскольку некоторые строки в таблице Recipe Classes могут совсем не иметь сопоставимых строк в Recipes, это первое JOIN возвратит строки, которые содержат значение Null в RecipelD. Вернувшись к рис. 9.12 можно увидеть, что между Recipe Classes и Recipes существует отношение один-ко-многим. Если для каких-либо рецептов не был назначен Recipe Class, мы все равно получим строки из таблицы Recipes! Следующий JOIN с таблицей Recipe Ingredients также запрашивает LEFT OUTER JOIN. Нам требуются все строки независимо от наличия каких-либо значений Null из предыдущего JOIN (Recipe Classes с Recipes) и всех сопоставленных им строк в Recipe Ingredients. Опять-таки, поскольку некоторые строки в Recipe Classes могут не иметь сопоставленных им строк в Recipes или у каких-либо строк в Recipes могут отсутствовать Recipe Ingredients, некоторые строки могут иметь значение Null в столбце IngredientID из таблицы Recipe Ingredients. Все, что делается с обоими условиями JOIN, это просто перемещение по отношениям один-ко-многим от Recipe Classes к Recipe и затем от Recipes к Recipe Ingredients. До сих пор все хорошо. (Кстати, окончательное INNER JOIN с Measurements несущественно - мы знаем, что все Ingredients имеют допустимый MeasureAmountID.)

А вот сейчас начинаются затруднения. Завершающее RIGHT OUTER JOIN запрашивает все строки из Ingredients и любые строки, удовлетворяюиие условию, из результата предыдущих условий JOIN. Вспомните, что Null является очень специфическим значением; оно не может быть равным ни любому другому значению, ни даже, другому значению Null. Если запрашиваются все строки из Ingredients, то для всех этих строк IngredientID имеет не-Null значение. Ни одна из строк из предыдущего JOIN, IngredientID которых имеет значение Null, не будет сопоставлена вообще, поэтому завершающий JOIN совсем отбрасывает их! Будут