Структура предиката в предложении Where

Специфицирует условие, для которого может быть вычислено логическое значение "true", "false" или

"unknown".

Формат

<predicate> ::=

<comparison predicate>

| <between predicate>

| <in predicate>

| <like predicate>

| <is null predicate>

| <quantified predicate>

| <exists predicate>

<comparison predicate> ::=

<value expression> <comp op> {<value expression> | <subquery>}

<comp op> ::=

= | <> | < | > | <= | >=

<between predicate> ::=

<value expression> [NOT] BETWEEN <value expression1> AND <value expression2>

<in predicate> ::=

<value expression> [NOT] IN { <subquery> | (<in value list>) }

<in value list> ::=

<value specification>

{ ,<value specification> }...

<like predicate> ::=

<column specification> [NOT] LIKE <pattern>

[ESCAPE <escape character>]

<pattern> ::=

<value specification>

<escape character> ::=

<value specification>

<null predicate> ::=

<column specification> IS [NOT] NULL

<quantified predicate> ::=

<value expression>

<comp op> <quantifier> <subquery>

<quantifier> ::=

<all> | <some>

<all> ::= ALL

<some> ::= SOME | ANY

<exists predicate> ::=

EXISTS <subquery>

Рассмотрим предикаты подробнее.

<comparison predicate> ::=

<value expression> <comp op>

{<value expression> | <subquery>}

<comp op> ::=

= | <> | < | > | <= | >=

Сравнение (операторы =, <>, <, <=, >, >=)

Наиболее распространенным условием отбора в SQL является сравнение. При сравнении СУБД вычисляет и сравнивает значения двух выражений для каждой строки данных. Выражения могут быть как очень простыми, например, содержать одно имя столбца или константу, так и более сложными, например, содержать арифметические операции. В SQL имеется шесть различных способов сравнения двух выражений.

В соответствии со спецификацией ANSI/ISO проверка на неравенство записывается как "А <> В". В ряде СУБД используются альтернативные системы записи, как, например, "А != В" (в SQL Server). Иногда такая форма записи является единственной, а иногда — только одной из допустимых форм.

Когда СУБД сравнивает значения двух выражений, могут получиться три результата:

• если сравнение истинно, то результат проверки имеет значение TRUE;

• если сравнение ложно, то результат проверки имеет значение false;

• если хотя бы одно из двух выражений имеет значение NULL, то результатом проверки будет "unknown".

Контрольные вопросы

1. Какие компоненты входят в состав оператора Select?

2. Назначение инструкции From оператора Select.

3. Какие функции выполняет инструкция Where оператора Select.

4. Перечислите предикаты используемые при построении выражений в инструкции Where.

Лекция 5

<between predicate> ::=

<value expression>

[NOT] BETWEEN <value expression1> AND <value expression2>

Другой формой условия отбора является проверка на принадлежность диапазону значений (оператор between. . .and). При этом проверяется, находится ли элемент данных между двумя заданными значениями. В условие отбора входят три выражения. Первое выражение определяет проверяемое значение; второе и третье выражения определяют верхний и нижний пределы проверяемого диапазона. Типы данных трех выражений должны быть сравнимыми.

При проверке на принадлежность диапазону верхний и нижний пределы считаются частью диапазона.

Инвертированная версия проверки на принадлежность диапазону позволяет выбрать значения, которые лежат за пределами диапазона, используя NOT BETWEEN.

Поверяемое выражение, заданное в операторе between. . .and, может быть любым допустимым выражением, однако на практике оно обычно представляет собой короткое имя столбца.

В стандарте ANSI/ISO определены относительно сложные правила обработки значений null в проверке between:

• если проверяемое выражение имеет значение null либо если оба выражения, определяющие диапазон, равны null, то проверка between возвращает "unknown";

• если выражение, определяющее нижнюю границу диапазона, имеет значение null, то проверка between возвращает false, когда проверяемое значение больше верхней границы диапазона, и "unknown" в противном случае;

