カテゴリ:

クエリ構文

JOIN

JOIN 操作により、2つのテーブル(またはビューやテーブル関数などの他のソース)の行を結合して、クエリで使用できる新しい結合された行が作成されます。結合の概念的な説明については、 JOIN(結合)の操作 を参照してください。

このトピックでは、 FROM 句で JOIN 構文を使用する方法について説明します。 JOIN 副次句は、あるテーブルの行を他のテーブルの対応する行に関連付ける方法を(明示的または暗黙的に)指定します。

テーブルを結合するための推奨方法は、 FROM 句の ON 節とともに JOIN を使用することですが、テーブルを結合する代替方法は、 WHERE 句を使用することです。詳細については、 WHERE 句のドキュメントをご参照ください。

構文

次のいずれかを使用します。

SELECT ...
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                   ]
                   JOIN <object_ref2>
  [ ON <condition> ]
[ ... ]
SELECT *
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                   ]
                   JOIN <object_ref2>
  [ USING( <column_list> ) ]
[ ... ]
SELECT ...
FROM <object_ref1> [
                     {
                       | NATURAL [ { LEFT | RIGHT | FULL } [ OUTER ] ]
                       | CROSS
                     }
                   ]
                   JOIN <object_ref2>
[ ... ]

オブジェクト参照1 および オブジェクト参照2

各オブジェクト参照は、テーブルまたはテーブルのようなデータソースです。

JOIN

JOIN キーワードを使用して、テーブルを結合する必要があることを指定します。 JOIN を他の結合関連キーワード(例: INNER または OUTER )と組み合わせて、結合のタイプを指定します。

結合のセマンティクスは次のとおりです(簡潔にするため、このトピックでは オブジェクト参照1 および オブジェクト参照2 にそれぞれ o1 および o2 を使用します)。

結合タイプ

セマンティクス

o1 INNER JOIN o2

o1 の各行について、 ON 条件 の副次句に従って一致する o2 の各行に対して行が生成されます。(コンマを使用して内部結合を指定することもできます。例については、以下の例をご参照ください。) ON 句なしで INNER JOIN を使用する場合(または WHERE 句なしでコンマを使用する場合)、結果は CROSS JOIN :デカルト積(o1 のすべての行が o2 のすべての行とペアになっている)を使用する場合と同じです。

o1 LEFT OUTER JOIN o2

内部結合の結果は、 o2 に一致しない o1 の行ごとに1行追加されます。 o2 を参照する結果列にnullが含まれています。

o1 RIGHT OUTER JOIN o2

内部結合の結果は、 o1 に一致しない o2 の行ごとに1行追加されます。 o1 を参照する結果列にnullが含まれています。

o1 FULL OUTER JOIN o2

結合されたすべての行に加えて、一致しない左側の行ごとに1行(右側にnullが拡張)、および一致しない右側の行ごとに1行(左側にnullが拡張)を返します。

o1 CROSS JOIN o2

o1 および o2 の行のあらゆる可能な組み合わせ(つまり、デカルト積)、結合テーブルには、 o1 のすべての列とそれに続く o2 のすべての列で構成される行が含まれます。 CROSS JOINON 条件 句と組み合わせることはできません。ただし、 WHERE 句を使用して結果をフィルタリングできます。

o1 NATURAL JOIN o2

NATURAL JOIN は、2つのテーブルの共通列の明示的な JOIN と同じですが、共通列は出力に1回だけ含まれます。(自然結合は、同じ名前で異なるテーブルの列に対応するデータが含まれていることを前提としています。)いくつかの例については、下記の例をご参照ください。 NATURAL JOINOUTER JOIN と組み合わせることができます。 JOIN 条件が既に暗黙指定されているため、 NATURAL JOINON 条件 句と組み合わせることはできません。ただし、 WHERE 句を使用して結果をフィルタリングできます。

こちらもご参照ください:

ラテラル結合

デフォルト: INNER JOIN

INNER または OUTER を指定せずに単語 JOIN を使用すると、 JOIN は内部結合になります。

ON 条件

一致すると見なされる JOIN の両側の行を定義するブール式。例えば、

ON object_ref2.id_number = object_ref1.id_number

条件については、 WHERE 句のドキュメントで詳しく説明します。

CROSS JOINON 句は禁止されています。

NATURAL JOIN には ON 句は不要です(禁止されています)。結合列が暗示されます。

他の結合の場合、 ON 句はオプションです。ただし、 ON 句を省略すると、デカルト積になります( オブジェクト参照1 の各行は オブジェクト参照2 の各行とペアになります)。 デカルト積は非常に大量の出力を生成する可能性があり、そのほとんどは実際には関係のない行のペアで構成されています。これは多くのリソースを消費し、多くの場合ユーザーエラーです。

USING( <column_list> )

結合される2つのテーブル間で共通する列のリスト。これらの列は結合列として使用されます。列は、結合される各テーブルで同じ名前と意味を持つ必要があります。

例えば、 SQL ステートメントに次が含まれているとします。

... o1 JOIN o2
    USING (key_column)

単純な場合、これは次と同等になります。

... o1 JOIN o2
    ON o2.key_column = o1.key_column

USING 句を適切に使用するには、投影リスト( SELECT キーワードの後の列およびその他の式のリスト)を「*」にする必要があります。これにより、サーバーはkey_columnを1回だけ返すことができます。これは、 USING 句を使用する標準的な方法です。標準および非標準の使用例については、以下の例をご参照ください。

JOIN の例の多くは、 t1t2 の2つのテーブルを使用しています。テーブルとそのデータは、次のように作成されます。

