Здравствуйте, уважаемые читатели! В этой статье мы поговорим о том, что такое вложенные запросы в SQL. Традиционно, рассмотрим несколько примеров с той базой данных, которую создавали в первых статьях.
Введение
Итак, само название говорит о том, что запрос во что-то вложен. Так вот, вложенный запрос в SQL означает, что запрос select выполняется в еще одном запросе select — на самом деле вложенность может быть и многоуровневой, то есть select в select в select и т.д.
Такие запросы обычно используются для получения данных из двух и более таблиц. Они нужны чтобы данные из разных таблиц можно было соотнести и по зависимости осуществить выборку. У вложенных запросов есть и недостаток — зачастую слишком долгое время работы занимает запрос, потому что идет большая нагрузка на сервер. Тем не менее, саму конструкцию необходимо знать и использовать при возможности.
Структура ранее созданных таблиц
Прежде чем перейдем к простому примеру, напомним структуру наших таблиц, с которыми будем работать:
- Таблица Salespeople (продавцы):
snum | sname | city | comm |
---|---|---|---|
1 | Колованов | Москва | 10 |
2 | Петров | Тверь | 25 |
3 | Плотников | Москва | 22 |
4 | Кучеров | Санкт-Петербург | 28 |
5 | Малкин | Санкт-Петербург | 18 |
6 | Шипачев | Челябинск | 30 |
7 | Мозякин | Одинцово | 25 |
8 | Проворов | Москва | 25 |
- Таблица Customers (покупатели):
сnum | сname | city | rating | snum |
---|---|---|---|---|
1 | Деснов | Москва | 90 | 6 |
2 | Краснов | Москва | 95 | 7 |
3 | Кириллов | Тверь | 96 | 3 |
4 | Ермолаев | Обнинск | 98 | 3 |
5 | Колесников | Серпухов | 98 | 5 |
6 | Пушкин | Челябинск | 90 | 4 |
7 | Лермонтов | Одинцово | 85 | 1 |
8 | Белый | Москва | 89 | 3 |
9 | Чудинов | Москва | 96 | 2 |
10 | Лосев | Одинцово | 93 | 8 |
- Таблица Orders (заказы)
onum | amt | odate | cnum | snum |
---|---|---|---|---|
1001 | 128 | 2016-01-01 | 9 | 4 |
1002 | 1800 | 2016-04-10 | 10 | 7 |
1003 | 348 | 2017-04-08 | 2 | 1 |
1004 | 500 | 2016-06-07 | 3 | 3 |
1005 | 499 | 2017-12-04 | 5 | 4 |
1006 | 320 | 2016-03-03 | 5 | 4 |
1007 | 80 | 2017-09-02 | 7 | 1 |
1008 | 780 | 2016-03-07 | 1 | 3 |
1009 | 560 | 2017-10-07 | 3 | 7 |
1010 | 900 | 2016-01-08 | 6 | 8 |
Основы вложенных запросов в SQL
Вывести сумму заказов и дату, которые проводил продавец с фамилией Колованов.
Начнем с такого примера и для начала вспомним, как бы делали этот запрос ранее: посмотрели бы в таблицу Salespeople, определили бы snum продавца Колыванова — он равен 1. И выполнили бы запрос SQL с помощью условия WHERE. Вот пример такого SQL запроса:
SELECT amt, odate FROM orders WHERE snum = 1
Очевидно, какой будет вывод:
amt | odate |
---|---|
348 | 2017-04-08 |
80 | 2017-09-02 |
Такой запрос, очевидно, не очень универсален, если нам захочется выбрать тоже самое для другого продавца, то всегда придется определять его snum. А теперь посмотрим на вложенный запрос:
SELECT amt, odate FROM orders where snum = (SELECT snum FROM salespeople WHERE sname = 'Колованов')
В этом примере мы определяем с помощью вложенного запроса идентификатор snum по фамилии из таблицы salespeople, а затем, в таблице orders определяем по этому идентификатору нужные нам значения. Таким образом работают вложенные запросы SQL.
Рассмотрим еще один пример:
Показать уникальные номера и фамилии продавцов, которые провели сделки в 2016 году.
SELECT snum, sname FROM salespeople where snum IN (SELECT snum FROM orders WHERE YEAR(odate) = 2016)
Этот SQL запрос отличается тем, что вместо знака = здесь используется оператор IN. Его следует использовать в том случае, если вложенный подзапрос SQL возвращает несколько значений. То есть в запросе происходит проверка, содержится ли идентификатор snum из таблицы salespeople в массиве значений, который вернул вложенный запрос. Если содержится, то SQL выдаст фамилию этого продавца.
Получился такой результат:
snum | sname |
---|---|
3 | Плотников |
4 | Кучеров |
7 | Мозякин |
8 | Проворов |
Вложенные запросы SQL с несколькими параметрами
Те примеры, которые мы уже рассмотрели, сравнивали в условии WHERE одно поле. Это конечно хорошо, но стоит отметить, что в SQL предусмотрена возможность сравнения сразу нескольких полей, то есть можно использовать вложенный запрос с несколькими параметрами.
Вывести пары покупателей и продавцов, которые осуществили сделку между собой в 2017 году.
Запрос чем то похож на предыдущий, только теперь мы добавляем еще одно поле для сравнения. Итоговый запрос SQL будет выглядеть таким образом:
SELECT cname as 'Покупатель', sname as 'Продавец' FROM customers cus, salespeople sal where (cus.cnum, sal.snum) IN (SELECT cnum, snum FROM orders WHERE YEAR(odate) = 2017)
Вывод запроса:
Покупатель | Продавец |
---|---|
Краснов | Колованов |
Колесников | Кучеров |
Лермонтов | Колованов |
Кириллов | Мозякин |
В этом примере мы сравниваем сразу два поля одновременно по идентификаторам. То есть из таблицы orders берутся те строки, которые удовлетворяют условию по 2017 году, затем вместо идентификаторов подставляются значение имен покупателей и продавцов.
На самом деле, такой запрос SQL используется крайне редко, обычно используют оператор INNER JOIN, о котором будет сказано в следующей статье.
Дополнительно скажем о конструкциях, которые использовались в этом запросе. Оператор as нужен для того, чтобы при выводе SQL показывал не имена полей, а то, что мы зададим. И после оператора FROM за именами таблиц стоят сокращения, которые потом используются — это псевдонимы. Псевдонимы можно называть любыми именами, в этом запросе они используются для явного определения поля, так как мы несколько раз обращаемся к одному и тому же полю, только из разных таблиц.
Примеры на вложенные запросы SQL
1.Напишите запрос, который бы использовал подзапрос для получения всех Заказов для покупателя с фамилией Краснов. Предположим, что вы не знаете номера этого покупателя, указываемого в поле cnum.
SELECT * FROM orders where cnum = (SELECT cnum FROM customers WHERE cname = 'Краснов')
2. Напишите запрос, который вывел бы имена и рейтинг всех покупателей, которые имеют Заказы, сумма которых выше средней.
SELECT cname, rating FROM customers where cnum IN (SELECT cnum FROM orders WHERE amt > (SELECT AVG(amt) from orders))
3. Напишите запрос, который бы выбрал общую сумму всех приобретений в Заказах для каждого продавца, у которого эта общая сумма больше, чем сумма наибольшего Заказа в таблице.
SELECT snum, SUM(AMT) FROM orders GROUP BY snum HAVING SUM(amt) > (SELECT MAX(amt) FROM orders)
4. Напишите запрос, который бы использовал подзапрос для получения всех Заказов для покупателей проживающих в Москве.
SELECT * FROM orders where cnum IN (SELECT cnum FROM customers WHERE city = 'Москва')
5. Используя подзапрос определить дату заказа, имеющего максимальное значение суммы приобретений (вывести даты и суммы приобретений).
SELECT amt, odate FROM orders WHERE AMT = (SELECT MAX(AMT) FROM orders)
6. Определить покупателей, совершивших сделки с максимальной суммой приобретений.
SELECT cname FROM customers WHERE cnum IN (SELECT cnum FROM orders WHERE amt = (SELECT MAX(amt) FROM orders))
Заключение
На этом сегодня все, мы познакомились с вложенными запросам в SQL. Очевидно, что это достаточно удобный и понятный способ получения данных из таблиц, но не всегда рационален с точки зрения скорости и нагрузки на сервер. Основные примеры, которые мы разобрали, действительно встречаются на практике языка SQL.
Спасибо за огромный труд.
Спасибо, все более менее понятно, без всяких за умных определений
Весьма однобокий разбор темы. Не рассмотрены варианты с вложенными select-ами идущими после элемента select(запрос в поле) и после элемента from(запрос в таблицу)
Спасибо, очень информативно!