29 Sql: Подзапросы.

Использование вложенных запросов (подзапросы)

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

SELECT * FROM USPWHERE SNUM = (SELECT SNUM FROM STUDENTS

WHERE SFAM = ‘Поляков’);

Результат этого запроса будет следующий:

UNUM OCENKA UDATE SNUM PNUM

--------------------------------------

1001 5 10/06/1999 3412 2001

1004 4 12/06/1999 3412 2003

Чтобы выполнить основной запрос, SQL сначала должен оценить внутренний запрос (его называют подзапросом) внутри предложения WHERE, Происходит это традиционным образом, т.е. исполняется вложенный запрос, извлекающий необходимые для определения значения предиката данные, а только затем -.основной. Разумеется, подзапрос должен выбрать только одно поле, а тип данных этого поля должен совпадать с тем значением, с которым он будет сравниваться в предикате.

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

В некоторых случаях стоит использовать DISTINCT для того. чтобы в подзапросе получить одиночное значение. Предположим, что преподаватели могут вести занятия по разным дисциплинам Тогда для получения ответа на вопрос о том, какие дисциплины ведет преподаватель Никулина, можно воспользоваться запросом:

SELECT * FROM PREDMET

WHERE TNUM = (SELECT DISTINCT TNUM

FROM TEACHERS WHERE TFAM * ‘Никулина’);

В результате будет получено:

PNUM PNAME TNUM HOURS COURS

------------------------------------

2001 Физика 4001 34 1

Подзапрос установил, что значение поля TNUM совпало с Фамилией Никулина при значении 4001, а затем основной запрос выделил все записи с этим значением TNUM из таблицы предметов Т.к., вообще говоря, могло получиться, что преподаватель ведет несколько предметов, то фраза DISTINCT в данном случае обязательна - если подзапрос возвратил бы более одного значения, то это вызвало бы ошибку.

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

<ВЫРАЖЕНИЕ> <ОПЕРАТОР> <ПОДЗАПРОС>,

и ни в коем случае не

<ПОДЗАПРОС> <ОПЕРАТОР> <ВЫРАЖБНИЕ>,

или

<ПОДЗАПРОС> <ОПЕРАТОР> <ПОДЗАПРОС>,

Иначе говоря, предыдущий пример, записанный следующим образом:

SELECT *

FROM PREDMET

WHERE (SELECT DISTINCT TNUM

FROM TEACHERS WHERE TFAM -= ‘Никулина’) = TNUM;

является неверным.

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

SELECT *

FROM USP

WHERE OCENKA >

(SELECT AVG (OCENKA) FROM USP) ;

Вывод такого запроса следующий:

UNUM OCENKA UDATE SNUM PNUM

----------------------------------------

1001 5 10/06/1999 3412 2001

1005 5 12/06/1999 3416 2004

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

Не стоит забывать, что сгруппированные агрегатные функции определенные в терминах предложения GROU? BY, могут выдавать многочисленные значения, а значит, не допускаются в под запросах такого характера. Даже если GROUP BY или HAVING используются так, что только одна группа значений выводится; помощью подзапроса, все равно команда будет отклонена. Кстати говоря, можно использовать подзапросы, которые производят любое число строк, если используется специальный оператор IN (операторы BETWEEN, LIKE, и IS NULL не могут использоваться с подзапросами). IN определяет набор значений предиката одно из которых должно совпадать с другим по порядку При использовании IN с подзапросом, SQL просто формирует этот набор из вывода подзапроса, а значит, допускается использование IN для того, чтобы выполнить такой же подзапрос. Например ,запрос

SELECT *

FROM PREDMET

WHERE PREDMET.TNUM IN

(SELECT TEACHERS.TNUM

FROM TEACHERS WHERE TEACHERS. TFAM

BETWEEN ‘И’ AND ‘ С ‘ ) ;

Вывод этого запроса такой:

PNUME TNUM HOURS COURS

---------------------------------

2002 Химия 4002 68 1

2003 Математика 4003 68 1

2005 Экономика 4004 17 3

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

SELECT PREDMET. PNUM, PREDMET . PNAME ,

TEACHERS. TNUM, PREDMET .HOURS,

