Материал: SQL в вопросах и задачах
56
SELECT DISTINCT
SUBJ
_
ID
FROM
EXAM
_
MARKS
A
WHERE EXISTS
(
SELECT
*
FROM
EXAM
_
MARKS
B
WHERE
A
.
SUBJ
_
ID
=
B
.
SUBJ
_
ID
AND
A
.
STUDENT
_
ID
< >
B
.
STUDENT
_
ID
);
Ч а сто
EXISTS
п р именяется
с
оп ера тором
NOT
(
п о
-
русски
NOT
EXISTS
интерп ретируется
,
ка к
“
не
сущест в ует
…
”
).
Е сли
п ред ыд ущ ий
з а п рос
сформулиров а ть
след ующ им
образ ом
–
на йти
ид ентифика торы
п ред метов
обуч ения
,
которые
сд а в а лись
од ним
и
только
од ним
студ ентом
(
д ругими
слов а ми
,
д ля
которых
не
сущ еств ует
д р угого
сд а в а в шего
студ ента
),
то
д оста точ но
п росто
п оста в ить
NOT
п еред
EXISTS
.
След ует
иметь
в
в ид у
,
ч то
в
п од з а п росе
,
ука з ыв а емом
в
оп ера торе
EXISTS
,
нельзя
и спо льзо ва т ь
а гр еги р ующ и е
ф ункци и
.
Воз можности
п рименения
в ложенных
з а п росов
в есьма
раз нообраз ны
.
Н а п ример
,
п усть
из
та блицы
STUDENT
требуется
из в леч ь
строки
д ля
ка жд ого
студ ента
,
сд а в шего
более
од ного
п ред мета
.
SELECT
*
FROM
STUDENT
FIRST
WHERE EXISTS
(
SELECT
SUBJ
_
ID
FROM
EXAM
_
MARKS
SECOND
GROUP
BY
SUBJ
_
ID
HAVING
COUNT
(
SUBJ
_
ID
) >
1
WHERE
FIRST
.
STUDENT
_
ID
=
SECOND
.
STUDENT
_
ID
);
УП Р АЖ НЕ НИ Я
33.
Н а п ишите
з а п рос
с
EXISTS
,
п оз в оляющ ий
в ыв ести
д а нные
обо
в сех
студ ента х
обуч а ющ ихся
в
в уз а х
,
имеющ их
рейтинг
в ыше
300.
34.
Н а п ишите
п ред ыд ущ ий
з а п рос
,
исп ользуя
соед инения
.
35.
Н а п ишите
з а п рос
с
EXISTS
,
в ыбирающ ий
св ед ения
обо
в сех
студ ента х
,
д ля
которых
в
том
же
город е
,
гд е
жив ет
студ ент
,
сущ еств уют
57
унив ерситеты
,
в
котор ых
он
не
уч ится
.
36.
Н а п ишите
з а п рос
,
в ыбира ющ ий
из
та блицы
SUBJECT
д а нные
о
на з в а ниях
п ред метов
обуч ения
,
э кз а мены
п о
которым
сд а ны
более
ч ем
од ним
студ ентом
.
2.12.
О перат оры
сравнения
с
м нож еством
з начений
IN
,
ANY
,
ALL
О п ера торы
срав нения
с
множеств ом
з на ч ений
имеют
след ующ ий
смысл
.
IN
Ра в но
любому
из
з на ч ений
,
п олуч енных
в о
в нутреннем
з а п росе
.
NOT IN
Не
р а в но
ни
од ному
из
з на ч ений
,
п олуч енных
в о
в нутреннем
з а п росе
.
=
ANY
То
же
,
ч то
и
IN
.
Соотв етств ует
логич ескому
оп ера тору
OR
.
>
ANY
,
>
=
ANY
Бо льш е
,
чем
(
либо
бо льш е
и ли
р а в но
)
любое
п олуч енное
ч исло
.
Э кв ив а лентно
>
или
> =
д ля
са мого
меньшего
п олуч енного
ч исла
.
<
ANY
,
<
=
ANY
М еньш е
,
чем
(
либо
м еньш е
и ли
р а в но
)
любое
п олуч енное
ч исло
.
Э кв ив а лент
<
или
< =
д ля
са мого
большего
п олуч енного
ч исла
.
=
ALL
Ра в но
в сем
п олуч енным
з на ч ениям
.
Э кв ив а лентно
логич ескому
оп ератору
AND
.
>
ALL
,
>
=
ALL
Бо льш е
,
чем
(
либо
бо льш е
и ли
р а в но
)
в се
п олуч енные
ч исла
.
Э кв ив а лент
>
или
> =
д ля
са мого
большего
п олуч енного
ч исла
.
<
ALL
,
<
=
ALL
М еньш е
,
чем
(
либо
м еньш е
и ли
р а в но
)
в се
п олуч енные
ч исла
.
Э кв ив а лентно
<
или
< =
са мого
меньшего
п олуч енного
ч исла
.
След ует
иметь
в
в ид у
,
ч то
в
некоторых
СУ Б Д
п од д ержив а ются
не
в се
из
э тих
оп ер а торов
.
П рим еры
з апросов
с
испол ь з ованием
приведенны х
операт оров
.
Выбрать
св ед ения
о
студ ента х
,
п рожив а ющ их
в
город е
,
гд е
ра сп оложен
унив ерситет
,
в
котором
они
уч а тся
.
58
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
59
(
SELECT
RATING
FROM
UNIVERSITY
WHERE
CITY
=
‘
Во р о н еж
’
);
В
э том
з а п росе
в место
ALL
можно
та кже
исп ользов а ть
ANY
.
(
Проа на лиз ируйте
,
ка к
в
э том
случ а е
из менится
смысл
п рив ед енного
з а п роса
?)
SELECT
*
FROM
UNIVERSITY
WHERE NOT
RATING
>
ANY
(
SELECT
RATING
FROM
UNIVERSITY
WHERE
CITY
=
‘Во р о н еж ’
);
2.13.
О собенности
прим енения
операт оров
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),
в
то
в ремя
ка к
з а п рос
60
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
B
WHERE
A
.
RATING
>=
B
.
RATING
AND
B
.
CITY
=
‘
М о сква ’
);
При
отсутств ии
в
та блица х
NULL
оба
э ти
з а п роса
в ед ут
себя
сов ершенно
од ина ков о
.
Пусть
теп ерь
в
та блице
UNIVERSITY
есть
стр ока
с
NULL
-
з на ч ениями
в
столбце
RATING
.
В
в ерсии
з а п р оса
c
ANY
в
основ ном
з а п росе
,
когд а
в ыбирается
п оле
RATING
с
NULL
,
п ред ика т
п ринима ет
з на ч ение
UNKNOWN
и
строка
не
в ключ а ется
в
соста в
в ыход ных
д а нных
.
Во
в тором
же
в а р иа нте
з а п роса
,
когд а
NOT
EXISTS
в ыбирает
э ту
стр оку
в
основ ном
з а п росе
,
NULL
-
з на ч ение
исп ользуется
в
п ред ика те
п од з а п роса
,
п рисв а ив а я