V této hádance se naučíme přepsat poddotaz pomocí vnitřního spojení. Znalosti o poddotazu versus vnitřním spojení vám mohou pomoci při otázkách na pohovoru a při problémech s výkonem. Ačkoli mají poddotazy jedinečné schopnosti, jsou situace, kdy je lepší použít jiné konstrukce jazyka SQL, například join.
Přečtením tohoto článku se dozvíte o několika typech poddotazů a o tom, jak lze každý z nich převést do jiné formy, například join.
Řešení hádanek je skvělý způsob, jak se naučit jazyk SQL. Není nad to, když si naučené věci procvičíte. Jakmile hádanku vyřešíte, napište svou odpověď do komentářů, abychom se všichni mohli učit jeden od druhého. O hádankách a dalších věcech diskutujeme také ve skupině Essential SQL Learning Group na Facebooku. Určitě nás tam najděte!
SQL Puzzle Question
Náprava zmatku v dotazech…
Spolupracovník se právě naučil o poddotazích a napsal si SQL pro získání jmen a dat narození zaměstnanců z databáze AdventureWorks. Problém je, že ho chce změnit a teď je špatně čitelný!“
Můžete mu pomoci zjednodušit následující 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)
Jaký příkaz byste napsali, aby byl lépe čitelný a možná i efektivněji fungoval?
Poddotaz versus Inner Join Odpověď
Než začneme, promluvme si o stávajícím dotazu… co to je?“
Uvidíte, že dotaz kombinuje data ze dvou různých tabulek. Činí tak pomocí poddotazů v klauzulích FROM i WHERE. Ty jsou níže zvýrazněny modře.
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)
Také vidíte, že každý poddotaz v klauzuli WHERE omezuje vrácené řádky na ty, které se rovnají Employee.BusinessEntityID. Tomu se říká korelovaný poddotaz.
Chci také upozornit, že dotazy ve FROM musí vracet jedinou hodnotu (skalár). Pokud tak neučiní, vyhodí se chyba.
Jak si dokážete představit, je to nebezpečné, protože může být obtížné zaručit, že dotaz vrátí nejvýše jeden řádek. Vím, že byly v tomto případě bezpečné, vím to, protože podmínka shody se vyskytuje mezi primárními klíči každé tabulky.
Poddotaz versus Inner Join – převod dotazu
Pokud bych psal tento dotaz, použil bych INNER JOIN. Tady je dotaz, který bych napsal:
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
Který je jednodušší na čtení?
O tom by se nemělo moc diskutovat, INNER JOIN je mnohem kratší a myslím, že i výstižnější. Spojovací klauzule mluví sama za sebe. Víte, že spojuje dvě tabulky dohromady; zatímco u poddotazu je to tak patrné.
Varianta INNER JOIN je jednodušší na údržbu.
Při metodě poddotazu se také velká část kódu opakuje. Teď se to možná nezdá jako velký problém, ale pokud budete někdy muset dotaz změnit, tak ano, protože nyní při změně musíte mít jistotu, že stejnou změnu provedete na několika místech.
SubQuery nebo Inner Join? Co je efektivnější?
Tady je plán dotazu pro verzi s poddotazem:
Zvýraznil jsem efekt čtyř poddotazů. U tohoto příkazu je výsledkem každého dotazu vnořená smyčka. Ty nejsou dobré.
To znamená, že pokud máte ve dvou tabulkách po deseti řádcích, pak musíte v průměru 50krát (100/2) iterovat druhou tabulku pro každý řádek v první, abyste našli shodu. To znamená, že místo toho, aby hledání trvalo dvě nebo tři operace k nalezení shody, může hledání shody trvat až 100 * 50 = 500 hledání.
Vnořené smyčky jsou faktem, ale méně je lépe.
A zde je verze pro 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
Tady vidíte, že je pouze jedna vnořená smyčka. Určitě je to lepší než čtyři.
Při sledování SQL i plánů dotazů pro každou sadu příkazů vidíte, že INNER JOIN je v několika ohledech lepší; však se podívejte na ten zjednodušený plán!
Skutečným úkolem dotazu je spojit sloupce ze dvou tabulek; v tom INNER JOIN vyniká. Jistě, jsou chvíle, kdy poddotazy dávají smysl a dají se použít k věcem, které se pomocí joinů dělat nedají, ale v tomto případě nemá smysl používat ani jeden.