Вложенный запрос может использоваться: Разбор тестирования 1С:Профессионал и PMP: Конструктор запросов. Источники данных

Содержание

1С вложенный запрос пример — Сборка-Доработка

Зачастую нужно заранее сделать выборку из базы данных по некоему условию. Для этого в 1С 8.3 необходимо использовать вложенные запросы.

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

Пример вложенного запроса на языке запросов

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

ВЫБРАТЬ
НераспОплатыОстатки.Заказчик,
НераспОплатыОстатки.СуммаОст
ИЗ

(ВЫБРАТЬ
Заказчики.Ссылка КАК СсылкаНаСпрЗаказчики
ИЗ
Справочник.Заказчики КАК Заказчики
ГДЕ
Заказчики.Ссылка В(&Заказчики)) КАК ВложЗапрос

Получите 267 видеоуроков по 1С бесплатно:

ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления. НераспОплаты.Остатки КАК НераспОплаты
ПО ВложЗапрос.СсылкаНаСпрЗаказчики = НераспОплатыОстатки.Заказчик

Когда СУБД будет выполнять такой запрос, не исключены неверные действия оптимизатора, так как трудно определиться с планом обработки запроса. Когда СУБД соединяет две таблицы, оптимизатор строит алгоритм на основе вычисления количества записей в этих таблицах.

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

Как лучше?

Именно поэтому фирма 1С крайне не рекомендует использовать вложенные запросы, а вместо них разработала временные таблицы. С использованием временных таблиц наш предыдущий запрос будет выглядеть так:

// Временная таблица
ВЫБРАТЬ
Заказчики.Ссылка КАК Заказчики
ПОМЕСТИТЬ табЗаказчики
ИЗ
Справочник.Заказчики КАК Заказчики
ГДЕ Заказчики.Ссылка В (&Заказчики)
;

// Основной запрос
ВЫБРАТЬ
табКлиенты. Ссылка,
НераспОплатыОстатки.СуммаОст,

ИЗ
табЗаказчики КАК табЗаказчики
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.НераспОплаты.Остатки(
,
Заказчик В
(ВЫБРАТЬ
табЗаказчики.Заказчики
ИЗ
табЗаказчики)) КАК НераспОплатыОстатки
ПО табЗаказчики.Заказчики = НераспОплатыОстатки.Заказчики

Смотрите также видео-урок про вложенные запросы:

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

Если Вы начинаете изучать 1С программирование, рекомендуем наш бесплатный курс (не забудьте подписаться на YouTube — регулярно выходят новые видео):

К сожалению, мы физически не можем проконсультировать бесплатно всех желающих, но наша команда будет рада оказать услуги по внедрению и обслуживанию 1С. Более подробно о наших услугах можно узнать на странице Услуги 1С или просто позвоните по телефону +7 (499) 350 29 00. Мы работаем в Москве и области.

Вложенный запрос – это запрос, который вложен в другой запрос. Синтаксически выглядит, как запрос, заключенный в круглые скобки. Ему присваивается псевдоним. Как правило, используется для получения отборов, группировок и агрегатных функций с последующим соединением в запросе верхнего уровня, также в операторах «В», «ИЗ», «ГДЕ» и других.

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

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

Еще пример вложенного запроса с использованием оператора-условия «В» в свойствах виртуальной таблицы регистра накопления. Вложенный запрос здесь используется как отбор данных, который будет использован в запросе верхнего уровня.

Фирма 1С не рекомендует использовать вложенные запросы без особой потребности и предлагает заменять их временными таблицами или соединениями таблиц, замечая при этом, что результат такого изменения может быть другим. Такая рекомендация объясняется тем, что при использовании вложенных запросов оптимизатор СУБД не всегда может правильно определить размер выборки вложенного запроса и построить оптимальный план обращений к физическим таблицам базы данных, что сильно (иногда в десятки раз) может замедлить выполнение запроса.

Главное отличие вложенного запроса от временной таблицы – это то, что временную таблицу можно использовать многократно в пакетном запросе, также передавать через менеджер временных таблиц в другие запросы, а вложенный запрос нужно вызывать каждый раз, когда он потребуется и в каждом случае явно указывать текст запроса, что затрудняет читаемость синтаксических конструкций. Также применение временных таблиц дает независимость от вида применяемого СУБД.

Тем не менее, вложенные запросы по-прежнему востребованы и используются в случаях:

При работе в 1С:Предприятие с версиями платформы 1С 8.3.7 и ниже и при использовании обратной совместимости с такими платформами по-прежнему невозможно использовать временные таблицы в динамических списках;
Во вложенных динамических списках эффективность применения временных таблиц может быть сильно снижена из-за того, что таблицы требуется создавать при каждом вызове (и поэтому данные не кэшируются). В противном случае данные сохраненной таблицы могут оказаться уже не актуальными;
Вложенные запросы нужно применять для заведомо небольших выборок, где временные таблицы менее эффективны, так как занимают оперативную память, а при использовании индексирования на больших выборках могут выгружать индексные файлы на носитель, что критически скажется на скорости получения данных из запроса.

Нужно заметить, что еще существуют неявные вложенные запросы. Это:

Запросы с использованием вложенных соединений. Конструкция вида:

Пример в 1С УТ (ред. 11.2.3.300):

Запросы с использованием виртуальных таблиц регистров сведений СрезПервых(), СрезПоследних() и регистров накопления Остатки(). Указанные функции могут использовать данные из одной или нескольких физических таблиц базы данных, а их результат транслирован оптимизатором в подзапрос (но не обязательно). Поэтому при использовании соединений с такой виртуальной таблицей нужно обязательно замерять производительность запроса.

Средства СУБД предоставляют механизмы для оптимизации запросов и повышения производительности. Это визуализация построения плана выполнения запроса, проведение трассировки запросов для сбора статистики и выявления узких мест, изучение кода итогового запроса на SQL. Эти механизмы помогут определить, что и как лучше использовать в конкретном случае, – подзапрос, временную таблицу или соединение.

рубрики: Запросы | Дата: 23 Январь, 2017

Рассмотрим, что такое

вложенные запросы в языке запросов 1С и для чего они нужны.

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

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

Структура самого примитивного вложенного запроса выглядит следующим образом

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

А теперь рассмотрим все вышесказанное на примере.
Пусть у нас есть таблица СотрудникиПодразделений:

Подразделение	Сотрудник
Бухгалтерия	Иванов
Администрация	Петров
Бухгалтерия	Сидоров

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

Первым шагом нам необходимо подсчитать количество сотрудников в каждом подразделении. Для этого сгруппируем исходную таблицу с использованием агрегатной функции КОЛИЧЕСТВО.

Если выполнить этот запрос, то в результате получим следующую таблицу

Подразделение	КоличествоСотрудников
Бухгалтерия	2
Администрация	1

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

Таким образом результат итогового запроса будет следующим

Подразделение
Бухгалтерия

Справедливости ради стоит отметить, что тот же результат можно достигнуть с помощью функции ИМЕЮЩИЕ языка запросов 1С, а также с использованием временных таблиц.
На практике вы конечно же столкнетесь с более сложными вложенными запросами в которых может использоваться как соединение, так и объединение таблиц. А также может быть несколько уровней вложенности.

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

Запросы 1С:Предприятие 8. Вложенные запросы

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

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

Большинство представленных запросов не имеют какой-либо ценности и могли бы быть выполнены проще. Они приведены только для иллюстрации механизма вложенных запросов.

Вложенные запросы могут использоваться в конструкции ИЗ:

В том числе в соединениях:

И в условиях запроса со сравнением В или В ИЕРАРХИИ:

При этом количество выбираемых полей вложенного запроса должно соответствовать количеству операндов в левой части выражения В или В ИЕРАРХИИ.

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

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

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

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

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

Запросы 1С:Предприятие 8. Вложенные запросы : 1 комментарий

При соединении подзапроса с другой таблицей на уровне СУБД всегда будет использоваться способ соединения nested loops, т.к для подзапроса нет статистики. Хорошо это или плохо — зависит от самого запроса и количества выбираемых данных. Именно поэтому при выполнении запроса, содержащего подзапрос, может проявляться замедление.

Вложенные запросы в 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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Информационные технологии. 10 класс (Базовый уровень)

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

Запрос — объект БД, который используется для реализации эффективного поиска и обработки данных.

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

Самый распространенный вид запросов — запрос на выборку. Он предназначен для отбора данных из источника в соответствии с некоторым условием. Условие запроса — это выражение, которое СУБД сравнивает со значениями в полях запроса, чтобы определить, следует ли включать в результат записи, содержащие то или иное значение.

Запрос на выборку позволяет:

1. Просматривать значения только из полей, которые вас интересуют.
2. Просматривать записи, которые отвечают указанным вами условиям.
3. Использовать выражения в качестве полей.

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

Основные режимы работы с запросами в Access:

1. Режим таблицы. Отображает информацию запроса на выборку в режиме таблицы.

2. Конструктор. В этом режиме определяется структура запроса и условия выбора данных (см. Приложение к главе 1).

Создать запрос можно с помощью Мастера запросов либо в Конструкторе (пример 5. 2).

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

Основные этапы создания запроса на выборку:

1. Выбор инструмента создания запроса.
2. Определение вида запроса.
3. Выбор источника(ов) данных.
4. Добавление из источника(ов) данных полей, которые должен содержать запрос.
5. Определение условий, которые формируют набор записей в запросе.
6. Добавление группировки, сортировки и вычислений (может отсутствовать).

Действие простых запросов на выборку ограничивается отбором данных по некоторым условиям без их обработки.

Примеры записи условий в запросах:

Действие в запросе

Поля с числовым типом данных

Выбираются записи, у которых значение в этом поле больше 0 и меньше 8.

Выбираются записи, у которых значение в этом поле не равно 0.

Поля с текстовым типом данных

Если значение в поле записи равно Орша, то запись включается в результат запроса.

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

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

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

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

В режиме конструктора процесс создания запроса находится под вашим контролем, однако здесь есть вероятность допустить ошибку и необходимо больше времени, чем в мастере (пример 5.4).

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

Все запросы, которые рассмотрены в примерах 5.3 и 5.4 , содержат конкретные значения названий, имен, времени и т. д. Если требуется повторить такой запрос с другими значениями в условиях отбора, его нужно будет открыть в конструкторе, изменить условие и выполнить. Чтобы не делать многократно этих операций, можно создать запрос с параметрами. При выполнении такого запроса выдается диалоговое окно Введите значение параметра, в котором пользователь может ввести конкретное значение, а затем получить нужный результат. Параметр запроса определяется в строке Условие отбора конструктора для столбца, содержащего запрашиваемые значения. Параметром является слово или фраза, заключенные в квадратные скобки. Параметр будет выдаваться в виде приглашения в диалоговом окне при выполнении запроса (пример 5.5).

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

В запросе итоговых значений в вычисляемом поле используют статистические функции. Задать статистическую функцию для вычисления итоговых значений по каждому из полей запроса можно путем выбора ее из раскрывающегося списка, который появляется после установки курсора в строке Групповая операция (пример 5.6).

Наряду с запросами на выборку часто применяются запросы на действие. С помощью таких запросов можно обновлять значения полей записей, добавлять новые или удалять уже существующие записи. В СУБД Access такие запросы можно создать в режиме конструктора, воспользовавшись инструментами группы Тип запроса:

Пример 5. 1. Режимы работы с запросами.

Режим SQL позволяет создавать и просматривать запросы с помощью инструкций языка SQL.

SQL (англ. structured query language — язык структурированных запросов). Применяется для создания, редактирования и управления данными в реляционной базе данных.

Пример 5.2. Группа инструментов Запросы вкладки Создание.

Пример 5.3. Создание запроса на выборку с помощью Мастера запросов.

1. Выбрать инструмент .

2. Выбрать вид запроса.

3. Выбрать источник данных.

4. Задать поле, содержащее повторяющееся значение.

