Подзапросы ms sql: MS SQL Server и T-SQL

MS SQL Server и T-SQL

Последнее обновление: 20.07.2017

Подзапросы в SELECT

В выражении SELECT мы можем вводить подзапросы четырьмя способами:

  1. Использовать в условии в выражении WHERE

  2. Использовать в условии в выражении HAVING

  3. Использовать в качестве таблицы для выборки в выражении FROM

  4. Использовать в качестве спецификации столбца в выражении SELECT

Рассмотрим некоторые из этих случаев. Например, получим все товары, у которых цена выше средней:


SELECT *
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products)

Чтобы получить нужные товары, нам вначале надо выполнить подзапрос на получение средней цены товара: SELECT AVG(Price) FROM Products.

Или выберем всех покупателей из таблицы Customers, у которых нет заказов в таблице Orders:


SELECT * FROM CUSTOMERS
WHERE Id NOT IN (SELECT CustomerId FROM Orders)

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

Получение набора значений

При использовании в операторах сравнения подзапросы должны возвращать одно скалярное значение. Но иногда возникает необходимость получить
набор значений. Чтобы при использовании в операторах сравнения подзапрос мог возвращать набор значений, перед ним необходимо использовать один из операторов: ALL, SOME или
ANY.

При использовании ключевого слова ALL условие в операции сравнения должно быть верно для всех значений,
которые возвращаются подзапросом. Например, найдем все товары, цена которых меньше чем у любого товара фирмы Apple:


SELECT * FROM Products
WHERE Price < ALL(SELECT Price FROM Products WHERE Manufacturer='Apple')

Если бы мы в данном случае опустили бы ключевое слово ALL, то мы бы столкнулись с ошибкой.

Допустим, если подзапрос возвращает значения vl1, val2 и val3, то условие фильтрации фактически было бы аналогично
объединению этих значений через оператор AND:


WHERE Price < val1 AND Price < val2 AND Price < val3

В тоже время подобный запрос гораздо проще переписать другим образом:


SELECT * FROM Products
WHERE Price < (SELECT MIN(Price) FROM Products WHERE Manufacturer='Apple')

При применении ключевых слов ANY и SOME условие в операции сравнения должно быть истинным для хотя бы одного из значений, возвращаемых
подзапросом. По действию оба этих оператора аналогичны, поэтому можно применять любое из них.
Например, в следующем случае получим товары, которые стоят меньше самого дорого товара компании Apple:


SELECT * FROM Products
WHERE Price < ANY(SELECT Price FROM Products WHERE Manufacturer='Apple')

И также стоит отметить, что данный запрос можно сделать проще, переписав следующим образом:


SELECT * FROM Products
WHERE Price < (SELECT MAX(Price) FROM Products WHERE Manufacturer='Apple')
Подзапрос как спецификация столбца

Результат подзапроса может представлять отдельный столбец в выборке. Например, выберем все заказы и добавим к ним информацию о названии товара:


SELECT *, 
(SELECT ProductName FROM Products WHERE Id=Orders.ProductId) AS Product 
FROM Orders

Подзапросы в команде INSERT

В команде INSERT подзапросы могут применяться для определения значения, которое вставляется в один из столбцов:


INSERT INTO Orders (ProductId, CustomerId, CreatedAt, ProductCount, Price)
VALUES
( 
	(SELECT Id FROM Products WHERE ProductName='Galaxy S8'), 
	(SELECT Id FROM Customers WHERE FirstName='Tom'),
	'2017-07-11',  
	2, 
	(SELECT Price FROM Products WHERE ProductName='Galaxy S8')
)

Подзапросы в команде UPDATE

В команде UPDATE подзапросы могут применяться:

  1. В качестве устанавливаемого значения после оператора SET

  2. Как часть условия в выражении WHERE

Так, увеличим количество купленных товаров на 2 в тех заказах, где покупатель Тоm:


UPDATE Orders
SET ProductCount = ProductCount + 2
WHERE CustomerId=(SELECT Id FROM Customers WHERE FirstName='Tom')

Или установим для заказа цену товара, полученную в результате подзапроса:


UPDATE Orders
SET Price = (SELECT Price FROM Products WHERE Id=Orders. ProductId) + 2000
WHERE Id=1

Подзапросы в команде DELETE

В команде DELETE подзапросы также применяются как часть условия. Так, удалим все заказы на Galaxy S8, которые сделал Bob:


DELETE FROM Orders
WHERE ProductId=(SELECT Id FROM Products WHERE ProductName='Galaxy S8')
AND CustomerId=(SELECT Id FROM Customers WHERE FirstName='Bob')

