Возможная двусмысленность квантора any

Первоначально в SQL в качестве квантора существования использовалось слово ANY. Это использование оказалось достаточно запутанным и приводило к ошибкам, так как в английском языке слово any иногда означает всеобщность, а иногда – существование."Do any of you know where Baker Street is?" (Кто-нибудь из вас знает, где находится улица Бейкер-Стрит?) "I can eat more eggs than any of you." (Я могу съесть больше яиц, чем любой из вас.) В первом предложении, скорее всего, задается вопрос, есть ли хотя бы один человек, который знает, где находится улица Бейкер-Стрит. Any используется как квантор существования. Второе предложение – это хвастливое заявление о том, что я могу съесть больше яиц, чем самый большой едок из окружающих. В этом случае any используется как квантор всеобщности. Поэтому разработчики стандарта SQL-92 хотя и оставили в нем от предыдущих версий SQL слово ANY, чтобы была совместимость с предшествующими продуктами, но в то же время добавили его менее запутанный синоним – слово SOME. SQL:2OO3 также поддерживает оба квантора существования.

В Американской лиге разрешается, чтобы назначенный хиттер (designated hitter, DH) мог бить битой по мячу вместо одного из девяти игроков, играющих в обороне. (Кто такой хиттер? Это игрок с битой. А назначенный хиттер – это тот хиттер, которому не требуется играть в оборонительной позиции.) Обычно DH делают это вместо питчеров, потому что те, как известно, плохие хиттеры. Питчерам приходится тратить слишком много времени и усилий на совершенствование своего броска. Поэтому у них остается мало времени, чтобы тренироваться с битой, как это делают остальные игроки.

Скажем, вам теоретически известно, что в среднем у стартовых питчеров (питчеров, играющих с самого начала игры) Американской лиги насчитывается больше игр, в которых они бессменно подавали мяч, чем у стартовых питчеров Национальной лиги. Такой вывод основан на тех ваших наблюдениях, что назначенные хиттеры дают возможность хорошо бросающим, но слабым в обращении с битой питчерам Американской лиги оставаться на подаче до завершения игры. Так как DH уже работают с битой вместо них, то становится не важным, что питчеры – слабые хиттеры. Однако в Национальной лиге питчер, как правило, заменяется в конце игры хиттером, у которого обычно больше шансов подготовить лучший удар. Чтобы проверить свою теорию, вы составляете следующий запрос:

SELECT FirstName, LastNameFROM AMERICAN_LEAGUERWHERE CompleteGames > ALL(SELECT CompleteGamesFROM NATIONAL_LEAGUER);

Подзапрос (внутренний оператор SELECT) возвращает список, показывающий для каждого питчера Американской лиги количество тех игр, в которых он бессменно подавал мяч. Внешний же запрос возвращает имена и фамилии всех питчеров Американской лиги, которые, бессменно подавая мяч, сыграли больше игр, чем каждый питчер Национальной лиги. В этом запросе используется квантор всеобщности ALL. Таким образом, возвращаются имена и фамилии тех питчеров Американской лиги, которые имеют больше игр с бессменной подачей мяча, чем самый лучший по этому показателю питчер Национальной лиги.

Посмотрите на следующий похожий оператор:

SELECT FirstName, LastNameFROM AMERICAN_LEAGUERWHERE CompleteGames > ANY(SELECT CompleteGamesFROM NATIONAL_LEAGUER);

В этом случае вместо квантора всеобщности ALL используется квантор существования ANY. Подзапрос (внутренний, вложенный запрос) здесь такой же, как и в предыдущем примере. В результате выполнения этого подзапроса получается полный список игр, в течение которых питчеры Национальной лиги бессменно подавали мяч. А внешний подзапрос возвращает имена и фамилии всех тех питчеров Американской лиги, которые, бессменно подавая мяч, сыграли больше игр, чем какой-либо из питчеров Национальной лиги. Вот в этом-то запросе и используется квантор существования ANY. Вы можете быть уверены, что хотя бы один из питчеров Национальной лиги ни одной игры не провел бессменно на подаче. Поэтому результат, скорее всего, будет включать в себя всех питчеров Американской лиги, которые бессменно провели хотя бы одну игру.

Если заменить ключевое слово ANY эквивалентным ему SOME (какой-то), то результат будет точно такой же. И если истинно утверждение, что "хотя бы один питчер Национальной лиги ни одной игры бессменно не провел на подаче", то тогда можно сказать, что "какой-то питчер Национальной лиги ни одной игры не провел на подаче бессменно".

