Когда я должен использовать Cross Apply over Inner Join?

cross apply иногда позволяет делать то, что вы не можете сделать с inner join.

пример (синтаксическая ошибка):

select F.* from sys.objects O  
inner join dbo.myTableFun(O.name) F   
on F.schema_id= O.schema_id

это синтаксис, потому что при использовании inner join, табличные функции могут принимать только переменные или константы как параметры. (Т. е. параметр функции таблицы не может зависеть от столбца другой таблицы.)

:

select F.* from sys.objects O  
cross apply ( select * from dbo.myTableFun(O.name) ) F  
where F.schema_id= O.schema_id

это законный.

Edit: Или, как вариант, более короткий синтаксис: (by ErikE)

select F.* from sys.objects O  
cross apply dbo.myTableFun(O.name) F
where F.schema_id= O.schema_id

Edit:

Примечание.: Informix 12.10 xC2+ имеет Боковые Производные Таблицы и Postgresql (9.3+) имеет Боковая Подзапросы, который может быть использован для аналогичного эффекта.

считайте, что у вас есть две таблицы.

ГЛАВНАЯ ТАБЛИЦА

x------x--------------------x
| Id   |        Name        |
x------x--------------------x
|  1   |          A         |
|  2   |          B         |
|  3   |          C         |
x------x--------------------x

ПОДРОБНАЯ ТАБЛИЦА

x------x--------------------x-------x
| Id   |      PERIOD        |   QTY |
x------x--------------------x-------x
|  1   |   2014-01-13       |   10  |
|  1   |   2014-01-11       |   15  |
|  1   |   2014-01-12       |   20  |
|  2   |   2014-01-06       |   30  |
|  2   |   2014-01-08       |   40  |
x------x--------------------x-------x

есть много ситуаций, когда нам нужно заменить INNER JOIN С CROSS APPLY.

1. Соедините две таблицы на основе TOP n результаты

подумайте, если нам нужно выбрать Id и Name С Master и последние две даты для каждого Id от Details table.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
INNER JOIN
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D      
    ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID

• SQL FIDDLE

приведенный выше запрос выдает следующий результат.

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
x------x---------x--------------x-------x

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

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
CROSS APPLY
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    WHERE M.ID=D.ID
    ORDER BY CAST(PERIOD AS DATE)DESC
)D

• SQL Скрипка

и формирует следующий результат.

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-08   |  40   |
|   2  |   B     | 2014-01-06   |  30   |
x------x---------x--------------x-------x

вот как это работает. Запрос внутри CROSS APPLY может ссылаться на внешнюю таблицу, где INNER JOIN не удается сделать это (он выдает ошибку компиляции). При поиске последних двух дат, присоединение делается внутри CROSS APPLY то есть,WHERE M.ID=D.ID.

2. Когда нам нужно INNER JOIN функции с помощью функции.

CROSS APPLY смогите быть использовано как замена с INNER JOIN когда нам нужно получить результат из Master таблица и function.

SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
CROSS APPLY dbo.FnGetQty(M.ID) C

а вот и функция

CREATE FUNCTION FnGetQty 
(   
    @Id INT 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ID,PERIOD,QTY 
    FROM DETAILS
    WHERE ID=@Id
)

• SQL FIDDLE

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

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-11   |  15   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-06   |  30   |
|   2  |   B     | 2014-01-08   |  40   |
x------x---------x--------------x-------x

ДОПОЛНИТЕЛЬНОЕ ПРЕИМУЩЕСТВО КРЕСТА ПРИМЕНЯЕТСЯ

APPLY может использоваться в качестве замены для UNPIVOT. Либо CROSS APPLY или OUTER APPLY можно использовать здесь, которые сменный.

считайте, что у вас есть таблица ниже (с именем MYTABLE).

x------x-------------x--------------x
|  Id  |   FROMDATE  |   TODATE     |
x------x-------------x--------------x
|   1  |  2014-01-11 | 2014-01-13   | 
|   1  |  2014-02-23 | 2014-02-27   | 
|   2  |  2014-05-06 | 2014-05-30   | 
|   3  |     NULL    |    NULL      |
x------x-------------x--------------x

запрос ниже.

SELECT DISTINCT ID,DATES
FROM MYTABLE 
CROSS APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)

• SQL FIDDLE

что приносит вам результат

  x------x-------------x
  | Id   |    DATES    |
  x------x-------------x
  |  1   |  2014-01-11 |
  |  1   |  2014-01-13 |
  |  1   |  2014-02-23 |
  |  1   |  2014-02-27 |
  |  2   |  2014-05-06 |
  |  2   |  2014-05-30 | 
  |  3   |    NULL     | 
  x------x-------------x

вот пример, когда CROSS APPLY имеет огромное значение с производительностью:

использование CROSS APPLY для оптимизации соединений между условиями

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

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

опции ограниченный и редко элегантный:

• подзапросы JOIN не могут вводить новые значения в набор данных на основе данных в Родительском запросе (он должен стоять сам по себе).

• UDFs аккуратны, но медленны, поскольку они, как правило, предотвращают параллельные операции. И быть отдельной сущностью может быть хорошей (меньше кода) или плохой (где код) вещью.

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

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

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

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

