I dette puslespil lærer vi, hvordan man omskriver en subquery ved hjælp af inner joins. Viden om en subquery versus inner join kan hjælpe dig med interviewspørgsmål og præstationsproblemer. Selvom subqueries har unikke evner, er der tidspunkter, hvor det er bedre at bruge andre SQL-konstruktioner som f.eks. joins.
Gennem at læse denne artikel lærer du om flere typer af subqueries, og hvordan de hver især kan ændres til en anden form, f.eks. et join.
Løse gåder er en god måde at lære SQL på. Der er intet bedre end at øve sig på det, du har lært. Når du har løst gåden, kan du skrive dit svar i kommentarerne, så vi alle kan lære af hinanden. Vi diskuterer også puslespil og meget mere i Essential SQL Learning Group på Facebook. Sørg for at finde os der!
SQL-puslespørgsmål
Et middel mod forespørgselsforvirring…
En kollega har lige lært om subqueries og skrev noget SQL for at hente medarbejdernes navne og fødselsdatoer fra AdventureWorks-databasen. Problemet er, at de ønsker at ændre det, og nu er det svært at læse!
Kan du hjælpe dem med at forenkle følgende 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)
Hvilken Statement ville du skrive for at gøre det lettere at læse og måske køre mere effektivt?
Subquery versus Inner Join Svar
Hvor vi begynder, lad os tale om den eksisterende forespørgsel … hvad er det?
Du vil se, at forespørgslen kombinerer data fra to forskellige tabeller. Det gør den ved hjælp af underafspørgsler i både FROM- og WHERE-klausulerne. Disse er fremhævet nedenfor med blå farve.
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)
Det ses også, at hver underafspørgsels WHERE-klausul begrænser de returnerede rækker til de rækker, der er lig med Employee.BusinessEntityID. Det er det, man kalder en korreleret underafspørgsel.
Jeg vil også gerne påpege, at forespørgslerne i FROM skal returnere en enkelt værdi (skalar). Hvis de ikke gør det, bliver der kastet en fejl.
Som du kan forestille dig, er dette farligt, da det kan være svært at garantere, at en forespørgsel returnerer højst én række. Jeg ved var sikker i dette tilfælde ved jeg dette, da den matchende betingelse sker mellem hver tables primærnøgler.
Subquery versus Inner Join – Konvertering af forespørgslen
Hvis jeg skrev denne forespørgsel ville jeg bruge en INNER JOIN. Her er den forespørgsel, jeg ville skrive:
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
Hvilken er nemmere at læse?
Dette burde ikke være nogen stor debat, INNER JOIN er meget kortere, og jeg synes, at den går lige til sagen. Join-klausulen taler for sig selv. Du ved, at den relaterer to tabeller sammen, mens det med subquery er så tydeligt.
Den INNER JOIN-version er nemmere at vedligeholde.
Også med subquery-metoden vil du se meget af koden gentaget. Det virker måske ikke som en stor ting, nu, men hvis du nogensinde skal ændre forespørgslen, er det det, for når du nu foretager en ændring, skal du være sikker på at foretage den samme ændring flere steder.
SubQuery eller Inner Join? Hvilken er mere effektiv?
Her er forespørgselsplanen for subquery-versionen:
Jeg har fremhævet effekten af de fire subqueries. For denne erklæring resulterer hver forespørgsel i en indlejret løkke. Disse er ikke gode.
Det betyder, at hvis du har ti rækker i to tabeller hver, så skal du i gennemsnit iterere gennem den anden tabel 50 gange (100/2) for hver række i den første for at finde et match. Det betyder, at i stedet for at en søgning tager to eller tre operationer for at finde et match, kan det tage op mod 100 * 50 = 500 søgninger at finde matchet.
Nested loops er en kendsgerning, men mindre er bedre.
Og her er versionen for 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
Her ser du, at der kun er én nested loop. Det er helt sikkert bedre end fire.
Efter at have observeret både SQL- og forespørgselsplaner for hvert sæt udsagn kan du se, at INNER JOIN er overlegen på flere måder; men se lige den forenklede plan!
Spørgsmålets egentlige opgave er at kombinere kolonner fra to tabeller; det er det, som INNER JOINS udmærker sig ved. Selvfølgelig er der tidspunkter, hvor subqueries giver mening, og kan bruges til at gøre ting, som du ikke kan gøre med joins, men i dette tilfælde giver det ikke mening at bruge en.