PREDMET. COURS

FROM PREDMET, TEACHERS

WHERE TEACHERS . TNUM = PREDMET. TNUM

AND TEACHERS. TFAM BETWEEN ‘И’ AND ‘C’;

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

И еще один интересный момент: в любой ситуации, где применяется реляционный оператор равенства (=), можно использовать IN. В отличие от первого, IN не может заставить запрос подпеть неудачу, если подзапросом выбрано больше чем одно значение. Это может быть или преимуществом или недостатком. Например: уже рассмотренный нами выше запрос можно переписать следующим образом:

SELKCT *

FROM PREDMET WHERE TNUM IN (SELECT TNUM

FROM TEACHERS

WHERE TFAM * ‘Никулина’);

Таким образом, подзапросы всегда определяют одиночные столбцы - это обязательно, поскольку выбранный вывод сравнивается с одиночным значением. Подтверждением этому является то, что команда SELECT * не может использоваться в подзапросе. В подзапросе допускается использовать выражение, основанное на поле, а не просто само поле, в предложении SELECT. Это может быть выполнено с помощью реляционных операторов иди при использовании IN. Примером может служить следующий запрос:

SELECT *

FROM PREDMET

WHERE PNUM =

(SELECT PNUM-1

FROM PREDMET

WHERE PNAME = ‘Философия’);

Вывод для этого запроса такой:

PNUM PNAME TNUM HOURS COURS

-------------------------------------

2003 Математика 4003 68 1

Этот запрос находит информацию об учебном предмете, код которого на 1 меньше кода философии. Разумеется, поле PNUM не должно содержать повторяющихся значений, иначе запрос вызовет ошибку.

Можно также использовать подзапросы внутри предложения HAVING. Эти подзапросы могут использовать свои собственные агрегатные функции, если они не производят многочисленных значений, или использовать GROUP BY или HAVING. Например, рассмотрим запрос:

SELECT OCENKA, COUNT (DISTINCT SNUM)

FROM USP GROUP BY OCENKA HAVING OCENKA > =

(SELECT AVG (OCENKA)

FROM. USP

WHERE PNUM = 2003);

Вывод для этого запроса следующий:

OCENKA

2

4 2

Данный запрос подсчитывает количество студентов с оценками выше средней, чем по дисциплине с PNUM = 2003.

Теперь несколько слов об объединении запросов и использовании при этом подзапросов. Операция, которая бывает часто полезна - это объединение из двух запросов, в котором второй запрос выбирает строки, исключенные первым. Наиболее часто это делают для того, чтобы не исключать строки, которые не удовлетворили предикату при объединении таблиц. Этот процесс принято называть внешним объединением.

Предположим, что некоторые из студентов еще не получили оценку, однако уже внесены в таблицу USP. Например, в эту таблицу добавлена запись {1006, NULL, NULL, 3416, NULL}. Если возникает необходимость в просмотре успеваемости студентов по дисциплине, не учитывая тех, кто еще не получил оценку. Этого можно достичь, формируя объединение из двух запросов, один из которых выполняет объединение, а другой выбирает студентов с NULL значениями поля OCENKA. Этот последний запрос должен вставлять сообщение в поля, соответствующие полю PNAME, и значение 0 в поле OCENKA в первом запросе. Как было рассмотрено ранее, можно вставлять текстовые строки в вывод, чтобы идентифицировать запрос, который вывел данную строку. Использование этой методики во внешнем объединении дает возможность применять предикаты для классификации, а не для исключения. Следующий запрос выполняет эти действия:

SELECT USP.SNTJM, STUDENTS. SFAM,

- PREDMET . PNAME , USP. OCENKA

FROM USP, STUDENTS, PREDMET

WHERE USP.SNUM = STUDENTS.SNUM

AND USP.PNUM = PREDMET.PNUM

UNION

SELECT USP.SNUM, STUDENTS. SFAM,

‘НЕТ’ , 0 FROM USP, STUDENTS

WHERE USP.SNUM = STUDENTS .SNUM

AND NOT USP.OCENKA = ANY

(SELECT OCENKA

FROM USP) ORDER BY 2 ASC;

Вывод этого запроса следующий:

