Студопедия

КАТЕГОРИИ:

АвтоАвтоматизацияАрхитектураАстрономияАудитБиологияБухгалтерияВоенное делоГенетикаГеографияГеологияГосударствоДомЖурналистика и СМИИзобретательствоИностранные языкиИнформатикаИскусствоИсторияКомпьютерыКулинарияКультураЛексикологияЛитератураЛогикаМаркетингМатематикаМашиностроениеМедицинаМенеджментМеталлы и СваркаМеханикаМузыкаНаселениеОбразованиеОхрана безопасности жизниОхрана ТрудаПедагогикаПолитикаПравоПриборостроениеПрограммированиеПроизводствоПромышленностьПсихологияРадиоРегилияСвязьСоциологияСпортСтандартизацияСтроительствоТехнологииТорговляТуризмФизикаФизиологияФилософияФинансыХимияХозяйствоЦеннообразованиеЧерчениеЭкологияЭконометрикаЭкономикаЭлектроникаЮриспунденкция

Примеры запросов с использованием приведенных операторов

Стр 1 из 2Следующая ⇒

Практическая работа 15

Операторы сравнения с множеством значений in, any, аLL

Операторы сравнения с множеством значений имеют следующий смысл.

 

 

IN Равно любому из значений, полученных во внутреннем запросе.
NOT IN Не равно ни одному из значений, полученных во внутреннем запросе
= ANY То же, что и IN. Соответствует логическому оператору OR.
> ANY, > = ANY Больше, чем (либо больше или равно) любое полученное число. Эквивалентно > или > = для самого меньшего полученного числа
<ANY, < = ANY Меньше, нем (либо меньше или равно) любое полученное число. Эквивалент < или < = для самого большего полученного числа
= ALL Равно всем полученным значениям. Эквивалентно логическому оператору AND.
ALL> ALL, >= ALL Больше, чем (либо больше или равно) все полученные числа. Эквивалент > или > = для самого большего полученного числа.
< ALL, <= ALL Меньше, чем (либо меньше или равно) все полученные числа. Эквивалентно < или < = самого меньшего полученного числа.

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

Примеры запросов с использованием приведенных операторов

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

SELECT *

FROM STUDENT S

WHERE CITY = ANY

(SELECT CITY

FROM UNIVERSITY U

WHERE U.UNIV_ID - S.UNIV_ID);

Другой вариант этого запроса:

select •

from student s

where city in

(SELECT CITY

FROM UNIVERSITY U

WHERE U.UNIV_ID - S.UNIV_ID);

Выборка данных об идентификаторах студентов, у которых оценки превосходят величину, по крайней мере, одной из оценок, полученных ими же 6 октября 1999 года.

SELECT DISTINCT STUDENT_ID

FROM EXAM_MARKS

WHERE MARK > ANY

(SELECT MARK

FROM EXAM_MARKS

WHERE EXAM DATE = '06/10/1999');

Оператор all, как правило, эффективно используется с неравенствами, а не с равенствами, поскольку значение равно всем, которое должно получиться в этом случае в результате выполнения
подзапроса, может иметь место, только если все результаты идентичны. Такая ситуация практически не может быть реализована, так как если подзапрос генерирует множество различных значений, то никакое одно значение не может быть равно сразу всем значениям в обычном смысле. В SQL выражение < > all реально означает не равно ни одному из результатов подзапроса.

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

SELECT *

FROM UNIVERSITY

WHERE RATING > ALL

(SELECT RATING

FROM UNIVERSITY

WHERE CITY = 'Воронеж') ;

В этом запросе вместо all можно использовать any (проанализируйте, как в этом случае изменится смысл приведенного запроса):

select *

from university

where not rating > any

(select rating

from university

WHERE CITY - 'Воронеж');

 

Особенности применения операторов any, all,
exists при обработке пустых значений (null)
Необходимо иметь в виду, что при обработке NULL-значений следует учитывать различие реакции на них операторов EXISTS, ANY И ALL.

Когда правильный подзапрос не генерирует никаких выходных данных, оператор ALL автоматически принимает значение истина, а оператор any — значение ложь.

