Связанные подзапросы

197 Исходники баз данных

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

USE SampleDb;

SELECT LastName
    FROM Employee
    WHERE 'p3' IN
        (SELECT ProjectNumber
         FROM Works_on
         WHERE Works_on.EmpId = Employee.Id);

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

Давайте проследим, как система может выполнять запрос в этом примере. Сначала система выбирает первую строку таблицы Employee (для внешнего запроса) и сравнивает табельный номер сотрудника в этом столбце (25348) со значениями столбца Works_on.EmpId вложенного запроса. Поскольку для этого сотрудника имеется только одно значение ProjectNumber равное p2, вложенный запрос возвращает значение p2. Это единственное значение результирующего набора вложенного запроса не равно значению p3 внешнего запроса, условие внешнего запроса (WHERE 'p3' IN... ) не удовлетворяется и, следовательно, внешний запрос не возвращает никаких строк для этого сотрудника.

Далее система берет следующую строку таблицы Employee и снова сравнивает номера сотрудников в обеих таблицах. Для этой строки в таблице Works_on есть две строки, для которых значение ProjectNumber равно p1 и p3 соответственно. Следовательно, вложенный запрос возвращает результат p1 и p3. Значение одного из элементов этого результирующего набора равно константе p3, поэтому условие удовлетворяется, и отображается соответствующее значение второй строки столбца LastName ('Фролов'). Такой же обработке подвергаются все остальные строки таблицы Employee, и в конечном результате возвращается набор из трех строк.

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

Подзапросы и функция EXISTS

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

USE SampleDb;

SELECT LastName
    FROM Employee
    WHERE EXISTS
        (SELECT *
         FROM Works_on
         WHERE Employee.Id = Works_on.EmpId
            AND ProjectNumber = 'p1');

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

Давайте проследим, как Database Engine может обрабатывать запрос в этом примере. Сначала внешний запрос рассматривает первую строку таблицы Employee (сотрудник Фролов). Далее функция EXISTS определяет, есть ли в таблице Works_on строки, чьи номера сотрудников совпадают с номером сотрудника в текущей строке во внешнем запросе и чей ProjectNumber равен p1. Поскольку сотрудник Фролов не работает над проектом p1, вложенный запрос возвращает пустой набор, вследствие чего функция EXISTS возвращает значение false. Таким образом, сотрудник Фролов не включается в конечный результирующий набор. Этому процессу подвергаются все строки таблицы Employee, после чего выводится конечный результирующий набор.

В примере ниже показано использование функции NOT EXISTS:

USE SampleDb;

SELECT LastName
    FROM Employee
    WHERE NOT EXISTS
        (SELECT *
         FROM Department
         WHERE Employee.DepartamentNumber = Department.Number
            AND Location = 'Санкт-Петербург');

В этом примере происходит выборка фамилий сотрудников, чей отдел не расположен в Санкт-Петербурге.

Список выбора инструкции SELECT во внешнем запросе с функцией EXISTS не обязательно должен быть в форме SELECT *, как в предыдущем примере. Можно использовать альтернативную форму SELECT colum_list, где column_list представляет список из одного или нескольких столбцов таблицы. Обе формы равнозначны, потому что функция EXISTS только проверяет на наличие (или отсутствие) строк в результирующем наборе. По этой причине в данном случае правильнее использовать форму SELECT *.

Что использовать, соединения или подзапросы?

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

Преимущества подзапросов

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

USE SampleDb;

SELECT EmpId, EnterDate
    FROM Works_on
    WHERE EnterDate = (SELECT MIN(EnterDate)
                       FROM Works_on);

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

Преимущества соединений

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

USE SampleDb;

SELECT Employee.Id, Employee.LastName, Job
    FROM Employee, Works_on
    WHERE Employee.Id = Works_on.EmpId
        AND EnterDate = '2007-04-15';

В этом примере происходит выборка информации о всех сотрудниках (табельный номер, фамилия и должность), которые начали участвовать в работе над проектом с 15 апреля 2007 г. Список выбора инструкции SELECT в запросе содержит столбцы Id и LastName из таблицы Employee и столбец Job из таблицы Works_on. По этой причине решение с применением подзапроса возвратило бы ошибку, поскольку подзапросы могут отображать информацию только из внешней таблицы.

Пройди тесты
Лучший чат для C# программистов