I det här pusslet ska vi lära oss hur man skriver om en subquery med hjälp av inner joins. Kunskap om en subquery kontra inner join kan hjälpa dig med intervjufrågor och prestandaproblem. Även om subqueries har unika möjligheter finns det tillfällen då det är bättre att använda andra SQL-konstruktioner, till exempel joins.
Om du läser den här artikeln kommer du att lära dig om flera olika typer av subqueries och hur var och en av dem kan bytas ut till en annan form, till exempel en join.
Lösning av pussel är ett bra sätt att lära sig SQL. Inget är bättre än att praktisera det du har lärt dig. När du har löst pusslet, skriv ditt svar i kommentarerna så att vi alla kan lära oss av varandra. Vi diskuterar också pussel och annat i Essential SQL Learning Group på Facebook. Se till att hitta oss där!
SQL-pusselfråga
Ett botemedel mot frågeförvirring…
En medarbetare har just lärt sig om subqueries och skrev lite SQL för att hämta anställdas namn och födelsedatum från AdventureWorks-databasen. Problemet är att de vill ändra det och nu är det svårt att läsa!
Kan du hjälpa dem att förenkla följande SQL?
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)
Vilket Statement skulle du skriva för att göra det lättare att läsa och kanske köra mer effektivt?
Subquery versus Inner Join Svar
Innan vi börjar ska vi prata om den befintliga frågan… vad är det?
Du kommer att se att frågan kombinerar data från två olika tabeller. Den gör det med hjälp av underfrågor i både FROM- och WHERE-klausulerna. Dessa är markerade i blått nedan.
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)
Du ser också att varje underfrågesats WHERE-klausul begränsar de rader som returneras till de rader som är lika med Employee.BusinessEntityID. Detta är vad man kallar en korrelerad underfråga.
Jag vill också påpeka att frågorna i FROM måste returnera ett enda värde (skalär). Om de inte gör det kastas ett fel.
Som du kan föreställa dig är detta farligt, eftersom det kan vara svårt att garantera att en fråga returnerar högst en rad. Jag vet att det var säkert i det här fallet Jag vet detta eftersom matchningsvillkoret sker mellan varje tabells primära nycklar.
Subquery versus Inner Join – Converting the query
Om jag hade skrivit den här frågan skulle jag använda en INNER JOIN. Här är den fråga jag skulle skriva:
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
Vad är lättare att läsa?
Det här borde inte vara någon större debatt, INNER JOIN är mycket kortare och jag tycker att det är mer relevant. Join-klausulen talar för sig själv. Du vet att den relaterar två tabeller till varandra, medan det med subquery är så uppenbart.
Den INNER JOIN-versionen är lättare att underhålla.
Och med subquery-metoden ser du att en stor del av koden upprepas. Detta kanske inte verkar vara en stor grej nu, men om du någonsin måste ändra frågan är det en stor grej, eftersom du nu när du gör en ändring måste vara säker på att göra samma ändring på flera ställen.
SubQuery eller Inner Join? Vilket är effektivare?
Här är frågeplanen för underfrågeversionen:
Jag har markerat effekten av de fyra underfrågorna. För det här uttalandet resulterar varje fråga i en inbäddad slinga. Dessa är inte bra.
Det innebär att om du har tio rader i två tabeller vardera så måste du i genomsnitt iterera genom den andra tabellen 50 gånger (100/2) för varje rad i den första för att hitta en matchning. Detta innebär att istället för att en sökning tar två eller tre operationer för att hitta en matchning, kan det ta uppåt 100 * 50 = 500 sökningar att hitta matchningen.
Nested loops är ett faktum, men mindre är bättre.
Och här är versionen för 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
Här ser du att det bara finns en nested loop. Det är säkert bättre än fyra.
Efter att ha observerat både SQL- och frågeplaner för varje uppsättning uttalanden kan man se att INNER JOIN är överlägsen på flera sätt, men kolla in den förenklade planen!
Frågans verkliga uppgift är att kombinera kolumner från två tabeller, och det är detta som INNER JOINS utmärker sig för. Visst finns det tillfällen då underfrågor är vettiga och kan användas för att göra saker som du inte kan göra med joins, men i det här fallet är det inte vettigt att använda en sådan.