sql - ejemplos - outer apply vs left join

¿Cuándo debo usar Cross Apply sobre Inner Join?

Question

¿Cuál es el propósito principal de usar CROSS APPLY ?

He leído (vagamente, a través de publicaciones en Internet) que cross apply pueden ser más eficientes cuando se seleccionan grandes conjuntos de datos si se está particionando. (La paginación viene a la mente)

También sé que CROSS APPLY no requiere un UDF como la tabla de la derecha.

En la mayoría de las consultas de INNER JOIN (relaciones uno a muchos), podría reescribirlas para usar CROSS APPLY , pero siempre me dan planes de ejecución equivalentes.

¿Puede alguien darme un buen ejemplo de cuándo CROSS APPLY hace una diferencia en los casos en que INNER JOIN también funcionará?

Editar:

Aquí hay un ejemplo trivial, donde los planes de ejecución son exactamente los mismos. (Muéstrame uno donde difieran y donde cross apply sea ​​más rápida / más eficiente)

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

Answer 1

¿Puede alguien darme un buen ejemplo de cuándo CROSS APPLY hace una diferencia en aquellos casos en los que INNER JOIN también funcionará?

Vea el artículo en mi blog para una comparación detallada del rendimiento:

• INNER JOIN vs. CROSS APPLY

CROSS APPLY funciona mejor en cosas que no tienen una condición JOIN CONEXIÓN.

Este selecciona 3 últimos registros de t2 para cada registro de 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

No se puede formular fácilmente con una condición de INNER JOIN .

Probablemente podrías hacer algo así usando la función de ventana y 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

, pero esto es menos legible y probablemente menos eficiente.

Actualizar:

Acabo de revisarlo.

master es una tabla de aproximadamente 20,000,000 registros con una PRIMARY KEY en id .

Esta consulta:

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

Funciona durante casi 30 segundos, mientras que éste:

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

es instantanea

Answer 2

Aquí hay un artículo que lo explica todo, con su diferencia de rendimiento y uso sobre UNIONES.

SQL Server CROSS APPLY y APLICACIÓN EXTERNA sobre UNIONES

Como se sugiere en este artículo, no hay diferencia de rendimiento entre ellos para las operaciones de unión normales (INTERNA Y CRUZ).

La diferencia de uso llega cuando tienes que hacer una consulta 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)

Es decir, cuando tienes que relacionarte con la función. Esto no se puede hacer usando INNER JOIN, que le daría el error "El identificador de varias partes" D.DepartmentID "no se pudo enlazar". Aquí el valor se pasa a la función a medida que se lee cada fila. Suena bien para mí. :)

Answer 3

Aquí hay un ejemplo cuando CROSS APPLY hace una gran diferencia con el rendimiento:

Uso de CROSS APPLY para optimizar las combinaciones en condiciones BETWEEN

Tenga en cuenta que, además de reemplazar las uniones internas, también puede reutilizar códigos como el truncado de fechas sin pagar la penalización de rendimiento por UDF escalares involuntarias, por ejemplo: Cálculo del tercer miércoles del mes con UDF en línea

Answer 4

Bueno, no estoy seguro de si esto califica como una razón para usar la Aplicación cruzada frente a la Unión interna, pero esta consulta me fue respondida en una publicación del Foro mediante la Aplicación cruzada, por lo que no estoy seguro de que exista un método equivalente utilizando la Unión interna:

Create PROCEDURE [dbo].[Message_FindHighestMatches]

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

COMO COMENZO

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

FIN

Answer 5

Considera que tienes dos mesas.

Mesa maestra

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

TABLA DE DETALLES

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

Hay muchas situaciones en las que necesitamos reemplazar INNER JOIN con CROSS APPLY .

1. Unir dos tablas basadas en resultados TOP n

Considere si necesitamos seleccionar Id y Name from Master y las dos últimas fechas para cada Id desde la 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

• FIDDLE SQL

La consulta anterior genera el siguiente 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

Vea, generó resultados para las dos últimas fechas con la Id dos últimas fechas y luego se unió a estos registros solo en la consulta externa en la Id , lo cual es incorrecto. Para lograr esto, necesitamos 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

• FIDDLE SQL

Y forma el siguiente 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

Así es como funciona. La consulta dentro de CROSS APPLY puede hacer referencia a la tabla externa, donde INNER JOIN no puede hacer esto (arroja un error de compilación). Al encontrar las dos últimas fechas, la unión se realiza dentro de CROSS APPLY , es decir, WHERE M.ID=D.ID

2. Cuando necesitamos la funcionalidad INNER JOIN usando funciones.

CROSS APPLY se puede usar como reemplazo con INNER JOIN cuando necesitamos obtener un resultado de la tabla Master y una function .

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

Y aquí está la función.

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

• FIDDLE SQL

que generó el siguiente 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

VENTAJAS ADICIONALES DE LA APLICACIÓN DE CRUZ

APPLY puede usarse como un reemplazo para UNPIVOT . Aquí se puede utilizar CROSS APPLY o OUTER APPLY , que son intercambiables.

