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

Какова основная цель использования CROSS APPLY ?

Я прочитал (смутно, через сообщения в Интернете), что cross apply может быть более эффективным при выборе больших наборов данных, если вы разделяете. (Пейджинг приходит на ум)

Я также знаю, что CROSS APPLY не требует UDF в качестве таблицы справа.

В большинстве запросов INNER JOIN (отношения один-ко-многим) я мог бы переписать их для использования CROSS APPLY, но они всегда дают мне эквивалентные планы выполнения.

Кто-нибудь может дать мне хороший пример того, когда CROSS APPLY имеет значение в тех случаях, когда INNER JOIN также будет работать?


Редактировать:

Вот тривиальный пример, где планы выполнения точно такие же. (Покажите мне, где они отличаются и где cross apply быстрее / эффективнее)

create table Company (
    companyId int identity(1,1)
,   companyName varchar(100)
,   zipcode varchar(10) 
,   constraint PK_Company primary key (companyId)
)
GO

create table Person (
    personId int identity(1,1)
,   personName varchar(100)
,   companyId int
,   constraint FK_Person_CompanyId foreign key (companyId) references dbo.Company(companyId)
,   constraint PK_Person primary key (personId)
)
GO

insert Company
select 'ABC Company', '19808' union
select 'XYZ Company', '08534' union
select '123 Company', '10016'


insert Person
select 'Alan', 1 union
select 'Bobby', 1 union
select 'Chris', 1 union
select 'Xavier', 2 union
select 'Yoshi', 2 union
select 'Zambrano', 2 union
select 'Player 1', 3 union
select 'Player 2', 3 union
select 'Player 3', 3 


/* using CROSS APPLY */
select *
from Person p
cross apply (
    select *
    from Company c
    where p.companyid = c.companyId
) Czip

/* the equivalent query using INNER JOIN */
select *
from Person p
inner join Company c on p.companyid = c.companyId
вопрос задан 16.07.2009
Jeff Meatball Yang
19137 репутация