НазадСодержаниеВперед

T-SQL — подзапросы — CoderLessons.com

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

Подзапросы могут использоваться с операторами SELECT, INSERT, UPDATE и DELETE вместе с такими операторами, как =, <,>,> =, <=, IN, BETWEEN и т. Д.

Есть несколько правил, которым должны следовать подзапросы:

  • Вы должны заключить подзапрос в скобки.

  • Подзапрос должен включать в себя предложение SELECT и предложение FROM.

  • Подзапрос может включать необязательные предложения WHERE, GROUP BY и HAVING.

  • Подзапрос не может включать предложения COMPUTE или FOR BROWSE.

  • Вы можете включить предложение ORDER BY, только если включено предложение TOP.

  • Вы можете вкладывать подзапросы до 32 уровней.

Вы должны заключить подзапрос в скобки.

Подзапрос должен включать в себя предложение SELECT и предложение FROM.

Подзапрос может включать необязательные предложения WHERE, GROUP BY и HAVING.

Подзапрос не может включать предложения COMPUTE или FOR BROWSE.

Вы можете включить предложение ORDER BY, только если включено предложение TOP.

Вы можете вкладывать подзапросы до 32 уровней.

Подзапросы с оператором SELECT

Синтаксис

Подзапросы чаще всего используются с оператором SELECT. Ниже приведен основной синтаксис.

SELECT column_name [, column_name ] 
FROM   table1 [, table2 ] 
WHERE  column_name OPERATOR 
   (SELECT column_name [, column_name ] 
   FROM table1 [, table2 ] 
   [WHERE])

пример

Рассмотрим таблицу CUSTOMERS, имеющую следующие записи.

ID  NAME       AGE       ADDRESS            SALARY 
1   Ramesh     32        Ahmedabad          2000.00 
2   Khilan     25        Delhi              1500.00 
3   kaushik    23        Kota               2000.00 
4   Chaitali   25        Mumbai             6500.00 
5   Hardik     27        Bhopal             8500.00 
6   Komal      22        MP                 4500.00 
7   Muffy      24        Indore             10000.00

Давайте применим следующий подзапрос с оператором SELECT.

SELECT *  
   FROM CUSTOMERS
   WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500)

Приведенная выше команда выдаст следующий вывод.

ID  NAME       AGE       ADDRESS          SALARY 
4   Chaitali   25        Mumbai           6500. 00 
5   Hardik     27        Bhopal           8500.00 
7   Muffy      24        Indore           10000.00

Подзапросы с оператором INSERT

Подзапросы также могут использоваться с операторами INSERT. Оператор INSERT использует данные, возвращенные из подзапроса, для вставки в другую таблицу. Выбранные данные в подзапросе могут быть изменены с помощью любой символьной, даты или числовой функции.

Синтаксис

Ниже приведен основной синтаксис.

INSERT INTO table_name [ (column1 [, column2 ]) ] 
   SELECT [ *|column1 [, column2 ] 
   FROM table1 [, table2 ] 
   [ WHERE VALUE OPERATOR ]

пример

Рассмотрим таблицу CUSTOMERS_BKP с такой же структурой, что и таблица CUSTOMERS. Ниже приведен синтаксис для копирования полной таблицы CUSTOMERS в CUSTOMERS_BKP.

INSERT INTO CUSTOMERS_BKP 
   SELECT * FROM CUSTOMERS  
   WHERE ID IN (SELECT ID FROM CUSTOMERS)

Подзапросы с оператором UPDATE

Подзапрос может использоваться вместе с оператором UPDATE. Можно использовать один или несколько столбцов в таблице при использовании подзапроса с оператором UPDATE.

Синтаксис

Ниже приведен основной синтаксис.

UPDATE table 
SET column_name = new_value 
[ WHERE OPERATOR [ VALUE ] 
   (SELECT COLUMN_NAME 
   FROM TABLE_NAME) 
   [ WHERE) ]

пример

Предположим, у нас есть таблица CUSTOMERS_BKP, которая является резервной копией таблицы CUSTOMERS.

Следующий пример команды обновляет SALARY в таблице CUSTOMERS в 0,25 раза для всех клиентов, чей возраст больше или равен 27.

UPDATE CUSTOMERS 
   SET SALARY = SALARY * 0.25 
   WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 )

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

ID  NAME       AGE       ADDRESS             SALARY 
1   Ramesh     32        Ahmedabad           500.00 
2   Khilan     25        Delhi               1500.00 
3   kaushik    23        Kota                2000. 00 
4   Chaitali   25        Mumbai              6500.00 
5   Hardik     27        Bhopal              2125.00 
6   Komal      22        MP                  4500.00 
7   Muffy      24        Indore              10000.00

