Подзапросы sql примеры: Подзапросы в основных командах SQL

Табличные выражения SQL

Прием № 1, чтобы писать хорошие читаемые SQL-запросы — это табличные выражения (CTE). Люди их боятся, а зря. Давайте разберемся за три минуты, читать увесистую книгу по SQL или проходить курсы не придется.

Проблема

Допустим, у нас есть таблица продаж по месяцам за два года:

┌──────┬───────┬───────┬──────────┬─────────┐
│ year │ month │ price │ quantity │ revenue │
├──────┼───────┼───────┼──────────┼─────────┤
│ 2019 │ 1     │ 60    │ 200      │ 12000   │
│ 2019 │ 2     │ 60    │ 660      │ 39600   │
│ 2019 │ 3     │ 60    │ 400      │ 24000   │
│ 2019 │ 4     │ 60    │ 300      │ 18000   │
│ 2019 │ 5     │ 60    │ 440      │ 26400   │
│ 2019 │ 6     │ 60    │ 540      │ 32400   │
│ 2019 │ 7     │ 60    │ 440      │ 26400   │
│ 2019 │ 8     │ 60    │ 440      │ 26400   │
│ 2019 │ 9     │ 60    │ 250      │ 15000   │
│ 2019 │ 10    │ 60    │ 420      │ 25200   │
│ ...  │ ...   │ ...   │ ...      │ ...     │
└──────┴───────┴───────┴──────────┴─────────┘

песочница

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

Для начала посчитаем среднемесячную выручку по годам:

select
  year,
  avg(revenue) as avg_rev
from sales
group by year;

┌──────┬─────────┐
│ year │ avg_rev │
├──────┼─────────┤
│ 2019 │ 25125.0 │
│ 2020 │ 48625.0 │
└──────┴─────────┘

Теперь можно выбрать только те записи, revenue в которых не уступает avg_rev:

select
  sales.year,
  sales.month,
  sales.revenue,
  round(totals.avg_rev) as avg_rev
from sales
  join (
    select
      year,
      avg(revenue) as avg_rev
    from sales
    group by year
  ) as totals
  on sales.year = totals.year
where sales.revenue >= totals.avg_rev;

┌──────┬───────┬─────────┬─────────┐
│ year │ month │ revenue │ avg_rev │
├──────┼───────┼─────────┼─────────┤
│ 2019 │ 2     │ 39600   │ 25125.0 │
│ 2019 │ 5     │ 26400   │ 25125.0 │
│ 2019 │ 6     │ 32400   │ 25125.0 │
│ 2019 │ 7     │ 26400   │ 25125.0 │
│ ...  │ ...   │ ...     │ ...     │
└──────┴───────┴─────────┴─────────┘

Решили с помощью подзапроса:

• внутренний запрос считает среднемесячную выручку;
• внешний соединяется с ним и фильтрует результаты.

Запрос в целом получился сложноват. Если вернетесь к нему спустя месяц — наверняка потратите какое-то время на «распутывание». Проблема в том, что такие вложенные запросы приходится читать наоборот:

• найти самый внутренний запрос, осознать;
• мысленно присоединить к более внешнему;
• присоединить к следующему внешнему, и так далее.

Хорошо, когда вложенных уровня два, как в нашем примере. На практике же я часто встречаю трех- и четырехуровневые подзапросы. Форменное издевательство над читателем.

Решение

Вместо подзапроса можно использовать табличное выражение (common table expression, CTE). Любой подзапрос X:

 select a, b, c
from (X)
where e = f

Механически превращается в CTE:

with cte as (X)
select a, b, c
from cte
where e = f

В нашем примере:

with totals as (
  select
    year,
    avg(revenue) as avg_rev
  from sales
  group by year
)
select
  sales. year,
  sales.month,
  sales.revenue,
  round(totals.avg_rev) as avg_rev
from sales 
  join totals on totals.year = sales.year
where sales.revenue >= totals.avg_rev;

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

with totals as (...)
select ... from sales_ru join totals ...
union all
select ... from sales_us join totals ...

Табличные выражения SQL чем-то похожи на функции в обычном языке программирования — они уменьшают общую сложность:

