外部結合の概要

外部結合

外部結合は、二つのテーブルの行を一致させるために使用されます。 一致する行が含まれていない場合でも。 一方のテーブルの行は常に含まれ、もう一方のテーブルの行は一致するものがない場合はNULL値が含まれます。

このシリーズは、”データベース結合の概要”の記事から始まります。 このレッスンのすべての例は、Microsoft SQL Server Management StudioとAdventureWorks2012データベースに基づいています。 での使用を開始したこれらの無償ツールを使ってガイドをはじめ用のSQLサーバーです。, この記事では、外部結合について説明します。

外部結合のタイプ

外部結合には三つのタイプがあります。

• 左外部結合–左のテーブルのすべての行が含まれ、右の一致しない行はNULL値に置き換
• 右外部結合-右側のテーブルのすべての行が含まれ、左側の一致しない行がNULL値に置き換えられます。
• 完全外部結合-両方のテーブルのすべての行が含まれ、null値が一致しない行を埋めます。

より深く掘り下げて、左外部結合を調べてみましょう。,

左外部結合

次のデータモデルをチェックしてください。 これはAdventureWorks2012データベースから取得されます。 このモデルでは、1人から0人または1人の従業員がいます。

すべての人の姓のリストを作成し、その人が従業員である場合にJobTitleも表示するには、二つのテーブルを結合し、Employeeと一致しない場合でも、結果にPerson行を含める方法が必要です。

このタイプの結合は左外部結合と呼ばれます。JOINキーワードの左側からのテーブルのすべての行が一致に関係なく含まれるためです。, 左外部結合の基本的な構文は次のとおりです。

SELECT columnlistFROM tableLEFT OUTER JOIN othertable ON join condition

上の図の結合のSQLは次のとおりです。

SELECT person.Person.BusinessEntityID, Person.Person.LastName, HumanResources.Employee.NationalIDNumber, HumanResources.Employee.JobTitleFROM person.PersonLEFT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID

クエリの最初の結果は次のとおりです。

nationalidnumberおよびjobtitle。 これは、293と一致する従業員がいないためです。

右外部結合

図をもう一度見てみましょうが、今回は右外部結合を行っています。, ご想像のとおり、左外部結合と右外部結合の間にはSQL文にあまり違いはありません。 右外部結合の基本的な構文は次のとおりです。

SELECT columnlistFROM tableRIGHT OUTER JOIN othertable ON join condition

以下は右外部結合として記述されたサンプルクエリです。

SELECT person.Person.BusinessEntityID, Person.Person.LastName, HumanResources.Employee.NationalIDNumber, HumanResources.Employee.JobTitleFROM person.PersonRIGHT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID

主な違いは、joinキーワードの右側にあるテーブルであるEmployeeテーブルからすべてのレコードを返すことです。 一致する従業員レコードが見つからない場合は、BusinessEntityIDおよびLastNameに対してNULLが返されます。

クエリの結果は次のとおりです。,

すべての結果をスクロールして、null値が表示されないことに驚きました。

なぜか分かりますか？

答えはデータモデルにあります。 0があります。.従業員と人の間の1対1の関係。 これは、すべての従業員に一人の人がいることを意味します。 これを考えると、right joinの場合、一致しない行は存在しません。 このタイプの関係では、inner joinを使用することもできます。

左外部結合と右外部結合の間に機能に違いはありません。

左外部結合と右外部結合の間に機能に違いはありません。,

ステートメント

SELECT person.Person.BusinessEntityID, HumanResources.Employee.NationalIDNumberFROM person.PersonLEFT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID

と同じ結果を返します

SELECT person.Person.BusinessEntityID, HumanResources.Employee.NationalIDNumberFROM HumanResources.EmployeeRIGHT OUTER JOIN person.Person ON person.BusinessEntityID = Employee.BusinessEntityID

もちろん、結合を左から右に変更しただけで、テーブル名を切り替えなかった場合、これは当てはまりません。

私は通常、右外部結合よりも左外部結合を使用します。 私はこれが私が関係を引くとき私がそう左から右にするのであることを考える。 また、頭の中のテーブルを左から右にトラバースします。

これは、”左”テーブルがFROM文にあるため、SQLとうまく適合します。

私はあなたが何を使っているのか知りたいです。, 私はあなたがアラビア語のネイティブスピーカーや他の”右から左”の言語であれば、右結合がより直感的に見えるかどうかを知りたいと思っています。

完全外部結合

完全外部結合は、左外部結合と右外部結合の結果の組み合わせです。 このタイプの結合から返される結果には、両方の表のすべての行が含まれます。 一致が発生する場合、値は関連しています。 いずれかのテーブルから一致しない場合は、代わりにNULLが返されます。,

完全外部結合の基本的な構文は次のとおりです。

SELECT columnlistFROM tableFULL OUTER JOIN othertable ON join condition

AdventureWork2012データベースの別の部分を見てみましょう。 今回は、SalesOrderHeaderテーブルとCurrencyRateテーブルの関係に焦点を当てます。

モデルは以下の通りです。

注文できるすべての通貨と、それらの通貨でどの注文が行われたかを知りたいとしますか？

