テーブル式は、テーブルを計算するためのものです。 テーブル式には FROM 句が含まれており、その後ろにオプションとして WHERE 句、GROUP BY 句、HAVING 句を付けることができます。単純なテーブル式は、単にディスク上のいわゆる基本テーブルと呼ばれるテーブルを参照するだけです。 しかし、さまざまな方法で基本テーブルを修正したり、組み合わせたりするためにより複雑な式を使用することができます。
テーブル式のオプション WHERE 句、GROUP BY 句、および HAVING 句は、FROM 句で派生したテーブル上に対して次々に変換を実行するパイプラインを指定します。 これらの変換によって仮想テーブルが 1 つ生成されます。そしてこの仮想テーブルの行が選択リストに渡され、問い合わせの出力行が計算されます。
FROM 句は、カンマで分けられたテーブル参照リストで与えられる 1 つ以上のテーブルから、1 つのテーブルを派生します。
FROM table_reference [, table_reference [, ...]]テーブル参照は、テーブル名 (おそらくはスキーマで修飾された)、または、副問い合わせ、テーブル結合、それらの複雑な組み合わせなどから派生されたテーブルです。 FROM 句に複数のテーブル参照がある場合、それらは、WHERE 句、GROUP BY 句、および HAVING 句で変換できる中間的な仮想テーブルを作るためにクロス結合 (CROSS JOIN、下記を参照) され、最終的にはすべてのテーブル式の結果となります。
テーブル参照で、テーブルの継承階層のスーパーテーブルの名前を指定すると、テーブル名の前に ONLY キーワードがない場合は、テーブル参照はそのテーブルだけでなくそのサブテーブルに継承されたすべての列を生成します。しかし、参照は、名前を指定したテーブルに現れた列のみを生成し、サブテーブルで追加された列は無視されます。
結合テーブルは、2 つの (実、または、派生) テーブルから、結合の規則に従って派生したテーブルです。内部結合、外部結合、およびクロス結合が使用可能です。
結合の種類
T1 CROSS JOIN T2
どのT1とT2の行の組み合わせについても、T1のすべての列に続き、T2のすべての列を含む行が派生テーブルに含まれます。2つのテーブルがN行とM行で構成されているとすると、結合されたテーブルの行数はN×M行となります。クロス結合はINNER JOIN ON TRUEと同じです。
Tip: FROM T1 CROSS JOIN T2 は次と同等です。 FROM T1, T2.
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2INNER や OUTER は、省略可能です。 INNER がデフォルトとなります。 LEFT、RIGHT、FULL は、外部結合を暗黙指定します。
結合条件は、ON 句か USING 句で指定するか、または NATURAL 記述で暗黙指定します。結合条件は、以下で詳しく説明するように、2つの元となるテーブルのどの行が"一致するか"を決めます。
ON 句は最も一般的な結合条件であり、WHERE 句で使われるのと同じブール値評価式となります。 ON で表現された部分が真となる場合、T1 および T2 の対応する行が対象となります。
USING は略記法です。それは、結合テーブルが共通で持つカンマで区切られた列名のリストから、各々の列の組み合わせをイコールとした結合条件を生成します。 さらに、JOIN USING の出力は、入力列でイコールとなった列の組み合わせに対して 1 つの列があり、各テーブルの他のすべての列がそのあと続きます。 つまり、USING (a, b, c) は ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) と等価です。 ただし、ON を使った場合は、結果において a、b、c は 2 つの列になりますが、USING を使うとそれぞれ 1 つの列になるという例外があります。
最後に、NATURAL は USING の略記形式です。 2 つの入力テーブルの両方に含まれている列名で構成される USING 句リストを形成します。 USING と同様、これらの列は出力テーブルに一度だけ現われます。
修飾付き JOIN には次のものがあります。
T1 の各 R1 行に対して、T2 において R1 との結合条件を満たしている行が、結合されたテーブルに含まれます。
まず、内部結合が行われます。 その後、T2 のどの行の結合条件も満たさない T1 の各行については、結合された行は T2 の列に NULL 値として追加されます。したがって、連結されたテーブルは、無条件にT1の行それぞれに少なくとも1つの行があります。
まず、内部結合が行われます。 その後、T1 のどの行の結合条件も満たさない T2 の各行については、結合された行は T1 の列に NULL 値として追加されます。これは左結合の反対です。結果のテーブルは、T2の行が無条件に入ります。
まず、内部結合が行われます。 その後、T2 のどの行の結合条件も満たさない T1 の各行については、結合された行は T2 の列に NULL 値として追加されます。 さらに、T1 のどの行でも結合条件を満たさない T2 の各行に対して、T1 の列が NULL 値である結合された行が追加されます。
すべての結合は、互いに結び付けたり、あるいは、入れ子にしたりすることができます。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; num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 2 | b | | 3 | c (3 rows)
テーブルや複雑なテーブル参照は、その先の処理で派生テーブルを参照するために一時的な名前を与えることができます。これを テーブルの別名（table alias）と呼びます。
テーブルの別名を作成するには以下のようにします。
FROM table_reference AS alias
or
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 ...
さらに、テーブル参照が副問い合わせの場合に別名が必要になります (Section 4.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 の外側では参照することができません。
派生テーブルを指定する副問い合わせは、小括弧で囲む必要があります。また、必ずテーブル別名が割り当てられている必要があります。Section 4.2.1.2 を参照。例を示します。
FROM (SELECT * FROM table1) AS alias_name
この例は、FROM table1 AS alias_nameと同じです。さらに興味深いケースとして、副問い合わせがグループ化や集約を含んでいる場合、単純結合にまとめることはできないということがあります。
WHERE 句の構文は、以下のとおりです。
WHERE search_condition
search_conditionには、Section 1.2で定義した、boolean型を返すどのような評価式も指定できます。
FROM 句の処理が終わった後、派生した仮想テーブルの各行は検索条件と照合されます。 条件の結果が真の場合、その行は出力されます。そうでない (すなわち結果が偽または null の) 場合は、その行は捨てられます。 一般的に検索条件は、FROM 句で生成されたテーブルの列を少なくともいくつか参照します。これは必須ではありませんが、そうしないと WHERE 句はまったく意味がなくなります。
Note: 結合 (JOIN) 構文が実装される以前には、WHERE 句の中に内部結合の結合条件を挿入する必要がありました。たとえば、以下の3つのテーブル式は等価です。
FROM a, b WHERE a.id = b.id AND b.val > 5と、
FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5以下でも同じです。
FROM a NATURAL JOIN b WHERE b.val > 5どれを使うかは、主にスタイルの問題です。 FROM 句で JOIN 構文を使用すると、おそらく他の製品に移植できません。 外部結合については、FROM 句以外に選択の余地はありません。 外部結合の ON 句や USING 句は、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 が副問い合わせの入力テーブルから派生した列名でもあるときにだけ必要です。列名の修飾は、必須の場合ではなくても、明確にするために役立ちます。これは、外側の問い合わせの列名の有効範囲を、どのようにして内側の問い合わせまで拡張するかを示します。
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 を参照してください。
Tip: 集約式を使用しないグループ化では、列内の個々の値のセットが効率良く計算されます。 これを行うには DISTINCT 句を使用します (Section 4.3.3 を参照)。
別の例を示します。 製品コードでグループ化された、テーブル上の 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_expressionHAVING 句内の式は、グループ化された式とグループ化されてない式 (この場合は集約関数が必要になります) の両方を参照することができます。
例を示します。
=> 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 を越えるグループに制限しています。集約式が、常に同じである必要がないことに注意してください。