EXISTS

Для определения того, возвращает ли подзапрос какие-либо строки, вместе с ним можно использовать предикат EXISTS (существует). Если подзапрос возвращает хотя бы одну строку, то этот результат удовлетворяет условию EXISTS и выполняется внешний запрос. Ниже приведен пример использования предиката EXISTS.

SELECT FirstName, LastNameFROM CUSTOMERWHERE EXISTS(SELECT DISTINCT CustomerlDFROM SALESWHERE SALES.CustomerID = CUSTOMER.CustomerlD)

В таблице SALES (продажи) хранятся данные обо всех продажах, выполненных компанией. В этой таблице в поле CustomerID находятся идентификаторы покупателей, которые участвовали в какой-нибудь из сделок. В таблице CUSTOMER (покупатель) хранятся имя и фамилия каждого покупателя, но нет никакой информации о конкретных сделках.

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

Предикат EXISTS, как показано в следующем запросе, эквивалентен сравнению COUNT с нулем:

SELECT FirstName, LastNameFROM CUSTOMERWHERE 0 <>(SELECT COUNT(*)FROM SALESWHERE SALES.CustomerID = CUSTOMER.CustomerlD);

Для каждой строки таблицы SALES, в которой значение CustomerlD равно какому-либо значению CustomerlD из таблицы CUSTOMER, этот оператор выводит столбцы FirstName (имя) и LastName (фамилия) из таблицы CUSTOMER. Поэтому для каждой сделки, отмеченной в таблице SALES, этот оператор выводит имя и фамилию того покупателя, который в ней участвовал.

UNIQUE

Вместе с подзапросом, как и предикат EXISTS, можно также использовать предикат UNIQUE (уникальный). Если первый из этих предикатов является истинным тогда, когда подзапрос возвращает хотя бы одну строку, то второй из них будет истинным тогда, когда среди возвращенных подзапросом строк нет двух одинаковых. Другими словами, предикат UNIQUE будет истинным, если все возвращаемые подзапросом строки будут уникальными. Проанализируйте следующий пример:

SELECT FirstName, LastNameFROM CUSTOMERWHERE UNIQUE(SELECT CustomerID FROM SALESWHERE SALES.CustomerID = CUSTOMER.CustomerID);

Этот оператор возвращает только имена и фамилии всех новых покупателей, которые участвовали лишь в одной из сделок, указанных в таблице SALES. Два значения NULL считаются не равными друг другу и, следовательно, уникальными. И когда ключевое слово UNIQUE применяется к таблице, полученной в результате выполнения подзапроса, а в этой таблице никаких строк, кроме двух неопределенных, больше нет, то и тогда предикат UNIQUE является истинным.

DISTINCT

Предикат DISTINCT (отличающийся) похож на UNIQUE, за исключением отношения к значениям NULL. Если в таблице, полученной в результате выполнения подзапроса, все значения являются уникальными, тогда они отличаются друг от друга. Однако, в отличие от результата предиката UNIQUE, если к такой таблице применить ключевое слово DISTINCT, а в ней, кроме двух неопределенных, больше никаких строк нет, то предикат DISTINCT является ложным. Два значения NULL не считаются отличающимися друг от друга, хотя и считаются уникальными. Такая странная ситуация выглядит противоречиво, но этому есть свое объяснение. Дело в том, что в некоторых ситуациях два значения NULL должны считаться отличными друг от друга, а в некоторых – одинаковыми. Тогда в первом случае надо использовать UNIQUE, а во втором – DISTINCT.

OVERLAPS

Предикат OVERLAPS (перекрывает) применяется для того, чтобы определить, не перекрывают ли друг друга два промежутка времени. Он полезен тогда, когда нужно избежать "накладок" в расписании. Когда два промежутка времени перекрываются, то этот предикат возвращает значение True. Если они не перекрываются, то будет возвращено значение False.

Промежуток времени можно указать двумя способами: в виде начального и конечного времени или в виде начального времени и длительности. Вот несколько примеров:

(TIME '2:55:00', INTERVAL '1' HOUR)OVERLAPS(TIME '3:30:00', INTERVAL '2' HOUR)

В только что приведенном примере будет возвращено значение True, так как 3:30 наступает после 2:55 меньше чем через час.

(TIME '9:00:00', TIME '9:30:00')OVERLAPS(TIME '9:29:00', TIME '9:31:00')

Во втором примере будет возвращено значение True, потому что два промежутка времени перекрываются в течение одной минуты.

