Главная | Обратная связь

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

Пример 18.21. Найти номера служащих отдела номер 65, зарплата которых в этом отделе не является минимальной.

SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL > SOME (SELECT EMP1.EMP_SAL FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);

Одна из возможных альтернативных формулировок этого запроса может основываться на использовании предиката EXISTS (пример 18.21.1):

SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EXISTS(SELECT * FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_SAL > EMP1.EMP_SAL);

Вот альтернативная формулировка этого запроса, основанная на использовании агрегатной функции MIN (пример 18.21.2):

SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL > (SELECT MIN(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);

Пример 18.22. Найти номера и имена служащих отдела 65, однофамильцы которых работают в этом же отделе.

SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND EMP_NAME = SOME (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO);

Заметим, что эта формулировка эквивалентна следующей формулировке (пример 18.22.1):

SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND EMP_NAME IN (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO);

Возможна формулировка с использованием агрегатной функции COUNT (пример 18.22.2):

SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND (SELECT COUNT(*) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO ) >= 1;

Наиболее лаконичным образом этот запрос можно сформулировать с использованием соединения ( пример 18.22.3):

SELECT DISTINCT EMP.EMP_NO, EMP.EMP_NAME FROM EMP, EMP EMP1 WHERE EMP.DEPT_NO = 65 AND EMP.EMP_NAME = EMP1.EMP_NAME AND EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO;

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

Пример 18.23. Найти номера служащих отдела номер 65, зарплата которых в этом отделе является максимальной.

SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL >= ALL(SELECT EMP1.EMP_SAL FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);

Одна из возможных альтернативных формулировок этого запроса может основываться на использовании предиката NOT EXISTS (пример 18.23.1):

SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND NOT EXISTS (SELECT * FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_SAL < EMP1.EMP_SAL);

Можно сформулировать этот же запрос с использованием агрегатной функции MAX (пример 18.23.2):

SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL = (SELECT MAX(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);

Пример 18.24. Найти номера и имена служащих, не имеющих однофамильцев.

SELECT EMP_NO, EMP_NAME FROM EMP WHERE EMP_NAME <> ALL (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO);

Этот запрос можно переформулировать на основе использования предиката NOT EXISTS или агрегатной функции COUNT (по причине очевидности мы не приводим эти формулировки), но, в отличие от случая в примере 18.22.3, формулировка в виде запроса с соединением здесь не проходит. Формулировка запроса

SELECT DISTINCT EMP_NO, EMP_NAME FROM EMP, EMP EMP1 WHERE EMP.EMP_NAME <> EMP1.EMP_NAME AND EMP1.EMP_NO <> EMP.EMP_NO);

эквивалентна формулировке

SELECT EMP_NO, EMP_NAME FROM EMP WHERE EMP_NAME <> SOME (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO);

Очевидно, что этот запрос является бессмысленным («Найти служащих, для которых имеется хотя бы один не однофамилец»).

Предикат match

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

match_predicate ::= row_value_constructor MATCH [ UNIQUE ] [ SIMPLE | PARTIAL | FULL ] query_expression

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

Пусть x обозначает строку-первый операнд. Тогда:

• Если отсутствует спецификация вида сопоставления или специфицирован тип сопоставления SIMPLE, то:
• если значение некоторого столбца x является неопределенным, то значением условия является true;
• если в x нет неопределенных значений, то:
• если не указано UNIQUE, и в результате выражения запроса существует (возможно, не уникальная) строка s в такая, что x = s, то значением условия является true;
• если указано UNIQUE, и в результате выражения запроса существует уникальная строка s, такая, что x = s, то значением условия является true;
• в противном случае значением условия является false.
• Если в условии присутствует спецификация PARTIAL, то:
• если все значения в x являются неопределенными, то значение условия есть true;
• иначе:
• если не указано UNIQUE, и в результате выражения запроса существует (возможно, не уникальная) строка s, такая, что каждое отличное от неопределенного значение x равно соответствующему значению s, то значение условия есть true;
• если указано UNIQUE, и в результате выражения запроса существует уникальная строка s, такая, что каждое отличное от неопределенного значение x равно соответствующему значению s, то значение условия есть true;
• в противном случае значение условия есть false.
• Если в условии присутствует спецификация FULL, то:
• если все значения в x неопределенные, то значение условия есть true;
• если ни одно значение в x не является неопределенным, то:
• если не указано UNIQUE, и в результате выражения запроса существует (возможно, не уникальная) строка s, такая, что x = s, то значение условия есть true;
• если указано UNIQUE, и в результате выражения запроса существует уникальная строка s, такая, что x = s, то значение условия есть true;
• в противном случае значение условия есть false.
• в противном случае значение условия есть false.