Что такое вложенные запросы

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 — 2023. Stepik

Наши условия использования и конфиденциальности

Public user contributions licensed under cc-wiki license with attribution required

Вложенные запросы в T-SQL – описание и примеры

Приветствую Вас на сайте Info-Comp.ru! В этой заметке мы рассмотрим вложенные запросы языка SQL, я расскажу, что такое вложенные запросы, где и в каких конструкциях их можно использовать, покажу примеры их использования, а также расскажу про особенности и некоторые ограничения вложенных SQL запросов или, как еще их иногда называют, подзапросов SQL.

Что такое вложенные запросы SQL?

Вложенный SQL запрос – это отдельный запрос, который используется внутри SQL инструкции. Вложенный запрос также называют внутренним SQL запросом или подзапросом, а инструкцию, в которой используется вложенный запрос, называют внешним SQL запросом.

Вложенные SQL запросы могут быть использованы везде, где разрешено использовать SQL выражения, это может быть и секция SELECT, и FROM, и WHERE, и даже JOIN, чуть ниже я покажу примеры использования вложенных запросов в каждой из перечисленных выше секций.

Использовать вложенные запросы иногда бывает очень удобно, но обязательно стоит отметить и то, что в некоторых случаях использование вложенного SQL запроса может снизить производительность, т.е. замедлить работу всей SQL инструкции. Тем более что не редко вложенный SQL запрос можно заменить простым объединением.

Кроме того, вложенные запросы могут быть вложены друг в друга (в некоторых случаях вплоть до 32-го уровня), но тем самым значительно снижается читабельность SQL инструкций и ее понятность, а также повышается ее сложность.

Кстати, о том, как писать хорошие понятные SQL инструкции на языке T-SQL, которые будут понятны и Вам спустя время, и другим программистам, я подробно рассказал в своей книге – «Стиль программирования на T-SQL – основы правильного написания кода».

Заметка! Для комплексного изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL.

Особенности вложенных запросов

Вложенные SQL запросы имеют несколько важных особенностей, про которые не стоит забывать при конструировании SQL инструкций:

• Вложенный запрос всегда заключен в скобки;
• Вложенный запрос не может содержать предложения COMPUTE, INTO и FOR BROWSE;
• Вложенный запрос может содержать конструкцию сортировки ORDER BY, только если он содержит оператор TOP, т.е. без TOP, ORDER BY в подзапросе использовать не получится;
• Если вложенный запрос используется в операции сравнения (за исключением операторов EXISTS и IN), он должен возвращать одно значение и один столбец;
• Типы данных ntext, text и image не могут участвовать в списке выбора вложенных запросов.

Примеры вложенных SQL запросов в Microsoft SQL Server

Ну а теперь пора переходить к практике, сейчас мы рассмотрим несколько примеров использования вложенных SQL запросов, при этом я, как и обещал, покажу применение вложенных запросов в разных конструкциях языка T-SQL.

Примечание! Все примеры тестовые, они сконструированы исключительно для демонстрации работы вложенных запросов.

Исходные данные для примеров

Сначала давайте определимся с исходными данными, чтобы Вы понимали, какие именно данные у нас есть, и наглядно видели, каким образом в примерах ниже получаются те или иные результаты.

Также сразу скажу, что в качестве SQL сервера у меня выступает версия Microsoft SQL Server 2017 Express.

Следующая инструкция создает таблицы, которые мы будет использовать в примерах, и добавляет в них данные.

--Создание таблицы Goods CREATE TABLE Goods ( ProductId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_ProductId PRIMARY KEY, Category INT NOT NULL, ProductName VARCHAR(100) NOT NULL, Price MONEY NULL, ); GO --Создание таблицы Categories CREATE TABLE Categories ( CategoryId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_CategoryId PRIMARY KEY, CategoryName VARCHAR(100) NOT NULL ); --Добавление строк в таблицу Categories INSERT INTO Categories(CategoryName) VALUES ('Комплектующие ПК'), ('Мобильные устройства'); GO --Добавление строк в таблицу Goods INSERT INTO Goods(Category, ProductName, Price) VALUES (1, 'Системный блок', 300), (1, 'Монитор', 200), (2, 'Смартфон', 250); GO --Выборка данных SELECT * FROM Goods; SELECT * FROM Categories;

Подробно останавливаться на том, что делает представленная выше инструкция, в этой статье я не буду, так как это совершенно другая тема, но если эта SQL инструкция Вам не понятна, и Вам интересно узнать, что конкретно она делает, можете почитать следующие статьи, а для комплексного изучения языка T-SQL — книгу, которую я уже упоминал:

• Создание таблиц в Microsoft SQL Server (CREATE TABLE);
• Изменение таблиц в Microsoft SQL Server (ALTER TABLE);
• Добавление данных в таблицы (INSERT INTO);
• SQL код – самоучитель по SQL для начинающих программистов.

Пример 1 – Вложенный запрос в секции SELECT

В этом примере мы рассмотрим стандартную ситуацию использования вложенного запроса в списке выборки оператора SELECT.

Допустим, что нам нужно получить список товаров с названием категорий, а так как названия категории в таблице Goods у нас нет, это название мы будем получать из таблицы Categories.

Это можно сделать с помощью вложенного запроса, в котором будет происходить объединение с внешним запросом в секции WHERE, посредством уточняющих псевдонимов. В данном случае вложенный запрос обязательно должен возвращать одно значение и один столбец.

А также это можно реализовать и с помощью объединения JOIN, что на самом деле предпочтительней, и в подобных случаях я рекомендую использовать именно JOIN, тем самым SQL запрос становится более читабельным и простым для понимания. Ниже я представлю оба SQL запроса.

--Выводим название категории с помощью вложенного запроса SELECT G.ProductName, (SELECT CategoryName FROM Categories C WHERE C.CategoryId = G.Category) AS CategoryName FROM Goods G; --Эквивалент с использованием объединения JOIN SELECT G.ProductName, C.CategoryName FROM Goods G INNER JOIN Categories C ON G.Category = C.CategoryId;

Пример 2 – Вложенный запрос в секции FROM

Сейчас давайте я покажу, как можно использовать вложенный запрос в секции FROM в качестве источника данных. Такие вложенные запросы обычно называют – Производные таблицы, так как они возвращают табличные данные.

SELECT ProductId, ProductName FROM (SELECT ProductId, ProductName FROM Goods WHERE Category = 1) AS Query;

В данном примере в качестве источника данных в секции FROM мы указали вложенный запрос, который возвращает идентификатор и наименование товаров из первой категории.

Пример 3 – Вложенный запрос в секции JOIN

В этом примере мы используем вложенный запрос в конструкции объединения JOIN, такие вложенные запросы также называют производными таблицами, так как в этом случае они возвращают табличные данные.

SELECT G.ProductName, C.CategoryName FROM Goods G INNER JOIN (SELECT CategoryId, CategoryName FROM Categories WHERE CategoryId = 1) AS C ON G.Category = C.CategoryId;

Здесь во вложенном запросе мы получаем идентификатор и наименование первой категории, а затем полученные табличные данные объединяем с таблицей Goods.

Пример 4 – Вложенный запрос в секции WHERE

Очень часто вложенные запросы используют в условии WHERE, при этом здесь стоит понимать, с каким именно оператором сравнения используется вложенный запрос, так как это важно.

Например, если использовать вложенный запрос с оператором равно (=), то он не может возвращать больше одного значения, т.е. если он вернет больше одного значения, выйдет ошибка, и SQL запрос не выполнится. Однако если использовать вложенный запрос с оператором IN (включая NOT IN) или EXISTS (включая NOT EXISTS), то вложенный запрос уже может возвращать список значений.

Вложенный запрос с оператором = (равно)

В этом запросе мы выводим все товары из таблицы Goods, у которых идентификатор категории равен значению, которое возвращает вложенный запрос, а он возвращает идентификатор категории с наименованием «Комплектующие ПК», таким образом, в нашем случае вложенный запрос возвращает только одно значение.

SELECT ProductId, ProductName FROM Goods G WHERE Category = (SELECT CategoryId FROM Categories WHERE CategoryName = 'Комплектующие ПК');

Вложенный запрос с оператором IN

Здесь мы используем для сравнения оператор IN, поэтому вложенный запрос в таком случае может уже возвращать несколько значений, для примера мы просто уберем условие WHERE во вложенном запросе.

SELECT ProductId, ProductName FROM Goods G WHERE Category IN (SELECT CategoryId FROM Categories);

Пример 5 – Множественная вложенность SQL запросов

Как я уже отмечал, вложенный запрос может быть вложен в другой вложенный SQL запрос, тем самым получается множественная вложенность.

В этом примере мы в качестве источника данных укажем вложенный SQL запрос, т.е. производную таблицу, который в свою очередь также будет содержать еще один вложенный запрос.

SELECT ProductId, ProductName FROM (SELECT ProductId, ProductName FROM Goods WHERE Category = (SELECT CategoryId FROM Categories WHERE CategoryName = 'Комплектующие ПК') ) AS Query;

Дополнительные примеры использования вложенных запросов, например, с использованием оператора EXISTS, можете посмотреть в статье – Логический оператор EXISTS в T-SQL – Описание и примеры.

На сегодня у меня все, надеюсь, материал был Вам полезен, пока!

Вложенные запросы против 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 выбирает две категории товаров:

1. У которых нет сведений о продажах ( s.year == NULL ).
2. Которые были проданы НЕ в 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 . Вложенные запросы лучше оставить для ситуаций, когда без их использования не обойтись.