• Можно написать нечитаемую простыню кода, а можно разбить код на понятные отдельные функции и составить программу из них.
• Можно возвести башню из пяти этажей подзапросов, а можно вынести подзапросы в CTE и составить общий запрос из них.

CTE против подзапроса

Существует миф, что «CTE медленные». Он пришел из старых версий PostgreSQL (11 и раньше), которые всегда

материализовали CTE — вычисляли полный результат табличного выражения и запоминали до конца запроса.

Обычно это хорошо: один раз вычислил результат, и дальше используешь его несколько раз по ходу основного запроса. Но иногда материализация мешала движку оптимизировать запрос:

with cte as (select * from foo)
select * from cte where id = 500000;

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

PostgreSQL 12+ и другие современные СУБД поумнели и больше так не делают. Материализация применяется, когда от нее больше пользы, чем вреда. Плюс, многие СУБД позволяют явно управлять этим поведением через инструкции

MATERIALIZED / NOT MATERIALIZED.

Так что CTE не медленнее подзапросов. А если сомневаетесь, всегда можно сделать два варианта — подзапрос и табличное выражение — и сравнить план и время выполнения.

Как понять, когда использовать подзапрос, а когда CTE? Я вывел для себя простое правило, которое пока ни разу не подвело:

Всегда использовать CTE

Чего и вам желаю.

Подписывайтесь на канал

, чтобы не пропустить новые заметки 🚀

Использование подзапросов в SQL | Info-Comp.ru

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

Как я уже сказал, о SQL мы разговариваем достаточно часто, так как это знание и умение использовать SQL требуется практически везде, будь то база данных сайта в Интернете или баз данных в организациях. Причем, даже совсем в небольших организациях, где всего один программист или системный администратор, но при этом имеется какая-нибудь база данных и для того, что ее администрировать, выгружать какие-то данные, для отчета, необходимы начальные знания SQL. Основы SQL мы рассматривали во многих статьях таких как «Язык запросов SQL – Оператор SELECT» или как «добавить колонку в таблицу на SQL».  Но, так или иначе, тему подзапросов мы не затрагивали, и пришло время поговорить об этом.

И начнем мы как всегда с теории.

Содержание

1. Что такое подзапрос?
2. В каких случаях использовать подзапрос?
3. Где можно использовать подзапрос?

Что такое подзапрос?

Подзапрос – это отдельный запрос внутри другого запроса, который может объединяться с основным, но не обязательно.

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

О том, что еще влияет на скорость выполнения запросов, и чего лучше не надо делать можете посмотреть полезные советы по написанию SQL запросов.

Для наглядности я попытался изобразить подзапрос схематично:

В каких случаях использовать подзапрос?

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

Например, когда выборка идет из одной таблице, которая имеет определенный ключ, а Вам необходимо получить одну колонку с максимальным  значением из другой таблицу по этому ключу, при этом, не объединяя эти таблицы (в данном случае подзапрос пишется в конструкции select). Или, например, Вам необходимо обращаться к данным, которые расположены во многих таблицах, при том, что данные, из этих таблиц будут браться путем каких-то вычислений и уже к этим данным Вам необходимо обращаться, это можно сделать путем написания запроса в конструкции from, без написания дополнительных VIEW представлений, допустим, что Вам этот запрос потребуется только один раз, и чтобы не засорять базу, Вы не будете создавать представление.

Где можно использовать подзапрос?

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

1. В конструкции Select. Пример:

select t1. col1, t1.col2, 
        (select max(t2.col3) from table2 as t2 where t2.col1=t1.col1) as col3
from table1 as t1

2. В конструкции From. Пример:

   
   SELECT col1, col2, col3
   FROM (SELECT t1.col1 AS col1, t2.col2 AS col2, t1.col1+t2.col2 AS col3
         FROM table1 as t1
         LEFT JOIN table2 as t2 ON t1.col3=t2.col3
         WHERE t1.col1 >1000) AS Q1

3.В конструкции WHERE. Пример:

   select col1, col2, col3 from table1 
   where col1 = (select avg(col1) from table2)

