• Что такое подзапрос?
• Подзапросы в операторе SELECT
• Подзапросы в операторе INSERT
• Подзапросы в операторе UPDATE
• Подзапросы в операторе DELETE
• Подзапросы внутри подзапросов
• Связанные подзапросы
• Резюме
• Вопросы и ответы
• Практикум
• Тесты
• Упражнения

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

• Что такое подзапрос?
• Выравнивание выводимых данных с помощью подзапросов
• Примеры типичных подзапросов к базе данных
• Использование подзапросов с операторами языка манипуляций данными
• Вложенные подзапросы

  Что такое подзапрос?

Подзапрос — это запрос, содержащийся в выражении ключевого слова WHERE другого запроса с целью дополнительных ограничений на выводимые данные. Подзапросы называют также вложенными запросами. Подзапрос в содержащем его запросе используют для наложения условий на выводимые данные. Подзапросы могут использоваться с операторами SELECT, INSERT, UPDATE или DELETE.

В некоторых случаях подзапрос можно использовать вместо связывания таблиц, тем самым связывая данные таблиц неявно. При использовании в запросе подзапроса сначала выполняется подзапрос, а только потом — содержащий его запрос, причем с учетом условий выполнения подзапроса. Результаты выполнения подзапроса используются при обработке условий в выражении ключевого слова WHERE основного запроса Подзапрос можно использовать либо в выражении ключевого слова WHERE, либо в выражении ключевого слова HAVING главного запроса. Логические операции и операции сравнения типа =, >, <, о, IN, NOT IN, AND, OR и т п. можно использовать как в подзапросе, так и для обработки результатов подзапроса в выражениях ключевых слов WHERE и HAVING.

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

При составлении подзапросов необходимо придерживаться следующих правил.

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

BLOB, ARRAY, CLOB ИЛИ NCLOB.

• Подзапрос нельзя непосредственно использовать как аргумент допускающей множество значений функяии.
• Операцию BETWEEN по отношению к подзапросу использовать нельзя, но ее можно использовать в самом подзапросе. Базовый синтаксис оператора с подзапросом выглядит следующим образом.

SELECT имя_столбиа

FROM таблица

WHERE имя_столбца = (SELECT имя__столбца

FROM таблица

WHERE условия);

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

Рассмотрим примеры правильного и неправильного использования операции BETWEEN в операторе с подзапросом.

Вот пример правильного использования BETWEEN:

SELECT имя_столбца

FROM таблица

WHERE имя_столбца ОПЕРАЦИЯ (SELECT имя_столбца

FROM таблица

WHERE значение BETWEEN значение);

Вот пример неправильного использования BETWEEN:

SELECT имя_столбца FROM таблица

WHERE имя_столбца BETWEEN значение AND (SELECT имя_столбца

FROM таблица);

  Подзапросы в операторе SELECT

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

Базовый синтаксис соответствующего оператора должен быть следующим.

SELECT имя_столбца [, имя_столбца ] 

FROM таблица1 [, таблица2 ] 

WHERE имя_столбца ОПЕРАЦИЯ

(SELECT имя_столбца [, имя_столбца ] 

FROM таблица1 [, таблица2 ]

[ WHERE ]);

Например,

SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME EP.PAY__RATE

FROM EMPLOYEEJTBL E, EMPLOYEE_PAY_TBL EP

WHERE E.EMP_ID = EP.EMP_ID

AND E?.PAY_RATE > (SELECT PAY_RATE

FROM EMPLOYEE_PAY_TBL

WHERE E.EMP_ID = '313782439');

Этот оператор SQL возвращает табельный номер служащего, фамилию, имя и норму оплаты труда для всех служащих, у которых эта норма оплаты превышает норму оплаты труда служащего с табельным номером 313782439. В данном случае нет необходимости знать (выяснять), какова норма оплаты того конкретного служащего — норма оплаты его труда нужна только для получения списка тех служащих, которые зарабатывают больше, чем он.

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

SELECT PAY_RATE

FROM EMPLOYEE_PAY_TBL

WHERE E.EXP_IC - ' 220S84332');

PAY_RATE 11

1 строка выбрана. 

Теперь используем этот запрос в качестве подзапроса в следующемзапросе.

SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME EP.PAY_RATE

FROM EMPLOYEEJTBL E, EMPLOYEE_PAY_TBL EP

WHERE E.EMP_ID = EP.EMP_ID

AND EP.PAY_RATE > (SELECT PAY_RATE

FROM EMPLOYEE_PAY_TBL

WHERE E.EMP_ID = '220984332');

EMP_ID LAST_NAM FIRST_NAM PAY_RATE

442346889 PLEW LINDA 14.75 

443679012 SPURGEON TIFFANY 15

2 строки выбраны.

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

AND EP.PAY_RATE > 11

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

Подзапросы часто используются тогда, когда в запросе требуется указать условия, точных данных для которых нет. Знамения уровня оплаты труда для служащего 220Э84332 у вас нет, но подзапрос и создается для того, чтобы выяснить это значение

  Подзапросы в операторе INSERT

Подзапросы могут использоваться и с операторами языка манипуляций данными (DML). Первым из таких операторов мы рассмотрим оператор INSERT. Оператор INSERT использует данные, возвращаемые подзапросом, для помещения их в другую таблицу. Выбранные в подзапросе данные можно модифицировать с помощью символьных или числовых функций, а также функций дат и времени.

Базовый синтаксис соответствующего оператора должен быть следующим.

INSERT INTO имя_таблицы [ (столбец! [, столбец2 ]) ]

SELECT [ *| столбец1 [, столбец2 ]]

FROM таблица1 [, таблица2 ]

[ WHERE значение ОПЕРАЦИЯ значение ]

Вот пример использования оператора INSERT с подзапросом.

INSERT INTO RICH_EMPLOYEES

SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME EP.PAY_RATE

FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP

WHERE E.EMP_ID = EP.EMP_ID

AND EP.PAY_RATE > (SELECT PAY_RATE

FROM EMPLOYEE_PAY_TBL

WHERE E.EMP_ID = '220984332');

2 строки созданы.

Этот оператор INSERT вставляет значения EMP_ID, LAST_NAME, FIRST_NAME и PAY_RATE в таблицу RICH_EMPLOYEES для всех служащих, норма оплаты труда которых превышает норму оплаты труда служащего с табельным номером 220984332.

При использовании команд DML типа INSERT не забывайте об использовании команд COMMIT и ROLLBACK

  Подзапросы в операторе UPDATE

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

Базовый синтаксис оператора следующий.

UPDATE таблица

SET имя_столбца [, имя_столбца ] =

(SELECT имя_столбца [,имя_столбца ] FROM таблица

[ WHERE ])

Рассмотрим примеры, разъясняющие использование оператора UPDATE с подзапросом. Сначала рассмотрим запрос, возвращающий табельные номера служащих из Индианаполиса. Как видите, таких служащих четыре.

SELECT EMP_ID

FROM EMPLOYEE_TBL

WHERE CITY = 'INDIANAPOLIS';

EMP_ID

442346889

313782439

220984332

443679012

4 строки выбраны.

Этот запрос будет использован в качестве подзапроса в следующем операторе UPDATE. Вот этот оператор UPDATE с подзапросом.

UPDATE EMPLOYEE_PAY_TBL

SET PAY_RATE = PAY_RATE * 1.1

WHERE EMP_ID IN (SELECT EMP_ID

FROM EMPLOYEE_TBL

WHERE CITY = 'INDIANAPOLIS');

4 строки обновлены.

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

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

  Подзапросы в операторе DELETE

Подзапросы можно использовать в операторе DELETE. Базовый синтаксис оператора следующий.

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