5. Выбрать поля для отображения вместе с повторяющимися значениями.

6. Просмотреть и/или сохранить запрос.

Пример 5.4. Создание простых запросов на выборку с помощью Конструктора запросов.

1. Выбрать инструмент

2. Выбрать источник данных.

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

4. Записать условие формирования набора записей в запросе.

4.1. Выбор по полю с текстовым типом данных.

4.2. Выбор по полю с числовым типом данных.

4.3. Использование составного условия.

5. Сохранить запросы.

Пример 5.5. Создание запроса с параметрами.

1. Открыть один из запросов, созданных в примере 5.4 в конструкторе.

2. Изменить условия отбора на:

3. Сохранить с новым именем и открыть в режиме таблицы.

4. В диалоговом окне набрать одно из названий кинотеатра.

5. Просмотреть запрос.

Пример 5.6. Создание итогового запроса.

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

1. Источник данных — таблица «Учащиеся».

2. Создать запрос с помощью конструктора (добавить в запрос только поле «Пол»).

3. Сгруппировать данные по полю «Пол» (нажать кнопку в группе Показать или скрыть).

4. Добавить вычисляемое поле (в строке нового поля Групповая операция в списке выбрать функцию Count).

MySQL. Вложеные запросы. JOIN LEFT/RIGHT….

В SQL подзапросы — или внутренние запросы, или вложенные запросы — это запрос внутри другого запроса SQL, который вложен в условие WHERE.

Вложеные запросы

SQL подзапрос — это запрос, вложенный в другой запрос.

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

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

Подзапросы должны быть заключены в круглые скобки.
Подзапрос может иметь только один столбец в условии SELECT, если только несколько столбцов не указаны в основном запросе для подзапроса для сравнения выбранных столбцов.
Подзапросы, которые возвращают более одной строки, могут использоваться только с несколькими операторами значений, такими как оператор IN.
Команда ORDER BY не может использоваться в подзапросе, хотя в основном запросе она использоваться может. В подзапросе может использоваться команда GROUP BY для выполнения той же функции, что и ORDER BY.
С подзапросом не может использоваться оператор BETWEEN. Однако оператор BETWEEN может использоваться внутри подзапроса.
Не рекомендуется создавать запросы со степенью вложения больше трех. Это приводит к увеличению времени выполнения и к сложности восприятия кода.

Синтаксис

SELECT

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


					
    SELECT имя_столбца FROM имя_таблицы WHERE часть условия IN
        (SELECT имя_столбца FROM имя_таблицы WHERE часть условия IN
            (SELECT имя_столбца FROM имя_таблицы WHERE условие)
                . ..
		)
		;

Ниже представлена струтура таблицы для демонстрации примеров

Пример таблицы продавцов `SALES`
snum	sname	city	comm
1	Колованов	Москва	10
2	Петров	Тверь	25
3	Плотников	Москва	22
4	Кучеров	Санкт-Петербург	28
5	Малкин	Санкт-Петербург	18
6	Шипачев	Челябинск	30
7	Мозякин	Одинцово	25
8	Проворов	Москва	25

Пример таблицы покупателей `CUSTOMERS`
cnum	cname	city	rating	snum
1	Деснов	Москва	90	6
2	Краснов	Москва	95	7
3	Кириллов	Тверь	96	3
4	Ермолаев	Обнинск	98	3
5	Колесников	Серпухов	98	5
6	Пушкин	Челябинск	90	4
7	Белый	Одинцово	85	1
8	Чудинов	Москва	89	3
9	Проворов	Москва	95	2
10	Лосев	Одинцово	75	8

Пример таблицы заказов `ORDERS`
onum	amt	odate(YEAR)	cnum	snum
1001	420	2013	9	4
1002	653	2005	10	7
1003	960	2016	2	1
1004	320	2016	3	3
1005	200	2015	5	4
1006	2560	2014	5	4
1007	1200	2013	7	1
1008	50	2017	1	3
1009	564	2012	3	7
1010	900	2018	6	8

Вывести суммы заказов и даты, которые проводил продавец с фамилией «Плотников».

Начнем с такого примера и для начала вспомним, как бы делали этот запрос ранее: посмотрели бы в таблицу SALES(или выполнили отдельный запрос), определили бы snum продавца «Плотников» — он равен 3. И выполнили бы запрос SQL с помощью условия WHERE.


					
    SELECT amt, odate
    FROM orders 
    WHERE snum = 3

Результат работы
amt	odate
320	2016
50	2017

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


					
    SELECT amt, odate
    FROM orders
    WHERE snum = (  SELECT snum
                    FROM sales
                    WHERE sname = 'Плотников')

В этом примере мы определяем с помощью вложенного запроса идентификатор snum по фамилии из таблицы SALES, а затем, в таблице ORDERS определяем по этому идентификатору нужные нам значения.

Показать уникальные номера и фамилии продавцов, которые провели сделки в 2016 году.


					
    SELECT snum, sname
    FROM sales
    WHERE snum IN ( SELECT snum
                    FROM orders
                    WHERE odate = 2016)

Этот SQL запрос отличается тем, что вместо знака = здесь используется оператор IN.

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

То есть в запросе происходит проверка, содержится ли идентификатор snum из таблицы SALES в массиве значений, который вернул вложенный запрос. Если содержится, то SQL выдаст фамилию этого продавца.

Результат запроса
snum	sname
1	Колованов
3	Плотников

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

Вывести пары покупателей и продавцов, которые осуществили сделку между собой, но не позднее 2014 года

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


					
    SELECT cname as 'Покупатель', sname as 'Продавец'
    FROM customers cus, sales sal
    WHERE (cus.cnum, sal.snum) IN ( SELECT cnum, snum
                                    FROM orders
                                    WHERE odate < 2014 )

Список пар покупатель — продавец
Покупатель	Продавец
Проворов	Кучеров
Лосев	Мозякин
Белый	Колованов
Кириллов	Мозякин

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

На самом деле, такой запрос SQL используется крайне редко, обычно используют оператор INNER JOIN.

Оператор as нужен для того, чтобы при выводе SQL показывал не имена полей, а то, что мы зададим. И после оператора FROM за именами таблиц стоят сокращения, которые потом используются — это псевдонимы. Псевдонимы можно называть любыми именами, в этом запросе они используются для явного определения поля, так как мы несколько раз обращаемся к одному и тому же полю, только из разных таблиц.

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

Далее будут показы примеры использования вложеных запросов с использованием базы данных world. БД находится в папке _lec\7\db вместе с лекцией.

CREATE

Данный пример несовсем относится к теме занятия, но по жизни он очень может пригодиться.

Задача — создать копию существующей таблицы.

Копия существующей таблицы может быть создана с помощью комбинации CREATE TABLE и SELECT.

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


					
    CREATE TABLE NEW_TABLE_NAME AS
                                    SELECT [ column1, column2. ..columnN ]
                                    FROM EXISTING_TABLE_NAME
                                    [ WHERE ]

Создадим копию таблицы city. Вопрос — почему 1=0?


					
    CREATE TABLE city_bkp AS
                            SELECT *
                            FROM city
                            WHERE 1=0

INSERT

Задача — создать копию существующей таблицы.

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

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

Копирование всей таблицы полностью


					
    INSERT INTO city_bkp SELECT * FROM city

Копируем города которые находся в стране с численостью не меньше 500тыс. человек, но не больше 1 миллиона.


					
    INSERT INTO city_bkp 
                SELECT * FROM city 
                WHERE CountryCode IN 
                                (SELECT Code FROM country 
                                 WHERE Population < 1000000 AND Population > 500000)

UPDATE

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

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

Исходя из того, что у нас есть таблица CITY_BKP, которая является резервной копией таблицы CITY, в следующем примере для всех записей, для которых Population больше или равно 100000, применяет коэффициент 0,25.


					
    UPDATE city_bkp SET Population = Population * 0.25 
    WHERE Population IN (
                         SELECT Population FROM city 
                         WHERE Population >= 100000 )

DELETE

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

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

Далее будут показы примеры использования вложеных запросов с использованием базы данных world. БД находится в папке _lec\7\db вместе с лекцией.

Внутреннее объединение

Вывести идентификатор и название города, а так же страну нахождения

Для этого проще всего обратиться к таблице CITY


				
    SELECT ID, Name, CountryCode FROM city

Но, что если нам необходимо, чтобы в ответе на запрос был не код страны, а её название? Вложенные запросы нам не помогут. А нам надо получить данные из двух таблиц и объединить их в одну. Запросы, которые позволяют это сделать, в SQL называются объединениями. Синтаксис самого простого объединения следующий:


				
    SELECT city.ID, city.Name, country.Name FROM city, country

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

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


				
    SELECT city.ID, city.Name, country.Name 
    FROM city, country 
    WHERE city.CountryCode = country.Code

Т.е. мы в запросе сделали следующее условие: если в обеих таблицах есть одинаковые идентификаторы, то строки с этим идентификатором необходимо объединить в одну результирующую строку.

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

JOIN LEFT/RIGHT

JOIN — оператор языка SQL, который является реализацией операции соединения реляционной алгебры. Входит в предложение FROM операторов SELECT, UPDATE и DELETE.

JOIN используется для объединения строк из двух или более таблиц на основе соответствующего столбца между ними.

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

Особенности операции соединения

в схему таблицы-результата входят столбцы обеих исходных таблиц
каждая строка таблицы-результата является «сцеплением» строки из одной таблицы со строкой второй таблицы

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

Ниже представлена струтура таблицы для демонстрации примеров

Таблица персонала `Person`
id	name	city_id
1	Колованов	1
2	Петров	3
3	Плотников	12
4	Кучеров	4
5	Малкин	2
6	Иванов	13

Ниже представлена струтура таблицы для демонстрации примеров

Таблица городов `City`
id	name	population
1	Москва	100
2	Нижний Новгород	25
3	Тверь	22
4	Санкт-Петербург	80
5	Выборг	18
6	Челябинск	30
7	Одинцово	5
8	Павлово	5

INNER JOIN

Оператор внутреннего соединения INNER JOIN соединяет две таблицы. Порядок таблиц для оператора неважен, поскольку оператор является симметричным.

Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.


					
    SELECT * FROM Person
    INNER JOIN
        City
    ON Person.city_id = City.id

Результат запроса
Person.id	Person.name	Person.city_id	City.id	City.name	City.population
1	Колованов	1	1	Москва	100
2	Петров	3	3	Тверь	22
4	Кучеров	4	4	Санкт-Петербург	80
5	Малкин	2	2	Нижний Новгород	25

INNER JOIN

Тело результата логически формируется следующим образом. Каждая строка одной таблицы сопоставляется с каждой строкой второй таблицы, после чего для полученной «соединённой» строки проверяется условие соединения (вычисляется предикат соединения). Если условие истинно, в таблицу-результат добавляется соответствующая «соединённая» строка.

В SQL существуют разные типы объединений. Мы рассмотрим только некоторые из них.

LEFT JOIN

Возвращает все строки из левой таблицы, даже если в правой таблице нет совпадений.

LEFT JOIN


					
    SELECT * FROM Person
    LEFT JOIN
        City
    ON Person.city_id = City.id

Для записей неудовлетворяющих условия объединения поля правой таблицы заполняются значениями NULL

Результат запроса
Person.id	Person. name	Person.city_id	City.id	City.name	City.population
1	Колованов	1	1	Москва	100
2	Петров	3	3	Тверь	22
3	Плотников	12	NULL	NULL	NULL
4	Кучеров	4	4	Санкт-Петербург	80
5	Малкин	2	2	Нижний Новгород	25
6	Иванов	13	NULL	NULL	NULL

RIGHT JOIN

Возвращает все строки из правой таблицы, даже если в левой таблице нет совпадений.

RIGHT JOIN


					
    SELECT * FROM Person
    RIGHT JOIN
        City
    ON Person.city_id = City.id

Для записей неудовлетворяющих условия объединения поля левой таблицы заполняются значениями NULL

Результат запроса
Person. id	Person.name	Person.city_id	City.id	City.name	City.population
1	Колованов	1	1	Москва	100
2	Петров	3	3	Тверь	22
4	Кучеров	4	4	Санкт-Петербург	80
5	Малкин	2	2	Нижний Новгород	25
NULL	NULL	NULL	5	Выборг	18
NULL	NULL	NULL	6	Челябинск	30
NULL	NULL	NULL	7	Одинцово	5
NULL	NULL	NULL	8	Павлово	5

azure-docs.ru-ru/sql-query-subquery.md at master · MicrosoftDocs/azure-docs.ru-ru · GitHub

title	description	author	ms.service	ms. subservice	ms.topic	ms.date	ms.author	ms.openlocfilehash	ms.sourcegitcommit	ms.translationtype	ms.contentlocale	ms.lasthandoff	ms.locfileid
Вложенные запросы SQL для Azure Cosmos DB	Сведения о вложенных запросах SQL и их типичных сценариях использования и различных типах вложенных запросов в Azure Cosmos DB	timsander1	cosmos-db	cosmosdb-sql	conceptual	12/02/2019	tisande	f5f209229d17a2587258d21ee90e7560e629d082	867cb1b7a1f3a1f0b427282c648d411d0ca4f81f	MT	ru-RU	03/19/2021	93340861

[!INCLUDEappliesto-sql-api]

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

В этой статье описываются вложенные запросы SQL и их распространенные варианты использования в Azure Cosmos DB. Все примеры запросов в этом документе можно выполнить для набора данных информации питании, который предварительно загружен на Площадка для тестирования запросов Azure Cosmos DB.

Типы вложенных запросов

Существует два основных типа вложенных запросов:

Коррелированный: вложенный запрос, который ссылается на значения из внешнего запроса. Вложенный запрос вычисляется один раз для каждой строки, обрабатываемой внешним запросом.
Некоррелированный: вложенный запрос, который не зависит от внешнего запроса. Его можно выполнять самостоятельно, не полагаясь на внешний запрос.

[!NOTE] Azure Cosmos DB поддерживает только коррелированные вложенные запросы.

Вложенные запросы можно дополнительно классифицировать на основе числа возвращаемых строк и столбцов. Здесь возможны три варианта:

Table: возвращает несколько строк и несколько столбцов.
Множественное значение: возвращает несколько строк и один столбец.
Scalar: Возвращает одну строку и один столбец.

SQL-запросы в Azure Cosmos DB всегда возвращают один столбец (простое значение или сложный документ). Таким образом, в Azure Cosmos DB применимы только многозначные и скалярные вложенные запросы. Вложенный запрос с несколькими значениями можно использовать только в предложении FROM в качестве реляционного выражения. Скалярный вложенный запрос можно использовать в качестве скалярного выражения в предложении SELECT или WHERE или в качестве реляционного выражения в предложении FROM.

Вложенные запросы с несколькими значениями

Вложенные запросы с несколькими значениями возвращают набор документов и всегда используются в предложении FROM. Они используются для:

Оптимизация выражений соединений.
Оценка дорогостоящих выражений один раз и многократная ссылка.

Выражения оптимизации соединений

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

Обратите внимание на следующий запрос:

SELECT Count(1) AS Count
FROM c
JOIN t IN c.tags
JOIN n IN c.nutrients
JOIN s IN c.servings
WHERE t.name = 'infant formula' AND (n.nutritionValue > 0 
AND n.nutritionValue < 10) AND s.amount > 1

Для этого запроса индекс будет соответствовать любому документу с тегом с именем «Формула новорожденный». Это элемент нутриент со значением от 0 до 10 и обслуживающим элементом с суммой больше 1. Выражение JOIN здесь выполняет перекрестное произведение всех элементов тегов, нутриентс и обслуживает массивы для каждого соответствующего документа до применения любого фильтра.

Затем предложение WHERE применит предикат фильтра для каждого <ного кортежа c, t, n, s>. Например, если в соответствующем документе в каждом из трех массивов было 10 элементов, оно будет расширено до 1 x 10 x 10 x 10 (то есть 1 000) кортежей. С помощью вложенных запросов можно фильтровать соединяемые элементы массива перед присоединением к следующему выражению.

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

SELECT Count(1) AS Count
FROM c
JOIN (SELECT VALUE t FROM t IN c.tags WHERE t.name = 'infant formula')
JOIN (SELECT VALUE n FROM n IN c.nutrients WHERE n.nutritionValue > 0 AND n.nutritionValue < 10)
JOIN (SELECT VALUE s FROM s IN c.servings WHERE s.amount > 1)

Предположим, что только один элемент в массиве Tags соответствует фильтру, а для нутриентс и обслуживания массивов существует пять элементов. Затем выражения объединения разворачиваются до 1 x 1 x 5 x 5 = 25 элементов, а не 1 000 элементов в первом запросе.

Многократное вычисление и ссылка

Вложенные запросы могут помочь оптимизировать запросы с дорогостоящими выражениями, такими как определяемые пользователем функции (UDF), сложные строки или арифметические выражения. Можно использовать вложенный запрос вместе с выражением объединения для вычисления выражения, но ссылаться на него много раз.

Следующий запрос дважды запускает определяемую пользователем функцию GetMaxNutritionValue :

SELECT c.id, udf.GetMaxNutritionValue(c.nutrients) AS MaxNutritionValue
FROM c
WHERE udf.GetMaxNutritionValue(c.nutrients) > 100

Ниже приведен эквивалентный запрос, запускающий UDF только один раз:

SELECT TOP 1000 c.id, MaxNutritionValue
FROM c
JOIN (SELECT VALUE udf.GetMaxNutritionValue(c.nutrients)) MaxNutritionValue
WHERE MaxNutritionValue > 100

[!NOTE] Учитывайте поведение перекрестного произведения выражений JOIN. Если выражение UDF может принимать значение undefine, следует убедиться, что выражение JOIN всегда создает одну строку, возвращая объект из вложенного запроса, а не значение напрямую.

Вот похожий пример, возвращающий объект, а не значение:

SELECT TOP 1000 c.id, m.MaxNutritionValue
FROM c
JOIN (SELECT udf.GetMaxNutritionValue(c.nutrients) AS MaxNutritionValue) m
WHERE m.MaxNutritionValue > 100

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

SELECT TOP 1000 c.id, AvgNutritionValue
FROM c
JOIN (SELECT VALUE avg(n.nutritionValue) FROM n IN c.nutrients) AvgNutritionValue
WHERE AvgNutritionValue > 80

Имитировать соединение с внешними эталонными данными

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

Например, рассмотрим следующий набор ссылочных данных:

Единица измерения	имя;	Множитель	Базовая единица
NG	Микрограмматика	1,00 e-09	Gram
μг	микрограм	1,00 e-06	Gram
mg	миллиграм	1,00 e-03	Gram
н	Gram	1,00 e + 00	Gram
кг	Килограмм	1,00 e + 03	Gram
MG	мегаграм	1,00 e + 06	Gram
GG	гигаграм	1,00 e + 09	Gram
nJ	наножауле	1,00 e-09	жауле
μж	микрожауле	1,00 e-06	жауле
mJ	миллижауле	1,00 e-03	жауле
J	жауле	1,00 e + 00	жауле
kJ	киложауле	1,00 e + 03	жауле
MJ	мегажауле	1,00 e + 06	жауле
гж	гигажауле	1,00 e + 09	жауле
Cal	калорие	1,00 e + 00	калорие
ккал	калорие	1,00 e + 03	калорие
IU	Международные единицы

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

SELECT TOP 10 n. id, n.description, n.nutritionValue, n.units, r.name
FROM food
JOIN n IN food.nutrients
JOIN r IN (
    SELECT VALUE [
        {unit: 'ng', name: 'nanogram', multiplier: 0.000000001, baseUnit: 'gram'},
        {unit: 'µg', name: 'microgram', multiplier: 0.000001, baseUnit: 'gram'},
        {unit: 'mg', name: 'milligram', multiplier: 0.001, baseUnit: 'gram'},
        {unit: 'g', name: 'gram', multiplier: 1, baseUnit: 'gram'},
        {unit: 'kg', name: 'kilogram', multiplier: 1000, baseUnit: 'gram'},
        {unit: 'Mg', name: 'megagram', multiplier: 1000000, baseUnit: 'gram'},
        {unit: 'Gg', name: 'gigagram', multiplier: 1000000000, baseUnit: 'gram'},
        {unit: 'nJ', name: 'nanojoule', multiplier: 0.000000001, baseUnit: 'joule'},
        {unit: 'µJ', name: 'microjoule', multiplier: 0.000001, baseUnit: 'joule'},
        {unit: 'mJ', name: 'millijoule', multiplier: 0.001, baseUnit: 'joule'},
        {unit: 'J', name: 'joule', multiplier: 1, baseUnit: 'joule'},
        {unit: 'kJ', name: 'kilojoule', multiplier: 1000, baseUnit: 'joule'},
        {unit: 'MJ', name: 'megajoule', multiplier: 1000000, baseUnit: 'joule'},
        {unit: 'GJ', name: 'gigajoule', multiplier: 1000000000, baseUnit: 'joule'},
        {unit: 'cal', name: 'calorie', multiplier: 1, baseUnit: 'calorie'},
        {unit: 'kcal', name: 'Calorie', multiplier: 1000, baseUnit: 'calorie'},
        {unit: 'IU', name: 'International units'}
    ]
)
WHERE n. units = r.unit

скалярные вложенные запросы;

Скалярное выражение вложенного запроса — это вложенный запрос, результатом которого является единственное значение. Значением скалярного выражения вложенного запроса является значение проекции (предложение SELECT) вложенного запроса. Можно использовать скалярное выражение вложенного запроса во многих местах, где допустимо использование скалярного выражения. Например, можно использовать скалярный вложенный запрос в любом выражении в предложениях SELECT и WHERE.

Однако использование скалярного вложенного запроса не всегда помогает оптимизировать. Например, передача скалярного вложенного запроса в качестве аргумента в системную или определяемую пользователем функцию не дает никаких преимуществ в использовании единицы ресурсов (RU) или задержке.

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

Скалярные вложенные запросы в простых выражениях
Агрегирование скалярных вложенных запросов

Скалярные вложенные запросы в простых выражениях

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

Рассмотрим несколько примеров.

Пример 1

SELECT 1 AS a, 2 AS b

Можно переписать этот запрос с помощью скалярного вложенного запроса простого выражения, чтобы:

SELECT (SELECT VALUE 1) AS a, (SELECT VALUE 2) AS b

Эти выходные данные создаются в обоих запросах:

[
  { "a": 1, "b": 2 }
]

Пример 2

SELECT TOP 5 Concat('id_', f.id) AS id
FROM food f

Можно переписать этот запрос с помощью скалярного вложенного запроса простого выражения, чтобы:

SELECT TOP 5 (SELECT VALUE Concat('id_', f.id)) AS id
FROM food f

Выходные данные запроса:

[
  { "id": "id_03226" },
  { "id": "id_03227" },
  { "id": "id_03228" },
  { "id": "id_03229" },
  { "id": "id_03230" }
]

Пример 3

SELECT TOP 5 f. id, Contains(f.description, 'fruit') = true ? f.description : undefined
FROM food f

Можно переписать этот запрос с помощью скалярного вложенного запроса простого выражения, чтобы:

SELECT TOP 10 f.id, (SELECT f.description WHERE Contains(f.description, 'fruit')).description
FROM food f

Выходные данные запроса:

[
  { "id": "03230" },
  { "id": "03238", "description":"Babyfood, dessert, tropical fruit, junior" },
  { "id": "03229" },
  { "id": "03226", "description":"Babyfood, dessert, fruit pudding, orange, strained" },
  { "id": "03227" }
]

Агрегирование скалярных вложенных запросов

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

Пример 1.

Вот вложенный запрос с одним выражением агрегатной функции в его проекции:

SELECT TOP 5 
    f. id, 
    (SELECT VALUE Count(1) FROM n IN f.nutrients WHERE n.units = 'mg'
) AS count_mg
FROM food f

Выходные данные запроса:

[
  { "id": "03230", "count_mg": 13 },
  { "id": "03238", "count_mg": 14 },
  { "id": "03229", "count_mg": 13 },
  { "id": "03226", "count_mg": 15 },
  { "id": "03227", "count_mg": 19 }
]

Пример 2

Вот вложенный запрос с несколькими выражениями агрегатных функций:

SELECT TOP 5 f.id, (
    SELECT Count(1) AS count, Sum(n.nutritionValue) AS sum 
    FROM n IN f.nutrients 
    WHERE n.units = 'mg'
) AS unit_mg
FROM food f

Выходные данные запроса:

[
  { "id": "03230","unit_mg": { "count": 13,"sum": 147.072 } },
  { "id": "03238","unit_mg": { "count": 14,"sum": 107.385 } },
  { "id": "03229","unit_mg": { "count": 13,"sum": 141.579 } },
  { "id": "03226","unit_mg": { "count": 15,"sum": 183.91399999999996 } },
  { "id": "03227","unit_mg": { "count": 19,"sum": 94. 788999999999987 } }
]

Пример 3

Вот запрос со статистическим вложенным запросом как в проекции, так и в фильтре:

SELECT TOP 5 
    f.id, 
    (SELECT VALUE Count(1) FROM n IN f.nutrients WHERE n.units = 'mg') AS count_mg
FROM food f
WHERE (SELECT VALUE Count(1) FROM n IN f.nutrients WHERE n.units = 'mg') > 20

Выходные данные запроса:

[
  { "id": "03235", "count_mg": 27 },
  { "id": "03246", "count_mg": 21 },
  { "id": "03267", "count_mg": 21 },
  { "id": "03269", "count_mg": 21 },
  { "id": "03274", "count_mg": 21 }
]

Более оптимальный способ написания этого запроса — соединение во вложенном запросе и ссылка на псевдоним вложенного запроса в предложениях SELECT и WHERE. Этот запрос более эффективен, поскольку необходимо выполнить вложенный запрос только внутри инструкции JOIN, а не в проекции и фильтре.

SELECT TOP 5 f.id, count_mg
FROM food f
JOIN (SELECT VALUE Count(1) FROM n IN f. nutrients WHERE n.units = 'mg') AS count_mg
WHERE count_mg > 20

Выражение EXISTs

Azure Cosmos DB поддерживает выражения EXISTs. Это совокупный скалярный вложенный запрос, встроенный в Azure Cosmos DB API SQL. EXISTs является логическим выражением, которое принимает выражение вложенного запроса и возвращает значение true, если вложенный запрос возвращает какие-либо строки. В противном случае возвращается значение false.

Так как API Azure Cosmos DB SQL не различает логические выражения и другие скалярные выражения, можно использовать в предложениях SELECT и WHERE. Это отличается от T-SQL, где логическое выражение (например, EXISTs, BETWEEN и IN) ограничено фильтром.

Если вложенный запрос EXISTs возвращает одиночное значение, которое не определено, то параметр EXISTs принимает значение false. Например, рассмотрим следующий запрос, результатом которого является значение false:

SELECT EXISTS (SELECT VALUE undefined)

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

SELECT EXISTS (SELECT undefined)

Вложенный запрос будет заключать список значений в выбранном списке в объекте. Если выбранный список не содержит значений, вложенный запрос возвратит единственное значение » {} «. Это значение определено, поэтому EXISTs вычисляется как true.

Пример: перезапись ARRAY_CONTAINS и присоединение как существует

Распространенным вариантом использования ARRAY_CONTAINS является фильтрация документа по существованию элемента в массиве. В этом случае мы проверяя, содержит ли массив Tags элемент с именем «оранжевый».

SELECT TOP 5 f.id, f.tags
FROM food f
WHERE ARRAY_CONTAINS(f.tags, {name: 'orange'})

Вы можете переписать тот же запрос, чтобы использовать EXISTs:

SELECT TOP 5 f.id, f.tags
FROM food f
WHERE EXISTS(SELECT VALUE t FROM t IN f.tags WHERE t.name = 'orange')

Кроме того, ARRAY_CONTAINS может проверять, равно ли значение любому элементу в массиве. Если требуются более сложные фильтры для свойств массива, используйте JOIN.

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

SELECT VALUE c. description
FROM c
JOIN n IN c.nutrients
WHERE n.units= "mg" AND n.nutritionValue > 0

Для каждого документа в коллекции перекрестное произведение выполняется с элементами массива. Эта операция объединения позволяет фильтровать свойства в массиве. Однако этот запрос будет иметь большое количество запросов. Например, если в 1 000 документах в каждом массиве содержалось 100 элементов, оно будет расширено до 1 000 x 100 (т. е. 100 000) кортежей.

Использование EXISTs может помочь избежать этого дорогостоящего перекрестного произведения:

SELECT VALUE c.description
FROM c
WHERE EXISTS(
    SELECT VALUE n
    FROM n IN c.nutrients
    WHERE n.units = "mg" AND n.nutritionValue > 0
)

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

Псевдоним также может существовать и ссылаться на него в проекции:

SELECT TOP 1 c. description, EXISTS(
    SELECT VALUE n
    FROM n IN c.nutrients
    WHERE n.units = "mg" AND n.nutritionValue > 0) as a
FROM c

Выходные данные запроса:

[
    {
        "description": "Babyfood, dessert, fruit pudding, orange, strained",
        "a": true
    }
]

Выражение массива

Можно использовать выражение массива для проецирования результатов запроса в виде массива. Это выражение можно использовать только в предложении SELECT запроса.

SELECT TOP 1   f.id, ARRAY(SELECT VALUE t.name FROM t in f.tags) AS tagNames
FROM  food f

Выходные данные запроса:

[
    {
        "id": "03238",
        "tagNames": [
            "babyfood",
            "dessert",
            "tropical fruit",
            "junior"
        ]
    }
]

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

SELECT TOP 1 c.id, ARRAY(SELECT VALUE t FROM t in c. tags WHERE t.name != 'infant formula') AS tagNames
FROM c

Выходные данные запроса:

[
    {
        "id": "03226",
        "tagNames": [
            {
                "name": "babyfood"
            },
            {
                "name": "dessert"
            },
            {
                "name": "fruit pudding"
            },
            {
                "name": "orange"
            },
            {
                "name": "strained"
            }
        ]
    }
]

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

SELECT TOP 1 c.id, ARRAY(SELECT VALUE t.name FROM t in c.tags) as tagNames
FROM c
JOIN n IN (SELECT VALUE ARRAY(SELECT t FROM t in c.tags WHERE t.name != 'infant formula'))

Выходные данные запроса:

[
    {
        "id": "03238",
        "tagNames": [
            "babyfood",
            "dessert",
            "tropical fruit",
            "junior"
        ]
    }
]

Дальнейшие действия

Примеры . NET для Azure Cosmos DB
Данные документов модели

Как работает вложенный запрос 1с

Вложенные запросы в 1С Предприятие 8.3

Пример вложенного запроса на языке запросов

ВЫБРАТЬ
НераспОплатыОстатки.Заказчик,
НераспОплатыОстатки.СуммаОст
ИЗ
(ВЫБРАТЬ
Заказчики.Ссылка КАК СсылкаНаСпрЗаказчики
ИЗ
Справочник.Заказчики КАК Заказчики
ГДЕ
Заказчики.Ссылка В(&Заказчики)) КАК ВложЗапрос
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления. НераспОплаты.Остатки КАК НераспОплаты
ПО ВложЗапрос.СсылкаНаСпрЗаказчики = НераспОплатыОстатки.Заказчик

Как лучше?

// Временная таблица
ВЫБРАТЬ
Заказчики.Ссылка КАК Заказчики
ПОМЕСТИТЬ табЗаказчики
ИЗ
Справочник.Заказчики КАК Заказчики
ГДЕ Заказчики.Ссылка В (&Заказчики)
;
// Основной запрос
ВЫБРАТЬ
табКлиенты. Ссылка,
НераспОплатыОстатки.СуммаОст,
ИЗ
табЗаказчики КАК табЗаказчики
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.НераспОплаты.Остатки(
,
Заказчик В
(ВЫБРАТЬ
табЗаказчики.Заказчики
ИЗ
табЗаказчики)) КАК НераспОплатыОстатки
ПО табЗаказчики.Заказчики = НераспОплатыОстатки.Заказчики

Смотрите также видео-урок про вложенные запросы:

Если вы только начинаете программировать в 1С или просто хотите систематизировать свои знания — попробуйте Школу программирования 1С нашего друга Владимира Милькина. Пошаговые и понятные уроки даже для новичка с поддержкой учителя.
Попробуйте бесплатно по ссылке >>

К сожалению, мы физически не можем проконсультировать бесплатно всех желающих, но наша команда будет рада оказать услуги по внедрению и обслуживанию 1С. Более подробно о наших услугах можно узнать на странице Услуги 1С или просто позвоните по телефону +7 (499) 350 29 00. Мы работаем в Москве и области.

Запросы 1С:Предприятие 8. Вложенные запросы

Вложенные запросы могут использоваться в конструкции ИЗ:

В том числе в соединениях:

И в условиях запроса со сравнением В или В ИЕРАРХИИ:

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

Запросы 1С:Предприятие 8. Вложенные запросы : 1 комментарий

Вложенные запросы

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

А теперь рассмотрим все вышесказанное на примере.
Пусть у нас есть таблица СотрудникиПодразделений:

Подразделение	Сотрудник
Бухгалтерия	Иванов
Администрация	Петров
Бухгалтерия	Сидоров

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

Если выполнить этот запрос, то в результате получим следующую таблицу

Подразделение	КоличествоСотрудников
Бухгалтерия	2
Администрация	1

Таким образом результат итогового запроса будет следующим

Подразделение
Бухгалтерия

Многотабличные и вложенные запросы — Проектирование баз данных на SQL (Информатика и программирование)

Лекция 22. Многотабличные и вложенные запросы

Как правило, запросы выполняют обработку данных, расположенных во множестве таблиц. Попытка соединить таблицы по «умолчанию » , приведет к декартовому произведению двух таблиц и вернет бессмысленный результат, например, если построить запрос по таблицам 7. 3 и 7.4, следующим образом:

SELECT *

FROM А, В

Из раздела реляционной алгебры известно, что под соединением двух таблиц (будем рассматривать экви-соединение) понимается последовательность выполнения операции декартового произведения и выборки, т.е.:

SELECT *

FROM А, В

WHERE А.Код_товара = В.Код_тов

Использование подобного метода возвратит верный результат, приведенный в таблице 7.6. Описанный способ соединения, был единственным в первом стандарте языка SQL.

Стандарт SQL2 расширил возможности соединения до так называемого внешнего соединения (внутренним принято считать соединение с использованием предложения WHERE).

В общем случае синтаксис внешнего соединения выглядит следующим образом:

FROM <Таблица1> <вид соединения> JOIN <Таблица2> ON <Условие соединения>

Вид соединения определяет главную (ведущую) таблицу в соединении и может определяться следующими служебными словами:

§ LEFT – левое внешнее соединение, когда ведущей является таблица слева от вида соединения;

§ RIGHT – правое внешнее соединение, когда ведущей является таблица справа от вида соединения;

§ FULL — полное внешнее соединение, когда обе таблица равны в соединении;

§ INNER – вариант внутреннего соединения.

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

Приведем пример реализации внутреннего соединения для стандарта SQL2:

SELECT *

FROM А INNER JOIN В ON А.Код_товара = В.Код_тов

Вариант внешнего соединения, когда левая таблица является главной (ведущей):

SELECT *

FROM А LEFT JOIN В ON А.Код_товара = В.Код_тов

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

