Conditions de jointure SQL : filtrer les données dans le ON ou dans le WHERE

Voilà une excellente question … Dont la réponse n’est pas si évidente que cela peut paraître. Et surtout elle dépend de ce que vous cherchez à obtenir, de votre requête donc, et de la manière dont vous formulez la question ….

Petit rappel sur le traitement des requêtes par SQL Server. Je passe sur les phases de vérification syntaxique, de validation des noms d’objets, l’utilisation ou non de tel ou tel index, etc … Ce qui m’intéresse, c’est l’exécution en elle-même.
Je pars du principe qu’aucun query hint n’a été spécifié lors de l’écriture de la requête et qu’aucun plan guide n’est présent pour cette requête.

Voici les phases de traitement d’une requête :

1. la clause FROM est examinée
   • éventuellement, la présence de jointures est envisagée, que ce soit du INNER JOIN, OUTER JOIN, CROSS JOIN et même les opérations APPLY, PIVOT et UNPIVOT.
   • si plusieurs tables sont jointes, SQL Server va traiter les jointures dans un ordre qui ne correspond pas forcément à ce que vous aurez spécifié dans l’écriture de votre requête (sauf à spécifier l’option FORCE ORDER ou bien a utiliser des bushy plans, mais on a exclu l’utilisation de query hints).
   • Les tables seront jointes 2 à 2 et chaque résultat intermédiaire correspond à une table virtuelle.
   • Les conditions de jointures sont évaluées à ce moment-là, donc la clause ON qui suit un JOIN est évaluée.
   • Si la jointure est externe (OUTER JOIN), les données externes sont ajoutées à la table virtuelle
2. la condition WHERE est évaluée sur la table virtuelle résultante de la phase précédente
3. si une opération de groupage est présente, le GROUP BY est effectué sur je jeu de résultat issus de la phase précédente
4. les conditions sur les données groupées sont appliquées (HAVING). Cela n’était pas possible auparavant car les données n’étaient pas groupées.
5. la phase de SELECT arrive enfin pour sélectionner les colonnes à renvoyer.
   • éventuellement, les options DISTINCT, TOP sont appliquées
6. pour terminer, le jeu de résultat est trié, la clause ORDER BY est évaluée.

Maintenant que les choses sont claires au niveau du traitement des requêtes, on constate qu’une restriction du jeu de données peut être effectuée au moment du traitement des jointures (la clause ON peut contenir des conditions de recherche plus évoluée qu’une simple égalité de colonne primary key – foreign key).

Comment doit-on écrire ces conditions ? Où doit-on placer ces conditions dans la requête ? Voici quelques éléments de réponse :

Considérons les tables SalesOrderHeader et Customer de la base AdventureWorks2008.
Si je recherche toutes les commandes passées par tous les clients, un simple INNER JOIN va renvoyer le résultat :

SELECT c.*,soh.*
FROM Sales.Customer AS c
INNER JOIN sales.SalesOrderHeader AS soh
ON c.CustomerID = soh.CustomerID
ORDER BY c.TerritoryID

Maintenant, si je recherche quelque chose de plus précis, par exemple filtré sur la date de commande :

SELECT c.*,soh.*
FROM Sales.Customer AS c
INNER JOIN sales.SalesOrderHeader AS soh
ON c.CustomerID = soh.CustomerID
WHERE soh.OrderDate = ‘20010719’
ORDER BY c.TerritoryID

Et effectivement, le jeu de résultat est bien plus concis :

On peut tuer une partie du suspense dès à présent, sur une jointure interne, le fait de placer la condition au niveau du WHERE ou du ON n’a aucun effet, ni sur le jeu de résultat ni sur la manière dont SQL Server va traiter la requête :

SELECT c.*,soh.*
FROM Sales.Customer AS c
INNER JOIN sales.SalesOrderHeader AS soh
ON c.CustomerID = soh.CustomerID
WHERE soh.OrderDate = ‘20010719’
ORDER BY c.TerritoryID

SELECT c.*,soh.*
FROM Sales.Customer AS c
INNER JOIN sales.SalesOrderHeader AS soh
ON c.CustomerID = soh.CustomerID
AND soh.OrderDate = ‘20010719’
ORDER BY c.TerritoryID

Les plans d’exécution sont identiques, et, promis, il ne s’agit pas d’un mauvais copier-coller. Vous pouvez faire le test. Au passage vous noterez aussi qu’il manque un index sur la colonne OrderDate … Mais ce n’est pas le propos.

Par contre, si nous faisons évoluer notre requête en LEFT OUTER JOIN, le résultat et le plan d’exécution seront foncièrement différents.

SELECT c.*,soh.*
FROM Sales.Customer AS c
LEFT OUTER JOIN sales.SalesOrderHeader AS soh
ON c.CustomerID = soh.CustomerID
WHERE soh.OrderDate = ‘20010719’
ORDER BY c.TerritoryID

SELECT c.*,soh.*
FROM Sales.Customer AS c
LEFT OUTER JOIN sales.SalesOrderHeader AS soh
ON c.CustomerID = soh.CustomerID
AND soh.OrderDate = ‘20010719’
ORDER BY c.TerritoryID

 

La première requête renvoie toujours 8 enregistrements, alors que la seconde en retourne 19820 … Oups, ne pas déplacer cette condition de recherche sans bien vous poser la question de ce que vous voulez récupérer comme données.

Quelle est donc la question posée à SQL Server ???

1. Pour la première requête : renvoie moi tous les clients, qu’ils aient commandé ou pas (le LEFT OUTER JOIN renvoie tous les clients avec leurs commandes, des NULLs si ils n’ont pas commandé) et ensuite, filtre ce jeu de résultat (la condition WHERE) pour ne garder que ceux dont les commandes ont une date au 19 juillet 2001. Dans ce cas à quoi sert le jointure externe car je veux des clients ayant commandé à une certaine date … Autant revenir sur la jointure interne.

2. Pour la seconde requête : renvoie moi tous les clients, qu’ils aient commandé ou pas , mais s’ils ont commandé, je ne veux que les commandes du 19 juillet 2001, des NULLs sinon.

C’est le moment de se remémorer la première partie de ce post sur l’ordre de traitement de la requête … L’application d’une condition WHERE se fait sur la table virtuelle issue de l’opération FROM et donc de tous les joins éventuels …

La question posée à SQL Server est bien différente dans les 2 requêtes. Le fait de déplacer la condition de recherche du ON vers le WHERE ou vice-versa n’est pas anodine et il convient de bien réfléchir à ce que l’on recherche. Sauf à utiliser la jointure interne qui elle est insensible à ce changement.

A vos jointures …