[ WHERE ОПЕРАЦИЯ [ значение ] 

(SELECT имя_столбца 

FROM имя_таблицы 

[ WHERE ])

В следующем примере из таблицы EMPLOYEE_PAY_TBL удаляется запись с информацией о служащем по имени BRANDON GLASS. Табельный номер этого служащего не известен, но можно создать подзапрос, который найдет этот номер в таблице EMPLOYEEJTBL по значениям столбцов с именами (FIRST_NAME) и фамилиями (LAST_NAME) служащих.

DELETE FROM EMPLOYEE_PAY_TBL WHERE EMP_ID = (SELECT EMP_ID

FROM EMPLOYEE_PAY_TBL

WHERE LAST_NAME a 'GLASS^'

AND FIRST_NAME = 'BRAHDON');

1 строка обновлена.

He забывайте использовать в операторах UPDATE и DELETE ключевое слово WHERE. Если последнее не использовать, будут обновлены или удалены данные всех столбцов¹ За подробностями обратитесь к уроку 5, "Манипуляция данными"

  Подзапросы внутри подзапросов

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

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

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

SELECT имя_столбца [, имя_столбца ] 

FROM таблица1 [, таблица2 ]

WHERE имя_столбца ОПЕРАЦИЯ (SELECT имя__столбца

FPOM таблица

WHERE имя_столбца ОПЕРАЦИЯ (SELECT имя_столбца FROM таблица 

[WHEREимя_столбца ОПЕРАЦИЯ значение]))

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

SELECT CUST_ID, CUST_NAME

FROM CUSTOMER_TBL

WHERE CUST_ID IN (SELECT O.CUST_ID

FROM ORDERS_TBL O, PRODUCTS_TBL P WHERE 0 PROD_ID = P.PROD_ID

AND O.QTY * P.COST > (SELECT STJM(COST)

FROM PRODUCTS_TBL));

CUST_ID CUST_NAME

287 GAVINS PLACE

43 SCHYLERS NOVELTIES

2 строки выбраны.

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

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

SELECT SUM(COST) FROM PRODUCTS_TBL));

 SUM(COST)

138.08 1 строка выбрана.

SELECT O.CUST_ID

FROM ORDERS_TBL O, PRODUCTSJTBL P 

WHERE О PROD_ID = P.PROD_ID 

AND O.QTY * P.COST > 138.08;

CUST_ID

287

43

2 строки выбраны.

После подстановки в главный запрос результатов внутреннего подзапроса главный запрос принимает следующий вид

SELECT CUST_ID, CUST_NAME

FROM CUSTOMER_TBL

WHERE CUST_ID IN (SELECT O.CUST_ID

FROM ORDERS_TBL O, PRODUCTS_TBL P WHERE О PROD_ID = P.PROD_ID

AND O.QTY * P.COST > 138.08);

А вот что получается после выполнения следующего подзапроса

SELECT CUST_ID, CUST_NAME

FROM CUSTOMER_TBL

WHERE CUST_ID IN  '287•,'43');

Поэтому в результате имеем

CUST_ID CUST_NAME

287 GAVINS PLACE

43 SCHYLERS NOVELTIES

2 строки выбраны.

При использовании в операторе нескольких подзапросов увеличивается время, необходимое для обработки запроса, и повышается вероятность ошибок из-за усложнения оператора

  Связанные подзапросы

Связанные подзапросы допускаются во многих реализациях SQL. Концепция связанного подзапроса определяется стандартом ANSI SQL и поэтому рассматривается здесь. Связанный подзапрос — это подзапрос, зависящий от информации, предоставляемой главным запросом.

В следующем примере в подзапросе определение связи между таблицами CUSTOMER_TBL И ORDERSJTBL использует псевдоним таблицы CUSTOMERJTBL (С), определенный в главном запросе. Этот оператор возвращает имена всех покупателей, заказавших более 10 единиц товара.

SELECT C.CUST_NAME

FROM CUSTOMER_TBL С

WHERE 10 < (SELECT SUM(O.QTY)

FROM ORDERS_TBL О

WHERE O.CUST_ID =C.CUST_ID);

CUST_NAME

SCOTTYS MARKET 

SCHYLERS NOVELTIES 

MARYS GIFT SHOP

В случае связанного подзапроса ссылка на таблицу главного запроса должна быть определена до начала выполнения подзапроса.

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

SELECT C.CUST_NAME, SUM(O.QTY) 

FROM CUSTOMER_TBL С,

ORDERS_TBL О GROUP BY CUST_NAME;

CUSTJMAME SUM(O.QTY)

GAVINS PLACE 10

LESLIE GLEASON 1

MARYS GIFT SHOP 100

SCHYLERS NOVELTIES 25

SCOTTYS MARKET 20

WENDY WOLF 2