Запрос

select *

from university

where rating > any

(select rating

from university                                                                                                                                                                                                          WHERE CITY = 'New York');

не генерирует выходных данных (подразумевается, что в базе нет данных об университетах города New York), в то время как запрос

SELECT *

FROM UNIVERSITY

WHERE RATING > ALL

(SELECT RATING

FROM UNIVERSITY

WHERE CITY = 'New York');

полностью воспроизведет таблицу university.

Использование NULL-значений создает определенные проблемы для рассматриваемых операторов. Когда в SQL сравниваются два значения, одно из которых NULL-значение, результат.
Принимает значение unknown (неизвестно). Предикат unknown, так же, как и FALSE-предикат, создает ситуацию, когда строка не включается в состав выходных данных, но результат при этом будет различен для разных типов запросов, в зависимости от использования в них ALL или any вместо exists. Рассмотрим
в качестве примера две реализации запроса: найти все данные об университетах, рейтинг которых меньше рейтинга любого университета в Москве.

1) SELECT *

FROM UNIVERSITY

WHERE RATING < ANY

(SELECT RATING

FROM UNIVERSITY

WHERE CITY - 'Москва');

2) SELECT *

FROM UNIVERSITY A

WHERE NOT EXISTS

(SELECT *

FROM UNIVERSITY В

WHERE A.RATING >= B.RATING

AND B.CITY = 'Москва');

При отсутствии в таблицах null оба эти запроса ведут себя совершенно одинаково. Пусть теперь в таблице university есть строка с NULL-значениями в столбце rating. В версии запроса с ANY в основном запросе, когда выбирается поле rating с null, предикат принимает значение unknown и строка не включается в состав выходных данных. Во втором же варианте запроса, когда not exists выбирает эту строку в основном запросе, NULL-значение используется в предикате подзапроса, присваивая ему значение unknown. Поэтому в результате выполнения подзапроса не будет получено ни одного значения, и подзапрос примет значение ложь. Это в свою очередь сделает not EXISTS истинным, и, следовательно, строка с NULL-значением в поле rating попадет в выходные данные. По смыслу запроса такой результат является неправильным, так как на самом деле рейтинг университета, описываемого данной строкой, может быть и больше рейтинга какого-либо московского университета
(он просто неизвестен). Указанная проблема связана с тем, что значение EXISTS всегда принимает значения истина или ложь, и никогда — unknown . Это является доводом для использования в таких случаях оператора any вместо exists.







Операторы сравнения с множеством значений in, any, аLL

Операторы сравнения с множеством значений имеют следующий смысл.

 

 

IN Равно любому из значений, полученных во внутреннем запросе.
NOT IN Не равно ни одному из значений, полученных во внутреннем запросе
= ANY То же, что и IN. Соответствует логическому оператору OR.
> ANY, > = ANY Больше, чем (либо больше или равно) любое полученное число. Эквивалентно > или > = для самого меньшего полученного числа
<ANY, < = ANY Меньше, нем (либо меньше или равно) любое полученное число. Эквивалент < или < = для самого большего полученного числа
= ALL Равно всем полученным значениям. Эквивалентно логическому оператору AND.
ALL> ALL, >= ALL Больше, чем (либо больше или равно) все полученные числа. Эквивалент > или > = для самого большего полученного числа.
< ALL, <= ALL Меньше, чем (либо меньше или равно) все полученные числа. Эквивалентно < или < = самого меньшего полученного числа.

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

Примеры запросов с использованием приведенных операторов

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

SELECT *

FROM STUDENT S

WHERE CITY = ANY

(SELECT CITY

FROM UNIVERSITY U

WHERE U.UNIV_ID - S.UNIV_ID);

Другой вариант этого запроса:

select •

from student s

where city in

(SELECT CITY

FROM UNIVERSITY U

WHERE U.UNIV_ID - S.UNIV_ID);

Выборка данных об идентификаторах студентов, у которых оценки превосходят величину, по крайней мере, одной из оценок, полученных ими же 6 октября 1999 года.

SELECT DISTINCT STUDENT_ID

