Содержание

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. Запрос становится более структурированным, его легче читать.
    2. Результат, загруженный во временную таблицу можно использовать несколько раз, и при этом нет необходимости заново выполнять запрос, чтобы этот результат получить. А вложенный запрос будет каждый раз выполняться заново, излишне загружая ресурсы системы.

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

    Запросы 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
    snumsnamecitycomm
    1КоловановМосква10
    2ПетровТверь25
    3ПлотниковМосква22
    4КучеровСанкт-Петербург28
    5МалкинСанкт-Петербург18
    6ШипачевЧелябинск30
    7МозякинОдинцово25
    8ПроворовМосква25

    Пример таблицы покупателей CUSTOMERS
    cnumcnamecityratingsnum
    1ДесновМосква906
    2КрасновМосква957
    3КирилловТверь963
    4ЕрмолаевОбнинск983
    5КолесниковСерпухов985
    6ПушкинЧелябинск904
    7БелыйОдинцово851
    8ЧудиновМосква893
    9ПроворовМосква952
    10ЛосевОдинцово758

    Пример таблицы заказов ORDERS
    onumamtodate(YEAR)cnumsnum
    1001420201394
    10026532005107
    1003960201621
    1004320201633
    1005200201554
    10062560201454
    10071200201371
    100850201713
    1009564201237
    1010900201868
    Вывести суммы заказов и даты, которые проводил продавец с фамилией «Плотников».

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

    
    					
        SELECT amt, odate
        FROM orders 
        WHERE snum = 3
    					
    				
    Результат работы
    amtodate
    3202016
    502017

    Такой запрос, очевидно, не очень универсален, если нам захочется выбрать тоже самое для другого продавца, то всегда придется определять его 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 выдаст фамилию этого продавца.

    Результат запроса
    snumsname
    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
    idnamecity_id
    1Колованов1
    2Петров3
    3Плотников12
    4Кучеров4
    5Малкин2
    6Иванов13

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

    Таблица городов City
    idnamepopulation
    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.idPerson.namePerson.city_idCity.idCity.nameCity.population
    1Колованов11Москва100
    2Петров33Тверь22
    4Кучеров44Санкт-Петербург80
    5Малкин22Нижний Новгород25
    INNER JOIN

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

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

    LEFT JOIN

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

    LEFT JOIN
    
    					
        SELECT * FROM Person
        LEFT JOIN
            City
        ON Person.city_id = City.id
    					
    				
    Для записей неудовлетворяющих условия объединения поля правой таблицы заполняются значениями NULL
    Результат запроса
    Person.idPerson. namePerson.city_idCity.idCity.nameCity.population
    1Колованов11Москва100
    2Петров33Тверь22
    3Плотников12NULLNULLNULL
    4Кучеров44Санкт-Петербург80
    5Малкин22Нижний Новгород25
    6Иванов13NULLNULLNULL

    RIGHT JOIN

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

    RIGHT JOIN
    
    					
        SELECT * FROM Person
        RIGHT JOIN
            City
        ON Person.city_id = City.id
    					
    				
    Для записей неудовлетворяющих условия объединения поля левой таблицы заполняются значениями NULL
    Результат запроса
    Person. idPerson.namePerson.city_idCity.idCity.nameCity.population
    1Колованов11Москва100
    2Петров33Тверь22
    4Кучеров44Санкт-Петербург80
    5Малкин22Нижний Новгород25
    NULLNULLNULL5Выборг18
    NULLNULLNULL6Челябинск30
    NULLNULLNULL7Одинцово5
    NULLNULLNULL8Павлово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С 8.3 необходимо использовать вложенные запросы.

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

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

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

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

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

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

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

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

    Как лучше?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Справедливости ради стоит отметить, что тот же результат можно достигнуть с помощью функции ИМЕЮЩИЕ языка запросов 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
     

    Набор данных нередко бывает разделен на несколько частей, особенно если данные проходят через 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
     

    Набор данных нередко бывает разделен на несколько частей, особенно если данные проходят через 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
     

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

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

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

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

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

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

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

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

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

    Что такое подзапрос в 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
    сгруппируйте по 1
    5

    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

    Джо

    Смит

    Майами

    FL

    458

    Фрэнк

    Доу

    Даллас

    ТХ

    Заказ

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

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

    Всего

    Дата заказа

    1

    321

    10

    02.01.2014

    2

    455

    40

    02.03.2014

    3

    456

    20

    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

    4

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

    458

    5

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

    Вам поручено получить список клиентов, которые оставили комментарии к вашим продуктам. У вас есть таблица в базе данных 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

    Джо

    Смит

    Майами

    FL

    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

    Джо

    Смит

    Майами

    FL

    НУЛЕВОЙ

    458

    Фрэнк

    Доу

    Даллас

    ТХ

    НУЛЕВОЙ

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