SELECT sales.SalesOrderHeader.AccountNumber, sales.SalesOrderHeader.OrderDate, sales.CurrencyRate.ToCurrencyCode, sales.CurrencyRate.AverageRateFROM sales.SalesOrderHeaderFULL OUTER JOIN sales.CurrencyRate ON sales.CurrencyRate.CurrencyRateID = sales.SalesOrderHeader.CurrencyRateID

ここでは、一部の売上が通貨と一致している場合と一致していない場合を示す結果の一部を示しています。, 一致しない売上高がある理由は、これらが米ドル単位の売上高であるためです。

結果のさらに下には、一致する売上高のない通貨が表示されます。 それは売れ通貨によるものです。

注：売り上げの大部分がこの通貨であると思うので、USDがリストされているのを見て驚きました。42463行目を参照してください。 私の考えでは、これらの取引の通貨レートを尊重するのではなく、CurrencyRateIDのSalesOrderHeader valeがすべてのUSD取引でnullに設定されていたということです。, これは矛盾しており、私がやる方法ではないと思いますが、私のデータベースではありません…

Advanced Example

これまでのところ、三つのタイプの外部結合を見てきましたが、複数のテーブルを結合したり、join句で複数の条件を使用したりするなど、より高度な概念を検討してきませんでした。

私たちは内部結合を検討したときにこれらの概念をカバーしたので、私があなたに示すことはあまり新しいものではありませんが、完全結合と内部結合を混合すると予期しないまたは意図しない結果が生じる可能性があるため、レビューすることはまだ理にかなっていると思います。,

私たちの焦点を生産スキーマに変え、製品とカテゴリを探りましょう。 すべての製品カテゴリとその中に含まれる製品モデルのリストを作成しましょう。

Productは、ProductModelおよびProductSubcategoryと一対多の関係を持ちます。 それはこれら二つのテーブルの間にあるので、ProductModelとProductSubcategoryの間に暗黙の多対多の関係があります。 このため、割り当てられたproductsのないproductモデルやproductのないProductSubcategoryエントリが存在する可能性があるため、外部結合の候補として適しています。,

この状況を克服するために、ProductModelテーブルとProductCategoryテーブルの両方に外部結合を行います。

ここにSQLがあります

SELECT PC.Name AS Category, PSC.Name AS Subcategory, PM.Name AS Model, P.Name AS ProductFROM Production.Product AS PFULL OUTER JOIN Production.ProductModel AS PM ON PM.ProductModelID = P.ProductModelIDFULL OUTER JOIN Production.ProductSubcategory AS PSC ON PSC.ProductSubcategoryID = P.ProductSubcategoryIDINNER JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryIDORDER BY PC.Name, PSC.Name

注意すべき項目がいくつかあります。

• SQLを読みやすくするためにテーブルエイリアスを使用しました。
• 複数の完全な外部結合句があります。,もともとこのクエリのSQLを書いたときに、ProductSubcategoryとProductCategoryの間に内部結合がありましたが、予期しないレコードのNULL値は表示されませんでした。

  結合を完全な外部結合に変更すると、期待した結果が表示されました。 これが起こる理由は微妙です。

  データを確認した後、すべてのカテゴリにサブカテゴリが割り当てられていることを確認しま, ただし、ステートメント全体が実行され、行が返されると、productがproductサブカテゴリと一致しないときは、ProductSubcategoryID値がNULLになることを考慮してください。

  Null値は、定義上、互いに等しくないため、内部結合は失敗します。 これを考えると、これらの値がProductCategoryに一致すると、ProductCategoryへの結合が外部結合でない限り、結果には含まれません。,

  実際、結合は完全外部結合である必要はなく、左結合も同様に機能します。

  SELECT PC.Name AS Category,PSC.Name AS Subcategory,PM.Name AS Model,P.Name AS ProductFROM Production.Product AS PFULL OUTER JOINProduction.ProductModel AS PMON PM.ProductModelID = P.ProductModelIDFULL OUTER JOINProduction.ProductSubcategory AS PSCON PSC.ProductSubcategoryID = P.ProductSubcategoryIDLEFT OUTER JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryIDORDER BY PC.Name, PSC.Name

  外部結合に使用します。

  外部結合は一致する行だけでなく、そうでない行も結合するため、テーブル内の欠落しているエントリを見つけるのに本当に良い方法です。 これは、データ整合性の問題があるかどうかを判断するためにデータベースで診断を行う必要がある場合に最適です。

  たとえば、Categoriesに一致しないProductSubcategoryエントリがある可能性があるとします。, 次のSQLを実行してテストできます

  SELECT PSC.Name AS SubcategoryFROM Production.ProductCategory AS PSCLEFT OUTER JOIN Production.ProductSubcategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryIDWHERE PSC.ProductCategoryID is NULL

  外部結合は、一致しない行の値をNULL値として返します。 Where句はnull以外の値をフィルタリングし、一致しないサブカテゴリ名のみを残して確認します。

  外部結合を使用して、次のような質問をすることもできます。

  “どの営業担当者が販売を行ったことがないのですか？”

  “製品モデルに割り当てられていない製品は何ですか？”

  “どの部門に割り当てられた従業員がいないのですか？”

  “

---