In deze puzzel gaan we leren hoe we een subquery kunnen herschrijven met behulp van inner joins. Kennis van een subquery versus inner join kan je helpen bij interviewvragen en prestatieproblemen. Hoewel subquery’s unieke mogelijkheden hebben, zijn er momenten waarop het beter is om andere SQL constructies te gebruiken, zoals joins.
Door het lezen van dit artikel leer je over verschillende soorten subquery’s, en hoe elk kan worden overgeschakeld naar een andere vorm, zoals een join.
Het oplossen van puzzels is een geweldige manier om SQL te leren. Er gaat niets boven het oefenen van wat je hebt geleerd. Als je de puzzel hebt opgelost, post dan je antwoord in de commentaren zodat we allemaal van elkaar kunnen leren. We bespreken ook puzzels en meer in de Essential SQL Learning Group op Facebook. Zorg ervoor dat u ons daar vindt!
SQL Puzzel Vraag
Een remedie voor query verwarring…
Een collega heeft net geleerd over subqueries en heeft wat SQL geschreven om namen en geboortedata van werknemers op te halen uit de AdventureWorks database. Het probleem is dat ze het willen veranderen en dat het nu moeilijk te lezen is!
Kunt u hen helpen de volgende SQL te vereenvoudigen?
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)
Welke verklaring zou u schrijven om het leesbaarder te maken, en misschien efficiënter te laten lopen?
Subquery versus Inner Join Antwoord
Laten we het eerst over de bestaande query hebben… wat is het?
Je zult zien dat de query gegevens uit twee verschillende tabellen combineert. Hij doet dat met behulp van subquery’s in zowel de FROM als de WHERE clausules. Deze zijn hieronder in blauw gemarkeerd.
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)
Ook zie je dat de WHERE-clausule van elke subquery de teruggegeven rijen beperkt tot rijen die gelijk zijn aan de Employee.BusinessEntityID. Dit is wat men noemt een gecorreleerde subquery.
Ik wil er ook op wijzen dat de query’s in de FROM een enkele waarde (scalar) moeten teruggeven.
Zoals je je kunt voorstellen, is dit gevaarlijk, omdat het moeilijk kan zijn om te garanderen dat een query hooguit één rij teruggeeft. Ik weet dat we veilig waren in dit geval, omdat de matching conditie optreedt tussen de primaire sleutels van elke tabel.
Subquery versus Inner Join – Het omzetten van de query
Als ik deze query zou schrijven, zou ik een INNER JOIN gebruiken. Dit is de query die ik zou schrijven:
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
What is makkelijker te lezen?
Dit is niet zo’n discussie, de INNER JOIN is veel korter, en ik denk to the point. De join-clausule spreekt voor zich. Je weet dat het twee tabellen met elkaar in verband brengt; terwijl, met de subquery, het zo duidelijk is.
De INNER JOIN versie is gemakkelijker te onderhouden.
Ook, met de subquery methode, zul je veel van de code herhaald zien. Dit lijkt nu misschien niet zo’n groot probleem, maar als je ooit de query moet wijzigen, is het dat wel, want als je nu een wijziging aanbrengt, moet je er zeker van zijn dat je dezelfde wijziging op verschillende plaatsen aanbrengt.
SubQuery of Inner Join? Which is More Efficient?
Hier volgt het queryplan voor de subquery-versie:
Ik heb het effect van de vier subquery’s gemarkeerd. Voor deze verklaring resulteert elke query in een geneste lus. Deze zijn niet goed.
Het betekent dat als je tien rijen in twee tabellen hebt, je gemiddeld 50 keer (100/2) door de tweede tabel moet gaan voor elke rij in de eerste om een overeenkomst te vinden. Dit betekent dat in plaats van een zoekopdracht die twee of drie bewerkingen vergt om een overeenkomst te vinden, het vinden van de overeenkomst meer dan 100 * 50 = 500 zoekopdrachten kan vergen.
Nested loops zijn een feit, maar minder is beter.
En hier is de versie voor de 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
Hier zie je dat er maar één nested loop is. Dat is zeker beter dan vier.
Na het bekijken van zowel de SQL als de query plannen voor elke set van verklaringen kun je zien dat INNER JOIN superieur is in verschillende opzichten; kijk echter eens naar dat vereenvoudigde plan!
De echte taak van de query is om kolommen uit twee tabellen te combineren; dit is waar INNER JOINS in uitblinken. Zeker, er zijn momenten dat subqueries zinvol zijn, en kunnen worden gebruikt om dingen te doen die je niet kunt doen met joins, maar in dit geval is het niet zinvol om er een te gebruiken.