4. При объединении. Другими словами можно осуществить объединение таблицы с подзапросом.  Пример:

select t1.col1, t1.col2, t1.col3, q1.col1, q1.col2, q1.col3 
    from table1 as t1
left join (select col1, col2, col3 from table2) as q1 on t1.col1=q1.col1

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

select col1, col2, col3, col4, col5, col6
from (select t1.col1 as col1, t1.col2 as col2, t1.col3 as col3, 
       q1.col1 as col4, q1.col2 as col5, q1.col3 as col6 
from table1 as t1
left join (select col1, col2, 
              (select avg(col1) from table3) as col3 from table2) as q1 
                 on t1.col1=q1.col1
where t1.col1 >1000)

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

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

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

Пример подзапроса SQL Server

Автор: Rick Dobson   | Комментарии (2)   | Похожие: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | Подробнее > Временные таблицы

Проблема

Подзапрос — это оператор SELECT, встроенный в другой оператор SQL, например Оператор SELECT, INSERT, DELETE или UPDATE. Набор возвращаемых значений внутренним оператором SELECT передаются внешнему оператору SQL. Внутренний Оператор SELECT всегда заключается в круглые скобки. Набор результатов из внутренний оператор SELECT является примером хранилища временных данных. Подзапросы может находиться во многих разных местах во внешнем операторе SELECT. Этот раздел учебника знакомит с темой подзапросов, представляя простые для понимания примеры кода, демонстрирующие использование подзапросов в предложениях WHERE, списке SELECT элементы, а также с операторами INSERT, UPDATE и DELETE. Два последующих раздела детализировать более сложные вопросы, относящиеся к подзапросам.

Решение

Для приведенных ниже примеров мы используем База данных AdventureWorks2014. Загрузите копию и восстановите на свой экземпляр SQL Server для тестирования приведенных ниже сценариев.

Использование подзапроса в операторе SQL Server SELECT WHERE Пункт

Одним из наиболее распространенных мест для вызова подзапроса является предложение WHERE оператор SELECT. Внутренний запрос может исходить из того же источника или из другого источника. source в качестве внешнего оператора SQL. Когда необходимо вычислить внутренний запрос для каждой строки внешнего запроса внутренний запрос является коррелированным подзапросом. В противном случае внутренний запрос является некоррелированным подзапросом. Последующий учебник Раздел противопоставляет коррелированные и некоррелированные подзапросы.

Внутренний подзапрос в предложении WHERE может возвращать одну или несколько строк. Типичный использование сравнивает строки набора результатов подзапроса с результатом внешнего запроса SELECT набор. Результат сравнения определяет, какие строки возвращаются из внешний запрос с ограничением подзапроса. В этом совете основное внимание будет уделено основывая сравнения на равенстве (или неравенстве), а также на принадлежности к множеству с использованием оператор in. Однако существует множество видов операторов для сравнения. значения столбца внешнего запроса во внутренние возвращаемые значения запроса. Среди них: =, !=, >, >=, <, <= , в, не в, существует, не существует, любой, все.

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

• Подзапрос появляется в конце скрипта в круглых скобках. Этот очень простой пример возвращает значение ProductCategoryID из ProductCategory таблицу в производственной схеме. Предложение where в подзапросе Оператор SELECT определяет, какое значение ProductCategoryID возвращается из подзапрос.
• В приведенном ниже примере возвращенное значение ProductCategoryID из подзапроса равно 1, но вы можете изменить это значение, изменив предложение WHERE в подзапросе.
• Источником внешнего запроса является внутреннее соединение ProductCategory и Таблицы ProductSubcategory из схемы Production в Adventureworks 2014 база данных.
  • Две таблицы объединены значениями ProductCategoryID из двух таблиц.
  • Внешний запрос может возвращать три столбца данных: ProductCategoryID и Name из таблицы ProductCategory, а также Name из ProductSubcategory стол.
• Предложение WHERE во внешнем операторе SELECT сравнивает ProductCategoryID значения из внешнего оператора SELECT с возвращенным значением ProductCategoryID из подзапроса. Когда значение ProductCategoryID из внешнего запроса равно значению ProductCategoryID из подзапроса, то соответствующий строка из внешнего запроса становится строкой в ​​общем наборе результатов.

 -- подзапрос в предложении where, который возвращает 1 строку