Подзапросы с оператором DELETE

Подзапрос может использоваться вместе с оператором DELETE, как и любые другие операторы, упомянутые выше.

Синтаксис

Ниже приведен основной синтаксис.

DELETE FROM TABLE_NAME 
[ WHERE OPERATOR [ VALUE ] 
   (SELECT COLUMN_NAME 
   FROM TABLE_NAME) 
   [ WHERE) ]

пример

Предположим, у нас есть таблица CUSTOMERS_BKP, которая является резервной копией таблицы CUSTOMERS.

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

DELETE FROM CUSTOMERS 
   WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >=27 )

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

Как писать подзапросы в SQL

В этой статье кратко объясняется, как написать подзапрос на языке SQL, на примерах.

Введение

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

  1. Выберите предложение : Это предложение используется для указания метаданных набора результатов (столбцы, фиксированные значения, выражения).
  2. Из пункта : Этот пункт используется для указания запрашиваемых источников данных. Источником данных может быть одна таблица или представление, а также более сложные формы.
  3. Где пункт : Этот пункт используется для указания операций фильтрации данных, необходимых в SQL-запросе.

В следующих разделах объясняется, как написать подзапрос на SQL в предложениях SELECT, FROM и WHERE.

  • Примечание: Все примеры в этой статье сделаны с использованием базы данных Stack Overflow 2013 и SQL Server 2019

Запись подзапросов в предложении SELECT

Сначала мы объясним, как написать подзапрос на SQL в предложении SELECT. Даже если написание подзапроса
поддерживается в предложении SELECT, разработчики должны тщательно написать свой запрос, как только они решат его использовать, поскольку он
снижает производительность запроса.

Предположим, что нам нужно написать SQL-запрос для получения первых десяти пользователей в базе данных переполнения стека и
последний значок, полученный каждым пользователем. Рассмотрим следующий запрос:

SELECT TOP (10) [Id]

    ,[DisplayName]

    ,(SELECT TOP 1 [Name] FROM [dbo].[Значки] badges WHERE badges.UserId = users.Id Order By [Date] Desc) as Latest_Badge

FROM [StackOverflow2013]. [dbo].[Users] пользователи

Рисунок 1. Написание подзапроса в предложении SELECT

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

Рисунок 2 – План выполнения

Запись подзапросов в предложении FROM

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

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

ВЫБЕРИТЕ [Идентификатор]

    ,[DisplayName]

    ,(SELECT TOP 1 [Name] FROM [dbo]. [Значки] badges WHERE badges.UserId = users.Id ORDER BY [Date] DESC) as Latest_Badge

FROM (SELECT TOP 10 * ОТ [StackOverflow2013].[dbo].[Пользователи] ) пользователи

Вместо того, чтобы писать параметр TOP 10 в предложении SELECT, мы решили заставить оптимизатор запросов SQL Server
выполните операцию извлечения данных TOP 10 перед присоединением к таблице Users с данными Badges, как показано
ранее.

Рисунок 3. Запись подзапроса в предложении FROM

На скриншоте ниже вы можете заметить, как выполняется оператор TOP сразу после сканирования кластера Users.
index, тогда как в предыдущем разделе он выполнялся как последний шаг.

Рисунок 4 – План выполнения

  • Примечание : Это не означает, что второй подход лучше первого. Пример используется только для иллюстрации влияния перемещения оператора TOP в подзапрос

Написание подзапросов в JOINS

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

SELECT users.[Id]

    ,[DisplayName]

    ,latest_posts.[CreationDate]

ИЗ [StackOverflow2013].[dbo].[Пользователи] пользователи INNER JOIN

(ВЫБЕРИТЕ ТОП-10 [OwnerUserId],[CreationDate] FROM [dbo].[Posts] ORDER BY [CreationDate] DESC)

В приведенном выше примере мы использовали подзапрос для получения последних десяти сообщений и даты их создания. Затем мы присоединились
результат с таблицей пользователей, чтобы получить информацию о владельцах сообщений.

Рисунок 5. Использование подзапроса SQL в предложении FROM с соединениями

Написание подзапросов в предложении WHERE

Чтобы проиллюстрировать, как написать подзапрос в SQL в предложении WHERE, мы отредактируем предыдущий запрос, чтобы получить
пользователей, опубликовавших последние десять сообщений в базе данных переполнения стека. Давайте воспользуемся следующим запросом:

SELECT [Id]

    ,[DisplayName]

FROM [StackOverflow2013].[dbo].[Users] users

WHERE  [Id] IN (SELECT TOP 10 [OwnerUserId] FROM [dbo].[Posts] ORDER ПО [Дата Создания] DESC)

В этом запросе мы переместили подзапрос из предложения FROM в предложение WHERE и использовали оператор IN для
отфильтровать идентификатор пользователя на основе результата подзапроса.

Рисунок 6. Запись подзапроса SQL в предложении WHERE

Альтернативы

Существует множество альтернатив использования подзапросов в SQL:

  1. Использование представлений: в некоторых случаях представления могут заменять подзапросы, чтобы запрос выглядел проще. Этот параметр не влияет на производительность запросов и не улучшает ее, за исключением индексированных представлений. Вы можете узнать больше о представлениях в следующей статье: Изучение SQL: Представления SQL
  2. Использование общих табличных выражений (CTE): Общие табличные выражения являются альтернативой подзапросам. Вы можете узнать больше об этой функции в следующей статье: CTE в SQL Server; Запрос общих табличных выражений

Сводка

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

  • Автор
  • Последние сообщения

Хади Фадлаллах

Хади — профессионал SQL Server с более чем 10-летним опытом. Его основная специализация — интеграция данных. Он является одним из ведущих участников ETL и SQL Server Integration Services на Stackoverflow.com. Кроме того, он опубликовал несколько серий статей о Biml, функциях SSIS, поисковых системах, Hadoop и многих других технологиях.

Помимо работы с SQL Server, он работал с различными технологиями обработки данных, такими как базы данных NoSQL, Hadoop, Apache Spark. Он сертифицированный профессионал MongoDB, Neo4j и ArangoDB.

На академическом уровне Хади имеет две степени магистра в области компьютерных наук и бизнес-вычислений. В настоящее время он является доктором философии. кандидат наук о данных, специализирующийся на методах оценки качества больших данных.

Хади действительно любит узнавать что-то новое каждый день и делиться своими знаниями. Вы можете связаться с ним на его личном сайте.

Просмотреть все сообщения от Хади Фадлаллы

Последние сообщения от Хади Фадлаллы (посмотреть все)0003

У меня также сложилось впечатление, что JOIN всегда лучше, чем подзапрос в MySQL, но EXPLAIN — лучший способ вынести суждение. Вот пример, когда подзапросы работают лучше, чем JOIN.

Вот мой запрос с 3 подзапросами:

 EXPLAIN SELECT vrl.list_id,vrl.ontology_id,vrl.position,l.name КАК list_name, vrlih.position КАК предыдущая_позиция, vrl.moved_date
ОТ `vote-ranked-listory` vrl
INNER JOIN списки l ON l.list_id = vrl.list_id
ВНУТРЕННЕЕ СОЕДИНЕНИЕ `История элемента списка голосов`
ВНУТРЕННЕЕ СОЕДИНЕНИЕ list_burial_state lbs ON lbs.list_id = vrl.list_id AND lbs.burial_score < 0,5
ГДЕ vrl.position <= 15 И l.status='ACTIVE' И l.is_public=1 И vrl.ontology_id <1000000000
 И (ВЫБЕРИТЕ list_id ИЗ list_tag, ГДЕ list_id=l.list_id И tag_id=43) IS NULL
 И (ВЫБЕРИТЕ list_id ИЗ list_tag, ГДЕ list_id=l.list_id И tag_id=55) IS NULL
 И (ВЫБЕРИТЕ list_id ИЗ list_tag, ГДЕ list_id=l.list_id И tag_id=246403) НЕ НУЛЕВОЕ
ЗАКАЗАТЬ ПО vrl.moved_date DESC LIMIT 200;

EXPLAIN показывает:

 +-----+------+----------+----- ---+-------------------------------- -------+--------------+---------+--- --------------------------------+------+----------- --+
| идентификатор | тип_выбора | стол | тип | возможные_ключи | ключ | key_len | ссылка | строки | Экстра |
+----+------+-----------+--------+--- -------------------------------------------------- +--------------+-----------+---------- -------------------------+--------------------+--- ---------+
| 1 | ОСНОВНОЙ | врл | индекс | ОСНОВНОЙ | дата_перемещения | 8 | НУЛЕВОЙ | 200 | Использование где |
| 1 | ОСНОВНОЙ | л | eq_ref | ПЕРВИЧНЫЙ, статус, общедоступный, idx_lookup, is_public_status | ОСНОВНОЙ | 4 | ranker. vrl.list_id | 1 | Использование где |
| 1 | ОСНОВНОЙ | врлих | eq_ref | ОСНОВНОЙ | ОСНОВНОЙ | 9| ranker.vrl.list_id,ranker.vrl.ontology_id,const | 1 | Использование где |
| 1 | ОСНОВНОЙ | фунты | eq_ref | ОСНОВНОЙ,idx_list_burial_state,burial_score | ОСНОВНОЙ | 4 | ranker.vrl.list_id | 1 | Использование где |
| 4 | ЗАВИСИМЫЙ ПОДЗАПРОС | list_tag | ссылка | list_tag_key, list_id, tag_id | list_tag_key | 9 | ranker.l.list_id, константа | 1 | Использование где; Использование индекса |
| 3 | ЗАВИСИМЫЙ ПОДЗАПРОС | list_tag | ссылка | list_tag_key, list_id, tag_id | list_tag_key | 9| ranker.l.list_id, константа | 1 | Использование где; Использование индекса |
| 2 | ЗАВИСИМЫЙ ПОДЗАПРОС | list_tag | ссылка | list_tag_key, list_id, tag_id | list_tag_key | 9 | ranker.l.list_id, константа | 1 | Использование где; Использование индекса |
+----+------+-----------+--------+--- -------------------------------------------------- +--------------+-----------+---------- -------------------------+--------------------+--- ---------+

Тот же запрос с JOIN:

 EXPLAIN SELECT vrl. list_id,vrl.ontology_id,vrl.position,l.name КАК list_name, vrlih.position КАК предыдущая_позиция, vrl.moved_date
ОТ `vote-ranked-listory` vrl
INNER JOIN списки l ON l.list_id = vrl.list_id
ВНУТРЕННЕЕ СОЕДИНЕНИЕ `История элемента списка голосов`
ВНУТРЕННЕЕ СОЕДИНЕНИЕ list_burial_state lbs ON lbs.list_id = vrl.list_id AND lbs.burial_score < 0,5
LEFT JOIN list_tag lt1 ON lt1.list_id = vrl.list_id AND lt1.tag_id = 43
LEFT JOIN list_tag lt2 ON lt2.list_id = vrl.list_id AND lt2.tag_id = 55
ВНУТРЕННЕЕ СОЕДИНЕНИЕ list_tag lt3 ON lt3.list_id = vrl.list_id AND lt3.tag_id = 246403
ГДЕ vrl.position <= 15 И l.status='ACTIVE' И l.is_public=1 И vrl.ontology_id <1000000000
И lt1.list_id — NULL, И lt2.tag_id — NULL
ЗАКАЗАТЬ ПО vrl.moved_date DESC LIMIT 200;

и вывод:

 +----+-------------+-------+--------+---- --------------------------------------------------+ --------------+----------+----------- ------+-------+-------- ------------------------+
| идентификатор | тип_выбора | стол | тип | возможные_ключи | ключ | key_len | ссылка | строки | Экстра |
+----+-------------+-------+---------+------------- -------------------------------------------------------+--------- -----+---------+---------------------------------- -----------+-------+----------------- ---------------+
| 1 | ПРОСТО | л3 | ссылка | list_tag_key, list_id, tag_id | идентификатор_тега | 5 | константа | 2386 | Использование где; Использование временного; Использование сортировки файлов |
| 1 | ПРОСТО | л | eq_ref | ПЕРВИЧНЫЙ, статус, общедоступный, idx_lookup, is_public_status | ОСНОВНОЙ | 4 | ranker. lt3.list_id | 1 | Использование где |
| 1 | ПРОСТО | врлих | ссылка | ОСНОВНОЙ | ОСНОВНОЙ | 4 | ranker.lt3.list_id | 103 | Использование где |
| 1 | ПРОСТО | врл | ссылка | ОСНОВНОЙ | ОСНОВНОЙ | 8 | ranker.lt3.list_id,ranker.vrlih.ontology_id | 65 | Использование где |
| 1 | ПРОСТО | лт1 | ссылка | list_tag_key, list_id, tag_id | list_tag_key | 9| ranker.lt3.list_id,const | 1 | Использование где; Использование индекса; Не существует |
| 1 | ПРОСТО | фунты | eq_ref | ОСНОВНОЙ,idx_list_burial_state,burial_score | ОСНОВНОЙ | 4 | ranker.vrl.list_id | 1 | Использование где |
| 1 | ПРОСТО | л2 | ссылка | list_tag_key, list_id, tag_id | list_tag_key | 9 | ranker.lt3.list_id,const | 1 | Использование где; Использование индекса |
+----+-------------+-------+---------+------------- -------------------------------------------------------+--------- -----+---------+---------------------------------- -----------+-------+----------------- ---------------+

Сравнение столбца строк указывает на разницу, и запрос с JOIN использует Использование временного; Использование сортировки файлов .