13 ответов


  • 578 рейтинг

    Кто-нибудь может дать мне хороший пример того, когда CROSS APPLY имеет значение в тех случаях, когда INNER JOIN также будет работать?

    См. Статью в моем блоге для подробного сравнения производительности:

    CROSS APPLY лучше работает с вещами, у которых нет простого условия JOIN.

    Этот выбирает 3 последних записей из t2 для каждой записи из t1:

    SELECT  t1.*, t2o.*
    FROM    t1
    CROSS APPLY
            (
            SELECT  TOP 3 *
            FROM    t2
            WHERE   t2.t1_id = t1.id
            ORDER BY
                    t2.rank DESC
            ) t2o
    

    Это не может быть легко сформулировано с условием INNER JOIN.

    Вы могли бы сделать что-то подобное, используя CTE и оконную функцию:

    WITH    t2o AS
            (
            SELECT  t2.*, ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rn
            FROM    t2
            )
    SELECT  t1.*, t2o.*
    FROM    t1
    INNER JOIN
            t2o
    ON      t2o.t1_id = t1.id
            AND t2o.rn <= 3
    

    , но это менее читабельно и, вероятно, менее эффективно.

    Обновление:

    Только что проверил.

    master - это таблица из примерно 20,000,000 записей с PRIMARY KEY на id.

    Этот запрос:

    WITH    q AS
            (
            SELECT  *, ROW_NUMBER() OVER (ORDER BY id) AS rn
            FROM    master
            ),
            t AS 
            (
            SELECT  1 AS id
            UNION ALL
            SELECT  2
            )
    SELECT  *
    FROM    t
    JOIN    q
    ON      q.rn <= t.id
    

    работает почти 30 секунд, а этот:

    WITH    t AS 
            (
            SELECT  1 AS id
            UNION ALL
            SELECT  2
            )
    SELECT  *
    FROM    t
    CROSS APPLY
            (
            SELECT  TOP (t.id) m.*
            FROM    master m
            ORDER BY
                    id
            ) q
    

    мгновенно.

    ответ дан Quassnoi, с репутацией 321748, 16.07.2009
  • 178 рейтинг

    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
    

    Это законно.

    Редактировать: Или, альтернативно, более короткий синтаксис: (ErikE)

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

    Редактировать:

    Примечание: Информикс 12. 10 xC2 + имеет боковых производных таблиц и Postgresql (9. 3+) имеет боковых подзапросов , которые можно использовать для аналогичного эффекта.

    ответ дан nurettin, с репутацией 7290, 3.03.2011
  • 116 рейтинг

    Предположим, у вас есть две таблицы.

    ОСНОВНОЙ СТОЛ

    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
    

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

    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
    

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

    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
    )
    

    , который генерирует следующий результат

    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
    

    ДОПОЛНИТЕЛЬНОЕ ПРЕИМУЩЕСТВО CROSS APPLY

    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)
    

    , который приносит вам результат

      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
    
    ответ дан Sarath Avanavu, с репутацией 10088, 26.02.2015
  • 37 рейтинг

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

    Использование CROSS APPLY для оптимизации соединений в условиях МЕЖДУ

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

    ответ дан A-K, с репутацией 13726, 16.07.2009
  • 33 рейтинг

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

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

    Варианты ограничены и редко элегантны:

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

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

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

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

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

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

    • Повторяющийся код. Что является наибольшим значением 3 long (CASE. , , ELSE. , , КОНЕЦ) заявления? Это будет читабельным!

      • Скажите вашим клиентам самим подсчитать проклятые вещи.

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

    Значения, введенные через CROSS APPLY, могут. , ,

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

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

    ответ дан mtone, с репутацией 856, 11.06.2012
  • 12 рейтинг

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

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

    
        
           
           
           
           
        
    
    

    Использование запроса

    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
    
    ответ дан Chris, с репутацией 121, 1.02.2013
  • 5 рейтинг

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

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

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

    ответ дан shahkalpesh, с репутацией 28888, 16.07.2009
  • 4 рейтинг

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

    подзапрос

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

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

    select P.*,T.CompanyName
    from Person p
    cross apply (
        select *
        from Company C
        where p.companyid = c.companyId and c.CompanyName like '%yyy%'
    ) T
    
    ответ дан balaji dileep kumar, с репутацией 116, 11.12.2014
  • 3 рейтинг

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

    SQL Server CROSS APPLY и OUTTER APPLY поверх JOINS

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

    enter image description here

    Разница в использовании возникает, когда вам нужно выполнить запрос, подобный следующему:

    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)
    

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

    ответ дан Shanid, с репутацией 413, 21.03.2016
  • 2 рейтинг

    Ну, я не уверен, является ли это причиной для использования Cross Apply против Inner Join, но на этот вопрос я получил ответ в сообщении на форуме с использованием Cross Apply, поэтому я не уверен, существует ли такой же метод с использованием Inner Join:

    Create PROCEDURE [dbo].[Message_FindHighestMatches]
    
    -- Declare the Topical Neighborhood
    @TopicalNeighborhood nchar(255)
    

    AS НАЧАЛО

    -- 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
    

    КОНЕЦ

    ответ дан user1054326, с репутацией 302, 8.03.2012
  • 0 рейтинг

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

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

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

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

    Оба могут возвращать несколько столбцов и строк.

    ответ дан Raf, с репутацией 406, 29.04.2018
  • 0 рейтинг

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

    Допустим, у вас есть две таблицы: клиент и заказ. У клиентов много заказов.

    Я хочу создать представление, в котором будут представлены подробные сведения о клиентах и ​​их последнем заказе. С просто JOINS это потребует некоторых самостоятельных объединений и агрегации, что не очень красиво. Но с Cross Apply это супер просто:

    SELECT *
    FROM Customer
    CROSS APPLY (
      SELECT TOP 1 *
      FROM Order
      WHERE Order.CustomerId = Customer.CustomerId
      ORDER BY OrderDate DESC
    ) T
    
    ответ дан Apneal, с репутацией 33, 9.07.2018
  • 0 рейтинг

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

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

    http: // sqlfiddle. ком / #! 3/23862/2

    ответ дан mrmillsy, с репутацией 185, 6.10.2015