Вложенный запрос всегда заключается в круглые скобки, но по-прежнему сохраняет знакомую структуру оператора select, содержащего предложение from и необязательные предложения where, group by и having. Структура этих предложений во вложенном запросе идентична их структуре в операторе select; во вложенном запросе эти предложения выполняют свои обычные функции. Однако между вложенным запросом и оператором select имеется ряд отличий.
• Таблица результатов вложенного запроса всегда состоит из одного столбца. Это означает, что в предложении select вложенного запроса всегда указывается один возвращаемый столбец,
• Во вложенный запрос не может входить предложение order by. Результаты вложенного запроса используются только внутри главного запрос и для пользователя остаются невидимыми, поэтому нет смысла и сортировать.
• Вложенный запрос не может быть запросом на объединение нескольки:
различных операторов select; допускается использование только одной оператора select.
• Имена столбцов во вложенном запросе могут являться ссылками ш столбцы таблиц главного запроса. Эти внешние ссылки подробно рассматриваются ниже в настоящей главе.
Чаще всего вложенные запросы указываются в предложении where оператора SQL. Когда вложенный запрос содержится в данном предложении, он участвует в процессе отбора строк. Рассмотрим еще раз пример запроса, приведенный в предьщущем параграфе.
Главный запрос считывает данные из таблицы offices, а его предложение where отбирает офисы, которые будут включены в таблицу результатов запроса. Условие, заданное в этом предложении, поочередно применяется ко всем строкам таблицы offices. Предложение where сравнивает значение текущей строки в столбце target со значением, возвращенным вложенным запросом. Для каждой строки результатов главного запроса выполняется вложенный запрос, который дает сумму плановых объемов продаж для служащих "текущего" офиса. Результатом вложенного запроса является одно число, и предложение where сравнивает его со значением столбца target, выбирая или отбрасывая текущий офис на основании результата сравнения. Как видно из рисунка, выполнение вложенного запроса повторяется для каждой строки, проверяемой предложением where главного запроса.
Часто в теле вложенного запроса требуется ссылаться на значение столбца в текущей строке главного запроса. Рассмотрим еще раз запрос из предыдущих параграфов:
В приведенном операторе select вложенный запрос играет следующую роль: он вычисляет сумму плановых объемов продаж для служащих, работающих в одном офисе, а именно в том, который в данный момент проверяется предложением where главного запроса. Вложенный запрос выполняет просмотр таблицы salesreps. Но обратите внимание: столбец office в предложении where вложенного запроса является столбцом не таблицы salesreps, а таблицы offices, которая входит в главный запрос. Во время последовательной проверки строк таблицы offices значение столбца office в текущей строке этой таблицы используется для выполнения вложенного запроса.
Столбец office во вложенном запросе является примером внешней ссылки. Внешняя ссылка представляет собой имя столбца, не входящего ни в одну из таблиц, перечисленных в предложении from вложенного запроса, и принадлежащего таблице, указанной в предложении fromглавного запроса. Как показывает предыдущий пример, значение в столбце внешней ссылки берется из строки, проверяемой в настоящий момент главным запросом.
Условия поиска во вложенном запросе
Вложенный запрос всегда является частью условия поиска в предложении where или having. В главе 6 были рассмотрены простые условия поиска, которые могут использоваться в этих предложениях. Кроме того, в SQL имеются следующие условия поиска во вложенном запросе:
• Сравнение с результатом вложенного запроса. Сравнивает значение выражения с одним значением, возвращенным вложенным запросом. Эта проверка напоминает простое сравнение.
• Проверка на принадлежность результатам вложенного запроса. Проверяет значение выражения на равенство с одним из значений множества, возвращенного вложенным запросом. Эта проверка напоминает простую проверку на членство во множестве.
• Проверка на существование. Проверяет наличие строк в таблице результатов вложенного запроса.
• Многократное сравнение. Сравнивает значение выражения с каждым из значений множества, возвращенного вложенным запросом.
Сравнение с результатом вложенного запроса (=,<>, <, <=, >, >=)
Сравнение с результатом вложенного запроса является модифицированной формой простого сравнения. Значение выражения сравнивается со значением, возвращенным вложенным запросом, и если условие сравнения выполняется, то проверка возвращает значение true. Эта проверка используется для сравнения значения из проверяемой строки с одним значением, возвращенным вложенным запросом, как показано в следующем примере:
В этом примере вложенный запрос считывает плановый объем продаж для офиса в Атланте. Затем считанное значение используется для отбора тех служащих, для которых плановый объем продаж превышает план этого офиса.
В операции сравнения с результатом вложенного запроса можно использовать те же шесть операторов сравнения (=, О, <, <=, >, >=), что и при простом сравнении. Вложенный запрос, участвующий в операции сравнения, должен возвращать в качестве результата одну строку. Если результате вложенного запроса являются несколько строк, то сравнение не име смысла и SQL выдаст сообщение об ошибке. Если в результате выполнения вложенного запроса не будет получено ни одной строки или будет получено значение null, то операция сравнения возвращает null.
Обратите внимание на то, что вложенный запрос в операции сравнения может стоять только справа от оператора сравнения. Неравенство
А < (вложенный запрос) разрешается, а неравенство
(вложенный запрос) > А
недопустимо. Сказанное не ограничивает возможности операции сравнения, поскольку знак любого неравенства всегда можно "перевернуть" так, чтобы вложенный запрос оказался с правой стороны. Однако это говорит о том, что иногда требуется "переворачивать" логику словесного запроса так, чтобы он формально соответствовал действительному оператору SQL.
Проверка на принадлежность результатам вложенного запроса (IN)
Проверка на принадлежность результата вложенного запроса (ключевое слово in) является видоизмененной формой простой проверки на членство в множестве. Одно значение сравнивается столбцом данных, возвращенных вложенным запросом, и если это значение равно одному из значений в столбце, проверка возвращает true, Дани проверка используется, когда необходимо сравнить значение из проверяема строки с множеством значений, возвращенных вложенным запросом, к показано в следующих примерах:
Во всех приведенных примерах вложенный запрос возвращает в качестве результата столбец данных, а предложение where главного запроса проверяет, равно ли значение из строки таблицы главного запроса одному из значений в полученном столбце. Таким образом, проверка in с вложенным запросом выполняется аналогично простой проверке in, за исключением того, что множество значений задается вложенным запросом, а не указывается явно в операторе select.
Проверка на существование (EXISTS)
В результате проверки на существование (ключевое слово exists) можно выяснить, содержится ли в таблице результатов вложенного запроса хотя бы одна строка. Аналогичной простой проверки не существует. Проверка на существование используется только с вложенными запросами.
Вот пример запроса, который можно легко сформулировать, используя проверку на существование:
Главный запрос последовательно перебирает все строки таблицы products, и для каждого товара выполняется вложенный запрос. Результатом вложенного запроса является столбец данных, содержащий номера всех заказов "текущего" товара на сумму не меньше чем $25000. Если такие заказы есть (т.е. столбец не пустой), то проверка exists возвращает true. Если вложенный запрос не дает ни одной строки заказов, проверка exists возвращает значение false. Эта проверка не может возвращать null.
Можно изменить логику проверки exists и использовать форму not exists. Тогда в случае, если вложенный запрос не создает ни одной строки результата, проверка возвращает true, в противном случае — false.
Обратите внимание на то, что условие поиска exists в действительности вовсе не использует результаты вложенного запроса. Проверяется только наличие результатов. По этой причине в SQL смягчается правило, согласно которому "вложенный запрос должен возвращать один столбец данных", и во вложенном запросе проверки exists допускается использование формы select *. Поэтому предыдущий запрос можно переписать следующим образом:
Отметим, что во всех приведенных примерах вложенный запрос содержит внешнюю ссылку на столбец таблицы из главного запроса. На практике во вложенном запросе проверки exists всегда имеется внешняя ссылка, "связывающая" вложенный запрос со строкой, проверяемой в настоящий момент главным запросом.
Многократное сравнение (ANY u ALL) *
В проверке IN выясняется, не равно ли некоторое значение одному из значений, содержащихся в столбце результатов вложенного запроса. В SQL имеется две разновидности многократного сравнения — any и all, расширяющие предыдущую проверку до уровня других операторов сравнения, таких как больше (>) или меньше (<).В обеих проверках некоторое значение сравнивается со столбцом данных, возвращенным вложенным запросом.
Проверка ANY *
В проверке any используется один из шести операторов сравнения (=, О, <, <=, >, >=) для того, чтобы сравнить одно проверяемое значение со столбцом данных, возвращенным вложенным запросом. Проверяемое значение поочередно сравнивается с каждым значением, содержащимся в столбце. Если любое из этих сравнений дает результат true, то проверка any возвращает значение true.
Вот пример запроса с проверкой any:
Главный запрос по очереди проверяет все строки таблицы salesreps. Вложенный запрос находит все заказы, принятые "текущим" служащим, и возвращает столбец, содержащий стоимости этих заказов. Предложение where главного запроса вычисляет десять процентов от плана текущего служащего и использует это число в качестве проверяемого значения, сравнивая его с каждой стоимостью заказа, возвращенной вложенным запросом. Если есть хотя бы один заказ, стоимость которого превышает вычисленное проверяемое значение, то проверка *'< any" возвращает значение true, а имя служащего заносится в таблицу результатов запроса. Если таких заказов нет, то имя служащего в таблицу результатов запроса не заносится. В соответствии со стандартом ANSI/ISO, вместо ключевого слова
any можно использовать ключевое слово some. Обычно можно использовать любое из них, но некоторые СУБД не поддерживают слово some.
Иногда проверка any может оказаться трудной для понимания, поскольку включает в себя не одно сравнение, а несколько. Если прочитать условие сравнения немного по-другому, это поможет понять его смысл. Например, проверку
WHERE Х < ANY (SELECT Y ...)
следует читать не как
"где Х меньше, чем любой выбранный Y../* атак:
"где для некоторого Y Х меньше, чем Y..." Тогда предыдущий запрос можно перефразировать таким образом:
Если вложенный запрос в проверке any не создает ни одной строки результата или если результаты содержат значения null, то в различных СУБД проверка any может выполняться по-разному. В стандарте ANSI/ISO для языка SQL содержатся подробные правила, определяющие результаты проверки any, когда проверяемое значение сравнивается со столбцом результатов вложенного запроса:
• Если вложенный запрос возвращает пустой столбец результатов, то проверка any имеет значение false (в результате выполнения вложенного запроса не получено ни одного значения, для которого выполнялось бы условие сравнения).
• Если операция сравнения имеет значение trueхотя бы для одного значения в столбце, то проверка any возвращает значение true (имеется некоторое значение, полученное вложенным запросом, для которого условие сравнения выполняется).
• Если операция сравнения имеет значение false для всех значений в столбце, то проверка any возвращает значение false (можно утверждать, что ни для одного значения, возвращенного вложенным запросом, условие сравнения не выполняется).
• Если операция сравнения не имеет значение true ни для одного значения в столбце, но в нем имеется одно или несколько значений null, то проверка any возвращает результат null. (В этой ситуации невозможно с определенностью утверждать, существует ли полученное вложенным запросом значение, для которого выполняется условие сравнения; может быть, существует, а может и нет — все зависит от "настоящих" значений неизвестных данных.)
На практике проверка any иногда может приводить к ошибкам, которые трудно выявить, особенно когда применяется оператор сравнения "не равно" (О). Вот пример, иллюстрирующий данную проблему:
Конечно, для любого служащего можно найти некоторый офис, руководителем которого данный служащий не является. В таблицу результатов запроса войдут все служащие, поэтому запрос не дает ответа на поставленный вопрос! А правильным является следующий запрос:
Запрос с проверкой any всегда можно преобразовать в запрос с проверкой exists, перенося операцию сравнения внутрь условия поиска вложенного запроса. Обычно так и следует поступать, поскольку в таком случае исключаются ошибки, подобные описанной выше. Вот альтернативная форма запроса с проверкой exists:
Проверка ALL *
В проверке all, как и в проверке any, используется один из шести операторов (=, О, <,<=, >, >=) для сравнения одного проверяем значения со столбцом данных, возвращенным вложенным запросом. Проверяемое значение поочередно сравнивается с каждым значением, содержащимся в столбце. Если все сравнения дают результат true, то проверка возвращает значение true.
Главный запрос поочередно проверяет каждую строку таблицы office Вложенный запрос находит всех служащих, работающих в "текущем" офис и возвращает столбец с фактическими объемами продаж для каждого служащего. Предложение where главного запроса вычисляет 50 процентов от плана продаж офиса и сравнивает это проверяемое значение со всем объемами продаж, получаемыми в результате выполнения вложенного запроса. Если все объемы продаж превышают проверяемое значение, то проверка "< all" возвращает значение true и данный офис включается таблицу результатов запроса. Если нет, то офис не включается в таблиц результатов.
Проверка all, подобно проверке any, может оказаться трудной для понимания, поскольку включает в себя не одно сравнение, а несколько Опять-таки, если читать условие сравнения немного иначе, то это помогает понять его смысл. Например, проверку
WHERE Х < ALL (SELECT Y...)
следует читать не как
"где Х меньше, чем все выбранные Y..."
а так:
"где для всех Y Х меньше, чем Y..." Тогда предыдущий запрос можно представить в таком виде:
Если вложенный запрос в проверке all не возвращает ни одной строки или если результаты запроса содержат значения null, то в различных СУБД проверка all может выполняться по-разному. В стандарте ANSI/ISO для языка SQL содержатся подробные правила, определяющие результаты проверки all, когда проверяемое значение сравнивается со столбцом результатов вложенного запроса:
• Если вложенный запрос возвращает пустой столбец результатов, то проверка all имеет значение true. Считается, что условие сравнения выполняется, даже если результаты вложенного запроса отсутствуют.
• Если операция сравнения дает результат true для каждого значения в столбце, то проверка all возвращает значение true. Условие сравнения выполняется для каждого значения, полученного вложенным запросом.
• Если операция сравнения дает результат false для какого-нибудь значения в столбце, то проверка all возвращает значение false. В этом случае можно утверждать, что условие поиска выполняется не для каждого значения, полученного вложенным запросом.
• Если операция сравнения не дает результат false ни для одного значения в столбце, но для одного или нескольких значений дает результат null, то проверка all возвращает значение null. В этой ситуации нельзя с определенностью утверждать, для всех ли значений, полученных вложенным запросом, справедливо условие сравнения; может быть, для всех, а может и нет — все зависит от "настоящих" значений неизвестных данных.
Ошибки, которые могут случиться, если проверка any содержит оператор сравнения "не равно" (О), происходят и в проверке all. Проверку all, так же как и проверку any, всегда можно преобразовать в эквивалентную проверку на существование, перенося операцию сравнения во вложенный запрос.
37. ВЛОЖЕННЫЕ ЗАПРОСЫ И ОБЪЕДИНЕНИЯ
Многие запросы, записанные с применением вложенных запросов, можно также записать в виде многотабличных запросов. Такое случается довольно часто, и SQL позволяет записать запрос любым способом, что иллюстрирует следующий пример:
Эта форма запроса очень близка к его словесной формулировке. Вложенный запрос возвращает список офисов западного региона, а главный запрос находит служащих, работающих в этих офисах. Вот альтернативная форма данного запроса, использующая объединение двух таблиц:
Данная форма запроса объединяет таблицы salesreps и offices, чтобы найти регион, в котором работает каждый служащий, а затем исключает тех, кто не работает в западном регионе.
Каждый из двух приведенных запросов находит соответствующих служащих, и ни один из них не является ни "правильным", ни "ошибочным". Для большинства людей первый вариант (с использованием вложенного запроса) покажется более естественным, так как в словесном запросе не требуется никакой информации об офисах, и объединение таблиц salesreps и offices для ответа на запрос кажется немного странным. Конечно, если запрос изменить так, чтобы в нем запрашивалась информация из таблицы offices, то вариант с вложенным запросом больше не годится и необходимо использовать запрос к двум таблицам:
С другой стороны, имеется много запросов с вложенными запросами, которые нельзя выразить в виде эквивалентного объединения. Вот простой пример:
В данном случае внутренний запрос является итоговым, а внешний — нет, поэтому из этих двух запросов нельзя сделать объединение.
Уровни вложенности запросов___
Все рассмотренные до сих пор запросы были "двухуровневыми" и состояли из главного и вложенного запросов. Точно так же, как внутри главного запроса может находиться вложенный запрос, внутри вложенного запроса может находиться еще один вложенный запрос. Вот пример "трехуровневого" запроса:
возвращает столбец данных, содержащий идентификаторы офисов восточного региона. Следующий вложенный запрос
возвращает столбец данных, содержащий идентификаторы служащих, рабо тающих в одном из выбранных офисов. И наконец, внешний запрос
находит клиентов, закрепленных за выбранными служащими.
По такой же методике можно создавать запросы с четырьмя и более уровнями вложенности. Стандарт ANSI/ISO не определяет максимально число уровней вложенности. Но на практике с ростом их числа очень быстро увеличивается время выполнения запроса. Когда запрос имеет более двух уровней вложенности, он становится трудным для чтения и понимания. Во многих СУБД количество уровней вложенности запросов ограничено относительно небольшим числом.
38. Связанные вложенные запросы
По идее, вложенный запрос выполняется многократно — один раз длякаждой строки произведения таблиц из главного запроса. Однако во многих случаях вложенный запрос возвращает одни и те же результаты для всех строк или для группы строк.
В этом запросе было бы неразумно выполнять вложенный запрос пять раз (один раз для каждого офиса). Усредненный план не изменяется; т абсолютно не зависит от проверяемого в настоящий момент офиса. Следовательно, вложенный запрос можно выполнить только один раз и, получить усредненный план ($550000), преобразовать главный запрос к виду:
В коммерческих СУБД это упрощение используется всякий раз, когда есть такая возможность, чтобы уменьшить объем работы, необходимой для выполнения запроса. Но когда вложенный запрос содержит внешнюю ссылку, данное упрощение применять нельзя, как показано на следующем примере:
В разных строках таблицы offices, проверяемой предложением where главного запроса, столбец office (который является внешней ссылкой во вложенном запросе) имеет различные значения. Поэтому вложенный запрос должен выполняться пять раз — один раз для каждой строки таблицы offices. Вложенный запрос, содержащий внешнюю ссылку, называется связанным вложенным запросом, так как его результаты связаны с отдельной строкой таблицы в главном запросе. По той же самой причине внешняя ссылка называется иногда связанной ссылкой.
Вложенный запрос может иметь внешнюю ссылку на таблицу в предложении from любого запроса, который содержит данный вложенный запрос,независимо от его уровня вложенности. Например, имя столбца во вложенном запросе четвертого уровня может относиться к одной из таблиц, указанных в предложении from главного запроса, или к таблице в любом вложенном запросе, содержащем данный вложенный запрос четвертого уровня. Независимо от уровня вложенности внешняя ссылка всегда принимает значение столбца в "текущей" строке проверяемой таблицы.
Так как вложенный запрос может содержать внешние ссылки, вероятность неоднозначных ссылок на имена столбцов во вложенном запросе еще выше, чем в главном запросе. Если во вложенном запросе присутствует неполное имя столбца, SQL должен определить, относится ли оно к таблице предложения from самого вложенного запроса или к предложению from запроса, содержащего вложенный запрос. Чтобы минимизировать возможность путаницы, в SQL всегда предполагается, что ссылка на столбец во вложенном запросе относится к ближайшему возможному предложению from. Для иллюстрации приведем пример, где та же самая таблица используется и в главном, и во вложенном запросах:
Столбцы MANAGER, QUOTA и SALES во вложенном запросе являются ссылками на таблицу salesreps в предложении from самого вложенного запроса; SQL не интерпретирует их как внешние ссылки, и вложенный запрос не является связанным вложенным запросом. Как уже говорилось ранее, SQL в данном случае может сначала выполнить вложенный запрос: найти служащих, опережающих план, и составить список, содержащий идентификаторы их руководителей. Затем SQL может приступить к выполнению главного запроса и отобрать руководителей из полученного списка.
Если вы хотите создать внешнюю ссылку во вложенном запросе, аналогичном рассмотренному в предыдущем примере, то для этого должны использовать псевдонимы таблицы. Как это делается, показано на примере запроса, в который по сравнению с предыдущим добавлено еще одно условие:
Теперь копия таблицы salesreps в главном запросе имеет метку mgrs, а копия во вложенном запросе —• метку emps. Вложенный запрос содержит одно дополнительное условие поиска, требующее, чтобы идентификатор офиса служащего не был равен идентификатору офиса руководителя. Полное имя столбца mgrs . office во вложенном запросе — это внешняя ссылка, и данный вложенный запрос является связанным.
ВЛОЖЕННЫЕ ЗАПРОСЫ В ПРЕДЛОЖЕНИИ HAVING
Хотя вложенные запросы чаще всего применяются в предложении where, их можно использовать и в предложении having главного запроса: Когда вложенный запрос содержится в предложении having, он участвует в отборе группы строк.
Вложенный запрос вычисляет среднюю стоимость по всем заказам. Это простой вложенный запрос, не содержащий внешних ссылок, поэтому искомая средняя стоимость вычисляется один раз, а затем многократно используется в предложении having. Главный запрос просматривает строки таблицы orders, отыскивая все заказы на товары компании ACI, и группирует их по именам служащих. Затем предложение having сравнивает среднюю стоимость по каждой группе заказов со средней стоимостью по всем заказам, вычисленной ранее. Если средняя стоимость по труппе больше, чем общая средняя стоимость, то данная группа строк сохраняется; если нет, то группа строк исключается. И, наконец, предложение select создает для каждой группы итоговую строку, содержащую имя служащего и среднюю стоимость принятых им заказов.
В предложении having можно также использовать связанный вложенный запрос. Однако поскольку вложенный запрос выполняется один раз для каждой группы строк, все внешние ссылки в связанном вложенном запросе должны иметь одно значение для каждой группы строк. На практике это означает, что внешняя ссылка должна либо быть ссылкой на столбец группировки внешнего запроса, либо находиться внутри агрегатной функции. В предложении having можно также использовать связанный вложенный запрос. Все внешние ссылки в самом вложенном запросе должны иметь одно значение для каждой группы строк. На практике это означает, что внешняя ссылка должна либо быть ссылкой на столбец группировки внешнего запроса, либо находиться внутри агрегатной функции. В последнем случае значение агрегатной функции для проверяемой группы строк вычисляется в ходе выполнения вложенного запроса.
39. Однострочный оператор INSERT
Ниже приведен оператор insert, который добавляет информацию о служащем Якобсене в учебную базу данных:
Вначале оператор создает новую строку, структура которой повторяет структуру столбцов таблицы, а затем заполняет ее значениями из предложения values и добавляет эту строку в таблицу. Строки в таблице не упорядочены, поэтому нет никаких указаний о том, где вставлять строку: "вверху", "внизу" или "между двух строк" таблицы. Эта строка будет входить в результаты последующих запросов на чтение таблицы salesreps, но в таблице результатов запроса она может находиться в любом месте.
Предположим, что служащий Якобсен получает свой первый заказ от компании InterCorp, нового клиента, которому присвоен идентификатор 2126. Это заказ на 20 изделий ACI-41004 общей стоимостью $2340, и ему присваивается номер 113069. Ниже приведены операторы insert, добавляющие в базу данных информацию о новом клиенте и заказе:
При добавлении в таблицу новой строки всем столбцам, имена которых отсутствуют в списке столбцов оператора insert, автоматически присваивается значение null. В операторе insert, с помощью которого в таблицу salesreps была добавлена информация о служащем Якобсене, были опущены столбцы quota и manager:
Из-за этого новая строка в столбцах quota и manager содержит значение null. Значение null можно присвоить явным образом, включив эти столбцы в список столбцов, а в списке значений задав для них ключевое слово null. Применение следующего оператора insert приведет к тому же результату, что и в предыдущем случае:
