SQL-Урок 9. Подзапросы (Subqueries)
До этого времени мы получали данные из базы данных с помощью простых запросов и одного оператора SELECT. Однако все-таки чаще нам нужно будет выбирать данные, соответствующие многим условиям, и здесь не обойтись без расширенных запросов. Для этого в SQL существуют подзапросы или вложенные запросы, когда один оператор SELECT вкладывается в другой.
Подзапрос – это оператор SELECT, вложенный в другой SELECT, SELECT. INTO, INSERT. INTO, DELETE, UPDATE или внутри другого подзапроса.
Текст подзапроса должен быть размещен в скобках. Часто подзапросы используют вместо сочетания таблиц (JOIN).
1. Фильтрация с помощью подзапросов
Таблицы баз данных, используемых в СУБД Access, являются реляционными таблицами, т.е. все таблицы можно связать между собой по общим полям. Допустим у нас хранятся данные в двух разных таблицах и нам нужно выбрать данные в одной из них, в зависимости от того, какие данные в другой. Для этого мы создадим еще одну таблицу в нашей базе данных. Это будет, например, таблица Sellers с информацией о поставщиках:
Теперь у нас две таблицы — Sumproduct и Sellers, которые имеют одинаковое поле City. Предположим, нам нужно посчитать, сколько товаров было продано только в Канаде. Сделать это нам помогут подзапросить. Итак, сначала напишем запрос для выборки городов, находящихся в Канаде:
Run SQLSELECT City FROM Sellers WHERE Country = 'Canada'
Try it Yourself
Теперь передадим эти данные в следующий запрос, который будет выбирать данные из таблицы Sumproduct:
Run SQLSELECT SUM(Quantity) AS Qty_Canada FROM Sumproduct WHERE City IN ('Montreal','Toronto')
Try it Yourself
Также мы можем объединить эти два запроса в один. Таким образом, один запрос, который выводит данные, будет главным, а второй запрос, который передает входящие данные, будет вспомогательным (подзапросом). Для вложения подзапроса используем конструкцию WHERE . IN (. ), о которой говорилось в разделе Try it Yourself
Мы видим, что мы получили аналогичные данные, как и с помощью двух отдельных запросов. Таким же образом, мы можем увеличивать глубину вложенности запросов, вкладывая подзапрос сколько раз.
2. Использование подзапросов в качестве расчетных полей
Мы также можем использовать подзапросы в качестве расчетных полей. Отразим, например, количество реализуемой продукции по каждому продавцу с помощью следующего запроса:
Run SQLSELECT Seller_name, (SELECT SUM(Quantity) FROM Sumproduct WHERE Sellers.City = Sumproduct.City) AS Qty FROM Sellers
Try it Yourself
Первый оператор SELECT отображает два столбца — Seller_name и Qty. Поле Qty является расчетным, оно формируется в результате выполнения подзапроса, взятого в круглые скобки. Этот подзапрос выполняется по одному разу для каждой записи в поле Seller_name и в общем будет выполнен четыре раза, поскольку выбраны имена четырех продавцов.
Также, в подзапросе, предложение WHERE выполняет функцию сочетания, поскольку с помощью WHERE мы соединили две таблицы по полю City, используя полные названия столбцов (Таблица.Поле).
- Изменение регистра букв в тексте
- Сумма прописью на украинском языке
- Поиск латиницы в кириллице и наоборот
- Транслитерация с украинского на английский
Подзапросы SQL
Подзапрос – это запрос, результат которого используется внутри другого запроса. В большинстве случаев (кроме использования специальных булевых операторов – см. ниже) требуется, чтобы результатом подзапроса было одно поле. Например, пусть требуется вывести список товаров, проданных на сумму свыше 90000:
SELECT GName FROM Goods WHERE GNum in (SELECT GNum FROM Sells WHERE SSum>90000)
В результате получим:
Gname |
Сахар |
Мыло |
В результате подзапроса выбрано одно поле GNum из таблицы Sells, на совпадение, со значениями которого сравнивается поле GNum из таблицы Goods.
Для работы с подзапросами также используются реляционные операторы, булевы операторы, и специальные операторы EXIST, ANY, ALL и SOME.
Оператор EXISTS в запросе используется в тех случаях, когда необходимо проверить результат подзапроса на его существование, т.е. выбрал подзапрос что-нибудь из базы данных или нет. Он может работать автономно в предикате или в комбинации с другими логическими выражениями, использующими булевы операторы AND, OR, и NOT. Оператор EXISTS берет подзапрос как аргумент и оценивает его как верный, если тот производит любой вывод, или как неверный, если тот не делает этого. Например, пусть необходимо выбрать только те товары, которые были проданы на сумму выше, чем цена. Напишем данный запрос с помощью оператора EXISTS:
SELECT Goods.GName FROM Goods WHERE EXISTS (SELECT * FROM Sells WHERE Sells.SSum>Goods.Gprice AND Sells.GNum=Goods.GNum)
Gname |
Сахар |
Мука |
Мыло |
Пиво |
Фен «Philips» |
В отличие от других операторов EXISTS выполняется только один раз для каждой строки таблицы, указанной во внешнем запросе.
EXISTS всегда можно заменить на оператор IN, что, однако, приводит к замедлению работы запроса.
Оператор ANY также используется с подзапросами, но в отличие от EXISTS, он работает с реляционными операторами. Оператор ANY берет все значения, выведенные подзапросом, и оценивает их как верные, если любое из них удовлетворяет условию.
Например, выведем только те группы товаров, для которых определены товары:
SELECT * FROM GoodsTypes WHERE GTNum=ANY(SELECT GoodsTypesFROM Goods)
Также как и оператор ANY, оператор ALL работает с реляционными операторами. Он работает таким образом, что предикат является верным, если каждое значение, выбранное подзапросом, удовлетворяет условию в предикате внешнего запроса.
Например, пусть необходимо выбрать только тех покупателей, которые не совершали покупки 05.05.01, т.е. если покупка совершена этой датой, то этого покупателя включать в результат запроса не нужно. Для этого напишем запрос с использованием ALL:
SELECT * FROM Buyers WHERE BNum<>ALL(SELECT BNum FROM Sells WHERE SDate="05.05.01")
Этот запрос работает следующим образом. Сначала подзапрос в ALL выбирает номера всех покупателей, совершивших покупки 05.05.01. Затем он находит номера покупателей не равных выбранным и выводит их в результате:
BNum | BName | BTown |
1 | Дельта | 1 |
5 | Москва | 3 |
Операторы ANY и ALL допускают использование EXISTS для альтернативного формулирования такого же запроса.
Оператор ALL редко используется со знаком «равно», т.к. предикат может быть верным, если сравниваемое значение равно для всех, т.е. все записи должны быть идентичны. Например, если в предыдущем запросе вместо знака неравенства поставить равенство, то вывод запроса осуществится только в том случае, если номера покупателей, совершивших покупки в этот день, будут совпадать. Основное применение оператор ALL находит со знаками неравенства.
SQL: подзапросы, их виды, корректное использование
Подзапросы, возвращающие единственное значение
Подзапросы (вложенные запросы), возвращающие единственное значение, наиболее часто применяются в случаях, когда значение определённого столбца в основном запросе требуется сравнить с некоторым единственным значением при помощи одного из операторов сравнения (=, , =). Значение, с которым производится сравнение, как раз и возвращается подзапросом (вложенным запросом).
Верные признаки того, что подзапрос вернёт одно единственное значение:
- в подзапросе применяется одна из агрегатных функций (COUNT, SUM, AVG, MAX, MIN);
- подзапрос извлекает значение уникального идентификатора, например, первичного ключа.
В остальных случаях нужно быть полностью уверенным, что условиям, указанным в секции WHERE подзапроса, соответствует единственное значение выбираемого столбца.
В примерах работаем с базой данных «Театр». Таблица Play содержит данные о постановках. Таблица Team — о ролях актёров. Таблица Actor — об актёрах. Таблица Director — о режиссёрах. Поля таблиц, первичные и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).
Пример 1. Вывести спектакли режиссёра John Barton. Запрос будет следующим:
SELECT Name FROM PLAY WHERE Dir_ID=( SELECT Dir_ID FROM DIRECTOR WHERE FName=’John’ AND LName=’Barton’)
Поскольку подзапрос возвращает значение уникального идентификатора, можно быть уверенным в том, что он вернёт единственное значение.
Более редкий случай использования подзапросов, возвращающих единственное значение — в запросах с предикатом BETWEEN, где подзапросы задают границы интервала.
Подзапросы, возвращающие множество значений
Подзапросы, возвращающие множество значений, могут применяться в запросах с предикатами IN и EXISTS и кванторными функциями ALL и ANY.
Пример 2. В таблице TEAM столбец Mainteam содержит данные о том, главная ли роль закреплена с спектакле за актёром. Значение столбца ‘Y’ означает «да», ‘N’ — «нет». Вывести список актёров, которые когда-либо исполняли главные роли. Запрос будет следующим:
SELECT FName, LName FROM ACTOR WHERE Actor_ID IN ( SELECT Actor_ID FROM TEAM WHERE Mainteam=’Y’)
Подзапрос вернёт множество значений FName и LName, которые через ключ Actor_ID будет передано в основной запрос и окончательно выведены в качестве результата.
На сайте есть отдельный урок о запросах с предикатом IN.
Пример 3. В таблице ACTOR (актёр) есть столбец SEX, содержащий данные о поле (‘M’ — мужской, ‘F’ — женский) актёра. Вывести спектакли, в которых играют только мужчины. Запрос будет следующим:
SELECT pl.* FROM PLAY pl WHERE NOT EXISTS ( SELECT 1 FROM TEAM te JOIN ACTOR ac ON ac.Actor_ID=te.Actor_ID WHERE te.Play_ID=pl.Play_ID AND ac.Sex=’F’)
Предикат NOT EXISTS принимает подзапрос как аргумент и оценивает его как подходящий, если значения sex для одного или более актёров в таблице actor не равны F.
На сайте есть подробный урок о запросах с предикатами EXISTS и NOT EXISTS.
Пример 4. Определить самый популярный жанр театра. Пишем запрос с использованием кванторной функции ALL:
SELECT Genre FROM PLAY GROUP BY Genre HAVING COUNT (*) >= ALL ( SELECT COUNT (*) FROM PLAY GROUP BY Genre)
Кванторная функция ALL проверяет значения количества жанров среди всех спектаклей и затем находит жанр с количеством большим, чем у любого другого жанра, или равным ему.
На сайте есть подробный урок о запросах с кванторными функциями ALL и ANY.
Некоррелирующие и коррелирующие подзапросы
Подзапрос, возвращающий результат или результаты, для получения которых значения указанного столбца не должны соотноситься (коррелировать) со значениями столбцов, указанных в основном запросе, называется некоррелирующим. Результат выполнения некоррелирующего запроса не зависит от значений, возвращаемых основным запросом. Обычно некоррелирующие запросы применяются в запросах, в которых значение определённого столбца сравнивается со значением, возвращаемым подзапросом, в запросах с предикатом IN, кванторными функциями ALL и ANY. Однако уже в запросах с предикатом EXISTS применяются коррелирующие подзапросы.
Подзапрос, возвращающий результат или результаты, для получения которых значения указанного столбца должны соотноситься (коррелировать) со значениями столбцов, указанных в основном запросе, называется коррелирующим. Иными словами, результат, выполнения подзапроса зависит от значений, возвращаемых основным запросом. Часто коррелирующие подзапросы применяются для получения значений одного из столбцов результирующей таблицы и в этих случаях подзапрос, заключённый в скобки, перечисляется через запятую вместе с именами столбцов из таблиц или соединения таблиц.
Пример 5. Вывести список актёров с количеством их ролей. Пишем следующий запрос с коррелирующим подзапросом:
SELECT DISTINCT a.Actor_ID, a.FName, a.LName, ( SELECT COUNT (*) FROM ACTOR a1 JOIN team t1 ON a1.Actor_ID=t1.ACTOR_ID WHERE a1.Actor_ID=a.Actor_ID GROUP BY a1.Actor_ID) AS NumRoles FROM ACTOR a JOIN team t ON a.Actor_ID=t.ACTOR_ID ORDER BY a.Actor_ID
В основном запросе происходит первое обращение к таблице ACTOR, которая получает псевдоним a. В подзапросе происходит второе обращение к таблице ACTOR, которая получает псевдоним a1. При этом в секции WHERE подзапроса указано условие: идентификаторы актёров, возвращаемые основным запросом и подзапросом, должны совпадать. Это условие — характерный признак коррелирующего подзапроса.
Подзапрос — Продвинутые SQL-запросы
Подзапрос SQL — это запрос, который включается в другой запрос. Подзапросы позволяют использовать результат выполнения внутреннего запроса во внешнем запросе, что позволяет строить сложные запросы из более простых.
Применение подзапросов
Подзапросы могут применяться практически везде, в любых операторах, в том числе в операторах DDL , DML .
Пример подзапроса, который выводит все продукты, цена которых выше средней по всем продуктам:
SELECT * FROM Products WHERE Price > (SELECT AVG(Price) FROM Products);
Пример подзапроса, который выводит все продукты, которые есть в таблице с чеками:
SELECT * FROM Products WHERE Product_id IN (SELECT Product_id FROM Sales);
ALL/ANY (MySQL)
x > ALL (1, 2) эквивалентно x > 2
x > ANY (1, 2) эквивалентно x > 1
x = ALL (1, 2) эквивалентно (x = 1) AND (x = 2)
x = ANY (1, 2) эквивалентно x IN (1, 2)
x <> ALL (1, 2) эквивалентно x NOT IN (1, 2)
x <> ANY (1, 2) эквивалентно (x <> 1) OR (x <> 2)
Подзапрос как новая колонка
Подзапросы в SQL могут быть использованы для создания новых колонок в результирующем наборе данных.
Новая колонка, созданная с помощью подзапроса, может содержать вычисленные значения, агрегатные функции или другие данные, основанные на значениях других столбцов в таблице.
SELECT *, (SELECT ProductName FROM Products WHERE Id = Orders.ProductId) AS Product FROM Orders;
Подзапросы в UPDATE
Одно из основных применений подзапросов в операции UPDATE — это обновление значений столбца на основе вычисленных или фильтрованных данных из других таблиц или столбцов.
UPDATE Orders SET Price = (SELECT Price FROM Products WHERE Id = Orders.ProductId) + 3000 WHERE Id = 1;
Подзапросы в INSERT
Подзапросов в операции INSERT используются для вставки данных, полученных из других таблиц или вычисленных с помощью подзапросов.
INSERT INTO Orders (ProductId, CreatedAt, ProductCount, Price) VALUES ( (SELECT Id FROM Products WHERE ProductName='Galaxy S8'), '2018-05-23', 2, (SELECT Price FROM Products WHERE ProductName='Galaxy S8'), )
Подзапросы в DELETE
Подзапросы в операции DELETE позволяют удалить данные из таблицы на основе результатов других запросов или данных из других таблиц.
DELETE FROM Orders WHERE ProductId = (SELECT Id FROM Products WHERE ProductName='Galaxy S8')
Коррелированный подзапрос
Коррелированный подзапрос — это подзапрос, который зависит от внешнего запроса и использует значения из внешнего запроса в своем выражении или фильтре.
Основное применение коррелированных подзапросов — это выполнение операций, связанных с каждой строкой основного запроса. Коррелированные подзапросы выполняются для каждой строки внешнего запроса и используют значения из этой строки для фильтрации или вычисления данных в подзапросе.
SELECT * FROM product_price pp WHERE pp.price = (SELECT min(ppm.price) FROM product_price ppm
Открыть доступ
Курсы программирования для новичков и опытных разработчиков. Начните обучение бесплатно
- 130 курсов, 2000+ часов теории
- 1000 практических заданий в браузере
- 360 000 студентов
Наши выпускники работают в компаниях: