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}