Tenga en cuenta que tiene la siguiente tabla (denominada 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

La consulta está abajo.

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

• FIDDLE SQL

lo que te trae el 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

Answer 6

Esta es quizás una pregunta antigua, pero aún me encanta el poder de CROSS APPLY para simplificar la reutilización de la lógica y para proporcionar un mecanismo de "encadenamiento" para los resultados.

A continuación, le proporciono un Fiddle de SQL que muestra un ejemplo simple de cómo puede utilizar CROSS APPLY para realizar operaciones lógicas complejas en su conjunto de datos sin que nada se complique. No es difícil extrapolar desde aquí cálculos más complejos.

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

Answer 7

Esto ya se ha respondido muy bien técnicamente, pero permítanme dar un ejemplo concreto de cómo es extremadamente útil:

Digamos que tienes dos mesas, Cliente y Orden. Los clientes tienen muchos pedidos.

Quiero crear una vista que me proporcione detalles sobre los clientes y el pedido más reciente que han realizado. Con solo JOINS, esto requeriría algunas auto-inscripciones y agregaciones que no son bonitas. Pero con Cross Apply, es super fácil:

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

Answer 8

La aplicación cruzada funciona bien con un campo XML también. Si desea seleccionar valores de nodo en combinación con otros campos.

Por ejemplo, si tienes una tabla que contiene algún xml

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

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

Devolverá un resultado

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

Answer 9

La aplicación cruzada se puede usar para reemplazar las subconsultas donde necesita una columna de la subconsulta

subconsulta

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

Aquí no podré seleccionar las columnas de la tabla de la compañía, por lo que, utilizando la aplicación cruzada

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

Answer 10

La esencia del operador APLICAR es permitir la correlación entre los lados izquierdo y derecho del operador en la cláusula FROM.

A diferencia de JOIN, la correlación entre entradas no está permitida.

Hablando de correlación en el operador APLICAR, quiero decir, en el lado derecho, podemos poner:

• una tabla derivada - como una subconsulta correlacionada con un alias
• una función de tabla de valores: una vista conceptual con parámetros, donde el parámetro puede referirse al lado izquierdo

Ambos pueden devolver múltiples columnas y filas.

Answer 11

Me parece que CROSS APPLY puede llenar un cierto vacío al trabajar con campos calculados en consultas complejas / anidadas, y hacerlas más sencillas y legibles.

Ejemplo simple: tiene un DoB y desea presentar varios campos relacionados con la edad que también se basarán en otras fuentes de datos (como empleo), como Age, AgeGroup, AgeAtHiring, MinimumRetirementDate, etc. para usar en su aplicación de usuario final (Excel PivotTables, por ejemplo).

Las opciones son limitadas y raramente elegantes:

• Las subconsultas de JOIN no pueden introducir nuevos valores en el conjunto de datos basándose en los datos de la consulta principal (debe valerse por sí misma).

• Los UDF están ordenados, pero son lentos, ya que tienden a evitar operaciones paralelas. Y ser una entidad separada puede ser una cosa buena (menos código) o mala (donde está el código).

• Mesas de unión. A veces pueden funcionar, pero pronto te unirás a las subconsultas con toneladas de UNIONs. Gran desorden.

• Cree otra vista de propósito único, asumiendo que sus cálculos no requieren datos obtenidos a mitad de camino a través de su consulta principal.

• Mesas intermedias. Sí ... eso generalmente funciona, y con frecuencia es una buena opción, ya que pueden indexarse ​​y ser rápidas, pero el rendimiento también puede disminuir debido a que las declaraciones de ACTUALIZACIÓN no son paralelas y no permiten que las fórmulas (reutilización de resultados) se actualicen para actualizar varios campos dentro del misma declaración Y a veces preferirías hacer las cosas de una sola vez.

• Consultas de anidación. Sí, en cualquier momento puede poner paréntesis en toda su consulta y usarla como una subconsulta en la que puede manipular datos de origen y campos calculados por igual. Pero solo puedes hacer esto mucho antes de que se ponga feo. Muy feo.

• Código de repetición. ¿Cuál es el mayor valor de 3 declaraciones largas (CASE ... ELSE ... END)? ¡Eso será legible!

  • Dile a tus clientes que calculen las malditas cosas por sí mismos.

¿Me he perdido algo? Probablemente, así que no dude en comentar. Pero bueno, CROSS APPLY es como un regalo de Dios en tales situaciones: simplemente agregas una simple CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl y voilà! Su nuevo campo ahora está listo para su uso prácticamente como si siempre hubiera estado allí en sus datos de origen.

Los valores introducidos a través de CROSS APPLY pueden ...

• ser utilizado para crear uno o varios campos calculados sin agregar problemas de rendimiento, complejidad o legibilidad a la mezcla
• como con JOINs, varias declaraciones subsiguientes de CROSS APPLY pueden referirse a sí mismas: CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2
• puede utilizar los valores introducidos por una aplicación cruzada en las siguientes condiciones de UNIR
• Como beneficio adicional, está el aspecto de la función de valor de tabla

¡Maldición, no hay nada que no puedan hacer!

Answer 12

Supongo que debería ser legible;)

CROSS APPLY será algo único para las personas que leen, para decirles que se está utilizando un UDF que se aplicará a cada fila de la tabla de la izquierda.

Por supuesto, hay otras limitaciones donde una APLICACIÓN CRUZADA se usa mejor que ÚNETE que otros amigos han publicado anteriormente.

Answer 13

cross apply veces le permite hacer cosas que no puede hacer con inner join .

Ejemplo (un error de sintaxis):

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

Este es un error de sintaxis , ya que, cuando se usa con inner join , las funciones de tabla solo pueden tomar variables o constantes como parámetros. (Es decir, el parámetro de la función de tabla no puede depender de la columna de otra tabla).

Sin embargo:

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

Esto es legal.

Edición: O alternativamente, sintaxis más corta: (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 + tiene tablas derivadas laterales y Postgresql (9.3+) tiene subconsultas laterales que se pueden utilizar para un efecto similar.