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)

WHERE句の中に制約を記述すると異なる結果になることに注意してください。

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
(1 row)

この理由はON句の中の制約は結合の前に処理され、一方WHERE句の中の制約は結合の後に処理されることによります。

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;    -- wrong

テーブルの別名は主に表記を簡単にするためにあります。 しかし次のように、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. 副問い合わせ

派生テーブルを指定する副問い合わせは括弧で囲む必要があります。 また、必ずテーブル別名が割り当てられている必要があります。 （項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関数（dblinkモジュールの一部）は遠隔問い合わせを実行します。 これは任意の問い合わせで使用できるように、recordを返すものと宣言されています。 実際の列集合は、パーサが例えば*がどのように展開されるかを理解できるように、呼び出した問い合わせ内で指定されなければなりません。