Использование подзапросов
11. Использование подзапросов
Язык SQL позволяет создавать подзапросы – запросы, которые вложены в другие запросы. Их также называют вложенные запросы, или подчиненные запросы.
11.1. Фильтрация посредством подзапросов
В качестве примера рассмотрим задачу – надо получить перечень всех клиентов, которые заказали продукт RGAN01. Для этого необходимо сделать:
1. Выбрать номера всех заказов, в которых содержится продукт RGAN01.
2. Выбрать идентификатор клиента из всех клиентов, которые имеют заказы, перечисленные среди номеров заказов, возвращенных на предыдущем шаге.
3. Выбрать информацию о клиенте для всех клиентов, идентификаторы которых были возвращены на предыдущем шаге.
Каждый из этих шагов можно выполнить в виде отдельного запроса. Результаты, возвращенные одним оператором SELECT, чтобы заполнить предложение WHERE для следующего оператора SELECT.
Можно использовать подзапросы для того, чтобы объединить все три запроса в один единственный оператор.
Рекомендуемые материалы
Первый оператор SELECT, будет выбирать столбец order_num для всех продуктов заказа, у которых в столбце prod_id значится RGAN01. Результат представляет собой номера двух заказов, содержащих этот предмет.
SELECT order_num
FROM OrderItems
WHERE prod_id = ‘RGAN01’;
--------------------------
order_num
20007
20008
Следующий шаг состоит в выборке идентификаторов клиентов, связанных с заказами 20007 и 20008. Используя предложение IN можно создать такой оператор SELECT.
SELECT cust_id
FROM Orders
WHERE order_num IN (20007, 20008);
--------------------------
cust_id
1000000004
1000000005
Теперь объединим эти два запроса путем превращения первого из них в подзапрос.
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = ‘RGAN01’);
--------------------------
cust_id
1000000004
1000000005
Подзапросы обрабатываются, начиная с самого внутреннего оператора SELECT. При обработке предыдущего оператора СУБД в действительности выполняет две операции.
Рассмотрим продолжение примера. Необходимо получить клиентскую информацию для каждого из этих идентификаторов клиентов.
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (‘1000000004’, ‘1000000005’);
--------------------------
cust_name cust_contact
Fun4All Denis L. Stephens
The Toy Store Kim Howard
Можно предложение WHERE превратить в подзапрос.
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = ‘RGAN01’));
--------------------------
cust_name cust_contact
Fun4All Denis L. Stephens
The Toy Store Kim Howard
Теперь выполняется три оператора SELECT. Самый внутренний подзапрос возвращает перечень номеров заказов, который затем используется как предложение WHERE для подзапроса, внешнего по отношению к данному. Этот подзапрос возвращает перечень идентификаторов клиентов, которые используются в предложении WHERE запроса высокого уровня. Запрос верхнего уровня возвращает искомые данные.
Не существует ограничений на число подчиненных запросов, но на практике может снижаться производительность.
Необходимо знать, что операторы SELECT подзапроса могут выбирать только один столбец.
11.2. Использование подзапросов в качестве вычисляемых полей
Другой способ использования подзапросов состоит в использовании вычисляемых полей.
В качестве примера рассмотрим получение общего количества заказов, размещенных каждым клиентом в таблице клиенты (Customers). Заказы хранятся в таблице Orders.
Чтобы выполнить эту операцию, необходимо сделать следующее
1. Выбрать перечень клиентов из таблицы Customers.
2. Для каждого выбранного клиента посчитать число его заказов в таблице Orders.
Для подсчета строк в таблице можно использовать оператор SELECT COUNT(*), а используя предложение WHERE для фильтрации идентификатора конкретного клиента, можно подсчитать заказы только этого клиента. Например, для клиента 1000000001.
SELECT COUNT(*) AS orders
FROM Orders
WHERE cust_id = ‘1000000001’;
--------------------------
orders
2
Чтобы получить итоговую информацию посредством функции COUNT(*) для каждого клиента, нужно использовать COUNT(*) как подзапрос.
SELECT cust_name, cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;
--------------------------
Cust_name cust_state orders
Fun4All AZ 1
Fun4All IN 1
Kids Place OH 0
The Toy Store IL 1
Village Toys MI 2
Оператор SELECT возвращает три столбца для каждого клиента. Поле orders является вычисляемым. Оно формируется в результате выполнения подзапроса. Этот подзапрос выполняется один раз для каждого выбранного клиента. В предложении WHERE в подзапросе используются полные имена столбцов. Проводится сравнение значения cust_id в таблице Orders с тем, которое в данный момент выбирается из таблицы Customers.
Orders.cust_id = Customers.cust_id
Если не использовать полностью определенные имена столбцов СУБД будет считать, что сравниваются cust_id в таблице Orders с самим собой. И запрос
SELECT COUNT(*)
FROM Orders
WHERE cust_id = cust_id;
будет всегда возвращать общее число заказов в таблице Orders.
SELECT cust_name, cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE cust_id = cust_id) AS orders
FROM Customers
ORDER BY cust_name;
--------------------------
Рекомендация для Вас - 1.4. Инструкции и имена.
Cust_name cust_state orders
Fun4All AZ 5
Fun4All IN 5
Kids Place OH 5
The Toy Store IL 5
Village Toys MI 5