Проверка на существование (EXISTS)

Проверка на принадлежность результатам вложенного запроса (IN)

Проверка на принадлежность результата вложенного запроса (ключевое слово in) является видоизмененной формой простой проверки на членство в множестве. Одно значение сравнивается столбцом данных, возвращенных вложенным запросом, и если это значение равно одному из значений в столбце, проверка возвращает true, Дани проверка используется, когда необходимо сравнить значение из проверяема строки с множеством значений, возвращенных вложенным запросом, к показано в следующих примерах:

Во всех приведенных примерах вложенный запрос возвращает в качестве результата столбец данных, а предложение where главного запроса проверяет, равно ли значение из строки таблицы главного запроса одному из значений в полученном столбце. Таким образом, проверка in с вложенным запросом выполняется аналогично простой проверке in, за исключением того, что множество значений задается вложенным запросом, а не указывается явно в операторе select.

В результате проверки на существование (ключевое слово 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, всегда можно преобразовать в эквивалентную проверку на существование, перенося операцию сравнения во вложенный запрос.

Дата добавления: 2014-01-11