SQL
application croisée, application extérieure

Notions de base CROSS APPLY et OUTER APPLY

Apply sera utilisé lorsque la valeur de la table fonctionne dans la bonne expression.

créer une table départementale pour contenir des informations sur les départements. Créez ensuite une table Employé contenant des informations sur les employés. Veuillez noter que chaque employé appartient à un département. Par conséquent, la table Employé a une intégrité référentielle avec la table Département.

La première requête sélectionne les données de la table Department et utilise CROSS APPLY pour évaluer la table Employee pour chaque enregistrement de la table Department. La deuxième requête rejoint simplement la table Department avec la table Employee et tous les enregistrements correspondants sont générés.

SELECT *
FROM Department D
CROSS APPLY (
    SELECT *
    FROM Employee E
    WHERE E.DepartmentID = D.DepartmentID
) A
GO
SELECT *
FROM Department D
INNER JOIN Employee E
  ON D.DepartmentID = E.DepartmentID

Si vous regardez les résultats qu’ils ont produits, c’est exactement le même résultat; En quoi diffère-t-il d'un JOIN et comment aide-t-il à écrire des requêtes plus efficaces?

La première requête du script n ° 2 sélectionne les données de la table Department et utilise OUTER APPLY pour évaluer la table Employee pour chaque enregistrement de la table Department. Pour les lignes pour lesquelles il n'y a pas de correspondance dans la table Employee, ces lignes contiennent des valeurs NULL, comme vous pouvez le voir dans les lignes 5 et 6. La deuxième requête utilise simplement LEFT OUTER JOIN entre la table Department et la table Employee. Comme prévu, la requête renvoie toutes les lignes de la table Department; même pour les lignes pour lesquelles il n'y a pas de correspondance dans la table Employee.

SELECT *
FROM Department D
OUTER APPLY (
    SELECT *
    FROM Employee E
    WHERE E.DepartmentID = D.DepartmentID
) A
GO
SELECT *
FROM Department D
LEFT OUTER JOIN Employee E
  ON D.DepartmentID = E.DepartmentID
GO

Même si les deux requêtes ci-dessus renvoient les mêmes informations, le plan d'exécution sera légèrement différent. Mais en termes de coûts, il n'y aura pas beaucoup de différence.

Maintenant vient le temps de voir où l'opérateur APPLY est vraiment nécessaire. Dans le script n ° 3, je crée une fonction table qui accepte DepartmentID comme paramètre et renvoie tous les employés appartenant à ce département. La requête suivante sélectionne les données de la table Department et utilise CROSS APPLY pour joindre la fonction créée. Il passe le DepartmentID pour chaque ligne de l'expression de la table externe (dans notre cas, la table Department) et évalue la fonction pour chaque ligne similaire à une sous-requête corrélée. La requête suivante utilise OUTER APPLY à la place de CROSS APPLY et, par conséquent, contrairement à CROSS APPLY qui n'a renvoyé que des données corrélées, OUTER APPLY renvoie également des données non corrélées, plaçant des valeurs NULL dans les colonnes manquantes.

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)
GO
SELECT
  *
FROM Department D
OUTER APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
GO

Alors maintenant, si vous vous demandez, pouvons-nous utiliser une simple jointure à la place des requêtes ci-dessus? Alors la réponse est NON, si vous remplacez CROSS / OUTER APPLY dans les requêtes ci-dessus par INNER JOIN / LEFT OUTER JOIN, spécifiez la clause ON (quelque chose comme 1 = 1) et lancez la requête, vous obtiendrez "l'identifiant multi-partie" D.DepartmentID "ne peut pas être lié". Erreur. En effet, avec JOINs, le contexte d'exécution de la requête externe est différent du contexte d'exécution de la fonction (ou d'une table dérivée) et vous ne pouvez pas lier une valeur / variable de la requête externe à la fonction en tant que paramètre. Par conséquent, l'opérateur APPLY est requis pour ces requêtes.