CREATE TABLE t1 (col1 INTEGER);
CREATE TABLE t2 (col1 INTEGER);
INSERT INTO t1 (col1) VALUES 
   (2),
   (3),
   (4);
INSERT INTO t2 (col1) VALUES 
   (1),
   (2),
   (2),
   (3);

内部結合:

SELECT t1.col1, t2.col1 FROM t1 INNER JOIN t2 ON t2.col1 = t1.col1 ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

これは、左外部結合を示しています。テーブルt2に一致する行がないテーブルt1の行の NULL 値に注意してください。

SELECT t1.col1, t2.col1 FROM t1 LEFT OUTER JOIN t2 ON t2.col1 = t1.col1 ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
|    4 | NULL |
+------+------+

これは、右外部結合を示しています。テーブルt2に一致する行がないテーブルt1の行の NULL 値に注意してください。

SELECT t1.col1, t2.col1 FROM t1 RIGHT OUTER JOIN t2 ON t2.col1 = t1.col1 ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
| NULL |    1 |
+------+------+

これは、完全な外部結合を示しています。各テーブルには他のテーブルに一致する行がない行があるため、出力には NULL 値を持つ2つの行が含まれます。

SELECT t1.col1, t2.col1 FROM t1 FULL OUTER JOIN t2 ON t2.col1 = t1.col1 ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
|    4 | NULL |
| NULL |    1 |
+------+------+

以下は直交結合の例で、デカルト積を生成します。クロス結合には ON 句がないことに注意してください。

SELECT t1.col1, t2.col1 FROM t1 CROSS JOIN t2 ORDER BY 1, 2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    1 |
|    2 |    2 |
|    2 |    2 |
|    2 |    3 |
|    3 |    1 |
|    3 |    2 |
|    3 |    2 |
|    3 |    3 |
|    4 |    1 |
|    4 |    2 |
|    4 |    2 |
|    4 |    3 |
+------+------+

次の例に示すように、クロス結合は WHERE 句でフィルタリングできます。

SELECT t1.col1, t2.col1 FROM t1 CROSS JOIN t2 WHERE t2.col1 = t1.col1 ORDER BY 1, 2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

これは自然な結合の例です。これにより、対応する内部結合と同じ出力が生成されますが、結合列の2番目のコピーは出力に含まれません。

CREATE OR REPLACE TABLE d1 (
  id number,
  name string
  );
+--------------------------------+
| status                         |
|--------------------------------|
| Table D1 successfully created. |
+--------------------------------+
INSERT INTO d1 (id, name) VALUES
  (1,'a'),
  (2,'b'),
  (4,'c');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+
CREATE OR REPLACE TABLE d2 (
  id number,
  value string
  );
+--------------------------------+
| status                         |
|--------------------------------|
| Table D2 successfully created. |
+--------------------------------+
INSERT INTO d2 (id, value) VALUES
  (1,'xx'),
  (2,'yy'),
  (5,'zz');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+
SELECT * FROM d1 NATURAL INNER JOIN d2 ORDER BY id;
+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
+----+------+-------+

自然結合は、次のように外部結合と組み合わせることができます。

SELECT *
  FROM d1 NATURAL FULL OUTER JOIN d2
  ORDER BY ID;
+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
|  4 | c    | NULL  |
|  5 | NULL | zz    |
+----+------+-------+

結合は FROM 句で組み合わせることができます。次のクエリは、 FROM 句で連結された2つの結合を示しています。

SELECT t1.*, t2.*, t3.*
  FROM t1
     LEFT OUTER JOIN t2 ON (t1.col1 = t2.col1)
     RIGHT OUTER JOIN t3 ON (t3.col1 = t2.col1)
  ORDER BY t1.col1;
+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
| NULL | NULL |    6 |
+------+------+------+

このようなクエリでは、結果は左から右に行われる結合に基づいて決定されます(ただし、異なる結合順序で同じ結果が生成される場合、オプティマイザは結合を並べ替えることができます)。右外部結合が左外部結合の前に行われることを意図している場合、クエリは次のように記述できます。

SELECT t1.*, t2.*, t3.*
  FROM t1
     LEFT OUTER JOIN
     (t2 RIGHT OUTER JOIN t3 ON (t3.col1 = t2.col1))
     ON (t1.col1 = t2.col1)
  ORDER BY t1.col1;
+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
|    3 | NULL | NULL |
|    4 | NULL | NULL |
+------+------+------+

以下の2つの例は、 USING 句の標準および非標準の使用法を示しています。

この最初の例は、正しい使用法を示しています。具体的には、投影リストには「*」のみが含まれます。クエリは2つのテーブルを結合し、各テーブルには1つの列があり、クエリはすべての列を要求しますが、出力には2つの列ではなく1つの列が含まれます。

WITH 
    l AS (
         SELECT 'a' AS userid
         ),
    r AS (
         SELECT 'b' AS userid
         )
  SELECT * 
    FROM l LEFT JOIN r USING(userid)
;
+--------+
| USERID |
|--------|
| a      |
+--------+

次の例は、非標準の使用法を示しています。投影リストに「*」以外が含まれています。使用法は非標準であるため、出力には「userid」という名前の2つの列が含まれ、2番目の発生(テーブル「r」の値が含まれると予想される)にはテーブルにない値(値「a」はテーブル「r」にありません)。

WITH 
    l AS (
         SELECT 'a' AS userid
       ),
    r AS (
         SELECT 'b' AS userid
         )
  SELECT l.userid as UI_L,
         r.userid as UI_R  -- Incorrect usage!
    FROM l LEFT JOIN r USING(userid)
;
+------+------+
| UI_L | UI_R |
|------+------|
| a    | a    |
+------+------+