Sous-requête par rapport à la jointure traditionnelle avec la clause WHERE?

Qu'en est-il d'une troisième option?

 SELECT ... FROM Customers AS c INNER JOIN Classification AS cf ON cf.CustomerType = 'Standard' AND c.TypeCode = cf.Code INNER JOIN SalesReps AS s ON cf.SalesRepID = s.SalesRepID 

Personnellement, je préfère utiliser la syntaxe JOIN pour indiquer les instructions sur lesquelles l'set global est défini, les foreign keys ou d'autres conditions qui indiquent que deux lignes doivent être jointes pour former une ligne dans le jeu de résultats.

La clause WHERE contient les critères qui filternt mon jeu de résultats. On peut argumenter que cela peut devenir tout à fait flou et compliqué lorsque vous effectuez un certain nombre de jointures, mais quand vous pensez dans les sets, cela suit une sorte de logique:

• SELECT les colonnes que je veux.
• JOIN tables pour définir l'set dont je veux get des lignes.
• Filtrer les lignes WHERE mes critères ne sont pas remplis.

Par cette logique, je choisirais toujours votre deuxième syntaxe pour une lisibilité constante.

La deuxième clause est nettement plus claire, et je soupçonne que l'optimiseur aimera mieux aussi. Et idéalement, vous devez spécifier les colonnes dont vous avez besoin.

La première version est une table dérivée. Ne le confondez pas avec une sous-requête.

Je voudrais vérifier les différentes versions en ce qui concerne les performances (et en veillant à ce qu'elles fournissent toutes les mêmes résultats, vous seriez surpris de voir à quelle fréquence les gens oublient en optimisant le code que les mêmes résultats sont importants!). Je suppose que la première version a été écrite pour réduire le nombre d'loggings joints afin d'améliorer les performances (les tables dérivées améliorent souvent les performances par rapport à d'autres constructions et remplacent peut-être une sous-requête corrélée ). Que ce soit fait ou pas, je devrais courir dans votre DB pour voir.

Fondamentalement, lorsque deux constructions ont le même résultat, ma preference est de choisir celle qui est la plus performante. Oui, cela peut être un peu plus difficile à comprendre (vous pouvez toujours append des commentaires expliquant ce que vous avez fait et pourquoi aider les responsables). Mais la performance est l'une des trois choses essentielles qui doivent être sockets en count dans tous les access à la database (la security et l'intégrité des données sont les deux autres). Les performances doivent prendre le pas sur la facilité de maintenance dans une database, en particulier pour les requêtes fréquemment exécutées. Éviter dix minutes de plus pour comprendre quelque chose une fois par an quand vous avez besoin de le regarder (et la plupart des requêtes sont less révisées) ne vaut pas de secondes supplémentaires pour chaque user à chaque fois qu'il est exécuté, en particulier lorsqu'il est exécuté fois par jour.

courir

 SET SHOWPLN_ALL ON 

et ensuite chaque requête.

Je pense que le premier peut exécuter le même plan lors d'une requête simple, mais que le second fonctionnerait toujours de la même manière ou mieux, en particulier dans les requêtes plus complexes.

J'utilise seulement des sous-requêtes quand il doit y avoir une requête distincte – comme un groupe par, ou quelque chose de trop complexe.

Je ferais aussi une variante sur la deuxième requête, comme ceci:

 SELECT ... FROM Customers AS c INNER JOIN Classification AS cf ON c.TypeCode = cf.Code AND cf.CustomerType = 'Standard' INNER JOIN SalesReps AS s ON cf.SalesRepID = s.SalesRepID 

Cela supprimera les "lignes supplémentaires" pendant la partie joing de la requête. Cela ne devrait pas faire de différence pour les optimiseurs pour cette requête, mais certainement pour d'autres (jointures externes, autres sous-requêtes, etc.).

Comme d'autres l'ont dit le 2ème est un meilleur choix. Mais considérez également les implications de l'location du filter si vous vous déplacez vers une jointure externe. Si vous voulez peut-être voir tous les clients et pour les clients qui sont classifiés comme "Standard" vous voulez les informations de représentant des ventes puis passez en revue le SQL ci-dessous.

  SELECT ... FROM Customers AS c LEFT JOIN Classification AS cf ON c.TypeCode = cf.Code AND cf.CustomerType = 'Standard' LEFT JOIN SalesReps s ON cf.SalesRepID = s.SalesRepID 

