Вложенные запросы SQL
Здравствуйте, уважаемые читатели! В этой статье мы поговорим о том, что такое вложенные запросы в SQL. Традиционно, рассмотрим несколько примеров с той базой данных, которую создавали в первых статьях.
Введение
Итак, само название говорит о том, что запрос во что-то вложен. Так вот, вложенный запрос в SQL означает, что запрос select выполняется в еще одном запросе select — на самом деле вложенность может быть и многоуровневой, то есть select в select в select и т.д.
Такие запросы обычно используются для получения данных из двух и более таблиц. Они нужны чтобы данные из разных таблиц можно было соотнести и по зависимости осуществить выборку. У вложенных запросов есть и недостаток — зачастую слишком долгое время работы занимает запрос, потому что идет большая нагрузка на сервер. Тем не менее, саму конструкцию необходимо знать и использовать при возможности.
Структура ранее созданных таблиц
Прежде чем перейдем к простому примеру, напомним структуру наших таблиц, с которыми будем работать:
- Таблица Salespeople (продавцы):
- Таблица Customers (покупатели):
- Таблица Orders (заказы)
Основы вложенных запросов в SQL
Вывести сумму заказов и дату, которые проводил продавец с фамилией Колованов.
Начнем с такого примера и для начала вспомним, как бы делали этот запрос ранее: посмотрели бы в таблицу Salespeople, определили бы snum продавца Колыванова — он равен 1. И выполнили бы запрос SQL с помощью условия WHERE. Вот пример такого SQL запроса:
Очевидно, какой будет вывод:
| amt | odate |
|---|---|
| 348 | 2017-04-08 |
| 80 | 2017-09-02 |
Такой запрос, очевидно, не очень универсален, если нам захочется выбрать тоже самое для другого продавца, то всегда придется определять его snum. А теперь посмотрим на вложенный запрос:
В этом примере мы определяем с помощью вложенного запроса идентификатор snum по фамилии из таблицы salespeople, а затем, в таблице orders определяем по этому идентификатору нужные нам значения. Таким образом работают вложенные запросы SQL.
Рассмотрим еще один пример:
Показать уникальные номера и фамилии продавцов, которые провели сделки в 2016 году.
Этот SQL запрос отличается тем, что вместо знака = здесь используется оператор IN. Его следует использовать в том случае, если вложенный подзапрос SQL возвращает несколько значений. То есть в запросе происходит проверка, содержится ли идентификатор snum из таблицы salespeople в массиве значений, который вернул вложенный запрос. Если содержится, то SQL выдаст фамилию этого продавца.
Получился такой результат:
| snum | sname |
|---|---|
| 3 | Плотников |
| 4 | Кучеров |
| 7 | Мозякин |
| 8 | Проворов |
Вложенные запросы SQL с несколькими параметрами
Те примеры, которые мы уже рассмотрели, сравнивали в условии WHERE одно поле. Это конечно хорошо, но стоит отметить, что в SQL предусмотрена возможность сравнения сразу нескольких полей, то есть можно использовать вложенный запрос с несколькими параметрами.
Вывести пары покупателей и продавцов, которые осуществили сделку между собой в 2017 году.
Запрос чем то похож на предыдущий, только теперь мы добавляем еще одно поле для сравнения. Итоговый запрос SQL будет выглядеть таким образом:
| Покупатель | Продавец |
|---|---|
| Краснов | Колованов |
| Колесников | Кучеров |
| Лермонтов | Колованов |
| Кириллов | Мозякин |
В этом примере мы сравниваем сразу два поля одновременно по идентификаторам. То есть из таблицы orders берутся те строки, которые удовлетворяют условию по 2017 году, затем вместо идентификаторов подставляются значение имен покупателей и продавцов.
На самом деле, такой запрос SQL используется крайне редко, обычно используют оператор INNER JOIN, о котором будет сказано в следующей статье.
Дополнительно скажем о конструкциях, которые использовались в этом запросе. Оператор as нужен для того, чтобы при выводе SQL показывал не имена полей, а то, что мы зададим. И после оператора FROM за именами таблиц стоят сокращения, которые потом используются — это псевдонимы. Псевдонимы можно называть любыми именами, в этом запросе они используются для явного определения поля, так как мы несколько раз обращаемся к одному и тому же полю, только из разных таблиц.
Примеры на вложенные запросы SQL
1.Напишите запрос, который бы использовал подзапрос для получения всех Заказов для покупателя с фамилией Краснов. Предположим, что вы не знаете номера этого покупателя, указываемого в поле cnum.
2. Напишите запрос, который вывел бы имена и рейтинг всех покупателей, которые имеют Заказы, сумма которых выше средней.
3. Напишите запрос, который бы выбрал общую сумму всех приобретений в Заказах для каждого продавца, у которого эта общая сумма больше, чем сумма наибольшего Заказа в таблице.
4. Напишите запрос, который бы использовал подзапрос для получения всех Заказов для покупателей проживающих в Москве.
5. Используя подзапрос определить дату заказа, имеющего максимальное значение суммы приобретений (вывести даты и суммы приобретений).
6. Определить покупателей, совершивших сделки с максимальной суммой приобретений.
Заключение
На этом сегодня все, мы познакомились с вложенными запросам в SQL. Очевидно, что это достаточно удобный и понятный способ получения данных из таблиц, но не всегда рационален с точки зрения скорости и нагрузки на сервер. Основные примеры, которые мы разобрали, действительно встречаются на практике языка SQL.
SQL подзапросы: руководство по использованию
Рассказываем, что такое подзапросы в SQL и как их использовать.
Содержание
Есть задачи, которые нельзя решить с помощью одного обычного запроса. Пример такой задачи — выборка всех записей со значением больше среднего по всей таблице. Для одного запроса нельзя и выбрать значения, и посчитать агрегатную функцию по всей таблице. Чтобы решить такие задачи, используют подзапросы.
Рассказываем в статье, что такое подзапросы в SQL и для чего они нужны.
Что такое подзапросы в SQL
SQL-подзапрос — это SELECT-запрос, вложенный в другой запрос или подзапрос.
Подзапрос — это внутренний запрос. Внешний запрос — это оператор, который содержит подзапрос.
Для чего нужны
Подзапросами пользуются, когда нужно использовать результат выполнения одного запроса в следующем запросе.
Приведем пример на базе данных из трех таблиц: «Студенты», «Учебные курсы», «Оценки».
Объединим два последовательных запроса в один, чтобы найти любимые студентами предметы — то есть предметы, по которым средний балл выше среднего балла всех предметов.
Для этого разобьем задачу на две части. Сначала найдем средний балл среди всех студентов по всем предметам:
Потом напишем запрос, который находит средний балл для каждого учебного предмета:
Чтобы найти любимые предметы, нужно в разделе HAVING подставить значение из первого запроса. В нашем случае — это HAVING avg_mark 3.4286. Если выполним такой запрос, получим корректный результат.
Чтобы не заниматься ручной подстановкой значений в запросы, воспользуемся подзапросом. Для этого нужно вместо значения подставить тело запроса, обернув его в скобки:
Синтаксис
Синтаксически подзапрос — это SELECT-запрос, обернутый в круглые скобки ( , ). Подзапрос может быть вложен в любой другой оператор. Можно вкладывать подзапросы в подзапросы.
Вложенные запросы можно использовать практически во всех частях внешнего запроса — везде, где разрешено использовать значения.
Типы вложенных запросов
Результат выполнения подзапроса подставляют во внешний запрос. Подзапросы могут возвращать как скалярные значения, так и табличные значения. От типа возвращаемого значения зависит, с какими операциями имеет смысл использовать подзапрос.
Скалярное значение — это когда возвращается одно значение. Обычно это число или строка. Со скалярными значениями можно использовать операторы сравнения (<, >, =), можно передавать как аргумент функции или как значение колонки в операторе SELECT. Например, посчитаем для каждого ученика, какой процент курсов он посещал:
Табличное значение — когда возвращается несколько строк. Заранее неизвестно сколько: может, ноль, одна или больше. С табличными значениями используют операции IN, ANY, ALL, EXISTS, NOT EXISTS. Все эти операции проверяют вхождение строк(и) внешнего запроса в табличное значение, возвращаемое подзапросом. Еще табличное значение можно использовать в разделе FROM как таблицу-источник.
Пример подзапроса, возвращающего табличное значение. Найдем учебные курсы, где есть студенты с хотя бы одной отметкой. Это можно сделать с помощью оператора IN, который проверяет вхождение Classes.id в список ID классов, для которых есть оценки:
И с помощью оператора EXISTS, который проверяет для каждого учебного курса наличие хотя бы одной оценки в таблице Marks.
Нужно быть аккуратным при работе с NOT IN: Если в список значений попадет NULL, результат выборки будет пустым:
Отличить подзапросы по возвращаемому значению очень просто: скалярные выбирают только одну колонку. А еще используют агрегатные функции без группировки GROUP BY. В таком случае СУБД видит, что запрос может вернуть только одну колонку и одну строку, то есть скалярное значение. Отсюда следует практическая рекомендация: если хочется использовать подзапрос как скалярное значение, нужно использовать агрегатную функцию.
Порядок выполнения подзапросов
По способу выполнения выделяют два типа подзапросов.
Простые. Такие подзапросы не зависят от внешнего запроса. СУБД выполнит такой подзапрос один раз перед выполнением внешнего запроса — и позже будет использовать значение столько раз, сколько понадобится. Пример простого подзапроса: найти всех студентов, которые не записались ни на один курс:
Сложные (коррелированные подзапросы — Correlated Subqueries). Такие подзапросы обращаются к полям внешнего запроса. СУБД будет вынуждена выполнить подзапрос для каждой строки, подставляя значение строки внешнего значения как параметр подзапроса.
Пример сложного подзапроса: найти всех студентов со средним баллом больше четырех. В этом примере важно заметить, что подзапрос использует Strudents.id из внешнего запроса.
Примеры вложенных запросов
Рассмотрим примеры вложенных запросов в различных операторах SQL.
SELECT
Выберем всех учеников, у которых по одному из курсов есть лучшая оценка среди всех студентов. Для этого воспользуемся операцией ALL. Синтаксически она немного отличается от уже рассмотренных операций: сначала идет оператор сравнения, потом ALL или ANY, после чего следует подзапрос в круглых скобках:
INSERT
В новую таблицу BestStudents2022 скопируем всех студентов со средней оценкой, которая больше, чем средняя оценка среди всех студентов. Воспользуемся конструкциями INSERT … SELECT. Они позволят вставлять строки, которые возвращают SELECT-часть запроса.
Нам нужно посчитать сразу два средних арифметических значения: средний балл для каждого студента и для всех студентов. Напишем запрос с двумя подзапросами: коррелированным и обычным.
UPDATE
Можно использовать подзапрос, чтобы изменить данные в таблицах. Например, можно отметить предметы, по которым есть более десяти оценок, как популярные (featured) — и таким образом рекомендовать их другим студентам.
Можно использовать следующий запрос:
В результате два учебных класса отмечены как популярные:
DELETE
SQL-подзапросы можно использовать с оператором DELETE. Давайте удалим все курсы, для которых нет ни одной оценки. Воспользуемся подзапросом и операцией NOT EXISTS:
Подзапросы в SQL (вложенные запросы SQL)
o В инструкции SELECT ;
o В инструкции FROM ;
o В условии WHERE .
- Подзапрос может быть вложен в инструкции SELECT , INSERT , UPDATE или DELETE , а также в другой подзапрос;
- Подзапрос обычно добавляется в условие WHERE оператора SQL SELECT ;
- Можно использовать операторы сравнения, такие как >, <, или =. IN, ANY или ALL ;
- Подзапрос также называется внутренним запросом. Оператор, содержащий подзапрос, также называется внешним;
- Внутренний запрос выполняется перед родительским запросом, чтобы результаты его работы могли быть переданы внешнему.
Подзапрос можно использовать в инструкциях SELECT , INSERT , DELETE или UPDATE для выполнения следующих задач:
- Сравнения выражения с результатом запроса;
- Определения того, включено ли выражение в результаты запроса;
- Проверки того, выбирает ли запрос любые строки.
- Подзапрос SQL ( внутренний запрос ) выполняется перед выполнением основного запроса ( внешнего запроса );
- Основной запрос использует результат выполнения подзапроса.
Подзапросы SQL-примеры
В этом разделе мы рассмотрим, как использовать подзапросы. У нас есть следующие две таблицы: ‘ student ‘ и ‘ marks ‘ с общим полем ‘ StudentID ‘:
Теперь нужно составить запрос, определяющий всех студентов, которые получают лучшие отметки, чем студент со StudentID — « V002 ». Но мы не знаем отметок студента « V002 ».
Поэтому нужно составить два SQL подзапроса в Select . Один запрос возвращает отметки ( хранятся в поле « Total_marks » ) для « V002 », а второй запрос выбирает учеников, которые получают лучшие оценки, чем результат первого запроса.
Результатом запроса будет 80 .
Используя результат этого запроса, мы написали еще один запрос, чтобы определить учеников, которые получают оценки лучше, чем 80 .
Два приведенных запроса определяют студентов, которые получают лучше оценки, чем студент StudentID « V002 » ( Abhay ).
Можно объединить эти два запроса, вложив один запрос в другой. Подзапрос — это запрос внутри круглых скобок. Рассмотрим подзапроса в SQL пример :
Графическое представление подзапроса SQL :
Подзапросы в SQL (вложенные запросы SQL): общие правила
Ниже приведен синтаксис подзапроса :
Подзапросы SQL (вложенные запросы SQL): рекомендации по использованию
Ниже приведен ряд рекомендаций, которым нужно следовать при использовании SQL подзапросов:
- Подзапрос должен быть заключен в круглые скобки;
- Подзапрос должен указываться в правой части оператора сравнения;
- Подзапросы не могут обрабатывать свои результаты, поэтому в подзапрос не может быть добавлено условие ORDER BY ;
- Используйте однострочные операторы с однострочными подзапросами;
- Если подзапрос возвращает во внешний запрос значение null , внешний запрос не будет возвращать никакие строки при использовании операторов сравнения в условии WHERE .
Подзапросы SQL (вложенные запросы SQL) — основные типы
- Однострочный подзапрос : возвращает ноль или одну строку;
- Многострочный подзапрос : возвращает одну или несколько строк;
- Многостолбцовый подзапрос : возвращает один или несколько столбцов;
- Коррелированные подзапросы : указывают один или несколько столбцов во внешней инструкции SQL . Такой подзапрос называется коррелированным, поскольку он связан с внешней инструкцией SQL ;
- Вложенные подзапросы : подзапросы помещенные в другой подзапрос.
Также можно использовать подзапрос внутри инструкций INSERT , UPDATE и DELETE .
Подзапросы SQL с инструкцией INSERT
Инструкция INSERT может использоваться с подзапросами SQL .
Если мы хотим вставить заказы из таблицы ‘ orders ‘, для которых в таблице « neworder » значение advance_amount составляет 2000 или 1500 , можно использовать следующий код SQL :
SQL Subqueries
Last time we introduced joining tables in SQL. This time we move on to Subqueries.
What is a subquery
Very simply, it is a query inside of a query. They are useful when you need to carry out a process multiple steps. Often, to get the information you want, you have to do some work on your data before you can begin with the selecting, filtering, or calculating. Subqueries are a popular way of performing this work.
Types of Queries
You can use a subquery in many different places of the main query: SELECT, FROM, WHERE.
A subquery can return different data types — a single value or scalar, a row, or a table. The data produced by the subquery determines how the main query may interact with the data. When the main query is complete, the data from any subqueries gets dropped. So the subquery acts like a temporary table.
Why would you use a Sub Query?
Here are some examples where a subquery could help:
- If you need to identify the top-selling Amazon products in November where sales are greater than $10,000. A subquery could aggregate hourly sales to November sales. You can use that output in the main query.
- If you need to determine which products are selling at a higher retail price than the average price. A subquery could aggregate individual items to create an average price. Then the main query can compare the average price against each item price.
Characteristics
A simple subquery can run independently. It has its own SELECT clause and FROM clause. You can run the inner query without the outer or main query. Knowing that a subquery can stand alone is helpful. You can confirm whether your subquery is working as expected simply by running it separately. A correlated subquery is different from a simple subquery. It depends on the outer query for its values, meaning that it cannot be executed independently like a simple subquery.
Ideally, subqueries should be easy to spot in SQL code. They are enclosed in parentheses and it is best practice to indent a subquery for easy reading. Subqueries can also be given an alias, in the same way as a table or column.
Examples
Subquery in the WHERE clause
- Filter results, based on data that you’d otherwise have to calculate separately
- Can create a list that you can use for filtering
- Subqueries in the WHERE clause only return a single column
In the examples, we’ll use an imaginary basketball database. In this example we are using two tables — team and game. The team table has details about each team, while the game table has details about the individual games.
Let’s look at what happens when you run this query:
The database first runs the subquery — the part at the bottom, between the parentheses. This gives us a list of team_ids where teams scored at least 130 points. The outer or main query uses this list to return our final result with the actual team name
Subquery in the FROM clause
- Help restructure and transform your data
- Provide additional processing before analysis. For example, you can filter or reshape the data
- Calculate aggregates of summary information
- You can create more than one query in one FROM statement
- Subqueries can be joined to each other or to other tables if they have a column in common.
Subquery in the SELECT clause
- Return a single aggregate value
- Create calculated columns with a single numeric value, which can be useful for calculations
- Note that filtering the main query does not apply to the subquery, so you need to include your filters in both the subquery and the main query
Best practices
- Line up statements — SELECT, FROM, WHERE, and indent information contained within a subquery
- Ask is the subquery necessary?
There are trade-offs with developing subqueries. You can sometimes solve the problem without using a subquery — using an aggregate function or a join. Things to consider when writing queries are the readability, the performance, and what the database is doing when running a query.
If I want to create a subquery, what are the advantages of using a subquery? Will I use it repetitively throughout the entire code? Or is it just a one-time effort? Where do I place the query to get the results I want?
Subqueries require computing power, so consider the size of your database or table. Subqueries can also make processing more efficient by aggregating tables separately and then joining them so that the datasets involved are much smaller.