In questo puzzle, impareremo come riscrivere una subquery usando gli inner join. Conoscere una subquery rispetto a un inner join può aiutarvi con le domande del colloquio e i problemi di performance. Anche se le subquery hanno capacità uniche, ci sono momenti in cui è meglio usare altri costrutti SQL come i join.
Leggendo questo articolo imparerai a conoscere diversi tipi di subquery, e come ognuna può essere trasformata in un’altra forma, come un join.
Solvere i puzzle è un ottimo modo per imparare SQL. Non c’è niente di meglio che mettere in pratica ciò che si è imparato. Una volta che hai capito il puzzle, pubblica la tua risposta nei commenti, così tutti possiamo imparare gli uni dagli altri. Discutiamo anche di puzzle e altro nel gruppo Essential SQL Learning su Facebook. Assicurati di trovarci lì!
Domanda puzzle SQL
Un rimedio per la confusione delle query…
Un collega ha appena imparato le subquery e ha scritto un po’ di SQL per recuperare nomi e date di nascita dei dipendenti dal database AdventureWorks. Il problema è che vogliono cambiarlo e ora è difficile da leggere!
Puoi aiutarli a semplificare il seguente 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)
Quale dichiarazione scriveresti per renderlo più facile da leggere e forse più efficiente?
Subquery contro Inner Join Risposta
Prima di iniziare parliamo della query esistente… cos’è?
Vedrai che la query combina i dati di due diverse tabelle. Lo sta facendo usando le subquery in entrambe le clausole FROM e WHERE. Queste sono evidenziate sotto in blu.
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)
Inoltre, vedete che la clausola WHERE di ogni subquery limita le righe restituite a quelle uguali a Employee.BusinessEntityID. Questo è ciò che la gente chiama una subquery correlata.
Voglio anche sottolineare che le query nel FROM devono restituire un singolo valore (scalare). Se non lo fanno, viene lanciato un errore.
Come potete immaginare, questo è pericoloso, poiché può essere difficile garantire che una query restituisca al massimo una riga. So che in questo caso sono al sicuro perché la condizione di corrispondenza si verifica tra le chiavi primarie di ogni tabella.
Subquery contro Inner Join – Conversione della query
Se stessi scrivendo questa query userei una INNER JOIN. Ecco la query che scriverei:
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
Che è più facile da leggere?
Questo non dovrebbe essere un gran dibattito, la INNER JOIN è molto più breve, e penso che vada al punto. La clausola di unione parla da sola. Sai che sta mettendo in relazione due tabelle insieme; mentre, con la subquery, è così evidente.
La versione INNER JOIN è più facile da mantenere.
Inoltre, con il metodo della subquery, vedrai ripetere molto del codice. Questo potrebbe non sembrare un grosso problema, ora, ma se mai dovessi cambiare la query, lo è, poiché ora quando fai un cambiamento devi essere sicuro di fare lo stesso cambiamento in diverse posizioni.
SubQuery o Inner Join? Qual è più efficiente?
Ecco il piano di query per la versione sub query:
Ho evidenziato l’effetto delle quattro subquery. Per questa dichiarazione, ogni query risulta in un ciclo annidato. Questi non sono buoni.
Significa che se avete dieci righe in due tabelle ciascuna, allora dovete in media, iterare attraverso la seconda tabella 50 volte (100/2) per ogni riga nella prima per trovare una corrispondenza. Questo significa che, invece di una ricerca che richiede due o tre operazioni per trovare una corrispondenza, la corrispondenza potrebbe richiedere fino a 100 * 50 = 500 ricerche per trovarla.
I cicli annidati sono un fatto della vita, ma meno è meglio.
E qui c’è la versione per la 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
Qui si vede che c’è solo un ciclo annidato. Di sicuro è meglio di quattro.
Dopo aver osservato sia l’SQL che i piani di query per ogni serie di istruzioni si può vedere che la INNER JOIN è superiore in diversi modi; tuttavia, guardate quel piano semplificato!
Il vero compito della query è combinare le colonne di due tabelle; questo è ciò in cui eccellono le INNER JOINS. Certo, ci sono momenti in cui le subquery hanno senso e possono essere usate per fare cose che non si possono fare con le join, ma in questo caso non ha senso usarne una.