ВЫБИРАТЬ
  cat.ProductCategoryID,
  кот.Имя кот_имя,
  subcat.Name subcat_name
ОТ [AdventureWorks2014].[Production]. [ProductCategory] cat
INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] subcategory
   ON cat.ProductCategoryID = subcat.ProductCategoryID
ГДЕ cat.ProductCategoryID =
   -- код в скобках - это подзапрос
   ( ВЫБЕРИТЕ cat.ProductCategoryID
     ОТ [AdventureWorks2014].[Production].[ProductCategory] cat
     ГДЕ cat.ProductCategoryID = 1
   ) 

На следующей вкладке «Результаты» показаны значения, возвращенные предыдущим скриптом. Обратите внимание, что возвращаются три строки. Все возвращенные строки имеют ProductCategoryID значение 1, которое соответствует значению ограничения подзапроса. Столбцы в набор результатов показывает элементы списка выбора во внешнем операторе SELECT с подзапросом ограничение из предложения WHERE.

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

• Первый запрос в следующем скрипте возвращает 37 строк; этот запрос является внешний запрос без ограничения подзапроса в предложении WHERE. Каждый ряд для отдельной строки ProductSubcategory. Столбец subcat_name обозначает значения столбца Name из таблицы ProductSubcategory.
• Следующий запрос включает ограничение подзапроса для внешнего запроса. Этот запрос возвращает всего 20 строк. Каждая из этих строк имеет ProductCategoryID. значение из таблицы ProductCategory 3 или 4.
• Ограничение подзапроса разрешает включение всех строк из внешнего запрос, где значение ProductCategoryID либо
  • больше или равно 3 и
  • меньше или равно 4
• Назначены минимальное и максимальное значения ограничения ProductCategoryID в операторе объявления для локальных переменных @cat_id_min и @cat_id_max.
• Ссылаясь на вывод первого подзапроса без ограничения подзапроса, вы можете убедиться, что есть восемь строк со значением ProductCategoryID, равным 3. и дополнительные двенадцать строк со значением ProductCategoryID 4 для общего из 20 строк ProductSubcategory со значениями ProductCategoryID 3 или более и меньше или равно 4,

 -- внешний запрос без ограничения подзапроса
-- запрос возвращает 37 строк
ВЫБИРАТЬ
  cat.ProductCategoryID cat_id,
  кот.Имя кот_имя,
  subcat.Name subcat_name
ОТ [AdventureWorks2014].[Production].[ProductCategory] cat
INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] subcategory
  ON cat.ProductCategoryID = subcat.ProductCategoryID

-- подзапрос в предложении where, который может возвращать более 1 строки
-- ограничение подзапроса приводит к тому, что внешний запрос возвращает 20 строк
-- со значениями ProductCategoryID 3 и 4
ЗАЯВИТЬ @cat_id_min int = 3,
        @cat_id_max целое = 4
ВЫБИРАТЬ
  cat.ProductCategoryID cat_id,
  кот.Имя кот_имя,
  subcat.Name subcat_name
ОТ [AdventureWorks2014].[Production].[ProductCategory] cat
INNER JOIN [AdventureWorks2014].[Production]. [ProductSubcategory] subcategory
   ON cat.ProductCategoryID = subcat.ProductCategoryID
ГДЕ cat.ProductCategoryID В
   -- код в скобках - это подзапрос
   ( ВЫБЕРИТЕ cat.ProductCategoryID
     ОТ [AdventureWorks2014].[Production].[ProductCategory] cat
     ГДЕ cat.ProductCategoryID >= @cat_id_min
       И cat.ProductCategoryID <= @cat_id_max
   ) 

Чтобы прояснить работу кода, два набора результатов из предыдущего сценарий отображается и описывается.

• Первая вкладка «Результат» показывает набор результатов из запроса без подзапроса ограничение.
  • Обратите внимание, что всего 37 строк.
  • Также обратите внимание, что есть
    • Восемь строк со значением ProductCategoryID 3
    • Двенадцать строк со значением ProductCategoryID 4
• На второй вкладке результатов показан набор результатов внешнего запроса с подзапросом. ограничение.
  • Для этого набора результатов всего 20 строк.
  • Первые восемь строк предназначены для тех, у кого значение ProductCategoryID равно 3.
  • Следующие двенадцать строк — это строки со значением ProductCategoryID, равным 4.

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

• В следующем блоке кода значение поля Name из ProductCategory таблица с помощью оператора any сравнивается с набором значений имени категории в подзапрос.
• Всякий раз, когда значение столбца Name из таблицы ProductCategory для соединения таблиц ProductCategory и ProductSubcategory соответствует любой категории значения имени, возвращенные подзапросом, затем значения поля SELECT для этого строка из соединения передается в набор результатов для внешнего запроса с подзапросом ограничение.
• Вы можете управлять набором результатов, добавляя и удаляя значения имени категории из подзапроса.
• Поскольку велосипеды и аксессуары перечислены в подзапросе, следующее блок кода возвращает все строки из объединенных ProductCategory и ProductSubcategory таблицы со значением столбца «Имя» «Велосипеды» или «Аксессуары» в столбце «Имя» таблицу ProductCategory.

 -- подзапрос в предложении where для подмножества элементов
-- продемонстрировать любое операторское приложение
ВЫБИРАТЬ
  cat.ProductCategoryID cat_id,
  кот.Имя кот_имя,
  subcat.Name subcat_name
ОТ [AdventureWorks2014].[Production].[ProductCategory] cat
INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] subcategory
   ON cat.ProductCategoryID = subcat.ProductCategoryID
ГДЕ cat.Name = ЛЮБОЙ
   -- код в скобках - это подзапрос
   ( ВЫБЕРИТЕ кат.Имя
     ОТ [AdventureWorks2014].[Production].[ProductCategory] cat
     ГДЕ кат.Имя В ('Велосипеды', 'Аксессуары')
   )
 

На следующей вкладке результатов отображается вкладка результатов предыдущего запроса.

• Все строки в результирующем наборе имеют значение cat_name либо Аксессуары, либо Велосипеды. Это вытекает из комбинации ограничения подзапроса и оператор any в предложении WHERE по завершении внешнего запроса.
• В результирующем наборе 15 строк:
  • Двенадцать со значением cat_name Аксессуары и
  • еще три со значением cat_name, равным Bikes

Использование подзапроса в качестве элемента списка SELECT

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

Первый тип запроса называется некоррелированным подзапросом. Это потому что значение, возвращаемое подзапросом, не зависит от других элементов списка выбора. Этот некоррелированный элемент списка подзапросов возвращает одно и то же значение для всех строк в набор результатов.

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

• Первые три столбца представляют собой значения полей, основанные на соединении ProductCategory и таблицы ProductSubcategory. Псевдоним для ProductCategory table — external_cat, а псевдоним для таблицы ProductSubcategory — subcat.
  • Первый столбец в результирующем наборе — это поле ProductCategoryID из таблица ProductCategory; этот столбец имеет псевдоним имени cat_id.
  • Второй столбец в результирующем наборе — это поле Name из ProductCategory. стол. Его псевдоним — external_cat_name.
  • Третий столбец в результирующем наборе — это поле Name из ProductSubcategory. стол. Его псевдоним — subcat_name.
• Четвертый столбец во внешнем операторе SELECT — это подзапрос, который возвращает одинаковое значение для всех строк. Подзапрос состоит из функции подсчета для значений ProductSubcategoryID в таблице ProductSubcategory. псевдоним для этого элемента списка SELECT — total_sub_cat_count.

 -- элемент списка выбора на основе некоррелированного подзапроса
ВЫБИРАТЬ
  external_cat.ProductCategoryID cat_id,
  external_cat.Name external_cat_name,
  subcat.Name subcat_name,
  ( ВЫБЕРИТЕ COUNT(ProductSubcategoryID) subcat_id_count
    ОТ [AdventureWorks2014].[Производство].[Категория_продукта] external_cat
    INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] subcategory
       ON external_cat.ProductCategoryID = subcat.ProductCategoryID
  ) total_sub_cat_count
ОТ [AdventureWorks2014].[Производство].[Категория_продукта] external_cat
INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] subcategory
   ON external_cat.ProductCategoryID = subcat.ProductCategoryID
 

На следующем изображении вкладки «Результат» показан набор результатов из предыдущего примера кода.

• Первые три столбца получены из объединения ProductCategory и таблицы ProductSubcategory.
• Четвертый столбец имеет одинаковое значение для всех строк. Это значение является количество значений ProductSubcategoryID в таблице ProductSubcategory. Значения для четвертого столбца не зависят ни от какого другого значения столбца в ряд.

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

• Новый подзапрос в приведенном ниже примере кода имеет псевдоним sub_cat_count.
• Подзапрос для этого элемента списка SELECT возвращает количество ProductSubcategoryID значения в объединении таблиц ProductCategory и ProductSubcategory сгруппированы по названию категории.
• Предложение HAVING в предложении GROUP BY для функции подсчета внутри подзапрос связывает столбец Name из таблицы ProductCategory для внутреннего запрос к значению столбца Name из таблицы ProductCategory во внешнем запросе (имея cat. name = external_cat.Name).
• Из-за установки предложения HAVING подзапрос является коррелированным подзапрос. Корреляция между значениями имени категории из подзапрос и значения имени категории из внешнего запроса.

 -- выберите элементы списка с некоррелированными и коррелированными подзапросами
ВЫБИРАТЬ
  external_cat.ProductCategoryID cat_id,
  external_cat.Name external_cat_name,
  subcat.Name subcat_name,
  ( ВЫБЕРИТЕ COUNT(ProductSubcategoryID) subcat_id_count
    ОТ [AdventureWorks2014].[Производство].[Категория_продукта] external_cat
    INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] subcategory
       ON external_cat.ProductCategoryID = subcat.ProductCategoryID
  ) total_sub_cat_count,
  ( ВЫБЕРИТЕ COUNT(ProductSubcategoryID) subcat_id_count
    ОТ [AdventureWorks2014].[Production].[ProductCategory] cat
    INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] subcategory
       ON cat.ProductCategoryID = subcat. ProductCategoryID
    СГРУППИРОВАТЬ ПО cat.name
    ИМЕЕТ cat.name = external_cat.Name
  ) sub_cat_count
ОТ [AdventureWorks2014].[Производство].[Категория_продукта] external_cat
INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] subcategory
   ON external_cat.ProductCategoryID = subcat.ProductCategoryID
 

На следующем снимке экрана показан набор результатов из предыдущего блока кода.

• Первые четыре столбца такие же, как в примере кода для некоррелированного подзапрос.
• Пятый столбец получает свои значения из коррелированного подзапроса с именем sub_cat_count. Следовательно, пятый столбец в результирующем наборе имеет имя sub_cat_count.
• Значения для пятого столбца зависят от имени категории (outer_cat_name) в внешний запрос. Напротив, все значения для четвертого столбца то же (37).
  • Значения, показанные в четвертом столбце, представляют собой общее количество ProductSubcategoryID значения в объединении таблиц ProductCategory и ProductSubcategory.
  • Значения, отображающие пятый столбец, являются количеством ProductSubcategoryID значения внутри каждого значения external_cat_name.

Использование подзапроса SQL Server для операторов INSERT, UPDATE и DELETE

До этого момента в учебнике демонстрировалось, как использовать подзапросы с ГДЕ предложение в операторе SELECT, а также в элементах списка выбора. Это заключение подраздел о введении подзапросов представляет примеры использования подзапросов в ВСТАВЛЯТЬ, Операторы UPDATE и DELETE.

Первая демонстрация предназначена для заполнения локальной временной таблицы массивом оператор вставки на основе оператора SELECT с подзапросом. Пример кода ниже начинается с создания новой копии локальной временной таблицы #bikes_subcategories. В таблице есть три столбца с именами ProductCategoryID, cat_name и subcat_name.

