1. SQLリファレンス・ガイド
  2. SQL問合せ管理
  3. 結合
  4. 左外部結合(LOJ)
  5. LOJを使用する様々なシナリオ

LOJを使用する様々なシナリオ

LOJを使用する様々なシナリオを理解するために、いくつかの階層表を作成し、それらにデータを移入します。

CREATE TABLE IF NOT EXISTS A (
        ida INTEGER,
        a1 string,
        PRIMARY KEY(ida))
CREATE TABLE IF NOT EXISTS A.B (
        idb INTEGER,
        b1 string,
        PRIMARY KEY(idb))
CREATE TABLE IF NOT EXISTS A.B.C (
        idc INTEGER,
        c1 string,
        PRIMARY KEY(idc))
CREATE TABLE IF NOT EXISTS A.G (
        idg INTEGER,
        g1 string,
        PRIMARY KEY(idg))
INSERT INTO A VALUES(1, 'a1')
INSERT INTO A VALUES(2, 'a2')
INSERT INTO A VALUES(3, 'a3')
INSERT INTO A.B VALUES(1, 1, 'b1')
INSERT INTO A.B VALUES(2, 2, 'b2')
INSERT INTO A.B.C VALUES(1, 1, 1, 'c1')
INSERT INTO A.B.C VALUES(2, 2, 1, 'c2')
INSERT INTO A.B.C VALUES(3, 3, 1, 'c3')
INSERT INTO A.G VALUES(1, 1, 'g1')
INSERT INTO A.G VALUES(2, 2, 'g2')

子孫との結合

ターゲット表をその子孫と結合できます。ターゲット表(左表)のすべての行が返され、子孫表(右表)に一致する行がない場合は、次の例に示すようにNULL値が移入されます。

例6-107 ターゲット表Aがその子表A.Bと結合されている場合

SELECT * FROM A a LEFT OUTER JOIN A.B b
ON a.ida = b.ida ORDER BY a.ida
出力:
{"a":{"ida":1,"a1":"a1"},"b":{"ida":1,"idb":1,"b1":"b1"}}
{"a":{"ida":2,"a1":"a2"},"b":{"ida":2,"idb":2,"b1":"b2"}}
{"a":{"ida":3,"a1":"a3"},"b":null}

例6-108 ターゲット表Aが子孫A.B.Cと結合されている場合

SELECT * FROM A a LEFT OUTER JOIN A.B.C c 
ON a.ida = c.ida ORDER BY a.ida
出力:
{"a":{"ida":1,"a1":"a1"},"c":{"ida":1,"idb":1,"idc":1,"c1":"c1"}}
{"a":{"ida":2,"a1":"a2"},"c":{"ida":2,"idb":2,"idc":1,"c1":"c2"}}
{"a":{"ida":3,"a1":"a3"},"c":{"ida":3,"idb":3,"idc":1,"c1":"c3"}}

祖先との結合

ターゲット表をその祖先と結合できます。ターゲット表(左表)のすべての行が戻され、祖先表(右表)に一致する行がない場合は、次の例に示すようにNULL値が移入されます。

例6-109 ターゲット表A.Bが親表Aと結合されている場合

SELECT * FROM A.B b LEFT OUTER JOIN A a 
ON a.ida = b.ida ORDER BY a.ida
出力:
{"a":{"ida":1,"a1":"a1"},"b":{"ida":1,"idb":1,"b1":"b1"}}
{"a":{"ida":2,"a1":"a2"},"b":{"ida":2,"idb":2,"b1":"b2"}}

例6-110 ターゲット表A.B.Cがその祖先A.Bと結合されている場合

SELECT * FROM A.B.C c LEFT OUTER JOIN A.B b 
ON c.ida = b.ida and c.idb = b.idb
出力:
{"b":null,"c":{"ida":3,"idb":3,"idc":1,"c1":"c3"}}
{"b":{"ida":2,"idb":2,"b1":"b2"},"c":{"ida":2,"idb":2,"idc":1,"c1":"c2"}}
{"b":{"ida":1,"idb":1,"b1":"b1"},"c":{"ida":1,"idb":1,"idc":1,"c1":"c1"}}

1つのSELECT文の複数のLOJ

ターゲット表(A)はその子(A.B)と結合でき、結合の結果はターゲット表の子孫(A.B.C)と結合できます。右表が子孫表の場合、ON式には、AND句を使用して左表のすべての主キー列に対する結合述語を含める必要があります。次の例では、左表に2つの主キー列(idaとidb)があります。AND句がこれらの2つの結合述語とともに使用されています(b.ida = c.ida AND b.idb = c.idb)。

