Вложенные запросы SQL (Microsoft Access SQL)
Вложенный запрос — это оператор SELECT, вложенный в оператор SELECT…INTO, INSERT…INTO, DELETE или UPDATE или в другой вложенный запрос.
Синтаксис
Вы можете использовать три формы синтаксиса для создания вложенного запроса:
сравнение [ANY | ALL | SOME] (sqlstatement)
expression [NOT] IN (sqlstatement)
[NOT] EXISTS (sqlstatement)
Вложенный запрос состоит из следующих частей:
Выражение и оператор сравнения, который сравнивает выражение с результатами вложенного запроса.
Выражение, для которого выполняется поиск по набору результатов для вложенного запроса.
Оператор SELECT с тем же форматом и правилами, что и любой другой оператор SELECT. Его необходимо включать в скобки.
Примечания
Вы можете использовать вложенный запрос вместо выражения в списке полей оператора SELECT или предложении WHERE или HAVING. Во вложенном запросе вы используете оператор SELECT для предоставления набора одного или нескольких определенных значений для оценки в выражении для предложения WHERE или HAVING.
Используйте предикат ANY или SOME, которые являются синонимами, для получения записей в основном запросе, который удовлетворяет сравнению с любыми записями, полученными во вложенном запросе. Следующий пример возвращает все продукты, для которых цена за единицу выше, чем у любого продукта, продаваемого со скидкой 25 процентов или более:
SELECT * FROM Products WHERE UnitPrice > ANY (SELECT UnitPrice FROM OrderDetails WHERE Discount >= .25);
Используйте предикат ALL для получения записей в основном запросе, который удовлетворяет сравнению со всеми записями, полученными во вложенном запросе. Если вы изменили предикат с ANY на ALL в предыдущем примере, запрос будет возвращать только те продукты, у которых цена за единицу больше, чем у всех продуктов, проданных со скидкой 25 процентов или более. Это гораздо более строгое ограничение.
Используйте предикат IN для получения только тех записей в основном запросе, для которых определенная запись во вложенном запросе содержит одинаковое значение. В примере ниже возвращаются все продукты со скидкой 25 процентов или больше:
SELECT * FROM Products WHERE ProductID IN (SELECT ProductID FROM OrderDetails WHERE Discount >= .25);
С другой стороны, вы можете использовать NOT IN для получения только тех записей в основном запросе, для которых ни одна запись во вложенном запросе не содержит одинаковое значение.
Используйте предикат EXISTS (с необязательным зарезервированным словом NOT) в сравнениях ИСТИНА/ЛОЖЬ, чтобы определить, возвращает ли вложенный запрос какие-либо записи.
Также можно использовать псевдонимы имени таблицы во вложенном запросе для ссылки на таблицы, указанные в предложении FROM за пределами вложенного запроса. Пример ниже возвращает имена сотрудников, чья заработная плата равна или выше средней заработной платы всех сотрудников на аналогичной должности. Для таблицы «Сотрудники» присваивается псевдоним «T1»:
SELECT LastName, FirstName, Title, Salary FROM Employees AS T1 WHERE Salary >= (SELECT Avg(Salary) FROM Employees WHERE T1.Title = Employees.Title) Order by Title;
В приведенном выше примере зарезервированное слово AS не является обязательным.
Некоторые вложенные запросы поддерживаются в перекрестных запросах, в частности, в качестве предикатов (например в предложении WHERE). Вложенные запросы в виде выходных данных (в списке SELECT) не поддерживаются в перекрестных запросах.
Пример
В данном примере перечислены имена и контактные данные каждого клиента, разместившего заказ во втором квартале 1995 года. В этом примере выполняется вызов процедуры EnumFields, которую можно найти в примере для оператора SELECT.
Sub SubQueryX() Dim dbs As Database, rst As Recordset ' Modify this line to include the path to Northwind ' on your computer. Set dbs = OpenDatabase("Northwind.mdb") ' List the name and contact of every customer ' who placed an order in the second quarter of ' 1995. Set rst = dbs.OpenRecordset("SELECT ContactName," _ & " CompanyName, ContactTitle, Phone" _ & " FROM Customers" _ & " WHERE CustomerID" _ & " IN (SELECT CustomerID FROM Orders" _ & " WHERE OrderDate Between #04/1/95#" _ & " And #07/1/95#);") ' Populate the Recordset. rst.MoveLast ' Call EnumFields to print the contents of the ' Recordset. Pass the Recordset object and desired ' field width. EnumFields rst, 25 dbs.Close End Sub
SQL — Урок 5. Вложенные запросы
В прошлом уроке мы столкнулись с одним неудобством. Когда мы хотели узнать, кто создал тему «велосипеды», и делали соответствующий запрос:
Вместо имени автора, мы получали его идентификатор. Это и понятно, ведь мы делали запрос к одной таблице — Темы, а имена авторов тем хранятся в другой таблице — Пользователи. Поэтому, узнав идентификатор автора темы, нам надо сделать еще один запрос — к таблице Пользователи, чтобы узнать его имя:
В SQL предусмотрена возможность объединять такие запросы в один путем превращения одного из них в подзапрос (вложенный запрос). Итак, чтобы узнать, кто создал тему «велосипеды», мы сделаем следующий запрос:
То есть, после ключевого слова WHERE, в условие мы записываем еще один запрос. MySQL сначала обрабатывает подзапрос, возвращает id_author=2, и это значение передается в предложение WHERE внешнего запроса.
В одном запросе может быть несколько подзапросов, синтаксис у такого запроса следующий:
SELECT имя_столбца FROM имя_таблицы WHERE часть условия IN (SELECT имя_столбца FROM имя_таблицы WHERE часть условия IN (SELECT имя_столбца FROM имя_таблицы WHERE условие) ) ;
Обратите внимание, что подзапросы могут выбирать только один столбец, значения которого они будут возвращать внешнему запросу. Попытка выбрать несколько столбцов приведет к ошибке.
Давайте для закрепления составим еще один запрос, узнаем, какие сообщения на форуме оставлял автор темы «велосипеды»:
Теперь усложним задачу, узнаем, в каких темах оставлял сообщения автор темы «велосипеды»:
-
Сначала MySQL выполнит самый глубокий запрос:
SELECT id_author FROM topics WHERE topic_name=’велосипеды’
SELECT id_topic FROM posts WHERE id_author IN (2);
SELECT topic_name FROM topics WHERE id_topic IN (4,1);
SELECT name FROM users WHERE id_user IN
(SELECT id_author FROM topics WHERE topic_name=’велосипеды’);
SELECT name FROM users WHERE id_user =
(SELECT id_author FROM topics WHERE topic_name=’велосипеды’);
Научись программировать на Python прямо сейчас!
- Научись программировать на Python прямо сейчас
- Бесплатный курс
Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.
Что такое вложенные запросы
Скачай курс
в приложении
Перейти в приложение
Открыть мобильную версию сайта
© 2013 — 2024. Stepik
Наши условия использования и конфиденциальности
Public user contributions licensed under cc-wiki license with attribution required
Вложенные запросы против JOIN в SQL
Одной из проблем при написании SQL-запросов является выбор между использованием вложенных запросов или объединением таблиц через JOIN . В каких ситуациях следует применять тот или иной подход — рассмотрим в данном материале.
Обычно сложные запросы состоят из основного внешнего SQL-запроса, в который вложены один или несколько подзапросов.
Подзапросы бывают простыми и коррелирующими. Корррлирующие вложенные запросы используют данные из внешнего по отношению к нему запроса.
А использование JOIN может вообще не подразумевать дополнительных подзапросов, а лишь объединяет данные двух или более таблиц в результирующий набор данных. Чаще всего такое объединение делается по первичным и внешним ключам.
При составлении сложных SQL-запросов использоваться могут как JOIN , так и подзапросы, но действуют они по-разному. Когда-то мы можем выбирать более удобный для себя вариант, а иногда подзапросы становятся нашим единственным выходом. Рассмотрим несколько примеров.
Перед вами таблица product , хранящая в себе данные о различных товарах.
Она содержит следующие данные:
- id — идентификатор товара.
- name — название товара.
- cost — стоимость товара.
- year — год изготовления товара.
- city — город изготовления товара.
А вот ещё одна таблица — sale . Здесь находятся сведения о продажах товаров из приведённой выше таблицы.
- id — идентификатор продажи.
- product_id — идентификатор проданного товара.
- price — цена продажи.
- year — год продажи.
- city — город, в котором товар был продан.
Эти две таблицы мы будем использовать при написании сложных SQL-запросов с JOIN и подзапросами.
Когда вложенные запросы стоит заменить на JOIN
Новички часто используют именно вложенные запросы, потому что их проще читать и понимать. В это же время JOIN работает более эффективно, не уступая в читаемости запросов по мере их усложнения. Для начала рассмотрим случаи, когда подзапросы лучше переписать с использованием JOIN для повышения эффективности и удобочитаемости.
Скалярный подзапрос
Работа со скалярным подзапросом один из тех вариантов, когда лучше использовать JOIN . Он возвращает единственное значение (один столбец и одну строку), которое будет использоваться внешним запросом. Рассмотрим пример.
Допустим, мы хотим получить названия и стоимость продуктов, проданных за $2000.
Посмотрим на код с вложенным запросом:
SELECT name, cost FROM product WHERE product_id FROM sale WHERE price=2000 AND product_id=product.id );
Результат выглядит следующим образом:
Внешний запрос выбирает из таблицы product столбцы с названиями и стоимостью товаров. Поскольку нам нужны не все товары, мы используем выражение WHERE для фильтрации результата, полученного из вложенного запроса, по идентификаторам товаров.
Теперь посмотрим на вложенный запрос. Таблица sale содержит записи о продажах товаров. Сначала подзапрос выбирает записи только тех товаров, которые были проданы за $2000. Затем он использует идентификаторы проданных товаров ( product_id ) в отобранных по условию продажах для их сопоставления с соответствующими записями в таблице product ( product_id=product.id ). Как мы можем видеть, за $2000 были проданы 2 товара: кресло и стол для телевизора, стоимость которых соответственно равна $500 и $2000. Этот подзапрос относится к числу коррелирующих, так как использует данные из внешнего запроса.
На самом деле, такой запрос не очень эффективен. Давайте перепишем этот же запрос, но уже с использованием JOIN .
SELECT p.name, p.cost FROM product p JOIN sale s ON p.id = s.product_id WHERE s.price = 2000;
В этом запросе мы соединили записи из двух таблиц с помощью оператора JOIN , связав полученные данные идентификаторами товаров. В конце, используя выражение WHERE , мы оставили записи лишь о тех продажах, в которых сумма сделки составила $2000.
Подзапрос внутри оператора IN
Если подзапрос содержится внутри оператора IN , его тоже следует переписать с помощью JOIN . В таком случае подзапрос вернет внешнему запросу список значений.
Допустим, мы хотим получить названия и стоимость товаров, которые были проданы:
SELECT name, cost FROM product WHERE id IN (SELECT product_id FROM sale)
В данном случае внешний запрос выбирает из таблицы product названия и стоимость товаров, после чего оставляет лишь те из них, чьи идентификаторы содержатся в списке, возвращаемом подзапросом. Подзапрос, в свою очередь, выбирает из таблицы sale все записи о проданных товаров. По этой причине конечный результат включает в себя информацию только о тех товарах из таблицы product , которые были проданы согласно записям в таблице sale .
Итоговая выборка данных выглядит следующим образом:
Из всех товаров было продано 5 (4 из таблицы выше + tv table, который там так же должен быть).
Перепишем запрос, используя оператор JOIN :
SELECT DISTINCT p.name, p.cost FROM product p JOIN sale s ON s.product_id = p.id;
В итоге наш запрос стал значительно проще. Он объединяет данные из двух таблиц по идентификаторам товаров. Поскольку это то же самое, что и INNER JOIN , запись о товаре из таблицы product не будет возвращена, если сведений о продаже этого товара нет в таблице sale .
Подзапрос внутри оператора NOT IN
Этот случай аналогичен предыдущему, только теперь мы должны получить список непроданных товаров.
Пример с подзапросом внутри оператора NOT IN :
SELECT name, cost FROM product WHERE id NOT IN (SELECT product_id FROM sale);
Подзапросом выбираем идентификаторы товаров в таблице sale и сравнивает их с идентификаторами из внешнего запроса. Если во внешнем запросе такого идентификатора нет, запись о товаре возвращается.
Переписав запрос с помощью JOIN , получаем следующий вариант:
SELECT DISTINCT p.name, p.cost FROM product p LEFT JOIN sale s ON s.product_id=p.id WHERE s.product_id IS NULL;
Как и в примерах выше, данный запрос объединяет записи из двух таблиц по идентификаторам товаров. Также нам следует использовать ключевое слово DISTINCT , чтобы отбросить дубликаты из итоговой выборки.
Обратите внимание, что мы использовали LEFT JOIN в сочетании с WHERE . Используя такую конструкцию запроса, изначально мы выбираем абсолютно все записи товаров из таблицы product , и лишь потом выбираем те из них, чьи идентификаторы в таблице sale равны NULL . В нашем случае значение NULL свидетельствует о том, что товар не был продан.
Коррелирующие подзапросы в выражениях EXISTS и NOT EXISTS
Если вложенный запрос используется с одним из этих операторов, его также можно переписать с использованием JOIN .
Давайте получим подробную информацию о продукции, которую не удалось реализовать в 2020 году.
SELECT name, cost, city FROM product WHERE NOT EXISTS (SELECT id FROM sale WHERE year = 2020 AND product_id = product.id);
Вот так выглядит результирующая выборка:
Из общей совокупности товаров, возвращаемой внешним запросом, вложенный запрос выбирает лишь те, которые были проданы в 2020 году. Если подзапрос не смог обнаружить запись, выражение NOT EXISTS вернет значение True .
В итоге мы получаем записи о товарах, которые либо были проданы НЕ в 2020 году, либо не были проданы вовсе.
А так выглядит тот же запрос с использованием JOIN :
SELECT p.name, p.cost, p.city FROM product p LEFT JOIN sale s ON s.product_id = p.id WHERE s.year<>2020 OR s.year IS NULL;
Данный запрос соединяет таблицы product и sale с помощью оператора LEFT JOIN . Это позволяет нам включить в выборку товары, которые не были проданы. Выражение WHERE выбирает две категории товаров:
- У которых нет сведений о продажах ( s.year == NULL ).
- Которые были проданы НЕ в 2020 году ( s.year <> 2020 ).
Когда вложенные запросы нельзя заменить оператором JOIN
Несмотря на эффективность оператора JOIN , иногда лучше использовать вложенные запросы. Рассмотрим такие случаи.
Подзапрос внутри FROM вместе с GROUP BY
В качестве первого примера рассмотрим применение запроса, вложенного FROM и использующего GROUP BY для вычисления агрегированных значений.
SELECT city, sum_price FROM ( SELECT city, SUM(price) AS sum_price FROM sale GROUP BY city ) AS s WHERE sum_price < 2100;
Что мы получим в итоге:
В данном случае запрос выбирает города и для каждого из них вычисляет сумму продаж с помощью агрегатной функции SUM() . Внешний запрос выбирает из вложенного только те города, сумма продаж в которых составляет менее $2100 ( WHERE sum_price < 2100 ).
Запрос, вложенный в WHERE и возвращающий агрегированное значение
Другая ситуация, при которой нельзя переписать вложенный запрос с помощью JOIN — агрегированное значение, сравниваемое в предложении WHERE . Пример:
SELECT name FROM product WHERE cost < (SELECT AVG(price) FROM sale);
Этот запрос отбирает названия товаров, чья цена ниже средней суммы продаж. Средняя сумма продаж рассчитывается с помощью агрегатной функции AVG() и возвращается из подзапроса. Затем во внешнем запросе стоимость каждого товара сравнивается с этим средним значением.
Подзапрос в комбинации с ALL
Теперь рассмотрим ситуацию, когда запрос вложен в ALL .
SELECT name FROM product WHERE cost > ALL(SELECT price FROM sale);
Подзапрос возвращает все цены продаж из таблицы sale . Внешний же запрос возвращает название товара, чья цена в product больше любой суммы продаж в sale .
Когда лучше использовать подзапросы, а когда JOIN ?
Мы рассмотрели распространенные случаи использования подзапросов, а также ситуации, в которых их можно переписать с использованием JOIN . В большинстве случаев использование JOIN более эффективно, однако иногда вложенные запросы просто необходимы.
Новичкам в SQL легче понимать именно вложенные запросы, хотя для опытных специалистов удобнее читать именно JOIN -конструкции по мере усложнения самих запросов. Более того, если ваш запрос будет содержать несколько уровней вложенных запросов, это сильно ударит по производительности и читаемости кода.
Там, где это возможно, лучше использовать оператор JOIN . Вложенные запросы лучше оставить для ситуаций, когда без их использования не обойтись.