Quand dois-je utiliser Cross appliquer sur la jointure interne?

cross apply vous permet parfois de faire des choses que vous ne pouvez pas faire avec inner join .

exemple (une erreur de syntaxe):

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

il s'agit d'une erreur de syntaxe , parce que, lorsqu'elle est utilisée avec inner join , les fonctions de table ne peuvent prendre variables ou constantes comme paramètres. (C'est-à-dire, le paramètre table function ne peut pas dépendre de la colonne d'une autre table.)

cependant:

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

c'est légal.

Edit: Ou alternativement, syntaxe plus courte: (by ErikE)

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

Edit:

Note: Informix 12.10 xC2+ a Lateral Derived Tables et Postgresql (9.3+) a Lateral Subqueries qui peut être utilisé à un effet similaire.

considérez que vous avez deux tables.

MASTER TABLE

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

DETAILS TABLE

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

il y a de nombreuses situations où nous devons remplacer INNER JOIN par CROSS APPLY .

1. Joindre deux tableaux basés sur TOP n résultats

envisager si nous devons sélectionnez Id et Name de Master et les deux dernières dates pour chaque 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

• VIOLON SQL

La requête ci-dessus génère le résultat suivant.

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

voir, il a généré des résultats pour les deux dernières dates avec les deux dernières Id et ensuite rejoint ces enregistrements seulement dans la requête externe sur Id , ce qui est faux. Pour ce faire, nous devons utiliser 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

• VIOLON SQL

et forme le résultat suivant.

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

Voici comment ça marche. La requête à l'intérieur de CROSS APPLY peut renvoyer à la table extérieure, où INNER JOIN ne peut pas faire cela (il lance compiler erreur.) Lorsque vous trouvez les deux dernières dates, l'assemblage se fait à l'intérieur de CROSS APPLY i.e., WHERE M.ID=D.ID .

2. Quand nous avons besoin de la fonctionnalité INNER JOIN en utilisant des fonctions.

CROSS APPLY peut être utilisé comme un remplacement avec INNER JOIN quand nous avons besoin d'obtenir le résultat de Master table et un function .

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

et voici la fonction

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

• VIOLON SQL

qui a généré le résultat suivant

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

AVANTAGE ADDITIONNEL DE CROSS APPLIQUER

APPLY peut être utilisé en remplacement de UNPIVOT . Soit CROSS APPLY ou OUTER APPLY peuvent être utilisés ici, qui sont interchangeables.

considérer vous avez le tableau ci-dessous(nommé 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 requête est ci-dessous.

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

• VIOLON SQL

qui vous apporte le résultat

  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

voici un exemple où L'application croisée fait une énorme différence avec la performance:

utiliser CROSS appliquer pour optimiser les jointures entre les conditions

notez qu'en plus de remplacer inner joins vous pouvez réutiliser le code tel que les dates tronquées sans payer la pénalité de performance pour l'implication UDFs scalaires, par exemple: calcul du troisième mercredi du mois avec UDFs inline

il me semble que CROSS APPLY peut combler une certaine lacune en travaillant avec des champs calculés dans des requêtes complexes/imbriquées, et les rendre plus simples et plus lisibles.

exemple Simple: vous avez une date de naissance et vous voulez présenter plusieurs champs liés à l'âge qui s'appuieront également sur d'autres sources de données (comme l'emploi), comme L'âge, le groupe D'âge, L'âge fatigant, Minimumretiredate, etc. pour une utilisation dans votre application utilisateur (Excel PivotTables, par exemple).

les Options sont limitées et rarement élégantes:

• " JOIN subqueries ne peut pas introduire de nouvelles valeurs dans l'ensemble de données basé sur des données dans la requête parent (il doit se tenir sur son propre).

• les UDF sont nets, mais lents, car ils ont tendance à empêcher les opérations parallèles. Et étant une entité distincte peut être une bonne (moins de code) ou mauvais (où est le code).

• La jonction des tables. Parfois, ça peut marcher, mais bientôt, vous rejoignez des sous-séries avec des tonnes de syndicats. Gros gâchis.

• Créez encore une autre vue à usage unique, en supposant que vos calculs ne nécessitent pas de données obtenues à mi-chemin dans votre requête principale.

• tableaux intermédiaires. Oui... cela fonctionne généralement, et souvent une bonne option, car ils peuvent être indexés et rapides, mais les performances peuvent également baisser en raison de de Les déclarations de mise à jour ne sont pas parallèles et ne permettent pas de formules en cascade (résultats de réutilisation) pour mettre à jour plusieurs champs dans la même déclaration. Et parfois tu préférerais faire les choses en un seul passage.

• Imbrication des requêtes. Oui, à tout moment, vous pouvez mettre la parenthèse sur l'ensemble de votre requête et l'utiliser comme une sous-requête sur laquelle vous pouvez manipuler les données source et les champs calculés de la même façon. Mais tu ne peux pas faire ça avant que ça devienne moche. Très laid.