• если выражение, определяющее верхнюю границу диапазона, имеет значение null, то проверка between возвращает false, когда проверяемое значение меньше нижней границы диапазона, и "unknown" в противном случае.

Однако прежде чем полагаться на эти правила, неплохо провести эксперименты со своей СУБД.

Необходимо отметить, что проверка на принадлежность диапазону не расширяет возможности SQL, поскольку ее можно выразить в виде двух сравнений.

Тем не менее, проверка between является более простым способом выразить условие отбора в терминах диапазона значений.

<in predicate> ::=

<value expression> [NOT] IN { <subquery> | (<in value list>) }

<in value list> ::=

<value specification>

{ ,<value specification> }...

Еще одним распространенным условием отбора является проверка на членство в множестве (оператор IN). В этом случае проверяется, соответствует ли элемент данных какому-либо значению из заданного списка.

Подстановочные знаки

Подстановочный знак % совпадает с любой последовательностью из нуля или более символов.

Оператор like указывает СУБД, что необходимо сравнивать содержимое указанного столбца с шаблоном заданным символьной строкой.

Подстановочный знак _ (символ подчеркивания) совпадает с любым отдельным символом в указанной позиции в строке.

Подстановочные знаки можно помещать в любое место строки шаблона, и в одной строке может содержаться несколько подстановочных знаков.

С помощью формы not like можно находить строки, которые несоответствуют шаблону. Проверку like можно применять только к столбцам, имеющим строковый тип данных. Если в столбце содержится значение null, то результатом проверки like будет "unknown".

Вероятно, вы уже встречались с проверкой на соответствие шаблону в операционных системах, имеющих интерфейс командной строки (Unix, MS-DOS). В этих системах звездочка (*) используется для тех же целей, что и символ процента (%) в SQL, а вопросительный знак (?) соответствует символу подчеркивания (_) в SQL, но в целом возможности работы с шаблонами строк в них такие же

Символы пропуска

При проверке строк на соответствие шаблону может оказаться, что подстановочные знаки входят в строку символов в качестве литералов. Например, нельзя проверить, содержится ли знак процента в строке, просто включив его в шаблон, поскольку СУБД будет считать этот знак подстановочным. Как правило, это не вызывает серьезных проблем, поскольку подстановочные знаки довольно редко встречаются в именах, названиях товаров и других текстовых данных, которые обычно хранятся в базе данных.

В стандарте ANSI/ISO определен способ проверки наличия в строке литералов, использующихся в качестве подстановочных знаков. Для этого применяются символы пропуска. Когда в шаблоне встречается такой символ, то символ, следующий непосредственно за ним, считается не подстановочным знаком, а литералом (происходит пропуск символа ) Непосредственно за символом пропуска может следовать либо один из двух подстановочных знаков, либо сам символ пропуска, поскольку он тоже приобретает в шаблоне особое значение.

Символ пропуска определяется в виде строки, состоящей из одного символа, и предложения escape. Ниже приведен пример использования знака доллара ($) в качестве символа пропуска:

Найти товары, коды которых начинаются с четырех букв "А%ВС\

SELECT ORDER_NUM, PRODUCT

FROM ORDERS WHERE PRODUCT LIKE 'A$%BC%' ESCAPE '$'

Первый символ процента в шаблоне, следующий за символом пропуска, считается литералом, второй — подстановочным знаком

Символы пропуска часто используются при проверке на соответствие шаблону, именно поэтому они были включены в стандарт ANSI/ISO. Однако они не входили в первые реализации SQL и поэтому не очень распространены. Для обеспечения переносимости приложений следует избегать использования предложения escape.

<null predicate> ::=

<column specification> IS [NOT] NULL

Значения null обеспечивают возможность применения трехзначной логики в условиях отбора. Для любой заданной строки результат применения условия отбора может быть true, false или null (в случае, когда в одном из столбцов содержится значение null) Иногда бывает необходимо явно проверять значения столбцов на равенство null и непосредственно обрабатывать их. Для этого в SQL имеется специальная проверка is null.

