内部結合とLOJとNESTED TABLESの比較
階層内の表は、「内部結合」、「左外部結合」およびNESTED TABLESタイプの結合を使用して結合できます。
基本的に、表間に祖先と子孫の関係を持つ表は、3つのタイプの結合のいずれかを使用して結合できます。ユース・ケースに基づいていずれかを使用することを選択できます。結合する表が祖先と子孫の関係にない場合は、内部結合を使用する必要があります。
表6-14 内部結合とLOJとNESTED TABLESの比較
| 内部結合 | 左外部結合 | NESTED TABLES |
|---|---|---|
| 同じ階層内の複数の表を問い合せます | 同じ階層内の複数の表を問い合せます | 同じ階層内の複数の表を問い合せます |
| ANSI-SQL標準ではありません | ANSI-SQL標準 | ANSI-SQL標準ではありません |
| 祖先と子孫の関係にある表と、祖先と子孫の関係にない表の結合がサポートされます。 | 祖先と子孫の関係にある表の結合のみがサポートされます | 祖先と子孫の関係にある表の結合がサポートされます兄弟表の和集合を実行します。 |
| 一致する行のみが返されます | 最初の(左の)表の一致しない行が維持され、2番目の(右の)表のNULL行と結合されます | 最初の(左の)表の一致しない行が維持され、2番目の(右の)表のNULL行と結合されます |
| すべてのフィールドが返されるSELECT *の場合、結果セット内のフィールドの順序は、FROM句で指定された表の順序によって異なります。 | すべてのフィールドが返されるSELECT *の場合、結果セット内のフィールドの順序は常に上から下の順序になります。つまり、結果セット内の出力は、常に最初に祖先表が出力され、次に子孫表が出力されるという順序になります。これは、FROM句で指定された表の順序に関係なく当てはまります。 | すべてのフィールドが返されるSELECT *の場合、結果セット内のフィールドの順序は常に上から下の順序になります。つまり、結果セット内の出力は、常に最初に祖先表が出力され、次に子孫表が出力されるという順序になります。これは、FROM句で指定された表の順序に関係なく当てはまります。 |
| 結合述語では、使用する表間の一致するシャード・キーをチェックする必要があります | 結合述語では、使用する表間の一致する主キーをチェックする必要があります | 結合述語では、一致する主キーを明示的にチェックする必要はありません。表の結合中に暗黙的に実行されます。 |
次に示す簡単な例を使用して、3種類の結合について理解しましょう。ここで、表Aは親です。表BとGは表Aの子であり、兄弟です。Cは表Bの子で、表AとBの子孫です。ida、idb、idcおよびidgは、それぞれ表A、B、CおよびGの主キーです。Aはルート表であり、シャード・キーが明示的に定義されていないため、主キー(ida)がシャード・キーとみなされます。
(ida)は、表Aから継承されたシャード・キーです。
(ida)は、表Aから継承されたシャード・キーです。(idb)は、表Bから継承された主キーです。
(ida)は、表Aから継承されたシャード・キーです。
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));表6-15 表A
| ida | a1 |
|---|---|
| 1 | a1 |
| 2 | a2 |
| 3 | a3 |
表6-16 表B
| (ida) | idb | b1 |
|---|---|---|
| 1 | 1 | b1 |
| 2 | 2 | b2 |
表6-17 表C
| (ida) | (idb) | idc | c1 |
|---|---|---|---|
| 1 | 1 | 1 | c1 |
| 2 | 2 | 2 | c2 |
| 3 | 1 | 3 | c3 |
表6-18 表G
| (ida) | idg | g1 |
|---|---|---|
| 1 | 1 | g1 |
| 2 | 2 | g2 |
親子表の結合
内部結合:
親表Aが子孫表Bと結合されます。一致する行のみが返されます。表Aの一致しない行(ida 3)は、出力に返されません。
SELECT * FROM A a, A.B b WHERE a.ida = b.ida ORDER BY a.ida表6-19 結合された表AB (表Aのすべての行が表Bのすべての行と結合されます)
| ida | a1 | (ida) | idb | b1 |
|---|---|---|---|---|
| 1 | a1 | 1 | 1 | b1 |
| 1 | a1 | 2 | 2 | b2 |
| 2 | a2 | 1 | 1 | b1 |
| 2 | a2 | 2 | 2 | b2 |
| 3 | a3 | 1 | 1 | b1 |
| 3 | a3 | 2 | 2 | b2 |
表6-20 内部結合(シャード等価述語に基づく)後の結果
| ida | a1 | (ida) | idb | b1 |
|---|---|---|---|---|
| 1 | a1 | 1 | 1 | b1 |
| 2 | a2 | 2 | 2 | b2 |
{"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 rows returned左外部結合とNESTED TABLES:
親表Aが子孫表Bと結合されます。表Aの一致しない行(ida 3)も、表BからNULLで返されます。この場合、動作は左外部結合とNESTED TABLESの両方で同じです。
LOJ問合せ:
SELECT * FROM A a LEFT OUTER JOIN A.B b ON a.ida = b.ida ORDER BY a.idaSELECT * FROM NESTED TABLES (A a descendants (A.B b)) ORDER BY a.ida表6-21 LOJ/NESTED TABLESの実行後の結果
| ida | a1 | (ida) | idb | b1 |
|---|---|---|---|---|
| 1 | a1 | 1 | 1 | b1 |
| 2 | a2 | 2 | 2 | b2 |
| 3 | a3 | null | null | null |
{"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}兄弟表の結合
内部結合:
次の問合せでは、表A、BおよびGの内部結合が実行されます。Aは親、BとGは兄弟表です。
出力:
SELECT * FROM A a, A.B b, A.G g WHERE a.ida = b.ida AND b.ida = g.ida表6-22 結合された表AB (表Aのすべての行が表Bのすべての行と結合されます)
| ida | a1 | (ida) | idb | b1 |
|---|---|---|---|---|
| 1 | a1 | 1 | 1 | b1 |
| 1 | a1 | 2 | 2 | b2 |
| 2 | a2 | 1 | 1 | b1 |
| 2 | a2 | 2 | 2 | b2 |
| 3 | a3 | 1 | 1 | b1 |
| 3 | a3 | 2 | 2 | b2 |
表6-23 結合された表ABG (表ABのすべての行が表Gのすべての行と結合されます)
| ida | a1 | (ida) | idb | b1 | (ida) | idg | g1 |
|---|---|---|---|---|---|---|---|
| 1 | a1 | 1 | 1 | b1 | 1 | 1 | g1 |
| 1 | a1 | 1 | 1 | b1 | 2 | 2 | g2 |
| 1 | a1 | 2 | 2 | b2 | 1 | 1 | g1 |
| 1 | a1 | 2 | 2 | b2 | 2 | 2 | g2 |
| 2 | a2 | 1 | 1 | b1 | 1 | 1 | g1 |
| 2 | a2 | 1 | 1 | b1 | 2 | 2 | g2 |
| 2 | a2 | 2 | 2 | b2 | 1 | 1 | g1 |
| 2 | a2 | 2 | 2 | b2 | 2 | 2 | g2 |
| 3 | a3 | 1 | 1 | b1 | 1 | 1 | g1 |
| 3 | a3 | 1 | 1 | b1 | 2 | 2 | g2 |
| 3 | a3 | 2 | 2 | b2 | 1 | 1 | g1 |
| 3 | a3 | 2 | 2 | b2 | 2 | 2 | g2 |
表6-24 内部結合(シャード・キー等価述語に基づく)後の結果
| ida | a1 | (ida) | idb | b1 | (ida) | idg | g1 |
|---|---|---|---|---|---|---|---|
| 1 | a1 | 1 | b1 | 1 | 1 | 1 | g1 |
| 2 | a2 | 2 | b2 | 2 | 2 | 2 | g2 |
{"a":{"ida":1,"a1":"a1"},"b":{"ida":1,"idb":1,"b1":"b1"},"g":{"ida":1,"idg":1,"g1":"g1"}}
{"a":{"ida":2,"a1":"a2"},"b":{"ida":2,"idb":2,"b1":"b2"},"g":{"ida":2,"idg":2,"g1":"g2"}}
2 rows returned左外部結合:
兄弟結合はできません。
SELECT * FROM A a LEFT OUTER JOIN A.B b ON a.ida=b.ida LEFT OUTER JOIN A.G g ON a.ida = g.idaError handling command SELECT * FROM A a LEFT OUTER JOIN A.B b ON a.ida=b.ida LEFT OUTER JOIN A.G g ON a.ida = g.ida: Error: at (1, 75) Table A.G is not descendant of table A.B. Tables in left-outer-joins must appear in top-down order after the target tableNESTED TABLES:
出力:
SELECT * FROM NESTED TABLES (A a descendants (A.b b, A.G g))表6-25 兄弟表BとGの和集合
| (ida) | idb | b1 | (ida) | idg | g1 |
|---|---|---|---|---|---|
| 1 | 1 | b1 | null | null | null |
| 2 | 2 | b2 | null | null | null |
| null | null | null | 1 | 1 | g1 |
| null | null | null | 2 | 2 | g2 |
表6-26 NESTED TABLES結合(AとBおよびGの和集合のLOJ)後の結果
| ida | a1 | (ida) | idb | b1 | (ida) | idg | g1 |
|---|---|---|---|---|---|---|---|
| 1 | a1 | 1 | 1 | b1 | null | null | null |
| 1 | a1 | null | null | null | 1 | 1 | g1 |
| 2 | a2 | 2 | 2 | b2 | null | null | null |
| 2 | a2 | null | null | null | 2 | 2 | g2 |
| 3 | a3 | null | null | null | null | null | null |
{"a":{"ida":1,"a1":"a1"},"b":{"ida":1,"idb":1,"b1":"b1"},"g":null}
{"a":{"ida":1,"a1":"a1"},"b":null,"g":{"ida":1,"idg":1,"g1":"g1"}}
{"a":{"ida":2,"a1":"a2"},"b":{"ida":2,"idb":2,"b1":"b2"},"g":null}
{"a":{"ida":2,"a1":"a2"},"b":null,"g":{"ida":2,"idg":2,"g1":"g2"}}
{"a":{"ida":3,"a1":"a3"},"b":null,"g":null}
5 rows returned祖先と子孫の関係にない表の結合
内部結合:
表GがCと結合されます。表Gの親はAで、表Cの親はBです。表GとCは、祖先と子孫の関係にはありません。
SELECT * FROM A.G g, A.B.C c WHERE g.ida=c.ida表6-27 結合された表GC (表Gのすべての行が表Cのすべての行と結合されます)
| (ida) | idg | g1 | (ida) | (idb) | idc | c1 |
|---|---|---|---|---|---|---|
| 1 | 1 | g1 | 1 | 1 | 1 | c1 |
| 1 | 1 | g1 | 2 | 2 | 2 | c2 |
| 1 | 1 | g1 | 3 | 1 | 3 | c3 |
| 2 | 2 | g2 | 1 | 1 | 1 | c1 |
| 2 | 2 | g2 | 2 | 2 | 2 | c2 |
| 2 | 2 | g2 | 3 | 1 | 3 | c3 |
表6-28 内部結合(シャード・キー等価述語に基づく)後の結果
| (ida) | idg | g1 | (ida) | (idb) | idc | c1 |
|---|---|---|---|---|---|---|
| 1 | 1 | g1 | 1 | 1 | 1 | c1 |
| 2 | 2 | g2 | 2 | 2 | 2 | c2 |
NESTED TABLESとLOJは、表間に祖先と子孫の関係がない表の結合には使用できません。