FROM EXAM_MARKS

WHERE MARK > ANY

(SELECT MARK

FROM EXAM_MARKS

WHERE EXAM DATE = '06/10/1999');

Оператор all, как правило, эффективно используется с неравенствами, а не с равенствами, поскольку значение равно всем, которое должно получиться в этом случае в результате выполнения
подзапроса, может иметь место, только если все результаты идентичны. Такая ситуация практически не может быть реализована, так как если подзапрос генерирует множество различных значений, то никакое одно значение не может быть равно сразу всем значениям в обычном смысле. В SQL выражение < > all реально означает не равно ни одному из результатов подзапроса.

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

SELECT *

FROM UNIVERSITY

WHERE RATING > ALL

(SELECT RATING

FROM UNIVERSITY

WHERE CITY = 'Воронеж') ;

В этом запросе вместо all можно использовать any (проанализируйте, как в этом случае изменится смысл приведенного запроса):

select *

from university

where not rating > any

(select rating

from university

WHERE CITY - 'Воронеж');

 

Особенности применения операторов any, all,
exists при обработке пустых значений (null)
Необходимо иметь в виду, что при обработке NULL-значений следует учитывать различие реакции на них операторов EXISTS, ANY И ALL.

Когда правильный подзапрос не генерирует никаких выходных данных, оператор ALL автоматически принимает значение истина, а оператор any — значение ложь.

Запрос

select *

from university

where rating > any

(select rating

from university                                                                                                                                                                                                          WHERE CITY = 'New York');

не генерирует выходных данных (подразумевается, что в базе нет данных об университетах города New York), в то время как запрос

SELECT *

FROM UNIVERSITY

WHERE RATING > ALL

(SELECT RATING

FROM UNIVERSITY

WHERE CITY = 'New York');

полностью воспроизведет таблицу university.

Использование NULL-значений создает определенные проблемы для рассматриваемых операторов. Когда в SQL сравниваются два значения, одно из которых NULL-значение, результат.
Принимает значение unknown (неизвестно). Предикат unknown, так же, как и FALSE-предикат, создает ситуацию, когда строка не включается в состав выходных данных, но результат при этом будет различен для разных типов запросов, в зависимости от использования в них ALL или any вместо exists. Рассмотрим
в качестве примера две реализации запроса: найти все данные об университетах, рейтинг которых меньше рейтинга любого университета в Москве.

1) SELECT *

FROM UNIVERSITY

WHERE RATING < ANY

(SELECT RATING

FROM UNIVERSITY

WHERE CITY - 'Москва');

2) SELECT *

FROM UNIVERSITY A

WHERE NOT EXISTS

(SELECT *

FROM UNIVERSITY В

WHERE A.RATING >= B.RATING

AND B.CITY = 'Москва');

При отсутствии в таблицах null оба эти запроса ведут себя совершенно одинаково. Пусть теперь в таблице university есть строка с NULL-значениями в столбце rating. В версии запроса с ANY в основном запросе, когда выбирается поле rating с null, предикат принимает значение unknown и строка не включается в состав выходных данных. Во втором же варианте запроса, когда not exists выбирает эту строку в основном запросе, NULL-значение используется в предикате подзапроса, присваивая ему значение unknown. Поэтому в результате выполнения подзапроса не будет получено ни одного значения, и подзапрос примет значение ложь. Это в свою очередь сделает not EXISTS истинным, и, следовательно, строка с NULL-значением в поле rating попадет в выходные данные. По смыслу запроса такой результат является неправильным, так как на самом деле рейтинг университета, описываемого данной строкой, может быть и больше рейтинга какого-либо московского университета
(он просто неизвестен). Указанная проблема связана с тем, что значение EXISTS всегда принимает значения истина или ложь, и никогда — unknown . Это является доводом для использования в таких случаях оператора any вместо exists.




12Следующая ⇒






Последнее изменение этой страницы: 2018-06-01; просмотров: 160.

stydopedya.ru не претендует на авторское право материалов, которые вылажены, но предоставляет бесплатный доступ к ним. В случае нарушения авторского права или персональных данных напишите сюда...