• повторяющийся код. Что такое наибольшее значение 3 long (CASE...ЕЩЕ...Конец) заявления? Это будет читаем!

  • скажите своим клиентам, чтобы они сами рассчитали эти чертовы вещи.

Я что-то пропустил? Наверное, поэтому не стесняйтесь комментировать. Но эй, крест применить это как находка в таких ситуациях: вы просто добавить простой CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl и вуаля! Ваше новое поле теперь готово к использованию практически так же, как оно всегда было в ваших исходных данных.

значения, введенные через CROSS APPLY can...

• использоваться для создайте одно или несколько вычисляемых полей без добавления проблем производительности, сложности или читаемости в микс
• как и в случае с соединениями, несколько последующих операторов CROSS APPLY могут ссылаться на себя:CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2
• вы можете использовать значения, введенные крестом применяются в последующих условиях соединения
• в качестве бонуса, есть табличное значение функции аспект

Черт, нет ничего, что они не могут сделать!

Cross apply также хорошо работает с полем XML. Если вы хотите выбрать значения узлов в сочетании с другими полями.

например, если у вас есть таблица, содержащая некоторые XML

<root>
    <subnode1>
       <some_node value="1" />
       <some_node value="2" />
       <some_node value="3" />
       <some_node value="4" />
    </subnode1>
</root>

используя запрос

SELECT
       id as [xt_id]
      ,xmlfield.value('(/root/@attribute)[1]', 'varchar(50)') root_attribute_value
  ,node_attribute_value = [some_node].value('@value', 'int')
  ,lt.lt_name   
FROM dbo.table_with_xml xt
CROSS APPLY xmlfield.nodes('/root/subnode1/some_node') as g ([some_node])
LEFT OUTER JOIN dbo.lookup_table lt
ON [some_node].value('@value', 'int') = lt.lt_id

возвращает результат

xt_id root_attribute_value node_attribute_value lt_name
----------------------------------------------------------------------
1     test1            1                    Benefits
1     test1            4                    FINRPTCOMPANY

Я думаю, это должна быть читаемость ;)

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

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

Cross apply можно использовать для замены подзапроса, где вам нужен столбец подзапроса

подзапрос

select * from person p where
p.companyId in(select c.companyId from company c where c.companyname like '%yyy%')

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

select P.*,T.CompanyName
from Person p
cross apply (
    select *
    from Company C
    where p.companyid = c.companyId and c.CompanyName like '%yyy%'
) T

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

SQL Server CROSS APPLY и OUTER APPLY over JOINS

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

разница в использовании приходит, когда вы должны сделать запрос, как это:

CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment(@DeptID AS INT)  
RETURNS TABLE 
AS 
RETURN 
   ( 
   SELECT * FROM Employee E 
   WHERE E.DepartmentID = @DeptID 
   ) 
GO 
SELECT * FROM Department D 
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)

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

Ну я не уверен, если это квалифицируется как повод использовать кросс применить против внутреннего соединения, но этот запрос был дан ответ в сообщение на форуме через Крест применить, поэтому я не уверен, если есть способ equalivent через внутреннее соединение:

Create PROCEDURE [dbo].[Message_FindHighestMatches]

-- Declare the Topical Neighborhood
@TopicalNeighborhood nchar(255)

как Начинайте

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

Create table  #temp
(
    MessageID         int,
    Subjects          nchar(255),
    SubjectsCount    int
)

Insert into #temp Select MessageID, Subjects, SubjectsCount From Message

Select Top 20 MessageID, Subjects, SubjectsCount,
    (t.cnt * 100)/t3.inputvalues as MatchPercentage

From #temp 

cross apply (select count(*) as cnt from dbo.Split(Subjects,',') as t1
             join dbo.Split(@TopicalNeighborhood,',') as t2
             on t1.value = t2.value) as t
cross apply (select count(*) as inputValues from dbo.Split(@TopicalNeighborhood,',')) as t3

Order By MatchPercentage desc

drop table #temp

конец

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

Я предоставил скрипку SQL ниже, которая показывает простой пример того, как вы можете использовать CROSS APPLY для выполнения сложных логических операций над вашим набором данных без каких-либо беспорядков. Это не трудно экстраполировать отсюда более сложные проведенные расчеты.

http://sqlfiddle.com/#!3/23862/2

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

в отличие от соединения, корреляция между входами не допускается.

говоря о корреляции в операторе APPLY, я имею в виду, что с правой стороны мы можем поставить:

• производная таблица - как коррелированный подзапрос с псевдонимом
• функция с табличным значением-концептуальное представление с параметрами, где параметр может ссылаться на левую сторону

Как может возвращать несколько столбцов и строк.

на это уже ответили очень хорошо технически, но позвольте мне привести конкретный пример того, как это чрезвычайно полезно:

допустим есть две таблицы Customer и Order. Клиенты имеют много заказов.

Я хочу создать представление, которое дает мне информацию о клиентах, и самый последний заказ они сделали. С помощью just JOINS это потребует некоторых самосоединений и агрегации, что не очень красиво. Но с крестом применить, его супер легко:

SELECT *
FROM Customer
CROSS APPLY (
  SELECT TOP 1 *
  FROM Order
  WHERE Order.CustomerId = Customer.CustomerId
  ORDER BY OrderDate DESC
) T