PostgreSQL 9.0.4文書 | ||||
---|---|---|---|---|
前のページ | 巻戻し | 第 7章問い合わせ | 早送り | 次のページ |
テーブル式は、テーブルを計算するためのものです。 テーブル式にはFROM句が含まれており、その後ろにオプションとしてWHERE句、GROUP BY句、HAVING句を付けることができます。 単純なテーブル式は、単にディスク上のいわゆる基本テーブルと呼ばれるテーブルを参照するだけです。 しかし、様々な方法で基本テーブルを修正したり、組み合わせたりするためにより複雑な式を使用することができます。
テーブル式のオプションWHERE句、GROUP BY句、およびHAVING句は、FROM句で派生したテーブル上に対して次々に変換を実行するパイプラインを指定します。 これらの変換によって仮想テーブルが1つ生成されます。 そしてこの仮想テーブルの行が選択リストに渡され、問い合わせの出力行が計算されます。
FROM句は、カンマで分けられたテーブル参照リストで与えられる1つ以上のテーブルから、1つのテーブルを派生します。
FROM table_reference [, table_reference [, ...]]
テーブル参照は、テーブル名(スキーマで修飾することもできます)、または、副問い合わせ、テーブル結合、それらの複雑な組み合わせなどから派生されたテーブルを取ることができます。 FROM句に複数のテーブル参照がある場合、それらは、WHERE句、GROUP BY句、およびHAVING句で変換できる中間的な仮想テーブルを作るためにクロス結合(下記を参照)され、最終的には全てのテーブル式の結果となります。
テーブル参照で、テーブルの継承階層の親テーブルの名前を指定すると、テーブル名の前にONLYキーワードがない場合は、テーブル参照はそのテーブルだけでなくその子テーブルに継承された全ての行を生成します。 しかし、この参照は名前を指定したテーブルに現れた列のみを生成し、子テーブルで追加された列は無視されます。
結合テーブルは、2つの(実または派生)テーブルから、指定した結合種類の規則に従って派生したテーブルです。 内部結合、外部結合、およびクロス結合が使用可能です。
結合の種類
T1 CROSS JOIN T2
T1および T2からの全ての可能な行の組み合わせ(つまりデカルト積)に対し、結合されたテーブルはT2の全ての列が続くT1の全ての列から成る行を含みます。 テーブルがそれぞれN行とM行で構成されているとすると、結合されたテーブルの行数はN * M行となります。
FROM T1 CROSS JOIN T2はFROM T1, T2と同じです。 また(後述の)FROM T1 INNER JOIN T2 ON TRUEとも同じです。
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 T2
INNERや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つの列になるという例外があります(そしてSELECT *が使われているとそれらは最初に現れます)。
最後に、NATURALはUSINGの略記形式です。 2つの入力テーブルの両方に含まれている全ての列名で構成されるUSINGリストを形成します。 USINGと同様、これらの列は出力テーブルに一度だけ現れます。
修飾付き結合には次のものがあります。
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 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句の中の制約は結合の後に処理されることによります。
テーブルや複雑なテーブル参照は、問い合わせの後の方で派生テーブルを参照するために一時的な名前を与えることができます。 これをテーブルの別名と呼びます。
テーブルの別名を作成するには以下のようにします。
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.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を参照してください。
テーブル関数は、基本データ型(スカラ型)、もしくは複合データ型(テーブル行)からなる行の集合を生成する関数です。 これらは、テーブル、ビュー、問い合わせの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を返すものと宣言されています。 実際の列集合は、パーサが例えば*がどのように展開されるかを理解できるように呼び出した問い合わせ内で指定されなければなりません。
WHERE句の構文は、以下の通りです。
WHERE search_condition
ここで、search_conditionにはboolean型を返すどのような評価式(項4.2を参照)も指定できます。
FROM句の処理が終わった後、派生した仮想テーブルの各行は検索条件と照合されます。 条件の結果が真の場合、その行は出力されます。 そうでない(すなわち結果が偽またはNULLの)場合は、その行は捨てられます。 一般的に検索条件は、FROM句で生成されたテーブルの最低1列を参照します。 これは必須ではありませんが、そうしないとWHERE句はまったく意味がなくなります。
注意: 内部結合の結合条件は、WHERE句でもJOIN句でも記述することができます。 例えば、以下のテーブル式は等価です。
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構文を使用すると、SQL標準であってもおそらく他のSQLデータベース管理システムに移植できません。 外部結合については、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の値がないからです。 グループごとに単一の値を持つので、選択リストでGROUP BYで指定した列を参照することができます。
一般的に、テーブルがグループ化されている場合、GROUP BYでリストされていない列は集約式を除いて参照することはできません。 集約式の例は以下の通りです。
=> SELECT x, sum(y) FROM test1 GROUP BY x; x | sum ---+----- a | 4 b | 5 c | 2 (3 rows)
上記でsum() は、グループ全体について単一の値を計算する集約関数です。 使用可能な集約関数の詳細については、項9.18を参照してください。
ティップ: 集約式を使用しないグループ化は、列内の重複しない値の集合を効率良く計算します。 これはDISTINCT句の使用で同じように達成することができます(項7.3.3を参照してください)。
別の例を示します。 これは各製品の総売上を計算します (全製品の総売上ではありません)。
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句は、グループ化されていない列によって行を選択している(この式では最近の4週間の売上のみが真になります)のに対し、HAVING句は出力を売上高が5000を超えるグループに制限しています。 集約式が、問い合わせ内で常に同じである必要がないことに注意してください。
ある問い合わせが集約関数を含んでいて、しかしGROUP BY句がない場合、グループ化は依然として行われます。 結果は単一グループ行(またはHAVINGで単一行が削除されれば、行が多分全くなくなる)となります。 HAVING句を含んでいれば、どんな集約関数呼び出し、またはGROUP BY句が存在しなくても同じことが言えます。
問い合わせがウィンドウ関数(項3.5、項9.19と項4.2.8を参照)を含んでいれば、これらの関数はグループ化、集約およびHAVING条件検索が行われた後に評価されます。つまり、問い合わせがどんな集約、GROUP BY、または HAVINGを使用すれば、ウィンドウ関数により見える行はFROM/WHEREでの本来のテーブル行ではなく、グループ行となります。
複数のウィンドウ関数が使用された場合、そのウィンドウ定義にある構文的に同等であるPARTITION BYおよびORDER BY句を持つ全てのウィンドウ関数は、データ全体に渡って単一の実行手順により評価されることが保証されています。 従って、ORDER BYが一意的に順序付けを決定しなくても同一の並び替え順序付けを見ることができます。 しかし、異なるPARTITION BYまたはORDER BY仕様を持つ関数の評価については保証の限りではありません。 (このような場合、並び替え手順がウィンドウ関数評価の諸手順間で一般的に必要となり、ORDER BYが等価と判断する行の順序付けを保存するような並び替えは保証されません。)
現時点では、ウィンドウ関数は常に事前に並び替えられたデータを必要とするので、問い合わせ出力はウィンドウ関数のPARTITION BY/ORDER BY句の1つかどれかに従って順序付けされます。 とはいえ、これを信頼することは薦められません。 結果が特定の方法で並び替えられる確信を得たいのなら、明示的な最上階層のORDER BYを使用します。