После создания локальной временной таблицы #bikes_subcategories код вызывает оператор массовой вставки на основе оператора SELECT с подзапросом. Подзапрос извлекает ProductCategoryID со значением 1 из таблицы ProductCategory в производственной схеме AdventureWorks2014. Значение ProductCategoryID 1 обозначает категорию велосипедов. Эта категория имеет три подкатегории с именами горных, шоссейных и туристических велосипедов.

Внешний запрос, ссылающийся на подзапрос, имеет источник, основанный на соединении Таблицы ProductCategory и ProductSubcategory в базе данных Adventureworks2014. Элементы списка выбора внешнего запроса включают ProductCategoryID, cat_name из имени в таблице ProductCategory и subcat_name из столбца Name в Таблица подкатегорий товаров. Предложение where во внешнем запросе извлекает строки из объединения, когда их значение ProductCategoryID равно значению, возвращаемому подзапрос, который равен 1 в примере ниже.

 -- создать новую версию таблиц #bikes_subcategories
НАЧАТЬ ПОПРОБУЙТЕ
  --drop table #МужчиныСтуденты --#bikes_subcategories
  УДАЛИТЬ ТАБЛИЦУ #bikes_subcategories
КОНЕЦ ПОПЫТКИ
НАЧАТЬ ЛОВИТЬ
  PRINT '#bikes_subcategories недоступны для сброса'
КОНЦЕВОЙ ЗАХВАТ
ИДТИ

СОЗДАТЬ ТАБЛИЦУ #bikes_subcategories (
  ProductCategoryID целое число,
  cat_name nvarchar(50),
  subcat_name nvarchar(50)
)

-- вставить во временную таблицу из
-- оператор select с подзапросом
ВСТАВИТЬ В #bikes_subcategories
-- подзапрос в предложении where, который возвращает 1 строку
ВЫБИРАТЬ
  cat. ProductCategoryID,
  кот.Имя кот_имя,
  subcat.Name subcat_name
ОТ [AdventureWorks2014].[Production].[ProductCategory] cat
INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] subcategory
   ON cat.ProductCategoryID = subcat.ProductCategoryID
ГДЕ cat.ProductCategoryID =
   -- код в скобках - это подзапрос
  ( ВЫБЕРИТЕ cat.ProductCategoryID
    ОТ [AdventureWorks2014].[Production].[ProductCategory] cat
    ГДЕ cat.ProductCategoryID = 1
  )

-- отображать строки, вставленные в #bikes_subcategories
ВЫБЕРИТЕ * ИЗ #bikes_subcategories
 

На следующем снимке экрана показаны три строки, возвращенные оператором SELECT для все столбцы в локальной временной таблице #bikes_subcategories.

• Все три строки на вкладке «Результаты» ниже имеют значение ProductCategoryID из 1; это значение, возвращенное подзапросом в предыдущем скрипте.
• Значение ProductCategoryID, равное 1, указывает на категорию «Велосипеды». Поэтому значение столбца cat_name равно Bikes для всех строк в таблице.
• В столбце subcat_name отображаются названия подкатегорий для категории «Велосипеды». Названия подкатегорий: горные велосипеды, шоссейные велосипеды и туристические велосипеды.

В следующем примере кода показано, как использовать подзапрос с оператором UPDATE. Оператор UPDATE изменяет имя значения подкатегории для строки в таблице. В следующем примере кода значение Racing Bikes присваивается столбцу subcat_name. в локальной временной таблице #bikes_subcategories. Ключевое слово UPDATE указывает на временную таблицу, а оператор set идентифицирует столбец, которому следует назначить Racing Велосипеды. Ключевое слово where принимает фильтр на основе подзапроса; этот filter идентифицирует строки, в которых нужно выполнить обновление.

 -- изменить subcat_name «Шоссейные велосипеды» на «Гоночные велосипеды».
-- subcat_name из #bikes_subcategories
ОБНОВЛЕНИЕ #bikes_subcategories
SET subcat_name = 'Гоночные велосипеды'
ГДЕ subcat_name IN ( ВЫБЕРИТЕ Имя subcat_name
                       ИЗ [AdventureWorks2014]. [Производство].[Подкатегория продукта]
                       ГДЕ [IDКатегорииТоваров] = 1
                         И Название = «Шоссейные велосипеды»
                     )

