SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] * | expression [ AS output_name ] [, ...] [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY expression [, ...] ] [ HAVING condition [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ] [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ] [ LIMIT { count | ALL } ] [ OFFSET start ] [ FOR UPDATE [ OF table_name [, ...] ] ] ここでfrom_itemは以下のいずれかです。 [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ] function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] ) from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]
SELECTは1つ以上のテーブルから行を返します。 SELECTの一般的な処理は以下の通りです。
FROMリストにある全要素が計算されます。 (FROMリストの各要素は実テーブルか仮想テーブルです。) FROMリストに複数の要素が指定された場合、それらはクロス結合されます。 (後述のFROM 句を参照してください。)
WHERE句が指定された場合、条件を満たさない行は全て出力から取り除かれます。 (後述のWHERE 句を参照してください。)
GROUP BY句が指定された場合、出力は、1つ以上の値に一致する行のグループに分割されます。 HAVING句が指定された場合、指定した条件を満たさないグループは取り除かれます。 (後述のGROUP BY 句とHAVING 句を参照してください。)
実際の出力行は、選択された各行に対して、SELECT出力式を使用して計算されたものです。 (後述のSELECT リストを参照してください。)
UNION、INTERSECT、および、EXCEPT演算子を使用すると、複数のSELECT文の出力を1つの結果集合にまとめることができます。 UNION演算子は、両方あるいは片方の結果集合に存在する行を全て返します。 INTERSECT演算子は両方の結果集合に存在する行を全て返します。 EXCEPT演算子は最初の結果集合にあり、2番目の結果集合に無い行を返します。 ALLが指定されない限り、これら3つとも、重複する行は取り除かれます。 (後述のUNION 句、INTERSECT 句、および、EXCEPT 句を参照してください。)
ORDER BY句が指定された場合、返される行は指定した順番でソートされます。 ORDER BYが指定されない場合は、システムが計算過程で最初に見付けたものという何らかの順番で行が返されます。 (後述のORDER BY 句を参照してください。)
DISTINCTは結果から重複行を取り除きます。 DISTINCT ONは、指定された式全てに一致する行を取り除きます。 (デフォルトの)ALLは重複した行も含め、全ての候補行を返します。 (後述のDISTINCT 句を参照してください。)
LIMITあるいはOFFSET句が指定された場合、SELECT文は結果行の一部分のみを返します。 (後述のLIMIT 句を参照してください。)
FOR UPDATE句により、SELECT文は、引続き実行される更新用に選択行をロックします。 (後述のFOR UPDATE 句を参照してください。)
テーブルから値を読み取るためにはSELECT権限が必要です。 FOR UPDATEを使用するためには、同様にUPDATE権限が必要です。
FROM句はSELECTの対象となるソーステーブルを1つ以上指定します。 複数のソースが指定された場合、結果は全てのソースの直積(クロス結合)となります。 しかし、通常は直積の小さな一部分を返すように制限するために制約条件が付け加えられます。
FROM句には以下の要素を指定できます。
既存のテーブルもしくはビューの名前です(スキーマ修飾名でも可)。 ONLYが指定された場合、そのテーブルのみがスキャンされます。 もし ONLYが指定されない場合、テーブルと(もしあれば)それを継承する全てのテーブルがスキャンされます。 子テーブルがスキャンされることを表すために、テーブル名に*を付けることができますが、現在のバージョンでは、子テーブルのスキャンはデフォルトで行われます。 (7.1より前のリリースでは、ONLYがデフォルトでした。) デフォルトの振舞いを変更するには、sql_inheritance設定オプションを変更します。
別名を含むFROMアイテムの代替名です。 別名は簡潔さのため、もしくは自己結合(同じテーブルが複数回スキャンされる場合です)の曖昧さをなくすために使われます。 別名が指定されている場合は、その別名によって実際のテーブル名または関数名が完全に隠されます。 例えば、FROM foo AS fと指定されている場合、以降のSELECT文ではこのFROMアイテムをfooではなくfとして参照する必要があります。 テーブルの別名があれば、そのテーブルの複数の列の名前を置き換える列の別名リストを記述することができます。
副SELECTはFROM句で使うことができます。 これは、その出力がこのSELECTコマンド実行時に一時テーブルとして作成されたかのように動作します。 副SELECTは括弧で囲まれなければならず、必ず別名が提供されなければなりません。
FROM句に関数呼び出しを使用することができます。 (これは特に関数が結果セットを返す場合に有用なものですが、任意の関数を使用することができます。) これは、その出力がこのSELECTコマンド実行時に一時テーブルとして作成されたかのように動作します。 また、別名を使用することもできます。 別名があれば、その関数の複合戻り型の複数の属性の代替名を提供する別名リストを記述することができます。 関数がrecordデータ型を返すものと定義されている場合は、別名すなわちASキーワードとそれに続く( column_name data_type [, ... ])という形式の列定義リストが必要です。 列定義リストは、関数によって返される実際の列の数およびデータ型に一致していなければなりません。
以下のいずれかです。
[ INNER ] JOIN
LEFT [ OUTER ] JOIN
RIGHT [ OUTER ] JOIN
FULL [ OUTER ] JOIN
CROSS JOIN
INNER、および、OUTER結合型では、結合条件、すなわち、NATURAL, ON join_condition、もしくは、USING (join_column [, ...])のいずれかを1つのみ指定する必要があります。 意味は後で説明します。 CROSS JOINでは、これらの句を記述しなくても構いません。
JOIN句は、2つのFROMアイテムを結び付けます。 入れ子の順番を決めるために、必要ならば括弧を使用してください。 括弧が無いと、JOINは左から右へと入れ子にされます。 どのような場合でもJOINは、カンマで分けられたFROM項目よりも強く結び付きます。
CROSS JOINとINNER JOINは、FROMの最上位で2つの項目を付けた結果と同一の、1つの直積を生成します。 しかし、(もしあれば)結合条件で制限をかけることができます。 CROSS JOINはINNER JOIN ON (true)と等価であり、条件によって削除される行はありません。 これらの結合型は記述上の便宜のためだけのものです。 従って、通常のFROMとWHEREを行わないと何も行いません。
LEFT OUTER JOINは条件に合う直積の全ての行(つまり、その結合条件を満たす全ての組み合わされた行)に加え、右側の行を持たないが結合条件を満たす左側のテーブルの各々の行のコピーを返します。 この左側の行は右側の行にNULL値を挿入することで結合されたテーブルの全幅に拡張されます。 どの行がマッチしているかを決める間、JOIN句自身の条件のみが考慮されることに注意してください。 他の外部結合条件は後で適用されます。
逆に、RIGHT OUTER JOINは全ての結合された行と、それぞれの当てはまるものがなかった右側の行(左側はNULLで拡張されています)に一行ずつを返します。 左と右の入力を入れ換えることでLEFT OUTER JOINに変換することもできるため、これはただの記述上の便宜でしかありません。
FULL OUTER JOINは、全ての結合された行、一致しなかった左側の行(を右側をNULLで拡張させた行)を1行ずつ、更に、一致しなかった右側の行(を左側をNULLで拡張させた行)を1行ずつ返します。
join_conditionは、結合においてどの行が一致したとみなすかどうかを指定する(WHERE句同様)boolean型の値を返す式です。
USING ( a, b, ... )句はON left_table.a = right_table.a AND left_table.b = right_table.b ...の省略形です。 USINGは等価となる列の組合せの両方ではなく片方のみが結合の出力に含まれることを意味します。
NATURALは2つのテーブル内の同じ名前を持つ全てを指定したUSINGリストの省略形です。
省略可能なWHERE句は通常以下の形式となります。
WHERE condition
ここで、conditionは、評価の結果boolean型を返す任意の式です。 この条件を満たさない行は全て出力から取り除かれます。 全ての変数参照に実際の行の値を代入した時に式が真を返す場合に、行は条件を満たします。
省略可能なGROUP BY句は通常以下の形式となります。
GROUP BY expression [, ...]
GROUP BYは、グループ化式で同じ値を共有する全ての行を1つの行に凝縮します。 expressionは、入力列名か、出力列(SELECTリスト項目)の名前もしくは序数、もしくは、入力列の値から計算される任意の式を取ることができます。 曖昧な場合、GROUP BYの名前は出力列名ではなく入力列名として解釈されます。
集約関数がもし使用されていると、各グループを生成する全ての行に対して計算され、グループ毎に分けた値が生成されます。 (一方GROUP BYが無い場合は、集約関数は全ての選択された行に対して計算され、1つの値を生成します。) GROUP BYがあると、集約関数内部以外でグループ化されていない列を参照するSELECTリストは無効になります。 グループ化されていない列について返される値は複数の値になってしまうからです。
省略可能なHAVINGは通常以下の形になります。
HAVING condition
ここでconditionはWHERE句で指定するものと同じです。
HAVINGはこの条件を満たさないグループ化された行を取り除きます。 HAVINGはWHEREとは違います。 WHEREでは、GROUP BYの適用前に、個別の行に対してフィルタを行い、HAVINGはGROUP BYで生成されたグループ化された行に対してフィルタを行います。 condition内の列参照は、集約関数内で表れる参照を除き、曖昧さが無いグループ化された列を参照しなければなりません。
SELECTリスト(SELECTとFROMの間にあるキーワード)は、SELECT文の出力行を形成する式を指定したものです。 この式では、FROM句内で計算された列を参照することができます(そして、通常は参照します)。 AS output_nameを使用して、出力列とは別の名前を指定できます。 この名前は主に表示用の列ラベルとして使われます。 また、ORDER BYとGROUP BY句内で列の値を参照するためにも使用されます。 しかし、WHEREやHAVING句では使用されず、ここには式を書かなければなりません。
式の代わりに、選択された行の全ての列を表す省略形として、出力リストに*と書くことができます。 また、そのテーブルのみに由来する列を表す省略形として、table_name.*と書くこともできます。
UNIONは通常以下の形式となります。
select_statement UNION [ ALL ] select_statement
select_statementは、ORDER BY、LIMIT、FOR UPDATE句を持たない、任意のSELECT文です。 (もし括弧で括られていたら、ORDER BYとLIMITは副式に付与できます。 括弧が無いと、これらの句は右側の入力式にではなく、UNIONの結果に対して適用されてしまいます。)
UNION演算子は、呼び出されたSELECT文で返される行の和集合を計算します。 少なくともどちらか片方に表れれば、その行は2つの結果セットの和集合内にあります。 直接UNIONの演算項目となるSELECT文は、同じ列数を返さなければならず、また、対応する列のデータ型には互換性が必要です。
ALLオプションが指定されていないと、UNIONの結果には重複行は含まれません。 ALLは重複除去を行いません。 (従って、通常UNION ALLはUNIONよりかなり高速です。 できるだけALLを使用してください。)
一つのSELECT文で複数のUNION演算子がある場合、括弧で示されない限り、その評価は左から右に行われます。
現時点では、FOR UPDATEをUNIONの結果に対しても、UNIONの何らかの入力に対しても指定することができません。
INTERSECTは通常以下の形式となります。
select_statement INTERSECT [ ALL ] select_statement
select_statementは、ORDER BY、LIMIT、FOR UPDATEを持たない、任意のSELECT文です。
INTERSECTは、呼び出されるSELECT文が返す行の積集合を計算します。 両方の結果セットに現れる場合、その行は2つの結果セットの積集合内に存在します。
ALLオプションが指定されていないと、INTERSECTの結果には重複行は含まれません。 ALLがある場合、左側テーブルにm個の重複、右側テーブルにn個の重複がある行は、結果セットにmin(m,n)個出現します。
一つのSELECT文で複数のINTERSECT演算子がある場合、括弧で示されない限り、その評価は左から右に行われます。 INTERSECTはUNIONよりも強い結び付きです。 つまり、A UNION B INTERSECT C はA UNION (B INTERSECT C)と解釈されます。
現時点では、FOR UPDATEをINTERSECTの結果に対しても、INTERSECTの何らかの入力に対しても指定することができません。
EXCEPTは通常以下の形式となります。
select_statement EXCEPT [ ALL ] select_statement
select_statementは、ORDER BY、LIMIT、FOR UPDATE句を持たない、任意のSELECT文です。
EXCEPTは、左側のSELECT文の結果に存在し、右側の結果には存在しない行の集合を計算します。
ALLオプションが指定されていないと、EXCEPTの結果には重複行は含まれません。 ALLがある場合、左側テーブルにm個の重複、右側テーブルにn個の重複がある行は、結果セットにmax(m-n,0)個出現します。
一つのSELECT文で複数のEXCEPT演算子がある場合、括弧で示されない限り、その評価は左から右に行われます。 EXCEPTの結び付きのレベルはUNIONと同じです。
現時点では、FOR UPDATEをEXCEPTの結果に対しても、EXCEPTの何らかの入力に対しても指定することができません。
省略可能なORDER BY句は通常以下の形式となります。
ORDER BY expression [ ASC | DESC | USING operator ] [, ...]
expressionには、出力列(SELECTリスト項目)の名前もしくは序数、あるいは、入力列値から形成される任意の式をとることができます。
ORDER BY句により、結果の行を指定した式に従ってソートすることができます。 もっとも左にある式で2つの行が等しくなった場合は、次の式に従い、それも等しければ更に次の式に進みます。 指定した式の全てで等しくなった場合は、実装に依存した順番で返されます。
序数とは、結果列の(左から右への)順序の位置です。 この機能により、一意な名前を持たない列に対して順序を定義することができます。 AS句を使用すれば結果列に名前を割り当てることができますので、これは決して必要なものではありません。
また、ORDER BY句には、SELECT結果リストに出現しない列を含む、任意の式を使用できます。 従って、以下の文は有効なものです。
SELECT name FROM distributors ORDER BY code;
この機能の制限は、UNION、INTERSECT、EXCEPTの結果にORDER BYを適用する場合は、式を使用できず、出力列の名前か序数のみを指定できるという点です。
ORDER BY式が、結果列名と入力列名の両方に一致する単なる名前であった場合、ORDER BYは結果列名としてそれを扱います。 これは、同じ状況におけるGROUP BYの選択とは反対です。 標準SQLとの互換性のためこの不整合が発生しています。
省略可能なキーワードASC(昇順)、DESC(降順)をORDER BY中の任意の式の後に付け加えることができます。 指定がなければ、デフォルトでASCが付いているものとして扱われます。 別方法に、順序指定演算子名をUSING 句に指定することができます。 通常、ASCはUSING <と、DESCはUSING >と同じです。 (しかし、ユーザ定義のデータ型の作者は、デフォルトのソート順が何かを正しく定義することができ、演算子に異なる名前を対応付けすることもできます。)
NULL値は他の値よりも高いものとしてソートされます。 言い替えると、昇順のソート順ではNULL値は最後に、降順のソート順ではNULL値は最初にソートされます。
文字型のデータでは、データベースクラスタの初期化時に決定されるロケール指定の照合順に従ってソートされます。
DISTINCTが指定されると、重複する行は全て結果セットから削除されます。 (各重複行の中で1行のみが保持されます。) ALLはこの反対のことを指定し、全ての行が保持されます。 これがデフォルトです。
DISTINCT ON ( expression [, ...] )は各行集合の中で与えられた式が等しいと評価した、最初の行のみを保持します。 DISTINCT ON式は、(上述の)ORDER BYと同じ規則で扱われます。 各集合の"最初の行"は、ORDER BYを使用して目的の行が最初に現れることを保証しない限り予測することはできません。 例えば、
SELECT DISTINCT ON (location) location, time, report FROM weather_reports ORDER BY location, time DESC;
は、各地点の最新の気象情報を取り出します。 しかし、ORDER BYを使用して各地点の時間値を降順に強制しなかったとしたら、各地点の予測できない時間の予報を得ることになります。
DISTINCT ON式はORDER BY式の最も左側に一致しなければなりません。 ORDER BY句は通常、各DISTINCT ONグループの中で希望する行の優先順位を決定する、追加的な式を含みます。
LIMIT句は2つの独立した副句から構成されます。
LIMIT { count | ALL } OFFSET start
countは返される行の最大数を指定し、一方、startは行を返し始める前に飛ばす行数を指定します。 両方とも指定された場合、start行が飛ばされ、そこから数えてcount行が返されます。
LIMITを使う時は、結果の行を一意な順番に強制するORDER BY句を使うのが良い考えです。 そうしないと、問い合わせの行の予測不可能なサブセットを得ることになります。 10から20行目までを出そうとしているかもしれませんが、どの順番の10から20行目までなのでしょうか? ORDER BYを指定しない限りどの順番なのか分からないのです。
問い合わせプランナは問い合わせ計画を作成する時にLIMITを考慮しますので、LIMITとOFFSETに何を使用するかによって(異なる行の順番を生む)異なった計画を得ることになるでしょう。 従って、問い合わせの、異なるサブセットを選択するために異なるLIMIT/OFFSETの値を使うと、ORDER BYで予測可能な結果の順序を強制しない限り、矛盾した結果を返します。 これはバグではありません。 これは、SQLがORDER BYで順番を制御しない限り、問い合わせの結果を特定の順番で返すことを約束しないという事実の本質的な結果なのです。
FOR UPDATEは以下の形式となります。
FOR UPDATE [ OF table_name [, ...] ]
FOR UPDATEを使用すると、問い合わせによって検索された行が更新用にロックされます。 これにより、現行のトランザクションが終了するまでは、これらの行が他のトランザクションによって変更されたり削除されたりすることがなくなります。 つまり、現行のトランザクションが終了するまでは、他のトランザクションがこれらの行に対してUPDATE、DELETE、または、SELECT FOR UPDATEを試行しても拒否されるのです。 また、他のトランザクションからのUPDATE、DELETE、または、SELECT FOR UPDATEによって選択されている行がロックされている場合にSELECT FOR UPDATEを実行しようとすると、SELECT FOR UPDATEはそのトランザクションが終了するのを待ち、その後更新された行をロックして返します (行が削除された場合は返しません)。 詳細は第12章を参照してください。
FOR UPDATE内に特定のテーブルが指定されている場合は、そのテーブルの行のみがロックされ、SELECT内の他のテーブルは通常通りに読み込まれるだけです。
FOR UPDATEは、返される行が対応する個々のテーブル行を明確に識別できない場合には使用することができません。 例えば、集約には使用できません。
LIMIT句とFOR UPDATE句の両方を使用したSELECTコマンドでは、LIMITで指定した行数よりも少ない行が返される場合があります。 LIMITで行の一部を選択した後、FOR UPDATEによるロック要求がブロックされるかもしれないからです。 SELECTのブロックが解除された時点で、問い合わせ条件に合わなくなっている可能性があり、こうした行がSELECTから返されません。
FOR UPDATEは、バージョン 7.3より前のPostgreSQLとの互換性のためにLIMITの前に書くことができます。 しかし、その場合でも実際にはLIMITの後に実行されるので、LIMITの後に書くことをお勧めします。
filmsテーブルをdistributorsテーブルと結合します。
SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM distributors d, films f WHERE f.did = d.did title | did | name | date_prod | kind -------------------+-----+--------------+------------+---------- The Third Man | 101 | British Lion | 1949-12-23 | Drama The African Queen | 101 | British Lion | 1951-08-11 | Romantic ...
全ての映画のlen列を合計しkind列によって結果をグループ化します。
SELECT kind, sum(len) AS total FROM films GROUP BY kind; kind | total ----------+------- Action | 07:34 Comedy | 02:58 Drama | 14:28 Musical | 06:42 Romantic | 04:38
全ての映画のlen列を合計しkind列によって結果をグループ化し、合計が5時間より少ないグループの合計を表示します。
SELECT kind, sum(len) AS total FROM films GROUP BY kind HAVING sum(len) < interval '5 hours'; kind | total ----------+------- Comedy | 02:58 Romantic | 04:38
以下の二つの例は個別の結果を二番目の列(name)の内容に基づいてソートする、同じ方法です。
SELECT * FROM distributors ORDER BY name; SELECT * FROM distributors ORDER BY 2; did | name -----+------------------ 109 | 20th Century Fox 110 | Bavaria Atelier 101 | British Lion 107 | Columbia 102 | Jean Luc Godard 113 | Luso films 104 | Mosfilm 103 | Paramount 106 | Toho 105 | United Artists 111 | Walt Disney 112 | Warner Bros. 108 | Westward
次の例は、結果をそれぞれのテーブルでWで始まるものだけに制限して、どのようにdistributorsテーブルとactorsテーブルの結合を獲得するかを表しています。 重複しない行のみが必要なので、ALLキーワードは省略されています。
distributors: actors: did | name id | name -----+-------------- ----+---------------- 108 | Westward 1 | Woody Allen 111 | Walt Disney 2 | Warren Beatty 112 | Warner Bros. 3 | Walter Matthau ... ... SELECT distributors.name FROM distributors WHERE distributors.name LIKE 'W%' UNION SELECT actors.name FROM actors WHERE actors.name LIKE 'W%'; name ---------------- Walt Disney Walter Matthau Warner Bros. Warren Beatty Westward Woody Allen
この例は、FROM句内での関数の使用方法を、列定義リストがある場合と無い場合の両方に関して示しています。
CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$ SELECT * FROM distributors WHERE did = $1; $$ LANGUAGE SQL; SELECT * FROM distributors(111); did | name -----+------------- 111 | Walt Disney CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS $$ SELECT * FROM distributors WHERE did = $1; $$ LANGUAGE SQL; SELECT * FROM distributors_2(111) AS (f1 int, f2 text); f1 | f2 -----+------------- 111 | Walt Disney
当然ながら、SELECT文は標準SQLと互換性があります。 しかし、拡張機能や実現されていない機能もいくつかあります。
PostgreSQLでは、FROM句を省略することができます。 以下のように素直に単純な式の結果を計算させることができます。
SELECT 2+2; ?column? ---------- 4
他のSQLデータベースでは、こういったSELECTを行うためにはダミーの1行テーブルを使わなければなりません。
明確さはありませんが、普通のSELECTからテーブルを省く、以下のような方法があります。
SELECT distributors.* WHERE distributors.name = 'Westward'; did | name -----+---------- 108 | Westward
これは、SELECTで参照されるけれども FROMでは記述されないテーブルが、暗黙のFROMアイテムに追加されるので、動作します。
これは便利な省略形ですが、間違って使いやすいです。 例えば、次のコマンド
SELECT distributors.* FROM distributors d;
はおそらく間違いです。 ほとんどのユーザは、
SELECT d.* FROM distributors d;
を意図し、実際に得られる制約の無い結合
SELECT distributors.* FROM distributors d, distributors distributors;
を意図しないでしょう。 この種の間違いを検出できるように、PostgreSQLは、明示的なFROM句があっても、暗黙的なFROM機能がSELECT文で使用されている場合に警告を発します。 また、add_missing_fromパラメータを偽に設定することで、暗黙的なFROM機能を無効にすることができます。
標準SQLでは、省略可能なASキーワードは単なるノイズで、その意味に影響すること無く省略することができます。 PostgreSQLのパーサは出力列の名前を変更する際にこのキーワードを必要とします。 なぜなら、型の拡張機能を考えると、このキーワードがないと構文解析の不明瞭さにつながるからです。 しかし、FROMアイテムではASは省略可能です。
SQL-92では、ORDER BY句は、結果の列名か序数のみを使用することができ、GROUP BY句は、入力列名からなる式のみを使用できます。 PostgreSQLは、これらの句でそれぞれ他の選択が同じようにできるように拡張しています。 (しかし不明瞭さがある場合は標準の解釈が使用されます。) また、PostgreSQLではどちらの句にも任意の式を指定できます。 式で使われる名前は常に結果列の名前ではなく入力列の名前としてみなされることに注意してください。
SQL:1999では、SQL-92との上位互換性が全く無い、多少異なった定義を使用します。 しかし、ほとんどの場合、PostgreSQLはSQL:1999と同じ方法でORDER BYやGROUP BYを解釈します。