Сейчас давайте рассмотрим достаточно полезный, в некоторых случаях, оператор CROSS APPLY языка T-SQL, затронем немного теории и, конечно же, рассмотрим примеры использования.
Мы с Вами оператор CROSS APPLY как в справочнике T-SQL, так и в материале про основы программирования на T-SQL не рассматривали. Честно говоря, и в официальной документации данный оператор описан кратко, а он действительно полезный, поэтому сегодня я предлагаю рассмотреть возможности CROSS APPLY, в том числе и те, которые не описаны в документации.
CROSS APPLY
CROSS APPLY – это тип оператора APPLY, который позволяет вызывать табличную функцию для каждой строки внешнего табличного выражения. Вместо табличной функции можно также использовать и подзапрос. Есть еще и другой тип OUTER APPLY, он в отличие от CROSS APPLY возвращает и строки, которые формируют результирующий набор и те, которые этого не делают, т.е. со значениями NULL в столбцах.
Примечание! Примеры ниже будут рассмотрены в MS SQL Server 2008 R2.
Примеры использования CROSS APPLY
Для начала давайте определимся с исходными данными, допустим, у нас есть таблица товаров (Products) и таблица продаж (Sales) вот с такими данными:
А также у нас есть функция (FT_GET_Sales), которая просто выводит список продаж по идентификатору товара.
Пример ее работы:
Пример использования CROSS APPLY с табличной функцией
Теперь допустим, нам необходимо получить продажи не только одного товара, но и других, для этого мы можем использовать оператор CROSS APPLY
Где как Вы понимаете, табличная функция FT_GET_Sales была вызвана для каждой строки таблицы Products.
Пример использования CROSS APPLY с подзапросом
Как уже было сказано выше, CROSS APPLY можно использовать и с подзапросом, для примера давайте допустим, что нам нужно получить последнюю дату продажи каждого товара.
Как видим, после CROSS APPLY у нас идет подзапрос.
Пример использования OUTER APPLY
Если Вы заметили, у нас в исходных данных есть позиция «Клавиатура», по которой не было продаж. И в случае возникновения необходимости получить последнюю дату продажи включая товары, по которым не было продаж, т.е. выводить NULL значения, чтобы мы видели, какие товары не продавались вообще, тип CROSS APPLY можно заменить на OUTER APPLY.
Пример:
Теперь видно, что некоторые товары продавались и последняя дата их продажи такая то, а некоторые товары не продавались, т.е. у них отсутствует дата продажи.
Заметка! Начинающим программистам рекомендую почитать мою книгу «Путь программиста T-SQL», в ней я подробно, с большим количеством примеров, рассказываю про другие полезные возможности языка Transact-SQL.
Оператор CROSS APPLY появился в 
Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server 2005. Он позволяет выполнить соединение двух табличных выражений. При этом каждая строка из левой таблицы сочетается с каждой строкой из правой.
Давайте попробуем разобраться в том, какие преимущества дает нам использование этого нестандартного оператора.
Мы получили просто декартово произведение таблиц Product и Laptop. Аналогичный результат мы можем получить с помощью следующих стандартных запросов:
Поставим теперь более осмысленную задачу.
Для каждого ноутбука дополнительно вывести имя производителя.
Эту задачу мы можем решить с помощью обычного соединения:
С помощью CROSS APPLY решение этой же задачи можно написать так:
«И что тут нового»? — спросите вы. Запрос стал даже более громоздким. Пока да, можно согласиться. Но уже здесь можно заметить весьма важную вещь, которая отличает CROSS APPLY от других видов соединений. А именно, мы используем коррелирующий подзапрос в предложении FROM, передавая в него значения из левого табличного выражения. В данном примере это значения из столбца P.model. Т.е. для каждой строки из левой таблицы правая таблица будет своя.
Поняв это, мы можем воспользоваться данными преимуществами. Рассмотрим следующую задачу.
Для каждого ноутбука дополнительно вывести максимальную цену среди ноутбуков того же производителя.
Эту задачу мы можем решить с помощью коррелирующего подзапроса в предложении SELECT:
Пока решение, использующее CROSS APPLY, будет мало чем отличаться от вышеприведенного:
пример
Применить будет использоваться, когда функция table value в правильном выражении.
создайте таблицу отдела для хранения информации о департаментах. Затем создайте таблицу Employee, в которой хранятся сведения о сотрудниках. Обратите внимание: каждый сотрудник принадлежит отделу, поэтому таблица Employee имеет ссылочную целостность с таблицей Департамента.
Первый запрос выбирает данные из таблицы Department и использует CROSS APPLY для оценки таблицы Employee для каждой записи таблицы Department. Второй запрос просто присоединяется к таблице Департамента с таблицей Employee, и все соответствующие записи создаются.
Если вы посмотрите на результаты, которые они произвели, это точно такой же результат; Чем он отличается от JOIN и как он помогает в написании более эффективных запросов.
Первый запрос в сценарии # 2 выбирает данные из таблицы Department и использует OUTER APPLY для оценки таблицы Employee для каждой записи таблицы Department. Для тех строк, для которых нет совпадений в таблице Employee, эти строки содержат значения NULL, как вы можете видеть в случае строк 5 и 6. Второй запрос просто использует LEFT OUTER JOIN между таблицей Департамента и таблицей Employee. Как и ожидалось, запрос возвращает все строки из таблицы Department; даже для тех строк, для которых нет совпадений в таблице Employee.
Несмотря на то, что вышеупомянутые два запроса возвращают одну и ту же информацию, план выполнения будет бит другим. Но стоить разумно, не будет большой разницы.
Теперь настало время посмотреть, где действительно нужен оператор APPLY. В сценарии №3 я создаю табличную функцию, которая принимает параметр DepartmentID как параметр и возвращает всех сотрудников, принадлежащих этому отделу. Следующий запрос выбирает данные из таблицы Department и использует CROSS APPLY для объединения с созданной нами функцией. Он передает идентификатор отдела для каждой строки из выражения внешней таблицы (в нашем случае таблица Департамента) и оценивает функцию для каждой строки, подобной коррелированному подзапросу. Следующий запрос использует OUTER APPLY вместо CROSS APPLY и, следовательно, в отличие от CROSS APPLY, который возвращает только коррелированные данные, OUTER APPLY также возвращает некоррелированные данные, помещая NULL в отсутствующие столбцы.