Тема 9. Подзапросы в SQL

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

Подзапрос заключается в круглые скобки и представляет собой вложенную команду SELECT, вложение подзапросов неограниченно. Причем, основной запрос и используемый в нем подзапрос могут обращаться как к одним и тем же таблицам, так и к разным таблицам баз данных. В подзапросе можно использовать почти все опции команды SELECT, кроме опций ORDER BY и INTO.

Подзапрос используется в выражении условия WHERE. Подзапросы дают возможность получать данные из одних таблиц, ориентируясь на условия, содержащиеся в других таблицах. С их помощью можно обойти ограничение на использование агрегатных функций SQL в условии WHERE. Дело в том, что нельзя напрямую использовать агрегатные функции в предложении WHERE, так как предикаты оцениваются в терминах одиночной строки, а агрегатные функ­ции – в терминах групп строк. Подзапросы дают возможность в условии сравнивать значения одного и того же поля таблицы между собой.

При использовании подзапросов необходимо учитывать тип возвращаемого результата подзапроса. Если известно, что оператор SELECT возвращает одно значение, то можно использовать все арифметические операторы сравнения (например, проверку на равенство). Если известно, что оператор SELECT возвращает множество значений, то используется проверка на вхождение (IN) или не вхождение (NOT IN) в множество возвращаемых значений.

Запрос с подзапросом может быть некоррелирован или коррелирован. Некоррелированный подзапрос выполняется в первую очередь, затем полученный результат подставляется в условие и выполняется внешний запрос.

Например, для выборки фамилий сотрудников (поле FAM) из таблицы KADR с заработной платой (поле ZARP) выше средней можно использовать запрос с подзапросом (для таблицы KADR используется локальный псевдоним S1):

SELECT S1.FAM FROM KADR S1 WHERE ZARP >

(SELECT AVG(ZARP) FROM KADR)

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

Например, для выборки фамилий сотрудников (поле FAM) и зарплаты (поле ZARP) из таблицы KADR с заработной платой выше средней по каждой лаборатории можно использовать запрос с подзапросом (для таблицы KADR используются локальные псевдонимы S1 во внешнем запросе и S2 в подзапросе для обеспечения сравнения):

SELECT FAM, ZARP FROM KADR S1 WHERE ZARP >=

(SELECT AVG(ZARP) FROM KADR S2 WHERE S2.LAB=S1.LAB)

В большинстве СУБД в запросах с подзапросами можно использовать логическую операцию EXISTS, соответствующую математическому квантору «Существует». С помощью логической операции EXISTS можно проверить, возвращает ли тот или иной SELECT оператор (подзапрос) какие-то значения. Соответственно, можно в условии определять только те записи, для которых существует (или не существует - NOT EXISTS) какая-то информация.

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

Формат логической операции EXISTS: EXISTS (SELECT подзапрос).

Сравнивая операцию EXISTS с операцией IN, можно заметить, что одни и те же запросы можно выразить с помощью разных средств (а именно, EXISTS или IN). Какое из средств выбрать, должно определяться логикой задачи и эффективностью исполнения. Последнее, в свою очередь, зависит от реализации и может принципиально различаться для SQL-серверов разных фирм.

В запросах с подзапросами можно использовать логическую операцию ALL, соответствующую математическому квантору «Для всех». Операция ALL возвращает значение «истина», если указанная перед ALL операция сравнения истинна для каждого значения, возвращаемого оператором SELECT в подзапросе. Можно использовать операцию ANY, которая возвращает значение «истина», если указанная перед ANY операция сравнения истинна хотя бы для одного значения, возвращаемого оператором SELECT в подзапросе. Вместо ключевого слова ANY можно использовать слово SOME.

Во многих СУБД (но не во всех) SQL-команду INSERT можно использовать с подзапросом – вложенной командой SELECT, если множество дополняемых данных является результатом запроса:

INSERT INTO <имя_таблицы> [(<имя_поля1> [, <имя_поля2> …])] <подзапрос>

Например, дополнение в таблицу STUD1 фамилий студентов (поле FAM) из таблицы STUD2 осуществляется следующей командой:

INSERT INTO STUD1 (FAM) VALUES SELECT DISTINCT FAM FROM STUD2

При модификации данных можно использовать подзапрос – вложенную команду SELECT для формирования операнда условия:

UPDATE <имя_таблицы> SET <имя поля>=<выражение>

[WHERE <операнд_условия> <оператор_условия> (<подзапрос>)]