SELECT Каталог. Наименование as Область_знаний, Книги.ISBN, Книги.Название as Книга

FROM Книги INNER JOIN (Каталог INNER JOIN Связь ON Каталог.Код_ОЗ = Связь.Код_ОЗ) ON Книги.ISBN = Связь.ISBN;

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

SELECT DISTINCT Читатели.ФИО, Count(*) AS (Количество_книг)

FROM Читатели INNER JOIN Экземпляры ON Читатели.Номер_ЧБ = Экземпляры.Номер_ЧБ

GROUP BY Читатели.ФИО, Читатели.Номер_ЧБ, Экземпляры.Наличие

HAVING Экземпляры.Наличие = No;

Следующий запрос возвращает информацию о должниках и книгах, которые они должны были сдать в библиотеку с сортировкой по дате возврата:

SELECT Книги. Название, Книги.Автор, Экземпляры.Инв_номер, Экземпляры.Дата_возврата, Читатели.Номер_ЧБ, Читатели.ФИО, Читатели.Тел_дом, Читатели.Тел_раб

FROM Книги INNER JOIN (Читатели INNER JOIN Экземпляры ON Читатели.Номер_ЧБ = Экземпляры.Номер_ЧБ) ON Книги.ISBN = Экземпляры.ISBN

WHERE Экземпляры.Дата_возврата < Now() AND Экземпляры.Наличие=No

ORDER BY Экземпляры.Дата_возврата;

Вложенные запросы

Язык SQL позволяет вкладывать запросы друга в друга, это относится к оператору SELECT. Оператор SELECT, вложенный в другой оператор SELECT, INSERT, UPDATE или DELETE., называется вложенным запросом.

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

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

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

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

(SELECT [ALL | DISTINCT] слисок_ столбцов _вложенного_запроса

[FROM список_ таблиц]

[WHERE директива]

[GROUP BY директива]

[HAVING директива])

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

SELECT Читатели.Номер_ЧБ, Читатели.ФИО, COUNT(*) AS Количество

FROM Читатели INNER JOIN Экземпляры ON Читатели.Номер_ЧБ = Экземпляры.Номер_ЧБ

GROUP BY Читатели.Номер_ЧБ, Читатели.ФИО, Экземпляры.Наличие

HAVING Экземпляры.Наличие = No AND COUNT(*) =

(SELECT MAX(Количество)

FROM

(SELECT COUNT(*) AS Количество

FROM Экземпляры

GROUP BY Экземпляры.Номер_ЧБ, Экземпляры. Наличие

HAVING Экземпляры.Наличие = No))

Как и положено вложенным запросом первым выполняется самый «глубокий » по уровню вложенности подзапрос, который определяет количество книг на руках у каждого читателя:

SELECT COUNT(*) AS Количество

FROM Экземпляры

GROUP BY Экземпляры.Номер_ЧБ, Экземпляры.Наличие

HAVING Экземпляры.Наличие = No

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

(SELECT MAX(Количество) FROM (SELECT …))

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

В данном примере вложенный запрос возвращает агрегированное значение, полученное в результате итоговой функции MAX, поэтому условное выражение COUNT(*) = (SELECT MAX(Количество)…) имеет смысл. Если вложенный подзапрос может вернуть множество значений, то простое сравнение не подходит, необходимо использование служебных слов ANY или предиката IN, множество значений которого будет формировать вложенный подзапрос. Служебное слово ANY указывает на необходимость использования условного выражения для каждого значения, полученного во вложенном запросе.

Контрольные вопросы

1. Как можно получить декартово произведение двух таблиц?

2. Чем отличается соединение от объединения?

3. Какие виды соединений предусмотрены первым стандартом SQL?

4. Какой синтаксис имеют соединения?

5. Какие виды соединений вы знаете?

6. Для чего необходимы вложенные запросы?

7. Какие ограничения налагаются на вложенные запросы?

8. Как можно использовать предикат IN или служебное слово ANY?

Задания для самостоятельной работы

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

Задание 1. Запишите запрос для определения:

1. к каким предметным областям относится какая-либо книга;

2. какие книги относятся к определенной предметной области.

Задание 2. Дана таблица «Книг_у_читателей » , содержащая поля «ФИО_читателя » и «Книг_на_руках » . Запишите текст запроса для определения:

1. читателей держащих больше всего книг на руках;

2. читателей держащих меньше всего книг на руках.

Написание подзапросов в SQL | Расширенный SQL

Начиная здесь? Этот урок является частью полного учебника по использованию SQL для анализа данных. Проверьте начало.

В этом уроке мы рассмотрим:

Основы подзапросов
Использование подзапросов для агрегирования в несколько этапов
Подзапросы в условной логике
Объединение подзапросов
Подзапросы и UNION

На этом уроке вы продолжите работать с теми же данными о преступности в Сан-Франциско, что и на предыдущем уроке.

Основы подзапросов

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

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

 SELECT sub.*
  ИЗ (
        ВЫБРАТЬ *
          ИЗ tutorial.sf_crime_incidents_2014_01
         ГДЕ day_of_week = 'Пятница'
       ) суб
 ГДЕ подразрешение = 'НЕТ'

Давайте разберем, что происходит, когда вы выполняете приведенный выше запрос:

Сначала база данных выполняет «внутренний запрос» — часть в круглых скобках:

 SELECT *
  ИЗ tutorial.sf_crime_incidents_2014_01
 ГДЕ day_of_week = 'Пятница'

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

 SELECT sub.*
  ИЗ (
       <<результаты внутреннего запроса идут сюда>>
       ) суб
 ГДЕ подразрешение = 'НЕТ'

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

Небольшое замечание по форматированию. Важно помнить, что при использовании подзапросов необходимо предоставить читателю возможность легко определить, какие части запроса будут выполняться вместе. Большинство людей делают это, тем или иным образом делая отступ в подзапросе. Примеры в этом руководстве имеют довольно большой отступ — вплоть до круглых скобок. Это нецелесообразно, если вы вкладываете много подзапросов, поэтому довольно часто отступ делается только на два пробела или около того.

Практическая задача

Напишите запрос, который выбирает все ордера на арест из набора данных tutorial.sf_crime_incidents_2014_01 , а затем оберните его во внешний запрос, который отображает только неразрешенные инциденты.

Попробуйте См. ответ

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

Использование подзапросов для агрегирования в несколько этапов

Что делать, если вы хотите выяснить, сколько инцидентов сообщается в каждый день недели? А что, если вы хотите узнать, сколько инцидентов происходит в среднем в пятницу декабря? В январе? Этот процесс состоит из двух шагов: подсчет количества инцидентов каждый день (внутренний запрос), затем определение среднемесячного значения (внешний запрос): суб. день_недели, AVG(sub.incidents) КАК среднее_происшествие ИЗ ( ВЫБЕРИТЕ день_недели, свидание, COUNT(incidnt_num) инцидентов AS ИЗ tutorial.sf_crime_incidents_2014_01 СГРУППИРОВАТЬ НА 1,2 ) суб СГРУППИРОВАТЬ НА 1,2 ЗАКАЗАТЬ ПО 1,2

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

Практическая задача

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

Попробуйте См. ответ

Подзапросы в условной логике

Вы можете использовать подзапросы в условной логике (в сочетании с WHERE , JOIN / ON или CASE ). Следующий запрос возвращает все записи с самой ранней даты в наборе данных (теоретически — плохое форматирование столбца даты на самом деле заставляет возвращать значение, отсортированное первым в алфавитном порядке):

 SELECT *
  ИЗ tutorial.sf_crime_incidents_2014_01
 ГДЕ Дата = (ВЫБЕРИТЕ МИН (дата)
                 ИЗ tutorial.sf_crime_incidents_2014_01
              )

Приведенный выше запрос работает, поскольку результатом подзапроса является только одна ячейка. Большая часть условной логики будет работать с подзапросами, содержащими результаты с одной ячейкой. Однако IN — это единственный тип условной логики, который будет работать, когда внутренний запрос содержит несколько результатов:

 SELECT *
  ИЗ tutorial.sf_crime_incidents_2014_01
 ГДЕ Дата В (ВЫБЕРИТЕ дату
                 ИЗ tutorial.sf_crime_incidents_2014_01
                ЗАКАЗАТЬ ПО дате
                ПРЕДЕЛ 5
              )

Обратите внимание, что вы не должны включать псевдоним при написании подзапроса в условном выражении. Это связано с тем, что подзапрос обрабатывается как отдельное значение (или набор значений в случае IN ), а не как таблица.

Объединение подзапросов

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

 ВЫБОР *
  ИЗ tutorial.sf_crime_incidents_2014_01 происшествий
  ПРИСОЕДИНЯЙТЕСЬ ( ВЫБЕРИТЕ дату
           ИЗ tutorial.sf_crime_incidents_2014_01
          ЗАКАЗАТЬ ПО дате
          ПРЕДЕЛ 5
       ) суб
    ON инциденты.дата = суб.дата

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

 ВЫБЕРИТЕ инциденты.*,
       sub.incidents AS инциденты_этот_день
  ИЗ tutorial.sf_crime_incidents_2014_01 происшествий
  ПРИСОЕДИНЯЙТЕСЬ ( ВЫБЕРИТЕ дату,
          COUNT(incidnt_num) инцидентов AS
           ИЗ tutorial.sf_crime_incidents_2014_01
          СГРУППИРОВАТЬ ПО 1
       ) суб
    ON инциденты.дата = суб.дата
 ORDER BY sub.incidents DESC, время

Практическая задача

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

ПопробуйтеСмотреть ответ

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

 SELECT COALESCE(acquisitions.acquired_month, Investments.funded_month) AS month,
       COUNT(DISTINCT Acquirements.company_permalink) КАК компании_приобретены,
       COUNT(DISTINCT Investments.company_permalink) КАК инвестиции
  ИЗ приобретения tutorial.crunchbase_acquisitions
  FULL JOIN tutorial.crunchbase_investments инвестиции
    ON приобретения.acquired_month = инвестиции.funded_month
 СГРУППИРОВАТЬ ПО 1

Обратите внимание, что для того, чтобы сделать это правильно, вы должны соединить поля даты, что вызывает массовый «взрыв данных». По сути, происходит то, что вы соединяете каждую строку в данном месяце из одной таблицы с каждым месяцем в данной строке в другой таблице, поэтому количество возвращаемых строк невероятно велико. Из-за этого мультипликативного эффекта вы должны использовать COUNT(DISTINCT) вместо COUNT , чтобы получить точные подсчеты. Вы можете увидеть это ниже:

Следующий запрос показывает 7414 строк:

 ВЫБРАТЬ СЧЕТЧИК(*) ИЗ tutorial.crunchbase_acquisitions

Следующий запрос показывает 83 893 строки:

 SELECT COUNT(*) FROM tutorial.crunchbase_investments

Следующий запрос показывает 6 237 396 строк:

 SELECT COUNT(*)
      ИЗ приобретения tutorial.crunchbase_acquisitions
      FULL JOIN tutorial.crunchbase_investments инвестиции
        ON приобретения.acquired_month = инвестиции.funded_month

Если вы хотите понять это немного лучше, вы можете провести дополнительное исследование декартовых произведений. Также стоит отметить, что FULL JOIN и COUNT , приведенные выше, на самом деле работают довольно быстро — это COUNT(DISTINCT) , который занимает вечность. Подробнее об этом в уроке по оптимизации запросов.

Конечно, вы могли бы решить эту проблему намного эффективнее, объединив две таблицы по отдельности, а затем соединив их вместе, чтобы подсчеты выполнялись для гораздо меньших наборов данных: приобретения.companies_acquired, Investments.companies_rec_investment ИЗ ( ВЫБЕРИТЕ приобретаете_месяц КАК месяц, COUNT(DISTINCT company_permalink) AS company_acquired ИЗ tutorial.crunchbase_acquisitions СГРУППИРОВАТЬ ПО 1 ) приобретения ПОЛНОЕ СОЕДИНЕНИЕ ( ВЫБЕРИТЕ funded_month AS месяц, COUNT(DISTINCT company_permalink) КАК company_rec_investment ИЗ tutorial.crunchbase_investments СГРУППИРОВАТЬ ПО 1 )инвестиции ON приобретения.месяц = инвестиции.месяц ЗАКАЗАТЬ ПО 1 ДЕСК

Примечание. Мы использовали FULL JOIN выше на тот случай, если в одной таблице были наблюдения за месяц, которых не было в другой таблице. Мы также использовали COALESCE для отображения месяцев, когда в подзапросе поступлений не было записей о месяцах (предположительно, в эти месяцы не было поступлений). Мы настоятельно рекомендуем вам повторно выполнить запрос без некоторых из этих элементов, чтобы лучше понять, как они работают. Вы также можете запускать каждый из подзапросов независимо, чтобы лучше понять их.

Практическая задача

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

Попробуйте См. ответ

Подзапросы и ОБЪЕДИНЕНИЯ

В следующем разделе мы возьмем урок, посвященный ОБЪЕДИНЕНИЯМ, снова используя данные Crunchbase:

 SELECT *
  ИЗ tutorial.crunchbase_investments_part1
 СОЮЗ ВСЕХ
 ВЫБРАТЬ *
   ИЗ tutorial.crunchbase_investments_part2

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

 SELECT COUNT(*) AS total_rows
  ИЗ (
        ВЫБРАТЬ *
          ИЗ tutorial.crunchbase_investments_part1
         СОЮЗ ВСЕХ
        ВЫБРАТЬ *
          ИЗ tutorial.crunchbase_investments_part2
       ) суб

Это довольно просто. Попробуйте сами:

Практическая задача

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

ПопробуйтеСмотреть ответ

Практическая задача

Напишите запрос, который делает то же самое, что и в предыдущей задаче, но только для компаний, которые все еще работают. Подсказка: рабочий статус указан в tutorial. crunchbase_companies 9.0030 .

ПопробуйтеСмотреть ответ

`Написание подзапросов в SQL | Расширенный SQL`

Начиная здесь? Этот урок является частью полного учебника по использованию SQL для анализа данных. Проверьте начало.

В этом уроке мы рассмотрим:

Основы подзапросов
Использование подзапросов для агрегирования в несколько этапов
Подзапросы в условной логике
Объединение подзапросов
Подзапросы и UNION

На этом уроке вы продолжите работать с теми же данными о преступности в Сан-Франциско, что и на предыдущем уроке.

`Основы работы с подзапросами`

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

Подзапросы могут использоваться в нескольких местах внутри запроса, но проще всего начать с С выписка. Вот пример простого подзапроса:

 SELECT sub.*
  ИЗ (
        ВЫБРАТЬ *
          ИЗ tutorial.sf_crime_incidents_2014_01
         ГДЕ day_of_week = 'Пятница'
       ) суб
 ГДЕ подразрешение = 'НЕТ'

Давайте разберем, что происходит, когда вы запускаете приведенный выше запрос:

Сначала база данных выполняет «внутренний запрос» — часть в скобках:

 SELECT *
  ИЗ tutorial.sf_crime_incidents_2014_01
 ГДЕ day_of_week = 'Пятница'

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

 SELECT sub. *
  ИЗ (
       <<результаты внутреннего запроса идут сюда>>
       ) суб
 ГДЕ подразрешение = 'НЕТ'

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

`Практическая задача`

Напишите запрос, который выбирает все ордера на арест из набора данных tutorial.sf_crime_incidents_2014_01 , а затем оберните его во внешний запрос, который отображает только неразрешенные инциденты.

Попробуйте См. ответ

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

`Использование подзапросов для агрегирования в несколько этапов`

Что делать, если вы хотите выяснить, сколько инцидентов сообщается в каждый день недели? А что, если вы хотите узнать, сколько инцидентов происходит в среднем в пятницу декабря? В январе? Этот процесс состоит из двух шагов: подсчет количества инцидентов каждый день (внутренний запрос), затем определение среднемесячного значения (внешний запрос): суб.день_недели, AVG(sub.incidents) КАК среднее_происшествие ИЗ ( ВЫБЕРИТЕ день_недели, свидание, COUNT(incidnt_num) инцидентов AS ИЗ tutorial. sf_crime_incidents_2014_01 СГРУППИРОВАТЬ НА 1,2 ) суб СГРУППИРОВАТЬ НА 1,2 ЗАКАЗАТЬ ПО 1,2

`Практическая задача`

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

Попробуйте См. ответ

`Подзапросы в условной логике`

Вы можете использовать подзапросы в условной логике (в сочетании с WHERE , JOIN / ON или CASE ). Следующий запрос возвращает все записи с самой ранней даты в наборе данных (теоретически — плохое форматирование столбца даты на самом деле заставляет возвращать значение, отсортированное первым в алфавитном порядке):

 SELECT *
  ИЗ tutorial. sf_crime_incidents_2014_01
 ГДЕ Дата = (ВЫБЕРИТЕ МИН (дата)
                 ИЗ tutorial.sf_crime_incidents_2014_01
              )

Приведенный выше запрос работает, поскольку результатом подзапроса является только одна ячейка. Большая часть условной логики будет работать с подзапросами, содержащими результаты с одной ячейкой. Однако IN — это единственный тип условной логики, который будет работать, когда внутренний запрос содержит несколько результатов:

 SELECT *
  ИЗ tutorial.sf_crime_incidents_2014_01
 ГДЕ Дата В (ВЫБЕРИТЕ дату
                 ИЗ tutorial.sf_crime_incidents_2014_01
                ЗАКАЗАТЬ ПО дате
                ПРЕДЕЛ 5
              )

Обратите внимание, что вы не должны включать псевдоним при написании подзапроса в условном выражении. Это связано с тем, что подзапрос обрабатывается как отдельное значение (или набор значений в случае IN ), а не как таблица.

`Объединение подзапросов`

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

 ВЫБОР *
  ИЗ tutorial.sf_crime_incidents_2014_01 происшествий
  ПРИСОЕДИНЯЙТЕСЬ ( ВЫБЕРИТЕ дату
           ИЗ tutorial.sf_crime_incidents_2014_01
          ЗАКАЗАТЬ ПО дате
          ПРЕДЕЛ 5
       ) суб
    ON инциденты.дата = суб.дата

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

 ВЫБЕРИТЕ инциденты. *,
       sub.incidents AS инциденты_этот_день
  ИЗ tutorial.sf_crime_incidents_2014_01 происшествий
  ПРИСОЕДИНЯЙТЕСЬ ( ВЫБЕРИТЕ дату,
          COUNT(incidnt_num) инцидентов AS
           ИЗ tutorial.sf_crime_incidents_2014_01
          СГРУППИРОВАТЬ ПО 1
       ) суб
    ON инциденты.дата = суб.дата
 ORDER BY sub.incidents DESC, время

`Практическая задача`

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

ПопробуйтеСмотреть ответ

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

 SELECT COALESCE(acquisitions. acquired_month, Investments.funded_month) AS month,
       COUNT(DISTINCT Acquirements.company_permalink) КАК компании_приобретены,
       COUNT(DISTINCT Investments.company_permalink) КАК инвестиции
  ИЗ приобретения tutorial.crunchbase_acquisitions
  FULL JOIN tutorial.crunchbase_investments инвестиции
    ON приобретения.acquired_month = инвестиции.funded_month
 СГРУППИРОВАТЬ ПО 1

Обратите внимание, что для того, чтобы сделать это правильно, вы должны соединить поля даты, что вызывает массовый «взрыв данных». По сути, происходит то, что вы соединяете каждую строку в данном месяце из одной таблицы с каждым месяцем в данной строке в другой таблице, поэтому количество возвращаемых строк невероятно велико. Из-за этого мультипликативного эффекта вы должны использовать COUNT(DISTINCT) вместо COUNT , чтобы получить точные подсчеты. Вы можете увидеть это ниже:

Следующий запрос показывает 7414 строк:

 ВЫБРАТЬ СЧЕТЧИК(*) ИЗ tutorial.crunchbase_acquisitions

Следующий запрос показывает 83 893 строки:

 SELECT COUNT(*) FROM tutorial. crunchbase_investments

Следующий запрос показывает 6 237 396 строк:

 SELECT COUNT(*)
      ИЗ приобретения tutorial.crunchbase_acquisitions
      FULL JOIN tutorial.crunchbase_investments инвестиции
        ON приобретения.acquired_month = инвестиции.funded_month

Если вы хотите понять это немного лучше, вы можете провести дополнительное исследование декартовых произведений. Также стоит отметить, что FULL JOIN и COUNT , приведенные выше, на самом деле работают довольно быстро — это COUNT(DISTINCT) , который занимает вечность. Подробнее об этом в уроке по оптимизации запросов.

Конечно, вы могли бы решить эту проблему намного эффективнее, объединив две таблицы по отдельности, а затем соединив их вместе, чтобы подсчеты выполнялись для гораздо меньших наборов данных: приобретения.companies_acquired, Investments.companies_rec_investment ИЗ ( ВЫБЕРИТЕ приобретаете_месяц КАК месяц, COUNT(DISTINCT company_permalink) AS company_acquired ИЗ tutorial. crunchbase_acquisitions СГРУППИРОВАТЬ ПО 1 ) приобретения ПОЛНОЕ СОЕДИНЕНИЕ ( ВЫБЕРИТЕ funded_month AS месяц, COUNT(DISTINCT company_permalink) КАК company_rec_investment ИЗ tutorial.crunchbase_investments СГРУППИРОВАТЬ ПО 1 )инвестиции ON приобретения.месяц = инвестиции.месяц ЗАКАЗАТЬ ПО 1 ДЕСК

Примечание. Мы использовали FULL JOIN выше на тот случай, если в одной таблице были наблюдения за месяц, которых не было в другой таблице. Мы также использовали COALESCE для отображения месяцев, когда в подзапросе поступлений не было записей о месяцах (предположительно, в эти месяцы не было поступлений). Мы настоятельно рекомендуем вам повторно выполнить запрос без некоторых из этих элементов, чтобы лучше понять, как они работают. Вы также можете запускать каждый из подзапросов независимо, чтобы лучше понять их.

`Практическая задача`

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

Попробуйте См. ответ

`Подзапросы и ОБЪЕДИНЕНИЯ`

В следующем разделе мы возьмем урок, посвященный ОБЪЕДИНЕНИЯМ, снова используя данные Crunchbase:

 SELECT *
  ИЗ tutorial.crunchbase_investments_part1
 СОЮЗ ВСЕХ
 ВЫБРАТЬ *
   ИЗ tutorial.crunchbase_investments_part2

 SELECT COUNT(*) AS total_rows
  ИЗ (
        ВЫБРАТЬ *
          ИЗ tutorial.crunchbase_investments_part1
         СОЮЗ ВСЕХ
        ВЫБРАТЬ *
          ИЗ tutorial. crunchbase_investments_part2
       ) суб

Это довольно просто. Попробуйте сами:

`Практическая задача`

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

ПопробуйтеСмотреть ответ

`Практическая задача`

Напишите запрос, который делает то же самое, что и в предыдущей задаче, но только для компаний, которые все еще работают. Подсказка: рабочий статус указан в tutorial.crunchbase_companies 9.0030 .

ПопробуйтеСмотреть ответ

`Написание подзапросов в SQL | Расширенный SQL`

В этом уроке мы рассмотрим:

Основы подзапросов
Использование подзапросов для агрегирования в несколько этапов
Подзапросы в условной логике
Объединение подзапросов
Подзапросы и UNION

`Основы работы с подзапросами`

 SELECT sub.*
  ИЗ (
        ВЫБРАТЬ *
          ИЗ tutorial.sf_crime_incidents_2014_01
         ГДЕ day_of_week = 'Пятница'
       ) суб
 ГДЕ подразрешение = 'НЕТ'

Давайте разберем, что происходит, когда вы запускаете приведенный выше запрос:

Сначала база данных выполняет «внутренний запрос» — часть в скобках:

 SELECT *
  ИЗ tutorial.sf_crime_incidents_2014_01
 ГДЕ day_of_week = 'Пятница'

 SELECT sub.*
  ИЗ (
       <<результаты внутреннего запроса идут сюда>>
       ) суб
 ГДЕ подразрешение = 'НЕТ'

`Практическая задача`

Попробуйте См. ответ

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

`Использование подзапросов для агрегирования в несколько этапов`

`Практическая задача`

Попробуйте См. ответ

`Подзапросы в условной логике`

 SELECT *
  ИЗ tutorial.sf_crime_incidents_2014_01
 ГДЕ Дата = (ВЫБЕРИТЕ МИН (дата)
                 ИЗ tutorial.sf_crime_incidents_2014_01
              )

Приведенный выше запрос работает, поскольку результатом подзапроса является только одна ячейка. Большая часть условной логики будет работать с подзапросами, содержащими результаты с одной ячейкой. Однако IN — это единственный тип условной логики, который будет работать, когда внутренний запрос содержит несколько результатов:

 SELECT *
  ИЗ tutorial.sf_crime_incidents_2014_01
 ГДЕ Дата В (ВЫБЕРИТЕ дату
                 ИЗ tutorial.sf_crime_incidents_2014_01
                ЗАКАЗАТЬ ПО дате
                ПРЕДЕЛ 5
              )

`Объединение подзапросов`

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

 ВЫБОР *
  ИЗ tutorial.sf_crime_incidents_2014_01 происшествий
  ПРИСОЕДИНЯЙТЕСЬ ( ВЫБЕРИТЕ дату
           ИЗ tutorial.sf_crime_incidents_2014_01
          ЗАКАЗАТЬ ПО дате
          ПРЕДЕЛ 5
       ) суб
    ON инциденты.дата = суб.дата

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

 ВЫБЕРИТЕ инциденты.*,
       sub.incidents AS инциденты_этот_день
  ИЗ tutorial.sf_crime_incidents_2014_01 происшествий
  ПРИСОЕДИНЯЙТЕСЬ ( ВЫБЕРИТЕ дату,
          COUNT(incidnt_num) инцидентов AS
           ИЗ tutorial.sf_crime_incidents_2014_01
          СГРУППИРОВАТЬ ПО 1
       ) суб
    ON инциденты.дата = суб.дата
 ORDER BY sub.incidents DESC, время

`Практическая задача`

ПопробуйтеСмотреть ответ

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

 SELECT COALESCE(acquisitions.acquired_month, Investments.funded_month) AS month,
       COUNT(DISTINCT Acquirements.company_permalink) КАК компании_приобретены,
       COUNT(DISTINCT Investments.company_permalink) КАК инвестиции
  ИЗ приобретения tutorial.crunchbase_acquisitions
  FULL JOIN tutorial.crunchbase_investments инвестиции
    ON приобретения.acquired_month = инвестиции.funded_month
 СГРУППИРОВАТЬ ПО 1

Обратите внимание, что для того, чтобы сделать это правильно, вы должны соединить поля даты, что вызывает массовый «взрыв данных». По сути, происходит то, что вы соединяете каждую строку в данном месяце из одной таблицы с каждым месяцем в данной строке в другой таблице, поэтому количество возвращаемых строк невероятно велико. Из-за этого мультипликативного эффекта вы должны использовать COUNT(DISTINCT) вместо COUNT , чтобы получить точные подсчеты. Вы можете увидеть это ниже:

Следующий запрос показывает 7414 строк:

 ВЫБРАТЬ СЧЕТЧИК(*) ИЗ tutorial.crunchbase_acquisitions

Следующий запрос показывает 83 893 строки:

 SELECT COUNT(*) FROM tutorial.crunchbase_investments

Следующий запрос показывает 6 237 396 строк:

 SELECT COUNT(*)
      ИЗ приобретения tutorial.crunchbase_acquisitions
      FULL JOIN tutorial.crunchbase_investments инвестиции
        ON приобретения.acquired_month = инвестиции.funded_month

`Практическая задача`

Попробуйте См. ответ

`Подзапросы и ОБЪЕДИНЕНИЯ`

В следующем разделе мы возьмем урок, посвященный ОБЪЕДИНЕНИЯМ, снова используя данные Crunchbase:

 SELECT *
  ИЗ tutorial.crunchbase_investments_part1
 СОЮЗ ВСЕХ
 ВЫБРАТЬ *
   ИЗ tutorial.crunchbase_investments_part2

 SELECT COUNT(*) AS total_rows
  ИЗ (
        ВЫБРАТЬ *
          ИЗ tutorial.crunchbase_investments_part1
         СОЮЗ ВСЕХ
        ВЫБРАТЬ *
          ИЗ tutorial.crunchbase_investments_part2
       ) суб

Это довольно просто. Попробуйте сами:

`Практическая задача`

ПопробуйтеСмотреть ответ

`Практическая задача`

ПопробуйтеСмотреть ответ

`Написание подзапросов в SQL | Расширенный SQL`

В этом уроке мы рассмотрим:

Основы подзапросов
Использование подзапросов для агрегирования в несколько этапов
Подзапросы в условной логике
Объединение подзапросов
Подзапросы и UNION

`Основы работы с подзапросами`

 SELECT sub.*
  ИЗ (
        ВЫБРАТЬ *
          ИЗ tutorial.sf_crime_incidents_2014_01
         ГДЕ day_of_week = 'Пятница'
       ) суб
 ГДЕ подразрешение = 'НЕТ'

Давайте разберем, что происходит, когда вы запускаете приведенный выше запрос:

Сначала база данных выполняет «внутренний запрос» — часть в скобках:

 SELECT *
  ИЗ tutorial.sf_crime_incidents_2014_01
 ГДЕ day_of_week = 'Пятница'

 SELECT sub. *
  ИЗ (
       <<результаты внутреннего запроса идут сюда>>
       ) суб
 ГДЕ подразрешение = 'НЕТ'

`Практическая задача`

Попробуйте См. ответ

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

`Использование подзапросов для агрегирования в несколько этапов`

Что делать, если вы хотите выяснить, сколько инцидентов сообщается в каждый день недели? А что, если вы хотите узнать, сколько инцидентов происходит в среднем в пятницу декабря? В январе? Этот процесс состоит из двух шагов: подсчет количества инцидентов каждый день (внутренний запрос), затем определение среднемесячного значения (внешний запрос): суб.день_недели, AVG(sub.incidents) КАК среднее_происшествие ИЗ ( ВЫБЕРИТЕ день_недели, свидание, COUNT(incidnt_num) инцидентов AS ИЗ tutorial. sf_crime_incidents_2014_01 СГРУППИРОВАТЬ НА 1,2 ) суб СГРУППИРОВАТЬ НА 1,2 ЗАКАЗАТЬ ПО 1,2

`Практическая задача`

Попробуйте См. ответ

`Подзапросы в условной логике`

 SELECT *
  ИЗ tutorial. sf_crime_incidents_2014_01
 ГДЕ Дата = (ВЫБЕРИТЕ МИН (дата)
                 ИЗ tutorial.sf_crime_incidents_2014_01
              )

Приведенный выше запрос работает, поскольку результатом подзапроса является только одна ячейка. Большая часть условной логики будет работать с подзапросами, содержащими результаты с одной ячейкой. Однако IN — это единственный тип условной логики, который будет работать, когда внутренний запрос содержит несколько результатов:

 SELECT *
  ИЗ tutorial.sf_crime_incidents_2014_01
 ГДЕ Дата В (ВЫБЕРИТЕ дату
                 ИЗ tutorial.sf_crime_incidents_2014_01
                ЗАКАЗАТЬ ПО дате
                ПРЕДЕЛ 5
              )

`Объединение подзапросов`

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

 ВЫБОР *
  ИЗ tutorial.sf_crime_incidents_2014_01 происшествий
  ПРИСОЕДИНЯЙТЕСЬ ( ВЫБЕРИТЕ дату
           ИЗ tutorial.sf_crime_incidents_2014_01
          ЗАКАЗАТЬ ПО дате
          ПРЕДЕЛ 5
       ) суб
    ON инциденты.дата = суб.дата

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

 ВЫБЕРИТЕ инциденты. *,
       sub.incidents AS инциденты_этот_день
  ИЗ tutorial.sf_crime_incidents_2014_01 происшествий
  ПРИСОЕДИНЯЙТЕСЬ ( ВЫБЕРИТЕ дату,
          COUNT(incidnt_num) инцидентов AS
           ИЗ tutorial.sf_crime_incidents_2014_01
          СГРУППИРОВАТЬ ПО 1
       ) суб
    ON инциденты.дата = суб.дата
 ORDER BY sub.incidents DESC, время

`Практическая задача`

ПопробуйтеСмотреть ответ

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

 SELECT COALESCE(acquisitions. acquired_month, Investments.funded_month) AS month,
       COUNT(DISTINCT Acquirements.company_permalink) КАК компании_приобретены,
       COUNT(DISTINCT Investments.company_permalink) КАК инвестиции
  ИЗ приобретения tutorial.crunchbase_acquisitions
  FULL JOIN tutorial.crunchbase_investments инвестиции
    ON приобретения.acquired_month = инвестиции.funded_month
 СГРУППИРОВАТЬ ПО 1

Обратите внимание, что для того, чтобы сделать это правильно, вы должны соединить поля даты, что вызывает массовый «взрыв данных». По сути, происходит то, что вы соединяете каждую строку в данном месяце из одной таблицы с каждым месяцем в данной строке в другой таблице, поэтому количество возвращаемых строк невероятно велико. Из-за этого мультипликативного эффекта вы должны использовать COUNT(DISTINCT) вместо COUNT , чтобы получить точные подсчеты. Вы можете увидеть это ниже:

Следующий запрос показывает 7414 строк:

 ВЫБРАТЬ СЧЕТЧИК(*) ИЗ tutorial.crunchbase_acquisitions

Следующий запрос показывает 83 893 строки:

 SELECT COUNT(*) FROM tutorial. crunchbase_investments

Следующий запрос показывает 6 237 396 строк:

 SELECT COUNT(*)
      ИЗ приобретения tutorial.crunchbase_acquisitions
      FULL JOIN tutorial.crunchbase_investments инвестиции
        ON приобретения.acquired_month = инвестиции.funded_month

Конечно, вы могли бы решить эту проблему намного эффективнее, объединив две таблицы по отдельности, а затем соединив их вместе, чтобы подсчеты выполнялись для гораздо меньших наборов данных: приобретения.companies_acquired, Investments.companies_rec_investment ИЗ ( ВЫБЕРИТЕ приобретаете_месяц КАК месяц, COUNT(DISTINCT company_permalink) AS company_acquired ИЗ tutorial. crunchbase_acquisitions СГРУППИРОВАТЬ ПО 1 ) приобретения ПОЛНОЕ СОЕДИНЕНИЕ ( ВЫБЕРИТЕ funded_month AS месяц, COUNT(DISTINCT company_permalink) КАК company_rec_investment ИЗ tutorial.crunchbase_investments СГРУППИРОВАТЬ ПО 1 )инвестиции ON приобретения.месяц = инвестиции.месяц ЗАКАЗАТЬ ПО 1 ДЕСК

`Практическая задача`

Попробуйте См. ответ

`Подзапросы и ОБЪЕДИНЕНИЯ`

В следующем разделе мы возьмем урок, посвященный ОБЪЕДИНЕНИЯМ, снова используя данные Crunchbase:

 SELECT *
  ИЗ tutorial.crunchbase_investments_part1
 СОЮЗ ВСЕХ
 ВЫБРАТЬ *
   ИЗ tutorial.crunchbase_investments_part2

 SELECT COUNT(*) AS total_rows
  ИЗ (
        ВЫБРАТЬ *
          ИЗ tutorial.crunchbase_investments_part1
         СОЮЗ ВСЕХ
        ВЫБРАТЬ *
          ИЗ tutorial. crunchbase_investments_part2
       ) суб

