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値が移入されます。

例1: ターゲット表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}

例2: ターゲット表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値が移入されます。

例1: ターゲット表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"}}

例2: ターゲット表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)とともに使用されます。

例1: 親表の複数の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}

例2: 親表の複数の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句を使用して左表のすべての主キー列に対する結合述語を含める必要があります。

例: ターゲット表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句の非結合述語は結果セット全体に適用されます。

例: 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"}}

例: 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}