このパズルでは、内部結合を使用してサブクエリを書き換える方法について学びます。 サブクエリと内側joinについて知っておくと、面接での質問やパフォーマンスの問題で役に立ちます。
この記事を読むことで、いくつかのタイプのサブクエリについて、また、それぞれがどのように結合などの別のフォームに切り替えられるかについて学ぶことができます。 学習したことを実践することに勝るものはありません。 パズルが解けたら、その答えをコメント欄に投稿してください。 FacebookのEssential SQL Learning Groupでも、パズルの話などをしています。
SQL Puzzle Question
A remedy for query confusion…
同僚がサブクエリについて学び、AdventureWorksデータベースから社員の名前と誕生日を取得するSQLを書いたところ、その社員はサブクエリで検索されました。 問題は、彼らはそれを変更したいので、今は読みにくいことです！
彼らが次の 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)
これを読みやすくし、おそらくより効率的に実行するために、どのステートメントを書けばよいでしょうか。
Subquery versus Inner Join Answer
開始する前に、既存のクエリについて説明します…それは何ですか？ これは、FROM および WHERE 句の両方でサブクエリを使用して行われます。
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)
また、各サブクエリの WHERE 句は、返される行を Employee.BusinessEntityID と等しいものに制限していることがわかります。 これは、相関サブクエリと呼ばれています。
また、FROM のクエリは単一の値 (スカラー) を返さなければならないことを指摘したいと思います。 871>
ご想像のとおり、これは危険です。
Subquery vs Inner Join – Converting the query
もし私がこのクエリを書いていたら、INNER JOIN を使用するでしょう。 871>
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
Which is Easier to Read?
これはあまり議論されるべきではありませんが、INNER JOIN ははるかに短く、要点がまとまっていると思います。 JOIN 句はそれ自身を物語っています。 一方、サブクエリでは、それは明白です。
また、サブクエリ・メソッドでは、コードの多くが繰り返されます。 これは今は大したことではないと思われるかもしれませんが、クエリを変更しなければならなくなった場合、変更を加えるときに同じ変更を複数の場所で行うことを確認する必要があるため、大きな問題となります。 どちらがより効率的ですか？
以下は、サブクエリ版のクエリ プランです。
4つのサブクエリの影響を強調表示しました。 このステートメントでは、各クエリの結果はネストされたループになります。 これらは良くありません。
それは、2 つのテーブルにそれぞれ 10 行がある場合、一致を見つけるために最初のテーブルの各行に対して平均で 50 回 (100/2) 2 番目のテーブルを反復する必要があることを意味します。
入れ子ループは事実ですが、少ないほどよいのです。
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
INNER JOIN のバージョンはこちらです。 4 つよりも確実に優れています。
ステートメントの各セットの SQL とクエリ プランの両方を観察すると、いくつかの点で INNER JOIN が優れていることがわかりますが、その単純化したプランをチェックしてください。 もちろん、サブクエリが意味を持つ場合もありますし、結合ではできないことを行うために使用することもできますが、この場合、サブクエリを使用することは意味がありません。