В следующем запросе проверка на null используется для нахождения в учебной базе данных служащего, который еще не был закреплен за офисом:

Найти служащего, который еще не закреплен за офисом.

SELECT NAME

FROM SALESREPS WHERE REP_OFFICE IS NULL

Инвертированная форма проверки на null (is not null) позволяет отыскать строки, которые не содержат значений null.

В отличие от условий отбора, описанных выше, проверка на null не может возвратить значение "unknown" в качестве результата. Она всегда возвращает true или false

Может показаться странным, что нельзя проверить значение на равенство null с помощью операции сравнения, например-

select name

from salespers where rep_office = null

Ключевое слово null здесь нельзя использовать, поскольку на самом деле это не настоящее значение; это просто сигнал о том, что значение неизвестно. Даже если бы сравнение

REP_OFFICE = NULL

было возможно, правила обработки значений null в сравнениях привели бы к тому, что оно вело бы себя не так, как ожидается. Если бы СУБД обнаружила строку, в которой столбец rep_office содержит значение null, выполнилась бы следующая проверка:

NULL = NULL

Что будет результатом этого сравнения: true или FALSE? Так как значения по обе стороны знака равенства неизвестны, то, в соответствии с правилами логики SQL, условие отбора должно вернуть значение "unknown". Поскольку условие отбора возвращает результат, отличный от true, строка исключается из таблицы результатов запроса. Из-за правил обработки значений null в SQL необходимо использовать проверку is null.

<quantified predicate> ::=

<value expression>

<comp op> <quantifier> <subquery>

<quantifier> ::=

<all> | <some>

<all> ::= ALL

<some> ::= SOME | ANY

Многократное сравнение (предикаты ANY и ALL) *

В проверке in выясняется, не равно ли некоторое значение одному из значений, содержащихся в столбце результатов подчиненного запроса. В SQL имеются также две разновидности многократного сравнения — any и all, расширяющие предыдущую проверку до уровня других операторов сравнения, таких как больше (>) или меньше (<). Обе этих проверки сравнивают некоторое значение со столбцом данных, отобранных подчиненным запросом.

Предикат ANY.

В проверке ANY, для того чтобы сравнить проверяемое значение со столбцом данных, отобранных подчиненным запросом, используется один из шести операторов сравнения (=, <>, <, <=, >, >=). Проверяемое значение поочередно сравнивается с каждым элементом, содержащимся в столбце. Если любое из этих сравнений дает результат true, то проверка any возвращает значение true.

В соответствии со стандартом ANSI/ISO вместо предиката ANY можно использовать предикат some. Обычно можно употреблять любой из них, но некоторые СУБД не поддерживают предикат SOME.

Иногда проверка any может оказаться трудной для понимания, поскольку включает в себя не одно сравнение, а несколько.

Если подчиненный запрос в проверке ANY не создает ни одной строки или если столбец результатов содержит значения NULL, то в различных СУБД проверка ANY может выполняться по-разному. В стандарте ANSI/ISO для языка SQL содержатся подробные правила, определяющие результаты проверки any, когда проверяемое значение сравнивается со столбцом результатов подчиненного запроса.

• Если подчиненный запрос возвращает результат в виде пустого столбца, то проверка any возвращает значение false (в результате выполнения подчиненного запроса не получено ни одного значения, для которого выполнялось бы условие сравнения).

• Если операция сравнения имеет значение TRUE хотя бы для одного значения в столбце, то проверка any возвращает значение true (имеется некоторое значение, полученное подчиненным запросом, для которого условие сравнения выполняется).

• Если операция сравнения имеет значение FALSE для всех значений в столбце, то проверка ANY возвращает значение FALSE (можно утверждать, что ни для одного значения, возвращенного подчиненным запросом, условие сравнения не выполняется).

