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構文ではありません。 この場合、実際には、FROM句に暗黙のテーブル参照が追加されます（これは標準SQLに対するPostgreSQL拡張です）。 つまり、次のような問い合わせを書いたものとして処理されます。

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

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

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

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

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

括弧は、あいまいさをなくすために使われます。 次のSQL文は、前の例題と異なり、結合の結果に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と同じです。 さらに興味深いケースとして、副問い合わせがグループ化や集約を含んでいる場合、単純結合にまとめることはできないということがあります。

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を返すものと宣言されています。 実際の列集合は、パーサが例えば、*がどのように展開されるかを理解できるように呼び出した問い合わせ内で指定されなければなりません。