• code de répétition. Quelle est la plus grande valeur de 3 long (CASE...AUTRE...FIN) états? Ça va être lisible!

  • dites à vos clients de calculer les maudites choses eux-mêmes.

ai-je manqué quelque chose? Probablement, donc n'hésitez pas à commenter. Mais hé, CROSS APPLY est comme une aubaine dans de telles situations: il suffit d'ajouter un simple CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl et voilà! Votre nouveau champ est maintenant prêt à être utilisé pratiquement comme il l'a toujours été dans vos données source.

introduites par CROSS peuvent s'appliquer...

• doit être utilisé pour créer un ou plusieurs champs calculés sans ajouter de problèmes de performance, de complexité ou de lisibilité au mélange
• comme avec les jointures, plusieurs déclarations D'application croisée subséquentes peuvent se référer à elles-mêmes: CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2
• vous pouvez utiliser les valeurs introduites par une croix S'appliquent dans les conditions D'adhésion suivantes
• en prime, il y a l'aspect de la fonction de valeur de Table

Dang, il n'y a rien qu'ils ne puissent faire!

Cross apply fonctionne aussi bien avec un champ XML. Si vous souhaitez sélectionner des valeurs de nœud en combinaison avec d'autres champs.

par exemple, si vous avez une table contenant du xml

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

à l'Aide de la requête

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

retournera un résultat

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

je suppose que cela doit être la lisibilité ;)

croix appliquer sera un peu unique pour les personnes lisant pour leur dire qu'un UDF est utilisé qui sera appliqué à chaque ligne de la table à gauche.

bien sûr, il ya d'autres limites où une croix S'applique est mieux utilisé que de rejoindre que d'autres amis ont posté ci-dessus.

Cross apply peut être utilisé pour remplacer les sous-commandes où vous avez besoin d'une colonne de la sous-commande

sous-requête

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

ici Je ne serai pas en mesure de sélectionner les colonnes de la table d'entreprise ainsi, en utilisant cross appliquer

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

voici un article qui explique tout, avec leur différence de performance et d'utilisation sur les jointures.

SQL Server CROSS APPLY and OUTER APPLY over JOINS

comme le suggère cet article, il n'y a pas de différence de performance entre les deux pour les opérations normales de jointure (intérieure et croisée).

la différence d'usage arrive quand vous devez faire une requête comme celle-ci:

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)

C'est-à-dire quand vous devez vous rapporter à la fonction. Cela ne peut pas être fait en utilisant la jointure intérieure, qui vous donnerait l'erreur "l'identificateur multi-parties "D. DepartmentID" ne pouvait pas être lié." ici la valeur est passée à la fonction comme chaque ligne est lue. Sons cool pour moi. :)

Eh bien, je ne suis pas sûr si cela peut être considéré comme une raison d'utiliser Cross Apply versus Inner Join, mais cette requête a été répondu pour moi dans un Forum post en utilisant Cross Apply, donc je ne suis pas sûr s'il y a une méthode equalivent en utilisant Inner Join:

Create PROCEDURE [dbo].[Message_FindHighestMatches]

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

AS 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

END

C'est peut-être une vieille question, mais j'aime toujours la puissance de CROSS APPLY pour simplifier la réutilisation de la logique et de fournir un mécanisme de "chaînage" pour les résultats.

j'ai fourni un Fiddle SQL ci-dessous qui montre un exemple simple de la façon dont vous pouvez utiliser CROSS APPLY pour effectuer des opérations logiques complexes sur votre ensemble de données sans que les choses se gâtent. Il n'est pas difficile d'extrapoler à partir d'ici, des calculs plus complexes.

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

l'essence de L'opérateur D'application est de permettre la corrélation entre le côté gauche et le côté droit de l'opérateur dans la clause de.

contrairement à la jointure, la corrélation entre les entrées n'est pas permise.

en parlant de corrélation dans L'application opérateur, je veux dire sur le côté droit nous pouvons mettre:

• une table dérivée - comme une sous-requête en corrélation avec un alias
• une fonction à valeur de table - un vue conceptuelle avec paramètres, où le paramètre peut se référer au côté gauche

les deux peuvent retourner plusieurs colonnes et lignes.

cela a déjà été très bien répondu techniquement, mais permettez-moi de donner un exemple concret de la façon dont il est extrêmement utile:

disons que vous avez deux tables, client et commander. Les clients ont de nombreuses Commandes.

je veux créer une vue qui me donne des détails sur les clients, et la plus récente commande qu'ils ont faite. Avec des jointures, cela nécessiterait quelques jointures et agrégation qui n'est pas jolie. Mais avec Cross appliquer, son super facile:

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