• Если операция сравнения не имеет значение TRUE ни для одного значения в столбце, но в нем имеется одно или несколько значений NULL, то проверка ANY возвращает результат "unknown". (В этой ситуации невозможно с определенностью утверждать, существует ли полученное подчиненным запросом значение, для которого выполняется условие сравнения; может быть, существует, а может и нет — все зависит от "настоящих" значений неизвестных данных.)

На практике проверка ANY иногда может приводить к ошибкам, которые трудно выявить, особенно когда применяется оператор сравнения "не равно" (<>).

Запрос с предикатом ANY всегда можно преобразовать в запрос с предикатом exists, перенося операцию сравнения внутрь условия отбора подчиненного запроса. Обычно так и следует поступать, поскольку в этом случае исключаются ошибки.

Предикат ALL

В проверке all, как и в проверке any, используется один из шести операторов (⁼> <>, <, <=, >, >=) для сравнения проверяемого значения со столбцом данных, отобранных подчиненным запросом. Проверяемое значение поочередно сравнивается с каждым элементом, содержащимся в столбце. Если все сравнения дают результат true, то проверка all возвращает значение true.

Проверка all, подобно проверке any, может оказаться трудной для понимания, поскольку включает в себя не одно сравнение, а несколько. Опять-таки, если читать условие сравнения немного иначе, то это помогает понять его смысл. Например, проверку

WHERE X < ALL (SELECT Y...)

следует читать не как

"где X меньше, чем все выбранные Y..." а так'

"где для всех Y X меньше, чем Y...".

Если подчиненный запрос в проверке all не возвращает ни одной строки или если столбец результатов запроса содержит значения null, то в различных СУБД проверка all может выполняться по-разному. В стандарте ANSI/ISO для языка SQL содержатся подробные правила, определяющие результаты проверки all, когда проверяемое значение сравнивается со столбцом результатов подчиненного запроса.

• Если подчиненный запрос возвращает результат в виде пустого столбца, то проверка all возвращает значение true. Считается, что условие сравнения выполняется, даже если результаты подчиненного запроса отсутствуют.

• Если операция сравнения дает результат true для каждого значения в столбце, то проверка ALL возвращает значение TRUE. Условие сравнения выполняется для каждого значения, возвращенного подчиненным запросом.

• Если операция сравнения дает результат false для какого-нибудь значения в столбце, то проверка all возвращает значение false. В этом случае можно утверждать, что условие сравнения выполняется не для каждого значения, возвращенного подчиненным запросом.

• Если операция сравнения не дает результат FALSE ни для одного значения в столбце, но для одного или нескольких значений дает результат null, то проверка all возвращает значение "unknown". В этой ситуации нельзя с определенностью утверждать, для всех ли значений, возвращенных подчиненным запросом, справедливо условие сравнения; может быть, для всех, а может и нет — все зависит от "настоящих" значений неизвестных данных.

Ошибки, которые могут случиться, если проверка ANY содержит оператор сравнения "не равно" (<>), происходят и в проверке all. Проверку all, так же как и проверку any, всегда можно преобразовать в эквивалентную проверку на существование (exists), перенеся операцию сравнения в подчиненный запрос.

<exists predicate> ::=

EXISTS <subquery>

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

В результате проверки на существование (предикат EXISTS) можно выяснить, содержится ли в таблице результатов подчиненного запроса хотя бы одна строка. Аналогичной простой проверки не существует. Проверка на существование допустима только в подчиненных запросах.

Вот пример запроса, который можно легко сформулировать, используя проверку на существование:

"Вывести список товаров, на которые получен заказ стоимостью $25000 или больше".

Теперь перефразируем этот запрос таким образом:

"Вывести список товаров, для которых в таблице ORDERS существует по крайней мере один заказ, удовлетворяющий условиям: а) является заказом на данный товар; б) имеет стоимость не менее чем $25000".

Инструкция SELECT, используемая для получения требуемого списка товаров, приведена ниже:

