カテゴリ:

クエリ構文

JOIN

JOIN 句は、 FROM 句の副次句です。

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

通常、2つのテーブルのデータは何らかの形で関連しています。例えば、1つのテーブルにプロジェクトに関する情報を保持し、1つのテーブルにそれらのプロジェクトで働いている従業員に関する情報を保持できます。

通常、2つのテーブルには1つ以上の列が共通して含まれているため、一方のテーブルの行を他方のテーブルの対応する行に関連付けることができます。例えば、プロジェクトテーブルの各行には一意のプロジェクト ID 番号があり、従業員テーブルの各行には、従業員が現在割り当てられているプロジェクトの ID 番号が含まれる場合があります。

JOIN 句は、通常プロジェクト ID などの共通の列を参照することにより、あるテーブルの行を他のテーブルの対応する行に関連付ける方法を(明示的または暗黙的に)指定します。

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

ラテラル結合

構文

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

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>
[ ... ]

JOIN

結合のセマンティクスは次のとおりです(簡潔にするため、このトピックでは オブジェクト参照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    |
+----+------+-------+

カンマ演算子は、 INNER JOIN の古い構文です。 INNER JOIN の代わりにコンマを使用すると、フィルター条件は ON 句ではなく WHERE 句に配置されます。次の2つのステートメントは同等です。

新しい(推奨)スタイル:

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

古いスタイル:

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

結合は 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    |
+------+------+