例6-111 親表とその子孫の複数のLOJ

SELECT * FROM A a LEFT OUTER JOIN A.B b ON a.ida = b.ida
LEFT OUTER JOIN A.B.C c ON b.ida = c.ida AND b.idb = c.idb ORDER BY a.ida
出力:
{"a":{"ida":1,"a1":"a1"},"b":{"ida":1,"idb":1,"b1":"b1"},"c":{"ida":1,"idb":1,"idc":1,"c1":"c1"}}
{"a":{"ida":2,"a1":"a2"},"b":{"ida":2,"idb":2,"b1":"b2"},"c":{"ida":2,"idb":2,"idc":1,"c1":"c2"}}
{"a":{"ida":3,"a1":"a3"},"b":null,"c":null}

例6-112 親表とその祖先の複数のLOJ

SELECT * FROM A.B.C c LEFT OUTER JOIN A a ON c.ida = a.ida
LEFT OUTER JOIN A.B b ON c.ida = b.ida AND c.idb = b.idb ORDER BY c.ida, c.idb
出力:
{"a":{"ida":1,"a1":"a1"},"b":{"ida":1,"idb":1,"b1":"b1"},"c":{"ida":1,"idb":1,"idc":1,"c1":"c1"}}
{"a":{"ida":2,"a1":"a2"},"b":{"ida":2,"idb":2,"b1":"b2"},"c":{"ida":2,"idb":2,"idc":1,"c1":"c2"}}
{"a":{"ida":3,"a1":"a3"},"b":null,"c":{"ida":3,"idb":3,"idc":1,"c1":"c3"}}

祖先および子孫との結合

ターゲット表を祖先および子孫と結合することもできます。まず、ターゲット表をその祖先と結合してから、この問合せの結果をターゲット表の子孫と結合します。このような結合は、単一のSQL文でいくつでも可能です。すべての結合で、左表のすべての行が返され、右表に一致する行がない場合はNULL値が移入されます。右表が子孫表の場合、ON式にはAND句を使用して左表のすべての主キー列に対する結合述語を含める必要があります。

例6-113 ターゲット表A.Bがその祖先Aと結合され、その結果がその子孫A.B.Cと結合されている場合

SELECT * FROM A.B b LEFT OUTER JOIN A a ON b.ida = a.ida AND a.a1 = "abc"
LEFT OUTER JOIN A.B.C c ON b.ida = c.ida AND b.idb = c.idb
出力:
{"a":null,"b":{"ida":1,"idb":1,"b1":"b1"},"c":{"ida":1,"idb":1,"idc":1,"c1":"c1"}}
{"a":null,"b":{"ida":2,"idb":2,"b1":"b2"},"c":{"ida":2,"idb":2,"idc":1,"c1":"c2"}}

LOJでの非結合述語の制限

LOJには、データをフィルタ処理するための制限として、非結合述語(つまり、結合の一部ではない列)を追加できます。LOJの結果セットは、ON句またはWHERE句のどちらに非結合述語制限を指定したかによって異なります。ON句の非結合述語は結合操作にのみ適用されますが、WHERE句の非結合述語は結果セット全体に適用されます。

例6-114 WHERE句内の非結合述語

WHERE句に非結合述語が指定されている場合、外部結合の結果に制限が適用されます。つまり、WHERE条件がTRUEでないすべての行が削除されます。次の例では、WHERE条件に一致する1つの行のみを結果セットとして取得します。

SELECT * FROM A a LEFT OUTER JOIN A.B b ON a.ida = b.ida
WHERE b.ida > 1 ORDER BY a.ida
出力:
{"a":{"ida":2,"a1":"a2"},"b":{"ida":2,"idb":2,"b1":"b2"}}

例6-115 ON句内の非結合述語

非結合述語の制限をON句に移動すると、結果セットにはON句の条件を満たすすべての行が含まれます。ON条件を満たさない右側の外部表の行には、次に示すようにNULL値が移入されます。

SELECT * FROM A a LEFT OUTER JOIN A.B b ON a.ida = b.ida
AND b.ida > 1 ORDER BY a.ida
出力:
{"a":{"ida":1,"a1":"a1"},"b":null}
{"a":{"ida":2,"a1":"a2"},"b":{"ida":2,"idb":2,"b1":"b2"}}
{"a":{"ida":3,"a1":"a3"},"b":null}