7.2. テーブル式

7.2.1.1. 結合テーブル

結合テーブルは、2つの（実または派生）テーブルから、指定した結合種類の規則に従って派生したテーブルです。 内部結合、外部結合、およびクロス結合が使用可能です。

全ての結合は、互いに結び付けたり、あるいは入れ子にしたりすることができます。 T1とT2のどちらか、あるいは両方が、結合テーブルになることがあります。 括弧は結合の順序を制御するためにJOIN句を括ることに使うことができます。 括弧がない場合、JOIN 句は左から右に入れ子にします。

まとめとして、 以下のテーブルt1

 num | name
-----+------
   1 | a
   2 | b
   3 | c

および、テーブルt2

 num | value
-----+-------
   1 | xxx
   3 | yyy
   5 | zzz

を想定すると、以下のように様々な結合に関する結果が得られます。

=> SELECT * FROM t1 CROSS JOIN t2;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   1 | a    |   3 | yyy
   1 | a    |   5 | zzz
   2 | b    |   1 | xxx
   2 | b    |   3 | yyy
   2 | b    |   5 | zzz
   3 | c    |   1 | xxx
   3 | c    |   3 | yyy
   3 | c    |   5 | zzz
(9 rows)

=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
(2 rows)

=> SELECT * FROM t1 INNER JOIN t2 USING (num);
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

=> SELECT * FROM t1 NATURAL INNER JOIN t2;
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
(3 rows)

=> SELECT * FROM t1 LEFT JOIN t2 USING (num);
 num | name | value
-----+------+-------
   1 | a    | xxx
   2 | b    |
   3 | c    | yyy
(3 rows)

=> SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
     |      |   5 | zzz
(3 rows)

=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
     |      |   5 | zzz
(4 rows)

ONで指定される結合条件には、結合に直接関係しない条件も含めることができます。 これは一部の問い合わせにおいては便利ですが、使用の際には注意が必要です。 例を示します。

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |     |
(3 rows)

7.2.1.2. テーブルと列の別名

テーブルや複雑なテーブル参照は、問い合わせの後の方で派生テーブルを参照するために一時的な名前を与えることができます。 これをテーブルの別名と呼びます。

テーブルの別名を作成するには以下のようにします。

FROM table_reference AS alias

もしくは

FROM table_reference alias

ASキーワードはなくても構いません。 aliasは任意の識別子になります。

テーブルの別名の一般的な適用法は、長いテーブル名に短縮した識別子を割り当てて結合句を読みやすくすることです。 例を示します。

SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;

別名は、現在の問い合わせでテーブル参照をする時の新しい名前になります。 その場合は、元々の名前でテーブルを参照することはできなくなります。 よって、

SELECT * FROM my_table AS m WHERE my_table.a > 5;

は標準SQLに準拠していません。 PostgreSQLでは、add_missing_from設定変数がoff（これがデフォルトです）の場合にエラーになります。 onの場合、FROM句に暗黙のテーブル参照が追加されます。 つまり、次のような問い合わせを書いたものとして処理されます。

SELECT * FROM my_table AS m, my_table AS my_table WHERE my_table.a > 5;

これはクロス結合になりますが、通常これは望ましいことではありません。

テーブルの別名は主に表記を簡単にするためにあります。 しかし次のように、1つのテーブルが自分自身と結合する場合は、必須となります。

SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;

さらに、テーブル参照が副問い合わせの場合に別名が必要になります （項7.2.1.3を参照してください）。

括弧は、曖昧さをなくすために使われます。 次の例では、最初の文で2つ目のmy_tableのインスタンスにbという別名を付与し、一方、2つ目の文では結合結果に対して別名を付与しています。

SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...

次のような形式でテーブル別名を付けて、テーブル自身と同様にテーブルの列に一時的な名前を付けることができます。

FROM table_reference [AS] alias ( column1 [, column2 [, ...]] )

もし、実際のテーブルが持つ列よりも少ない数の列の別名が与えられる場合、残りの列は改名されません。 この構文は、自己結合あるいは副問い合わせで特に役立ちます。

別名がJOIN句の結果に適用される場合、これらの形式のいずれかを使うと、別名はJOIN内の元々の名を隠します。 例えば、次の例の通りです。

SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...

は有効なSQLですが、

SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c

は有効ではありません。 テーブルの別名aは、別名cの外側では参照することができません。

7.2.1.3. 副問い合わせ（Subqueries）

派生テーブルを指定する副問い合わせは、括弧で囲む必要があります。 また、必ずテーブル別名が割り当てられている必要があります （項7.2.1.2を参照してください）。 例を示します。

FROM (SELECT * FROM table1) AS alias_name

この例は、FROM table1 AS alias_nameと同じです。 さらに興味深いケースとして、副問い合わせがグループ化や集約を含んでいる場合、単純結合にまとめることはできないということがあります。

また、副問い合わせをVALUESリストとすることもできます。

FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
     AS names(first, last)

繰り返しますが、テーブルの別名が必要です。 VALUESリストの列に別名を付与することは省略することもできますが、付与することを勧めます。 項7.7を参照してください。

7.2.1.4. テーブル関数

テーブル関数は、基本データ型（スカラ型）、もしくは複合データ型（テーブル行）からなる行の集合を生成する関数です。 これらは、テーブル、ビュー、問い合わせのFROM句内の副問い合わせのように使用されます。 テーブル関数から返される列は、テーブル、ビュー、副問い合わせ列と同様の手順で、SELECT、JOIN、WHEREの中に含めることができます。

テーブル関数が基本データ型を返す場合、単一の結果列には関数名にちなんだ名前が付けられます。 関数が複合型を返す場合は、結果列はその型の個々の属性と同じ名前になります。

FROM句でテーブル関数に別名を付けることも、別名を付けずにそのまま使用することもできます。 別名を付けずにFROM句で関数を使用した場合、関数名が出力テーブル名として使用されます。

以下に数例示します。

CREATE TABLE foo (fooid int, foosubid int, fooname text);

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

SELECT * FROM foo
    WHERE foosubid IN (select foosubid from getfoo(foo.fooid) z
                           where z.fooid = foo.fooid);

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

呼び出し方法に応じて異なる列集合を返すテーブル関数を定義することが役に立つ場合があります。 これをサポートするには、テーブル関数をrecord仮想型を返すものと宣言します。 こうした関数を問い合わせで使用する場合、システムがその問い合わせをどのように解析し計画を作成すればよいのかが判断できるように、想定した行構造を問い合わせ自身内に指定しなければなりません。 次の例で考えてみましょう。

SELECT *
    FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc')
      AS t1(proname name, prosrc text)
    WHERE proname LIKE 'bytea%';

dblink関数は遠隔問い合わせを実行します（contrib/dblinkを参照）。 これは任意の問い合わせで使用できるように、recordを返すものと宣言されています。 実際の列集合は、パーサが例えば*がどのように展開されるかを理解できるように呼び出した問い合わせ内で指定されなければなりません。