Le code ci-dessous ne produirait pas les mêmes résultats que ci-dessus. Il aurait less de lignes et serait incorrect.

  SELECT ... FROM Customers AS c LEFT JOIN Classification AS cf ON c.TypeCode = cf.Code LEFT JOIN SalesReps s ON cf.SalesRepID = s.SalesRepID WHERE cf.CustomerType = 'Standard' 

Mais spécifiquement pour votre question, je voudrais voir la version suivante. Je crois que l'intention est claire dans cette version.

  SELECT ... FROM Customers AS c JOIN Classification AS cf ON c.TypeCode = cf.Code AND cf.CustomerType = 'Standard' JOIN SalesReps s ON cf.SalesRepID = s.SalesRepID 

 SELECT ... FROM Customers AS c INNER JOIN (SELECT * FROM Classification WHERE CustomerType = 'Standard') AS cf ON c.TypeCode = cf.Code INNER JOIN SalesReps s ON cf.SalesRepID = s.SalesRepID SELECT ... FROM Customers AS c INNER JOIN Classification AS cf ON c.TypeCode = cf.Code INNER JOIN SalesReps AS s ON cf.SalesRepID = s.SalesRepID WHERE cf.CustomerType = 'Standard' 

SQL Server traitera les deux requêtes de la même manière.

Ces requêtes sont identiques en termes de performances. Vous pouvez librement échanger des conditions d'affichage ON , WHERE et inline: l'optimiseur de SQL Server est suffisamment intelligent pour déterminer le meilleur plan.

La première requête est plus facilement convertible en OUTER JOIN chaque fois que le besoin s'en fait sentir, mais dans ce cas, il peut être mieux formulé comme suit:

 SELECT ... FROM Customers AS c INNER JOIN -- or OUTER JOIN Classification AS cf ON cf.Code = c.TypeCode AND cf.CustomerType = 'Standard' INNER JOIN -- or OUTER JOIN SalesReps AS s ON s.SalesRepID = cf.SalesRepID 

Lorsque j'écris les requêtes, j'essaie de les écrire pour que la nature de la key soit évidente à partir de la requête.

S'il y a une seule key de colonne sur cf.code , j'utiliserais ceci:

 SELECT ... FROM Customers AS c INNER JOIN Classification AS cf ON cf.Code = c.TypeCode INNER JOIN SalesReps AS s ON s.SalesRepID = cf.SalesRepID WHERE cf.CustomerType = 'Standard' 

Si la key est cf (Code, CustomerType) , alors celui-ci:

 SELECT ... FROM Customers AS c INNER JOIN Classification AS cf ON cf.Code = c.TypeCode AND cf.CustomerType = 'Standard' INNER JOIN SalesReps AS s ON s.SalesRepID = cf.SalesRepID 

, et si la key est cf (CustomerType, Code) , alors celui-ci:

 SELECT ... FROM Customers AS c INNER JOIN ( SELECT * FROM Classification WHERE CustomerType = 'Standard' ) AS cf ON cf.Code = c.TypeCode INNER JOIN SalesReps s ON s.SalesRepId = cf.SalesRepID 

Un petit détail: dans MySQL , les vues en ligne sont beaucoup less efficaces que les jointures, donc je ne les utiliserai pas dans MySQL .

Ce n'est pas le cas pour SQL Server .

J'ai exécuté 4 variantes sur un SGBD Oracle et elles étaient toutes plus ou less équivalentes en exécution. Je ne chantais pas une sous-requête compliquée, mais je sélectionnais une seule colonne à partir d'une table (en utilisant des methods de sous-requêtes) et la filtrais de différentes façons (directement dans la table subselect / derived ou dans la clause where). dans les conditions d'expression de jointure.

Je dirais que dans des situations de jointures / sous-requêtes équivalentes, l'optimiseur va produire des plans d'exécution similaires et quel path utiliser devrait être conduit par ce qui fournit la plus grande clarté d'intention dans la requête. (par exemple choisir en fonction de la maintenabilité)

J'irais toujours avec la seconde jusqu'à ce que je sois forcé d'utiliser une alternative.

Conserver les jointures dans le FROM et les conditions dans le WHERE.

Je préfère toujours la deuxième variante parce que si vous utilisez d'abord et la requête devient très complexe, il pourrait y avoir un côté négatif en tant que performance.