Подзапросы и временные таблицы

131

Работа с базами данных в .NET Framework --- SQL Server 2012 --- Подзапросы и временные таблицы

Подзапросы

Во всех рассмотренных ранее примерах значения столбцов сравниваются с выражением, константой или набором констант. Кроме таких возможностей сравнения язык Transact-SQL позволяет сравнивать значения столбца с результатом другой инструкции SELECT. Такая конструкция, где предложение WHERE инструкции SELECT содержит одну или больше вложенных инструкций SELECT, называется подзапросом (subquery). Первая инструкция SELECT подзапроса называется внешним запросом (outer query), а внутренняя инструкция (или инструкции) SELECT, используемая в сравнении, называется вложенным запросом (inner query). Первым выполняется вложенный запрос, а его результат передается внешнему запросу. Вложенные запросы также могут содержать инструкции INSERT, UPDATE и DELETE.

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

Независимый подзапрос может применяться со следующими операторами:

операторами сравнения;
оператором IN;
операторами ANY и ALL.

Подзапросы и операторы сравнения

Использование оператора равенства (=) в независимом подзапросе показано в примере ниже:

USE SampleDb;

SELECT FirstName, LastName
    FROM Employee
    WHERE DepartamentNumber = (SELECT Number
                               FROM Department
                               WHERE DepartmentName = 'Исследования');

В этом примере происходит выборка имен и фамилий сотрудников отдела 'Исследования'. Результат выполнения этого запроса:

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

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

USE SampleDb;

SELECT FirstName, LastName
    FROM Employee
    WHERE DepartamentNumber = 'd1';

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

Подзапросы и оператор IN

Оператор IN позволяет определить набор выражений (или констант), которые затем можно использовать в поисковом запросе. Этот оператор можно использовать в подзапросах при таких же обстоятельствах, т.е. когда вложенный запрос возвращает набор значений. Использование оператора IN в подзапросе показано в примере ниже:

USE SampleDb;

SELECT FirstName, LastName
    FROM Employee
    WHERE DepartamentNumber IN (SELECT Number
                               FROM Department
                               WHERE DepartmentName = 'Исследования')

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

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

USE SampleDb;

SELECT FirstName, LastName
    FROM Employee
    WHERE ID IN 
        (SELECT EmpId
         FROM Works_on
         WHERE ProjectNumber IN
            (SELECT Number
             FROM Project
             WHERE ProjectName = 'Apollo')
        )

В этом примере происходит выборка фамилий всех сотрудников, работающих над проектом Apollo. Самый глубокий вложенный запрос выбирает из таблицы ProjectNumber значение p1. Этот результат передается следующему вышестоящему запросу, который обрабатывает столбец ProjectNumber в таблице Works_on. Результатом этого запроса является набор табельных номеров сотрудников: (10102, 29346, 9031, 28559). Наконец, самый внешний запрос выводит фамилии сотрудников, чьи номера были выбраны предыдущим запросом.

Подзапросы и операторы ANY и ALL

Операторы ANY и ALL всегда используются в комбинации с одним из операторов сравнения. Оба оператора имеют одинаковый синтаксис:

column_name operator [ANY | ALL] query


Соглашения по синтаксису

Параметр operator обозначает оператор сравнения, а параметр query - вложенный запрос. Оператор ANY возвращает значение true (истина), если результат соответствующего вложенного запроса содержит хотя бы одну строку, удовлетворяющую условию сравнения. Ключевое слово SOME является синонимом ANY. Использование оператора ANY показано в примере ниже:

USE SampleDb;

SELECT DISTINCT EmpId, ProjectNumber, Job
    FROM Works_on
    WHERE EnterDate > ANY
        (SELECT EnterDate
         FROM Works_on);

В этом примере происходит выборка табельного номера, номера проекта и названия должности для сотрудников, которые не затратили большую часть своего времени при работе над одним из проектов. Каждое значение столбца EnterDate сравнивается со всеми другими значениями этого же столбца. Для всех дат этого столбца, за исключением самой ранней, сравнение возвращает значение true (истина), по крайней мере, один раз. Строка с самой ранней датой не попадает в результирующий набор, поскольку сравнение ее даты со всеми другими датами никогда не возвращает значение true (истина). Иными словами, выражение "EnterDate > ANY (SELECT EnterDate FROM Works_on)" возвращает значение true, если в таблице Works_on имеется любое количество строк (одна или больше), для которых значение столбца EnterDate меньше, чем значение EnterDate текущей строки. Этому условию удовлетворяют все значения столбца EnterDate, за исключением наиболее раннего.

Оператор ALL возвращает значение true, если вложенный запрос возвращает все значения, обрабатываемого им столбца.

Настоятельно рекомендуется избегать использования операторов ANY и ALL. Любой запрос с применением этих операторов можно сформулировать лучшим образом посредством функции EXISTS, которая рассматривается далее в следующей статье. Кроме этого, семантическое значение оператора ANY можно легко принять за семантическое значение оператора ALL и наоборот.

Временные таблицы

Временная таблица - это объект базы данных, который хранится и управляется системой базы данных на временной основе. Временные таблицы могут быть локальными или глобальными. Локальные временные таблицы представлены физически, т.е. они хранятся в системной базе данных tempdb. Имена временных таблиц начинаются с префикса #, например #table_name.

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

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

USE SampleDb;

CREATE TABLE #project_temp (
    Number NCHAR(4) NOT NULL,
    Name NCHAR(25) NOT NULL
);

-- Аналог предыдущей инструкции со вставкой
-- данных во временную таблицу из существующей
-- таблицы Project
SELECT Number, ProjectName
    INTO #project_temp
    FROM Project;

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