Это довольно просто. Попробуйте сами:

`Практическая задача`

ПопробуйтеСмотреть ответ

`Практическая задача`

ПопробуйтеСмотреть ответ

`Что такое подзапрос в SQL и когда они полезны?`

Подзапрос — это то, что следует из названия: запрос внутри другого запроса. _Истинное начало SQL_. Подзапросы часто используются, когда вам нужно обработать данные в несколько шагов. Для большинства подзапросов, которые вы встретите на практике, внутренний запрос будет выполняться первым и передавать результат внешнему запросу, в который он вложен. . В отличие от CTE, которые обычно являются отдельными Операторы SELECT внутри запроса, подзапросы обычно представляют собой операторы SELECT , вложенные в операторы JOIN , FROM или WHERE в запросе.

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

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

Закрывающие круглые скобки
Имя
Фактический оператор SELECT
Основной запрос, в который он вложен через предложение FROM, WHERE или JOIN

Давайте рассмотрим это на примере , используя пример набора данных jaffle_shop.

 выберите customer_id, count(order_id) как cnt_orders 
 из ( 
 select * from {{ ref('orders') }} 
 ) all_orders 
 group by 1

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

Subquery elements	Example
Enclosing parentheses	✅
Subquery name	`all_orders`
`SELECT` statement	`select * from {{ ref('orders') }}`
Main query it is nested in	`select customer_id, count(order_id) as cnt_orders from all_orders group by 1`

Когда этот запрос действительно выполняется, он сначала запустит самый внутренний запрос. В этом случае он сначала запустит select * from {{ ref('orders') }} . Затем он передаст эти результаты внешнему запросу, где вы получите количество заказов на 9. 0029 идентификатор_клиента .

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

В повседневной жизни вы обычно не формализуете имена различных типов подзапросов, которые вы можете написать, но когда кто-то использует термин «коррелированный подзапрос» на конференции по данным, вам нужно знать, что это означает!

`Вложенные подзапросы`

Вложенные подзапросы — это подзапросы, подобные тому, который вы видели в первом примере: подзапрос, в котором внутренний запрос выполняется первым (и один раз) и передает свой результат основному запросу. Большинство подзапросов, которые вы увидите в реальном мире, скорее всего, будут вложенными подзапросами. Они наиболее полезны, когда вам нужно обрабатывать данные в несколько этапов.

`Совет по отладке подзапросов`

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

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

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

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

`Скалярные и нескалярные подзапросы`

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

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

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

`Подзапрос в соединении`

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

выберите orders.user_id, sum(payments.amount) как life_value from {{ ref('raw_orders') }} как заказы левое соединение ( выберите order_id, сумма from {{ ref('raw_payments') }}

) all_payments on orders.id = payment.order_id сгруппируйте по 15

2 вы видели в первом примере, давайте разберем элементы этого запроса.

5 90 Имя подзапроса0845

Элементы подзапроса	Пример
Закрывающие скобки	✅
`all_payments`
`SELECT` statement	`select order_id, amount from {{ ref('raw_payments') }}`
Main query it is nested in	`select orders. user_id, sum(payments.amount) как life_value from {{ ref('raw_orders') }} как заказы...`

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

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

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

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

 ОБНОВЛЕНИЕ raw_orders 
 set status = 'returned' 
 где order_id in ( 
 выберите order_id 
 из raw_payments 
 где payment_method = 'bank_transfer')

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

CTE	Subquery
Обычно более читаемые, так как CTE можно использовать для того, чтобы дать структуру для вашего запроса	типично. query	Должен объявлять подзапрос каждый раз, когда он используется в запросе
Допускает рекурсивность	Не допускает рекурсивность
CTE должны иметь уникальные CTE_EXPRESSION_NAMES при использовании в запросе	Подзапросы не всегда должны иметь явные имена
CTE нельзя использовать в предложении `WHERE`

Пример подзапроса и CTE

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

Пример подразделения
CTE Пример

SELECT CUSTORD_ID, COUNT (ORDER_ID) в качестве CNT_ORDERS
из (

SELECT * FROD {{ref ('ORDERS')}

) ALL_ORDERS
Группа на 1 9083 3

)}

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

Опять же, выбор использования CTE вместо подзапросов является личным выбором. Может помочь написать ту же функциональность кода в подзапросе и с CTE и посмотреть, что вам более понятно.

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

Хранилище данных	Поддерживает подзапросы?
Snowflake	✅
Amazon Redshift	✅
Google BigQuery	✅
Databricks	✅
Postgres	✅

I'm честно говоря, я не решался начать писать страницу глоссария для подзапросов SQL. Как человека, который использовал CTE почти исключительно в своей работе с данными, я был напуган этой концепцией. Тем не менее, я рад сообщить: подзапросы не так страшны, как я ожидал!

По своей сути подзапросы представляют собой вложенные запросы в основной запрос. Они часто реализуются в предложениях FROM , WHERE и JOIN и используются для написания кода, который строится сам по себе. Несмотря на то, что подзапросы представляют собой SQL, как и любой другой запрос, важно отметить, что подзапросы могут иметь проблемы с читабельностью, структурой и процессом отладки из-за их вложенного характера. Из-за этих недостатков мы рекомендуем по возможности использовать CTE вместо подзапросов.

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

Дополнительная литература

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

Глоссарий: CTE
О важности именования: соглашения об именовании моделей (Часть 1)

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

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

Пересмотр оператора SELECT

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

Клиент

идентификатор клиента

Имя

Фамилия

908:45

Город

Состояние

321

Фрэнк

Лоэ

Даллас

ТХ

455

Эд

Томпсон

Атланта

Г. А.

456

Эд

Томпсон

Атланта

Г.А.

457

Джо

Смит

Майами

458

Фрэнк

Доу

Даллас

ТХ

Заказ

идентификатор заказа

идентификатор клиента

Всего

Дата заказа

321

02.01.2014

455

02.03.2014

456

10. 03.2014

Следующий код SQL представляет собой стандартный оператор SQL, который получает имя и фамилию клиента. " Техас."

Переходим к подзапросу

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

SELECT * FROM Customer

WHERE State IN (SELECT * FROM Order)

Ошибка в предложении WHERE. Условие IN запрашивает значение состояния. Подзапрос возвращает все строки из таблицы Order, поэтому внешнее предложение IN не может определить, к какому столбцу он должен обращаться.

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

Следующий оператор SQL дает вам пример.

SELECT * FROM Customer

WHERE CustomerId IN (SELECT CustomerId FROM Order)

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

идентификатор клиента

Имя

Фамилия

Город

Состояние

321

Фрэнк

Лоэ

Даллас

ТХ

455

Эд

Томпсон

Атланта

Г. А.

456

Эд

Томпсон

Атланта

Г. А.

Возвращенный набор данных соответствует записям клиентов в таблице заказов.

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

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

SELECT * FROM Customer

WHERE CustomerId IN (SELECT CustomerId FROM Order WHERE OrderDate МЕЖДУ '3/1/2014' И '31/31/2014')

При выполнении приведенного выше оператора механизм SQL сначала запускает подзапрос ВЫБЕРИТЕ заявление. Этот подзапрос возвращает набор записей, содержащий только список идентификаторов клиентов, соответствующих отфильтрованному предложению WHERE. В этом примере результатами являются любые заказы, созданные в марте. Внутренний подзапрос возвращает две записи за март или две записи ID клиента.

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

идентификатор клиента

Имя

Фамилия

Город

Состояние

455

Эд

Томпсон

Атланта

Г. А.

456

Эд

Томпсон

Атланта

Г. А.

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

Запросы к внешним базам данных

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

Давайте настроим среду данных для базы данных контента. У вас есть таблица для комментариев к вашим статьям или продуктам. Эта таблица называется «Комментарий» в базе данных контента и содержит идентификатор клиента для комментария.

Вот пример таблицы комментариев.

идентификатор клиента

Идентификатор статьи

Комментарий

457

Тестовый комментарий.

458

Этот товар не для меня.

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

SELECT * FROM Ecommerce.Customer

WHERE Ecommerce.Customer.CustomerId IN (SELECT Content.Comment.CustomerId FROM Content.Comment)

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

Следующим идентификатором является имя таблицы. «Ecommerce.Customer» означает «использовать таблицу Customer в базе данных электронной торговли». Во внутреннем запросе идентифицируются база данных Content.Comment и имя таблицы. Идентификатор CustomerId — это имя столбца, возвращенное из внутреннего запроса. Поскольку у вас есть два комментария в таблице Comment, оператор SQL возвращает две записи. Возвращается следующий набор записей.

идентификатор клиента

Имя

Фамилия

Город

Состояние

457

Джо

Смит

Майами

ФЗ

458

Фрэнк

Доу

Даллас

ТХ

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

Подзапросы также полезны, когда вы хотите ВСТАВИТЬ или ОБНОВИТЬ столбцы. Вы видели, как использовать подзапрос для ВСТАВКИ данных из одной таблицы в другую. Тот же самый тип подзапроса также используется для ОБНОВЛЕНИЯ данных. Предположим, вы добавили столбец даты с именем «OrderDate» в свою таблицу Customer. Теперь ваша таблица Customer выглядит следующим образом.

идентификатор клиента

Имя

Фамилия

Город

Состояние

Дата заказа

321

Фрэнк

Лоэ

Даллас

ТХ

455

Эд

Томпсон

Атланта

Г. А.

456

Эд

908:45

Томпсон

Атланта

Г. А.

457

Джо

Смит

Майами

908:45

458

Фрэнк

Доу

Даллас

ТХ

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

UPDATE Customer

SET OrderDate = (SELECT TOP 1 OrderDate FROM Order WHERE Order.CustomerId=Customer.CustomerId)

Вышеприведенный оператор UPDATE не содержит условия WHERE, поэтому все записи обновляются с указанием даты. Пункт «ТОП 1» является здесь новым. Фраза TOP 1 получает только одну запись из таблицы Order. Если вы не использовали фразу TOP 1 и имели более одной записи для конкретного клиента, SQL возвращает ошибку, поскольку в вашем подзапросе возвращается более одной записи.

Подзапрос сопоставляет значение идентификатора клиента в таблице "Клиент" с идентификатором клиента в таблице "Заказ". Этот результат гарантирует, что вы получите дату, соответствующую правильному идентификатору клиента. Затем подзапрос используется для изменения значения в столбце Customer OrderDate. Подзапрос получает только одну запись, поэтому при наличии нескольких записей нет гарантии, что правильная дата будет обновлена в таблице Customer. Вы можете добавить предложение WHERE во внутренний подзапрос, чтобы получить первую или последнюю дату из таблицы Order. Вы также можете использовать предложение ORDER BY для своего внутреннего запроса. Помните, что внутренний запрос — это стандартный оператор SELECT, который может иметь любое предложение WHERE, чтобы гарантировать, что вы возвращаете правильные данные.

Следующий набор данных показывает вашу новую таблицу Customer.

идентификатор клиента

Имя

Фамилия

Город

Состояние

Дата заказа

321

Фрэнк

Лоэ

Даллас

ТХ

02. 01.2014

455

Эд

Томпсон

908:45

Атланта

Г. А.

02.03.2014

456

Эд

Томпсон

Атланта

Г. А.

10.03.2014

457

Джо

Смит

Майами

НУЛЕВОЙ

458

Фрэнк

Доу

Даллас

ТХ

НУЛЕВОЙ

Обратите внимание, что последние две записи имеют NULL для значений даты. Посмотрите на свой стол заказов. Заказа для клиентов 457 и 458 нет. Результат внутреннего подзапроса равен NULL, поэтому NULL обновляется в вашей таблице.