Quando devo usar Cross aplicar sobre a junção interna?

cross apply Às vezes permite-nos fazer coisas que não podemos fazer com inner join.

Exemplo (um erro de sintaxe):

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

Este é um erro de sintaxe , Porque, quando usado com inner join, as funções da tabela só podem tomar variáveis ou constantes como parâmetros. (I. e., O parâmetro da função da tabela não pode depender da coluna de outra tabela.)

No entanto:

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

Editar: Ou, em alternativa, uma sintaxe mais Curta: (por ErikE)

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

Editar:

Nota: Informix 12. 10 xC2+ has tabelas derivadas laterais e Postgresql (9.3+) tem Subqueries laterais que podem ser usadas para um efeito semelhante.

TABELA PRINCIPAL

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

TABELA DE DETALHES

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

Há muitas situações em que precisamos de substituir INNER JOIN por CROSS APPLY.

1. Juntar duas tabelas com base nos resultados TOP n

Considere se precisamos selecionar Id e Name de Master e as duas últimas datas para cada Id de 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

• VIOLINO SQL

A a consulta acima gera o seguinte resultado.

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

Veja, ele gerou resultados para as duas últimas datas com as duas últimas datas Id e depois juntou estes registros apenas na consulta externa em Id, o que está errado. Para conseguir isso, precisamos usar 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

• VIOLINO SQL

E forma o seguinte resultado.

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

2. Quando precisamos da funcionalidade INNER JOIN usando funções.

CROSS APPLY pode ser usado como substituto de INNER JOIN Quando precisamos de obter resultados da tabela Master e de um function.

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

E aqui está a função

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

• VIOLINO SQL

Que gerou o seguinte resultado

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

VANTAGEM ADICIONAL DA APLICAÇÃO CRUZADA

APPLY pode ser utilizado como substituto de UNPIVOT. Pode utilizar-se aqui CROSS APPLY ou OUTER APPLY, que são permutáveis.

Considere que tem a tabela abaixo (nomeado 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

A consulta está em baixo.

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

• VIOLINO SQL

O que te traz o resultado

  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

Aqui está um exemplo quando a aplicação CROSS faz uma enorme diferença com o desempenho:

Usar CROSS aplicar para otimizar as ligações entre as condições

Note que, além de substituir as juntas internas, Você também pode reutilizar códigos como datas truncadas sem pagar a penalidade de desempenho por envolver UDFs escalares, por exemplo: Calculando a terceira quarta-feira do mês com UDFs inline

Parece-me que CROSS APPLY pode preencher uma certa lacuna ao trabalhar com campos calculados em consultas complexas/aninhadas, e torná-los mais simples e legíveis.

Exemplo simples: você tem um DoB e você quer apresentar vários campos relacionados com a idade que também irá depender de outras fontes de dados (como o emprego), como a idade, um grupo, AgeAtHiring, Minimumretidato, etc. para uso em sua aplicação de usuário final (Pivotables Excel, por exemplo).

As opções são limitadas e raramente elegante:

• As subqueries do JOIN não podem introduzir novos valores no conjunto de dados com base em dados na consulta-mãe (ele deve permanecer por conta própria).

• UDFs são limpos, mas lentos como eles tendem a evitar operações paralelas. E ser uma entidade separada pode ser uma coisa boa (menos código) ou má (onde está o código).

• Mesas de junção. Às vezes eles podem trabalhar, mas em breve você está se juntando a subquadorias com toneladas de sindicatos. Grande bagunca.

• Crie mais uma vista de propósito único, assumindo que os seus cálculos não requerem dados obtidos a meio da sua consulta principal.

• Mesas intermediárias. Sim... isso geralmente funciona, e muitas vezes uma boa opção como eles podem ser indexados e rápidos, mas o desempenho também pode cair devido à atualização de declarações não sendo paralelas e não permitindo que as fórmulas em cascata (resultados de reutilização) para atualizar vários campos dentro da mesma declaração. E às vezes preferias fazer as coisas de uma só vez.

• Perguntas de nidificação. Sim em qualquer ponto você pode colocar parênteses em toda a sua consulta e usá-lo como um subquery sobre o qual você pode manipular os dados de fonte e campos calculados da mesma forma. Mas não podes fazer isto muito antes que fique feio. Muito feio.

• Repito código. Qual é o maior valor de 3 longo (caso...MAIS...Declarações? Isso vai ser legível!
  Diz aos teus clientes para calcularem as coisas. eles mesmos.

Os valores introduzidos através da aplicação cruzada podem...

• ser usado para criar um ou vários campos calculados sem adicionar problemas de desempenho, complexidade ou legibilidade Ao misturar
• tal como com as juntas, várias declarações de Aplicação cruzada subsequentes podem referir-se a si próprias: CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2
• pode utilizar os valores introduzidos por uma cruz nas condições de junção subsequentes
• como bónus, há o aspecto da função do valor da Tabela

A aplicação cruzada também funciona bem com um campo XML. Se você deseja selecionar os valores do nó em combinação com outros campos.

Por exemplo, se tiver uma tabela contendo algum xml

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

Usando a consulta

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

Vai devolver um resultado

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

Acho que deve ser legível;)

CROSS APPLY será um pouco único para as pessoas que lêem dizer-lhes que está a ser usado um UDF que será aplicado a cada linha a partir da mesa à esquerda.

O Cross apply pode ser usado para substituir o subquery onde você precisa de uma coluna do subquery

Subquery

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

Aqui não serei capaz de seleccionar as colunas da tabela da empresa então, usando 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

Aqui está um artigo que explica tudo, com a sua diferença de desempenho e uso sobre as juntas.

SQL Server CROSS APPLY and OUTER APPLY over JOINS

Como sugerido neste artigo, não há diferença de desempenho entre eles para operações normais de junção (interna e cruzada).

A diferença de Utilização chega quando você tem que fazer uma pesquisa como esta:

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)

Bem, eu não tenho certeza se isso se qualifica como uma razão para usar Cross Apply versus Inner Join, mas esta consulta foi respondida para mim em um Post do fórum usando Cross Apply, então eu não tenho certeza se existe um método equalivent usando Inner Join:

Create PROCEDURE [dbo].[Message_FindHighestMatches]

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

Como BEGIN

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

FIM

Eu forneci um violino SQL abaixo que mostra um exemplo simples de como você pode usar CROSS APPLY para realizar operações lógicas complexas em seu conjunto de dados sem que as coisas fiquem confusas. Não é difícil extrapolar daqui cálculos mais complexos.

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

A essência do operador aplicar é permitir a correlação entre o lado esquerdo e direito do operador na cláusula FROM.

Em contraste com a junção, a correlação entre entradas não é permitida.

• um quadro derivado - como um subcontingente correlacionado com um nome falso
• a table valued function - a conceptual view with parameters, where the parameter can refer to the table valued function lado esquerdo

Ambos podem devolver múltiplas colunas e linhas.

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