Когда я должен использовать крест применить поверх внутреннего соединения?

Ответ 1

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

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

• INNER JOIN vs. CROSS APPLY

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

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

Update:

Только что отмечен.

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

мгновенно.

Ответ 2

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 функции таблицы могут принимать только переменные или константы в качестве параметров. (I.e., параметр функции таблицы не может зависеть от другого столбца таблицы.)

Однако:

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

Это законно.

Edit: Или, альтернативно, более короткий синтаксис: (по 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+) имеет Боковые подзапросы, которые могут быть использованы для аналогичного эффекта.

Ответ 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 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   |
|   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 [email protected]
)

• 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

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

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

Ответ 4

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

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

Опции ограничены и редко изящны:

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

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

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

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

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

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

• Повторяющийся код. Какова максимальная ценность трех длинных (CASE... ELSE... END) заявлений? Это будет читаемо!

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

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

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

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

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

Ответ 5

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

Использование CROSS APPLY для оптимизации объединений в условиях МЕЖДУНАРОДНОЙ РАБОТЫ

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

Ответ 6

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

Например, если у вас есть таблица, содержащая некоторый 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

Ответ 7

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

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

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

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

Ответ 8

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

Ответ 9

Я предполагаю, что он должен быть читабельным;)

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

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

Ответ 10

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

SQL Server CROSS APPLY и OUTER APPLY через JOINS

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

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

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" не может быть связан. " Здесь значение передается функции, поскольку каждая строка читать. Звучит здорово для меня.:)

Ответ 11

Ну, я не уверен, что это может послужить основанием для использования Cross Apply по сравнению с Inner Join, но этот запрос был получен для меня в сообщении форума с помощью Cross Apply, поэтому я не уверен, есть ли метод equalivent с использованием Inner Регистрация:

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

КОНЕЦ

Ответ 12

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

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

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

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

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

Ответ 13

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

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

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