左外部結合(LOJ)

注意:

ここで説明する一般的な左外部結合演算子は、Oracle NoSQL Databaseではサポートされていません。この項は、左外部結合に精通していないユーザーの背景情報としてのみ機能します。

標準SQLでは、左外部結合の構文は次のようになります。

left_outer_join ::=
   table_reference LEFT [OUTER] JOIN table_reference ON expression

ここで、table_refは既存の表、または表を返す他の式(別の左外部結合など)です。LEFT OUTER JOINキーワードの左の表は左表と呼ばれ、LEFT OUTER JOINの右の表は右表と呼ばれます。ONキーワードの後の式は条件式であるため、ブール値を返す必要があります。

他の種類の結合の場合と同様に、LOJでは、左表および右表から一致する行のペアを含む結果セットが作成されます。ただし、LOJでは左表のすべての行も保持されます。つまり、一致する行を持たない左の行も、右の行のかわりにNULL値と組み合せて結果に表示されます。正式には、LOJは概念的には次のように処理されます。

2つの表のデカルト積、つまり、すべての左の行が右のすべての行と対になっている行のペアのセットS1が形成されます。
ON式はSの行の各ペアに対して評価され、条件を満たさないすべてのペアからSを削除します。
右の行とペアになってSに表示されないすべての左の行は、右表の列のNULL値とペアになり、Sに再び追加されます。Sは、LOJの最終結果であり、残りの問合せでは他のすべての表と同様に扱われます。

departmentsとemployeesの2つの表を使用した例を見てみましょう。次に、表の定義およびいくつかのサンプル行を示します。

CREATE TABLE departments (
   did INTEGER, location STRING, mission STRING, PRIMARY KEY(did));
CREATE TABLE employees (
eid INTEGER, did INTEGER, name STRING, salary INTEGER, PRIMARY KEY(eid));

{ "did" : 10, "location" : "Hawaii", "mission":"surfing" }
{ "did" : 20, "location" : "Toronto", "mission":"work" }
{ "did" : 30, "location" : "Everest", "mission":"climbing" }
{ "did" : 40, "location" : "Boston", "mission":"work" }
{ "eid" : 100, "did": 10, "name":"Mark", "salary":110000 }
{ "eid" : 200, "did": 10, "name":"Dave", "salary":300000 }
{ "eid" : 300, "did": 30, "name":"Linda", "salary":120000 }
{ "eid" : 400, "did": 30, "name":"Charlie", "salary":100000 }
{ "eid" : 500, "did": 40, "name":"Sam", "salary":90000 }
{ "eid" : 600, "did": 40, "name":"Tim", "salary":100000 }

次の問合せを実行すると、各部門の場所とミッション、およびその部門で所得が100,000を超える従業員の名前が返されます。ON式には2つの述語があります。1つ目は、部門と従業員の行の一致条件を決定する結合述語です。2つ目は、従業員の行のローカル述語です。問合せの結果を次に示します。

SELECT mission, location, name
   FROM departments d LEFT OUTER JOIN employees e 
   ON d.did = e.did and salary > 100000

{ "mission":"surfing", "location":"Hawaii", "name":"Mark" }
{ "mission":"surfing", "location":"Hawaii", "name":"Dave" }
{ "mission":"work", "location":"Toronto", "name":NULL }
{ "mission":"climbing", "location":"Everest", "name":"Linda" }
{ "mission":"work", "location":"Boston", "name":NULL }

この結果に示されているように、3番目と5番目の行ではトロントとボストンの部門がそれぞれ返されますが、従業員は関連付けられていません。トロントにはまったく従業員がおらず、ボストンには所得が100,000を超える従業員がいません。

最後に、次の問合せについて考えてみます。この問合せでは、salary述語がON式ではなくWHERE句に配置されています。

SELECT mission, location, name
   FROM departments d LEFT OUTER JOIN employees e 
   ON d.did = e.did
   WHERE salary > 100000

この問合せの結果を次に示します。トロントとボストンの部門が含まれていないことがわかります。これは、WHERE述語がLOJ処理の一部としてではなくFROM句の結果に適用されるためです。このため、トロント部門の場合、LOJではsalary列にNULLがある行が生成され、その後WHERE句で消去されます。ボストン部門の場合、LOJでは、給与が90,000と100,000の2つの行が生成され、どちらもWHEREによって消去されます。

{ "mission":"surfing", "location":"Hawaii", "name":"Mark" }
{ "mission":"surfing", "location":"Hawaii", "name":"Dave" }
{ "mission":"climbing", "location":"Everest", "name":"Linda" }