4.2. テーブル式

4.2.1. FROM 句

FROM 句は、カンマで分けられたテーブル参照リストで与えられる 1 つ以上のテーブルから、1 つのテーブルを派生します。

FROM table_reference [, table_reference [, ...]]

テーブル参照は、テーブル名 (おそらくはスキーマで修飾された)、または、副問い合わせ、テーブル結合、それらの複雑な組み合わせなどから派生されたテーブルです。 FROM 句に複数のテーブル参照がある場合、それらは、WHERE 句、GROUP BY 句、および HAVING 句で変換できる中間的な仮想テーブルを作るためにクロス結合 (CROSS JOIN、下記を参照) され、最終的にはすべてのテーブル式の結果となります。

テーブル参照で、テーブルの継承階層のスーパーテーブルの名前を指定すると、テーブル名の前に ONLY キーワードがない場合は、テーブル参照はそのテーブルだけでなくそのサブテーブルに継承されたすべての列を生成します。しかし、参照は、名前を指定したテーブルに現れた列のみを生成し、サブテーブルで追加された列は無視されます。

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

 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)

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

FROM table_reference AS alias

FROM table_reference 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;

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

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

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

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

FROM (SELECT * FROM table1) AS alias_name

4.2.2. WHERE 句

WHERE 句の構文は、以下のとおりです。

WHERE search_condition

search_conditionには、Section 1.2で定義した、boolean型を返すどのような評価式も指定できます。

FROM 句の処理が終わった後、派生した仮想テーブルの各行は検索条件と照合されます。 条件の結果が真の場合、その行は出力されます。そうでない (すなわち結果が偽または null の) 場合は、その行は捨てられます。 一般的に検索条件は、FROM 句で生成されたテーブルの列を少なくともいくつか参照します。これは必須ではありませんが、そうしないと WHERE 句はまったく意味がなくなります。

WHERE 句の例を以下に示します。

SELECT ... FROM fdt WHERE c1 > 5

SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)

SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)

SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)

SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100

SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)

fdt は、FROM 句から派生したテーブルです。 WHERE 句の検索条件を満たさなかった行は、fdt から削除されます。評価式としてのスカラ副問い合わせの使い方に注目してください。他の問い合わせのように、副問い合わせは、複雑なテーブル式を使うことができます。 副問い合わせの中でどのように fdt が参照されるかに注意してください。 c1 を fdt.c1 のように修飾することは、c1 が副問い合わせの入力テーブルから派生した列名でもあるときにだけ必要です。列名の修飾は、必須の場合ではなくても、明確にするために役立ちます。これは、外側の問い合わせの列名の有効範囲を、どのようにして内側の問い合わせまで拡張するかを示します。

4.2.3. GROUP BY 句と HAVING 句

WHERE フィルタを通した後、派生された入力テーブルを、GROUP BY 句でグループ化し、また、HAVING 句を使用して不要なグループを取り除くことができます。

SELECT select_list
    FROM ...
    [WHERE ...]
    GROUP BY grouping_column_reference [, grouping_column_reference]...

GROUP BY 句は、テーブル内で選択された全列で同じ値を共有する行をまとめてグループ化するために使用されます。 列をリストする順番は関係ありません。この目的は、共通する値を持つ行を、代表となる1つのグループ行へまとめることにより、各グループの行を減らすことにあります。 これにより、出力の冗長度を排除し、さらにまた、これらのグループに適用される集約が計算されます。 たとえば下記のような場合です。

=> SELECT * FROM test1;
 x | y
---+---
 a | 3
 c | 2
 b | 5
 a | 1
(4 rows)

=> SELECT x FROM test1 GROUP BY x;
 x
---
 a
 b
 c
(3 rows)

2 番目の問い合わせでは、SELECT * FROM test1 GROUP BY x と書くことはできません。各グループに関連付けられる列 y の値がないからです。 グループごとに既知の一定の値を持つので、選択リストでグループ化された列を参照することができます。

一般に、テーブルがグループ化されている場合、グループ化の際に使用されていない列を集約式以外で参照することはできません。 集約式の例は以下のとおりです。

=> SELECT x, sum(y) FROM test1 GROUP BY x;
 x | sum
---+-----
 a | 4
 b | 5
 c | 2
(3 rows)

上記で sum() は、グループ全体について単一の値を計算する集約関数です。 使用可能な集約関数の詳細については、Section 6.14 を参照してください。

別の例を示します。 製品コードでグループ化された、テーブル上の sum(sales) は、各製品の売り上げ (sales) の合計であり、全製品の売り上げの合計ではありません。

SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
    FROM products p LEFT JOIN sales s USING (product_id)
    GROUP BY product_id, p.name, p.price;

この例では、product_id 列、p.name 列、p.price 列は必ず GROUP BY 句で指定する必要があります。 なぜなら、これらは、問い合わせ選択リストの中で使われているからです。 (products テーブルをどの程度厳密にセットアップするかによって、名前および価格を完全に product ID に依存させることもできるので、理論的にはさらにグループ化する必要をなくすことも可能です。しかし、これはまだ実装されていません。) s.units 列は GROUP BY で指定する必要はありません。これは、製品ごとの売り上げ計算の集約式 （sum()）の中だけで使われるためです。各製品に対して、製品のすべての販売に関する合計行が返されます。

厳密な SQL では、GROUP BY は、ソーステーブルの列によってのみグループ化できますが、PostgreSQL では、選択リストの列によるグループ化もできるように拡張しています。単純な列名の代わりに、評価式でグループ化することもできます。

GROUP BY 句を使ってグループ化されたテーブルで、特定のグループのみ必要な場合、グループ化されたテーブルから不要なグループを除くのに、WHERE 句のように HAVING 句を使うことができます。 構文は以下のとおりです。

SELECT select_list FROM ...[WHERE ...] GROUP BY ... HAVING boolean_expression

HAVING 句内の式は、グループ化された式とグループ化されてない式 (この場合は集約関数が必要になります) の両方を参照することができます。

例を示します。

=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;
 x | sum
---+-----
 a | 4
 b | 5
(2 rows)

=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';
 x | sum
---+-----
 a | 4
 b | 5
(2 rows)

次に、より現実的な例を示します。

SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
    FROM products p LEFT JOIN sales s USING (product_id)
    WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
    GROUP BY product_id, p.name, p.price, p.cost
    HAVING sum(p.price * s.units) > 5000;

上の例で、WHERE 句は、グループ化されていない列によって行を選択しているのに対し、HAVING 句は出力を売り上げ高が 5000 を越えるグループに制限しています。集約式が、常に同じである必要がないことに注意してください。