Dans ce puzzle, nous allons apprendre à réécrire une sous-requête en utilisant des jointures internes. Connaître une sous-requête par rapport à une jointure interne peut vous aider à répondre aux questions d’entretien et aux problèmes de performance. Bien que les sous-requêtes aient des capacités uniques, il y a des moments où il est préférable d’utiliser d’autres constructions SQL telles que les jointures.
En lisant cet article, vous apprendrez à connaître plusieurs types de sous-requêtes, et comment chacune d’entre elles peut être basculée vers une autre forme, telle qu’une jointure.
Résoudre des énigmes est un excellent moyen d’apprendre le SQL. Rien ne vaut la mise en pratique de ce que vous avez appris. Une fois que vous avez compris le puzzle, postez votre réponse dans les commentaires afin que nous puissions tous apprendre les uns des autres. Nous discutons également des énigmes et d’autres sujets dans le groupe d’apprentissage Essential SQL sur Facebook. Assurez-vous de nous y trouver !
Question casse-tête SQL
Un remède à la confusion des requêtes…
Un collègue vient d’apprendre les sous-requêtes et a écrit du SQL pour récupérer les noms et les dates de naissance des employés dans la base de données AdventureWorks. Le problème est qu’ils veulent le modifier et maintenant c’est difficile à lire !
Pouvez-vous les aider à simplifier le SQL suivant ?
SELECT E.HireDate, (SELECT FirstName FROM Person.Person P1 WHERE P1.BusinessEntityID = E.BusinessEntityID), (SELECT LastName FROM Person.Person P2 WHERE P2.BusinessEntityID = E.BusinessEntityID), E.BirthDateFROM HumanResources.Employee EWHERE (SELECT PersonType FROM Person.Person T WHERE T.BusinessEntityID = E.BusinessEntityID) = 'EM'ORDER BY HireDate, (SELECT FirstName FROM Person.Person P1 WHERE P1.BusinessEntityID = E.BusinessEntityID)
Quel Statement écririez-vous pour le rendre plus facile à lire, et peut-être s’exécuter plus efficacement ?
Sous-requête versus jointure interne Réponse
Avant de commencer, parlons de la requête existante… qu’est-ce que c’est ?
Vous verrez que la requête combine les données de deux tables différentes. Elle le fait en utilisant des sous-requêtes dans les clauses FROM et WHERE. Celles-ci sont mises en évidence ci-dessous en bleu.
SELECT E.HireDate, (SELECT FirstName FROM Person.Person P1 WHERE P1.BusinessEntityID = E.BusinessEntityID), (SELECT LastName FROM Person.Person P2 WHERE P2.BusinessEntityID = E.BusinessEntityID), E.BirthDateFROM HumanResources.Employee EWHERE (SELECT PersonType FROM Person.Person T WHERE T.BusinessEntityID = E.BusinessEntityID) = 'EM'ORDER BY HireDate, (SELECT FirstName FROM Person.Person P1 WHERE P1.BusinessEntityID = E.BusinessEntityID)
De plus, vous voyez que la clause WHERE de chaque sous-requête restreint les lignes renvoyées à celles égales à l’Employee.BusinessEntityID. C’est ce que les gens appellent une sous-requête corrélée.
Je veux aussi souligner que les requêtes dans le FROM doivent retourner une seule valeur (scalaire). Si elles ne le font pas, alors une erreur est lancée.
Comme vous pouvez l’imaginer, c’est dangereux, car il peut être difficile de garantir qu’une requête renvoie au plus une ligne. Je sais que nous étions en sécurité dans ce cas, je le sais car la condition de correspondance se produit entre les clés primaires de chaque table.
Sous-requête versus jointure interne – Conversion de la requête
Si j’écrivais cette requête, j’utiliserais une jointure interne. Voici la requête que j’écrirais :
SELECT E.HireDate, P.FirstName, P.LastName, E.BirthDateFROM HumanResources.Employee E INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityIDWHERE P.PersonType = 'EM'ORDER BY E.HireDate, P.FirstName
Qu’est-ce qui est le plus facile à lire ?
Cela ne devrait pas être un grand débat, le INNER JOIN est beaucoup plus court, et je pense au point. La clause de jonction parle d’elle-même. Vous savez qu’elle relie deux tables ensemble ; alors qu’avec la sous-requête, c’est tellement apparent.
La version INNER JOIN est plus facile à maintenir.
De plus, avec la méthode de sous-requête, vous verrez une grande partie du code se répéter. Cela peut ne pas sembler être un gros problème, maintenant, mais si vous devez un jour modifier la requête, ça l’est, car maintenant, lorsque vous faites un changement, vous devez être sûr de faire le même changement à plusieurs endroits.
Sous-requête ou jointure interne ? Lequel est le plus efficace ?
Voici le plan de requête pour la version sous-requête :
J’ai mis en évidence l’effet des quatre sous-requêtes. Pour cette déclaration, chaque requête résulte en une boucle imbriquée. Celles-ci ne sont pas bonnes.
Cela signifie que si vous avez dix lignes dans deux tables chacune, alors vous devez en moyenne, itérer dans la deuxième table 50 fois (100/2) pour chaque ligne de la première pour trouver une correspondance. Cela signifie qu’au lieu d’une recherche prenant deux ou trois opérations pour trouver une correspondance, la correspondance pourrait prendre jusqu’à 100 * 50 = 500 recherches pour trouver.
Les boucles imbriquées sont un fait de la vie, mais moins est mieux.
Et voici la version pour le INNER JOIN:
SET SHOWPLAN_ALL ONSELECT E.HireDate, P.FirstName, P.LastName, E.BirthDateFROM HumanResources.Employee E INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityIDWHERE P.PersonType = 'EM'ORDER BY E.HireDate, P.FirstName
Vous voyez ici qu’il y a seulement une boucle imbriquée. Pour sûr, c’est mieux que quatre.
Après avoir observé à la fois le SQL et les plans de requête pour chaque ensemble d’instructions, vous pouvez voir que INNER JOIN est supérieur à plusieurs égards ; cependant, regardez ce plan simplifié !
La véritable tâche de la requête est de combiner les colonnes de deux tables ; c’est ce à quoi les INNER JOIN excellent. Bien sûr, il y a des moments où les sous-requêtes ont un sens, et peuvent être utilisées pour faire des choses que vous ne pouvez pas faire avec les jointures, mais dans ce cas, cela n’a pas de sens d’en utiliser une.