3414 Гриценко Экономика 3

3416 Нагорный Философия 5

3416 Нагорный НЕТ 0

3412 Поляков Физика 5

3412 Поляков Математика 4

3413 Огарева Математика 4

Обратите внимание на то, что строка ‘НЕТ ‘ была дополнена пробелами, чтобы получить совпадение поля PNAME по длине. Второй запрос выбирает даже те строки, которые были исключены первым.

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

В заключение разговора о вложенных запросах, поговорим о 1 так называемых соотнесенных подзапросах. Когда используются ‘; подзапросы, в SQL имеется возможность обратиться к внутреннему запросу таблицы в предложении внешнего запроса FROM, с помощью соотнесенного подзапроса. При этом подзапрос выполняется неоднократно, по одному разу для каждой записи таблицы основного запроса. С использованием соотнесенного подзапроса можно найти данные на всех студентов, которые получали оценки 10/06/1999:

SELECT *

FROM STUDENTS FIRST

WHERE 10/06/1999 IN (SELECT UDATE

FROM USP SECOND

WHERE FIRST.SNUM = SECOND.SNUM);

Вывод этого запроса таков:

SNUM SFAM SIMA SOTCH STIP

---------------------------------------

3412 Поляков Анатолий Алексеевич 25.50

3413 Огарева Любовь Михайловна 17.00

В этом примере FIRST и SECOND - псевдонимы таблиц, при этом получается, что значение в поле SNUM внешнего запроса меняется, а значит, внутренний запрос должен выполняться отдельно для каждой строки внешнего запроса.

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

- выбор строки из таблицы во внешнем запросе - это текущая строка;

- сохранение значения текущей строки в псевдониме, имя которого определено в предложении FROM внешнего запроса;

- выполнение подзапроса, при этом везде, где найден псевдоним из внешнего запроса, используется значение из текущей строки (это принято называть внешней ссылкой);

в оценка предиката внешнего запроса на основе результатов подзапроса;

- описанная выше последовательность повторяется для следующей строки из таблицы внешнего запроса, и так до тех пор, пока все строки не будут проверены. Вообще говоря, последний пример мог быть реализован, используя объединение следующего вида:

SELECT STUDENTS.SNUM,STUDENTS.SFAM,

STUDENTS.SIMA, STUDENTS.SOTCH,

STUDENTS.STIP

FROM STUDENTS, USP

WHERE STUDENTS.SNUM = USP.SNUM

AND USP.UDATE = 10/06/1999;

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

SELECT SNUM, SFAM

FROM STUDENTS FIRST WHERE 1 <

(SELECT COUNT(*)

FROM USP

WHERE SNUM = FIRST.SNUM);

Вывод этого запроса приведен ниже:

SNUM SFAM

----------------

3412 Поляков

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

Часто соотнесенный подзапрос используют на основе той же самой таблицы, что и основной запрос. Это дает возможность извлечь сложные формы информации. Например, с помощью следующего запроса можно найти все оценки по дисциплине со значениями, выше средней по этой же дисциплине:

SELECT *

FROM USP FIRST

WHERE OCENKA >

(SELECT AVG (OCENKA)

FROM USP SECOND

WHERE SECOND.PNUM = FIRST.PNUM);

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

Предложение HAVING может также работать и с соотнесенными подзапросами. При использовании соотнесенного подзапроса в предложении HAVING необходимо помнить, что предикат оценивается для каждой группы из внешнего запроса, а не для каждой строки. Следовательно подзапрос будет выполняться один раз для каждой группы, выведенной из внешнего запроса, а яе для каждой строки. Например, для установления среднего значения оценок за каждый день, причем такой, что это среднее должно быть больше или равно хотя бы на половину балла; чем минимальное значение оценки в этот день, можно воспользоваться запросом:

SELECT UDATE, AVG (OCENKA)

FROM USP FIRST

GROUP BY UDATE

HAVING AVG (OCENKA) >=

(SELECT MIN(OCENKA) +0.5

FROM USP SECOND ,

WHERE FIRST.UDATE = SECOND.UDATE);

Результат запроса будет такой:

UDATE

---------------

10/06/1999 4.5

12/06/1999 4.5

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

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

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