При удалении записей в SQL можно использовать подзапрос – вложенную команду SELECT для формирования операнда условия:

DELETE FROM <имя_таблицы>

[WHERE <операнд_условия> <оператор_условия> (<подзапрос>)]

Удаляются записи, удовлетворяющие условию, указанному после опции WHERE.

Результаты работы двух или более операторов SELECT могут быть объединены в одну выборку с помощью операторов UNION или UNION ALL. Оператор UNION, помещенный между двумя операторами SELECT делает из двух выборок одну, причем повторяющиеся записи отсутствуют в результирующей выборке. Результатом будет множество, состоящее из всех строк, входящих в какую-либо выборку или в несколько выборок. Но при этом результаты исходных выборок должны иметь одинаковое число полей (столбцов), тип и ширина i-го поля одной выборки должны совпадать с типом и шириной i-го поля любой другой выборки. При использовании опции UNION часто оказывается полезным включение константы в получаемый результат выборки. Заголовки колонок в выборке определяются первым запросом. Например, текстовую константу можно использовать в качестве поясняющего текста при выборе из таблицы STUD фамилий студентов (поле FAM), получающих стипендию (поле STIP) больше 2000 или проживающих в городе Алматы (город проживания указывается в поле ADRESS):

SELECT FAM AS Фамилии, “стипендия>2000” AS Признак_выборки FROM STUD

WHERE STIP> 2000

UNION

SELECT FAM, “ город Алматы ” FROM STUD WHERE ADRESS LIKE “%Алматы”

Оператором UNION можно соединить любое число команд SELECT, но опция ORDER BY в запросе с использованием оператора UNION может входить только в последнее предложение SELECT. При указании критерия упорядочивания указываются номера полей в получаемой выборке. Например, при выборке из таблицы KADR фамилий сотрудников (поле FAM), имеющих заработную плату (поле ZARP) меньше 10000 или проживающих в городе Алматы (город проживания указывается в поле ADRESS), можно сначала упорядочить данные по второй колонке (признак выборки), а затем по первой колонке (фамилии в алфавитном порядке):

SELECT FAM , “зарплата<1000”

FROM KADR WHERE ZARP<10000

UNION

SELECT FAM , “город Алматы ” FROM KADR

WHERE ADRESS LIKE “%Алматы” ORDER BY 2,1

По умолчанию оператор UNION устраняет из результата повторяющиеся строки. Чтобы отобразить все строки, необходимо использовать оператор UNION с опцией ALL (UNION ALL).

В настоящее время SQL представляет собой не просто язык запросов, а наиболее распространенный язык взаимодействия с реляционными базами данных типа клиент-сервер. Основное достоинство SQL заключается в том, что он унифицирован: стандартный набор инструкций SQL можно использовать в любой системе управления базами данных, которая поддерживает SQL. Первый американский стандарт SQL был зарегистрирован в 1986 г. как ANSI X3.135-1986. ANSI (Американский национальный институт стандартизации) – это организация, которая занимается созданием и обновлением научных и инженерных стандартов. ANSI-стандарт SQL был принят в качестве всемирного стандарта отделом ООН Международной организацией стандартизации (ISO) в 1989 г. – SQL/89 (SQL/1). В настоящее время в РСУБД поддерживается стандарт ANSI X3.135-1992, широко известный как SQL/92 (SQL/2). В настоящее время принят стандарт SQL/3.

SQL является языком управления реляционными базами данных, а не языком программирования. В SQL определены операторы доступа к базе данных, но не описан ни способ показа данных, ни ввода их пользователем. ANSI SQL не включает ни средств управления выполнением программы (ветвлений и циклов), ни средств для создания форм или отчетов. А функции управления реализуются в языках программирования (например, xBase, C++, COBOL и др.), в которые встраивается язык SQL. Однако в некоторые версии (диалекты) SQL, например, в Transact-SQL, используемый в серверах БД Sybase и Microsoft SQL Server, добавлены два оператора (IF ELSE и WHILE).

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

Основная литература: 1[122:128], 2[16:26], 6[12:19].

Дополнительная литература: 10[1028:1040].

Контрольные вопросы:

1. Какие возможности дает использование подзапросов.

2. Что собой представляет некоррелированный подзапрос.

3. Что собой представляет коррелированный подзапрос.

4. Какой оператор позволяет объединить результаты нескольких выборок в одном запросе.

5. Каким условиям должны удовлетворять результаты исходных выборок при их объединении в один запрос.