SELECT DESCRIPTION

FROM PRODUCTS WHERE EXISTS (SELECT ORDER_NOM

FROM ORDERS

WHERE PRODUCT = PRODUCT_ID AND MFR = MFR_ID AND AMOUNT >= 25000.00)

Главный запрос последовательно перебирает все строки таблицы products, и для каждого товара выполняется подчиненный запрос. Результатом подчиненного запроса является столбец данных, содержащий номера всех заказов "текущего" товара на сумму не меньше чем $25000. Если такие заказы есть (т.е. столбец не пустой), то проверка exists возвращает true. Если подчиненный запрос не дает ни одной строки заказов, проверка exists возвращает значение false. Эта проверка не может возвращать null.

Можно изменить логику проверки exists и использовать форму not exists. Тогда в случае, если подчиненный запрос не создает ни одной строки результата, проверка возвращает true, в противном случае — false.

Обратите внимание на то, что предикат EXISTS в действительности вовсе не использует результаты подчиненного запроса. Проверяется только наличие результатов. По этой причине в SQL смягчается правило, согласно которому "подчиненный запрос должен возвращать один столбец данных", и в подчиненном запросе проверки exists допускается использование формы select *. Поэтому предыдущий запрос можно переписать/следующим образом:

Вывести список товаров, на которые получен заказ стоимостью $25000 или больше.

SELECT DESCRIPTION

FROM PRODUCTS

WHERE EXISTS (SELECT *

FROM ORDERS

WHERE PRODUCT = PRODUCT_ID

AND MFR = MFR_ID

AND AMOUNT >= 25000.00)

На практике при использовании подчиненного запроса в проверке exists всегда применяется форма select *.

Отметим, что в приведенном примере подчиненный запрос содержит внешнюю ссылку на столбец таблицы из главного запроса. На практике в подчиненном запросе проверки exists всегда имеется внешняя ссылка, "связывающая" подчиненный запрос со строкой, проверяемой в настоящий момент главным запросом.

Контрольные вопросы

1. Назначение предиката Between в инструкции Where.

2. Назначение предиката In в инструкции Where.

3. Назначение предиката Like в инструкции Where, как используются замещающие символы.

4. Функции выполняемые предикатом Exists.

Лекция 6

Составные условия отбора (AND OR NOT)

Для образования сложных условий отбора данных рассмотренные выше предикаты могут объединяться с использованием логических операций (AND OR NOT).

Т.к. в SQL принята трехзначная логика рассмотрим таблицы истинности этих операций для различных сочетаний аргументов.

Агрегатные функции в предложении select

Агрегатные функции применяются для обобщения значений 1-го поля, они дают единственное значение для целой группы строк таблицы.

Функции: COUNT() - определяет количество строк или значений поля, выбранных посредством запроса и не являющихся NULL - значениями.

COUNT(*) - общее количество строк, включая строки являющиеся NULL - значениями.

SUM(<имя поля>) - вычисляет арифметическую сумму всех выбранных значений данного поля.

AVG(<имя поля>) - вычисляет среднее значение для всех выбранных значений данного поля.

MAX(<имя поля>) - вычисляет наибольшее из всех выбранных значений данного поля.

MIN(<имя поля>) - вычисляет наименьшее для всех выбранных значений данного поля.

Функции агрегирования используются как имена полей в предложении SELECT, имена столбцов таблиц используются как аргументы этих функций.

Функции AVG, SUM работают только с числовыми типами данных. Функции COUNT, MAX, MIN могут использовать числовые и символьные поля в качестве аргументов. В случае применения MIN или MAX к символьным полям MIN выбирает 1-е значение (наименьшее) в соответствии с алфавитным порядком, MAX - последнее(наибольшее) значение в соответствии с алфавитным порядком.

EX:

Найти суммарный вес всех деталей:

SELECT SUM(Weight) From Parts

Агрегатные функции и Null значения