-- отображать #bikes_subcategories после обновления
ВЫБЕРИТЕ * ИЗ #bikes_subcategories
 

На следующей вкладке «Результаты» показаны три строки из локальной временной таблицы после заявление об обновлении. Сравнивая эту вкладку «Результат» с предыдущим результатом на вкладке вы можете проверить работу оператора UPDATE на основе подзапроса. Обратите внимание, что вторая строка имеет значение subcat_name для Racing Bikes в следующем Вкладка «Результат», но значение subcat_name для второй строки — «Шоссейные велосипеды».

Последний пример для этого вводного раздела руководства по подзапросам демонстрирует использование подзапроса в сочетании с оператором DELETE. Пример кода ниже удаляет строку из локальной временной таблицы #bikes_subcategories. Подзапрос обозначает строку для удаления, а ключевое слово DELETE указывает на временную таблицу из какой удалить строку. Подзапрос указывает строку с subcat_name стоимость гоночных велосипедов. Оператор DELETE удаляет одну строку в #bikes_subcategories. локальная временная таблица со значением Racing Bikes.

 -- удалить subcat_name "Шоссейные велосипеды" из #bikes_subcategories
УДАЛИТЬ #bikes_subcategories
ГДЕ subcat_name IN ( SELECT subcat_name
                       ОТ #bikes_subcategories
                       ГДЕ [IDКатегорииТоваров] = 1
                         AND subcat_name = 'Гоночные велосипеды'
                     )

-- отображать оставшиеся строки #bikes_subcategories
ВЫБЕРИТЕ * ИЗ #bikes_subcategories
 

Вот вкладка «Результаты», которая показывает строки в локальной подкатегории #bikes_subcategories. временную таблицу после операции оператора удаления. Потому что одна строка удалены, теперь в таблице две строки вместо трех. Уведомление это строка со значением subcat_name для Racing Bikes, которая удаляется из временная таблица.

Следующие шаги

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

• Введение в подзапросы в SQL Server
• Подзапрос SQL Server
• Подзапросы SQL
• SQL — подзапросы
• Использование подзапросов в операторе Select
• Вставка записей с помощью подзапросов
• Обновление SQL с использованием подзапросов
• SQL Удаление записей с помощью подзапросов

Об авторе

Рик Добсон — писатель и индивидуальный трейдер. Он также является профессионалом в области SQL Server с многолетним опытом работы с T-SQL, в том числе написанием книг, проведением национальных семинаров, работой с предприятиями над проектами в области финансов и здравоохранения, а также постоянным вкладчиком в MSSQLTips.com. Он оттачивал свои навыки работы с Python в течение более чем пятидесяти лет, особенно для визуализации данных и задач ETL с файлами JSON и CSV. Его последние профессиональные увлечения включают данные и анализ финансовых временных рядов, модели искусственного интеллекта и статистику. Он считает, что правильное применение этих навыков может помочь трейдерам и инвесторам принимать более прибыльные решения.

Посмотреть все мои советы

Как писать подзапросы в 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 в качестве источника данных, отметив, что требуется присвоение псевдонима. Попробуем написать предыдущий запрос по-другому:

SELECT [Id]     ,[DisplayName]     ,(SELECT TOP 1 [Name] FROM [dbo]. [Значки] badges WHERE badges.UserId = users.Id ORDER BY [Date] DESC) as Latest_Badge 9000 3 FROM (SELECT TOP 10 * FROM [StackOverflow2013].[dbo].[Users] ) пользователи

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

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

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

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

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

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

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

ВЫБЕРИТЕ пользователей.[Id]     ,[DisplayName]     ,latest_posts.[CreationDate] ИЗ [StackOverflow2013].[dbo].[Пользователи] пользователи ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБЕРИТЕ ТОП-10 [OwnerUserId],[CreationDate] FROM [dbo].[Сообщения] 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 ] ЗАКАЗАТЬ ПО [Дата Создания] DESC)

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

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

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

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

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

Сводка

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

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

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

Хади — профессионал SQL Server с более чем 10-летним опытом. Его основная специализация — интеграция данных.