6 строк выбраны.

Ключевое слово GROUP BY здесь требуется потому, что по отношению ко второму столбцу используется итоговая функция SUM. Это позволяет подсчитать суммы для каждого из заказчиков В предыдущем примере ключевое слово GROUP BY не требовалось, поскольку там функция зим использовалась для суммирования всех результатов запроса, выполняемого для каждого конкретного заказчика.

  Резюме

Попросту говоря, подзапрос представляет собой запрос, выполняемый в рамках другого запроса для задания дополнительных условий на выводимые данные. Подзапрос можно использовать в выражениях ключевых слов WHERE и HAVING. Подзапросы обычно используют в других запросах (операторах DQL — языка запросов к данным), но подзапросы можно использовать и в операторах DML (языка манипуляций данными) таких, как INSERT, UPDATE и DELETE. Все основные правила использования операторов языка манипуляций данными применимы и при использовании в них подзапросов.

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

  Вопросы и ответы

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

Нет. Отступы используются исключительно для того, чтобы разбить оператор на части, чтобы его было легче читать и проще понять.

Имеются ли ограничения на число вложений подзапросов в запросы?

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

Отладка операторов с подзапросами кажется непростым делом, особенно если используются еще и вложенные подзапросы. Есть ли какие-либо рекомендации по поводу оптимизации процесса отладки запросов с подзапросами?

Лучше всего для отладки выделить из сложного запроса составляющие его запросы. Сначала следует проверить внутренний подзапрос самого низшего уровня и постепенно продвигаться по уровням до главного запроса (точно так же, как запрос обрабатывается базой данных). На каждом шагу после обработки выделенного из сложного оператора подзапроса можно подставить возвращенные этим подзапросом значения в исходный оператор, чтобы проверить правильность работы последнего. Чаще всего ошибки возникают из-за выражений, содержащих неправильное использование знаков операций для оценки результатов подзапроса, таких как =, IN, >, < и т. п.

  Практикум

Задания практических занятий разделены на тесты и упражнения. Тесты предназначены для проверки общего уровня понимания рассмотренного материала. Упражнения дают возможность применить на практике идеи, обсуждавшиеся в ходе текущего урока, в комбинации с идеями из предыдущих уроков. Мы рекомендуем ответить на тестовые вопросы и выполнить упражнения прежде, чем продолжать дальнейшее чтение книги. Ответы можно проверить по Приложению Б, "Ответы".

  Тесты

1. В чем состоит назначение подзапроса при использовании его в операторе SELECT?

2. Можно ли одновременно обновить несколько столбцов таблицы с помощью оператора UPDATE с подзапросом?

3. Будут ли работать следующие операторы? Если нет, то что в них следует исправить?

a. SELECT CUST_ID, CUST_NAME FROM CUSTOMER_TBL WHERE CUST_ID =

(SELECT CUST_ID FROM ORDERS_TBL WHERE ORD_NUM = ' 16C17' ) ;

6. SELECT EMP_ID, SALARY FROM EMPLOYEE_PAY_TBL WHERE SALARY BETWEEN '20000'

AND (SELECT SALARY

FROM EMPLOYEE_ID

WHERE SALARY = '40000');

B. UPDATE PRODUCTS_TBL SET COST = 1.15 WHERE CUST_ID =

(SELECT CUST_ID FROM ORDERS_TBL WHERE ORD_NUM = '32A132');

4. Каков будет результат выполнения следующего оператора?

DELETE FROM EMPLOYEE_TBL WHERE EMP_ID IN

(SELECT EMP_ID FROM EMPLOYEE_PAY_TBL};

  Упражнения

Выполните упражнения для следующих таблиц.

1. Используя подзапрос, запишите оператор SQL, который в таблице CUSTOMER_TBL заменит имя заказчика, разместившего заказ с номером 23Е934, На DAVIDS MARKET.

2. Используя подзапрос, создайте запрос, возвращающий имена всех служащих, которые имеют более высокую зарплату, чем служащий по имени JOHN DOE, чей табельный номер 343559876.

3. Используя подзапрос, создайте запрос, возвращающий список всех товаров с ценой, превышающей среднюю цену всех имеющихся товаров.