(TIME '9:00:00', TIME '10:00:00')OVERLAPS(TIME '10:15:00', INTERVAL '3' HOUR)

В третьем примере будет возвращено значение False, так как два промежутка времени не перекрываются.

(TIME '9:00:00', TIME '9:30:00')OVERLAPS(TIME '9:30:00', TIME '9:35:00')

И наконец, в последнем примере будет возвращено значение False – хотя два промежутка времени и являются смежными, но они не перекрываются.

MATCH

В главе 5 шла речь о ссылочной целостности, которая включает в себя поддержание согласованности в многотабличной базе данных. Целостность может быть нарушена, если в дочернюю таблицу добавить строку, у которой нет соответствующей строки в родительской таблице. Можно вызвать похожие сложности, удалив из родительской таблицы строку и оставив в дочерней те строки, которые соответствуют удаленной.

Скажем, вы, ведя свой бизнес, собираете данные о своих покупателях в таблицу CUSTOMER (покупатель), а данные о продажах заносите в таблицу SALES (продажи). Вам не хочется добавлять строку в SALES до тех пор, пока данные о покупателе, участвующем в соответствующей сделке, не появятся в таблице CUSTOMER. Вам также не хочется удалять из CUSTOMER данные о покупателе, если он участвовал в сделках, информация о которых все еще хранится в таблице SALES. Перед тем как выполнять вставку или удаление, вам, возможно, захочется проверить, не приведет ли к нарушениям целостности выполнение со строкой какой-либо из этих операций. Такую проверку может выполнить предикат MATCH (соответствие).

Как используется предикат MATCH, можно узнать с помощью примера, где применяются опять же таблицы CUSTOMER и SALES. CustomerID (идентификатор покупателя) – это первичный ключ таблицы CUSTOMER, и работает он как внешний ключ таблицы SALES. В каждой строке таблицы CUSTOMER должно быть уникальное значение CustomerlD, не равное NULL. А в таблице SALES ключ CustomerlD не является уникальным, потому что в ней повторяются его значения, относящиеся к тем, кто покупал больше одного раза. Это нормальная ситуация, которая не угрожает целостности, потому что в этой таблице CustomerlD является не первичным, а внешним ключом.

Совет:
По-видимому, в столбце CustomerlD таблицы SALES могут быть и значения NULL, потому что кто-то может зайти к вам с улицы, купить что-то и выйти еще до того, как вы сможете ввести его или ее имя, фамилию и адрес в таблицу CUSTOMER. Тогда в дочерней таблице может появиться строка, у которой нет соответствующей строки в родительской таблице. Чтобы справиться с этой трудностью, можно включить в таблицу CUSTOMER строку для "общего" пользователя и заносить все эти анонимные продажи в базу на его идентификатор.

Скажем, к кассиру подходит покупательница и утверждает, что 18 мая 2003 года она купила истребитель-невидимку F-117A "Стелс". Теперь же она хочет вернуть самолет, потому что его, словно авианосец, видно на вражеских радарах. Ее заявление может быть подтверждено с помощью проверки вашей базы SALES с помощью MATCH. Прежде всего необходимо найти в столбце CustomerlD идентификатор покупательницы и присвоить его значение переменной.vcustid, а затем можно использовать следующий синтаксис, в котором применяются столбцы CustomerlD, ProductID (идентификатор товара), SaleDate (дата продажи):

… WHERE (-.vcustid, 'F-117-A1, '2003-05-18')MATCH(SELECT CustomerID, ProductID, SaleDateFROM SALES).

Если есть запись о продаже с этим идентификатором пользователя, товаром и датой, то предикат MATCH возвращает значение True. А вы возвращаете покупательнице деньги. (Примечание: если какое-либо значение в первом аргументе предиката MATCH будет неопределенным, то всегда будет возвращаться значение True.)

Технические подробности:
Разработчики языка SQL добавили в него предикаты MATCH и UNIQUE по одной и той же причине – эти предикаты дают возможность явно выполнять проверки, которые определены для неявных ограничений, связанных со ссылочной целостностью и уникальностью.

Предикат MATCH имеет такой общий вид:

Значение_типа_записи ROW MATCH [UNIQUE] [SIMPLE| PARTIAL | FULL ] Подзапрос

Ключи UNIQUE (уникальный), SIMPLE (простой), PARTIAL (частичный) и FULL (полный) связаны с правилами обработки выражения типа записи, имеющего столбцы с неопределенными значениями. Правила для предиката MATCH являются точной копией соответствующих правил ссылочной целостности.