WITH句

WITH句により主問い合わせ内で名前により参照可能な、1つ以上の副問い合わせを指定することができます。 副問い合わせは実質的に主問い合わせの間の一時的なテーブルかビューのように動作します。 各副問い合わせはSELECT、TABLE、VALUES、INSERT、UPDATE、DELETEにすることができます。 WITH内でデータ変更文（INSERT、UPDATE、DELETE）を記述する場合は、RETURNING句を含めるのが普通です。 主問い合わせで読み取られる一時テーブルを形成するのは、RETURNINGの出力であり、文が変更する背後のテーブルではありません。 RETURNINGを省いても文は実行されますが、出力を生成しませんので、主問い合わせでテーブルとして参照することができません。

（スキーマ修飾がない）名前を各WITH問い合わせで指定しなければなりません。 列名のリストをオプションで指定することもできます。 これを省略すると、列名は副問い合わせから推定されます。

RECURSIVEが指定されると、SELECT副問い合わせは自身で名前により参照することができます。 こうした副問い合わせは以下のような形式でなければなりません。

non_recursive_term UNION [ ALL | DISTINCT ] recursive_term

ここで再帰的な自己参照はUNIONの右辺に現れなければなりません。 問い合わせ当たり1つの再帰的な自己参照のみが許されます。 再帰的なデータ変更文はサポートされていませんが、データ変更文で再帰的なSELECTの結果を使用することができます。 例は7.8を参照してください。

RECURSIVEには他にも、WITH問い合わせが順序通りでなくても構わないという効果があります。 つまり、問い合わせはリストの後にある別のものを参照することができます。 （しかし巡回する参照や相互的な参照は実装されていません。） RECURSIVEがないと、WITH問い合わせは主問い合わせが共通するWITH問い合わせのうち、WITHリストの前方にあるもののみを参照することができます。

WITH句に複数の問い合わせがある場合、RECURSIVEはWITHの直後に一度だけ書くべきです。 再帰や前方参照を使わない問い合わせには効果はないですが、WITH句内の問い合わせすべてに適用されます。

主問い合わせとWITH問い合わせは（理論的には）同時に実行されます。 このことは、WITH中のデータ更新文の効果は、RETURNING出力の読み込みを行ったことによるものを除き、問い合わせ中の他の部分から見えないことを意味します。 2つのそうしたデータ更新文が同じ行を更新しようとした時の結果は不定です。

WITH問い合わせの重要な特性は、これらを主問い合わせが複数回参照していたとしても、主問い合わせの実行当たり通常一度のみ評価される点です。 特にデータ変更文は、主問い合わせがその出力のすべてまたは一部を読み取るかに関係なく、本当に一度のみ実行されることが保証されています。

しかし、WITH問い合わせにNOT MATERIALIZEDと印を付けることにより、この保証を取り除くことができます。 その場合、WITH問い合わせは、主問い合わせのFROM句中の単純な副SELECTであるかのように可能な限り主問い合わせ中に畳み込むことができます。 この結果、主問い合わせがWITH問い合わせを複数回参照している場合には複数回の計算が行われます。 しかし、そこで使用される問い合わせがWITH問い合わせ全体の出力のうちの数行しか必要としないなら、NOT MATERIALIZEDは問い合わせを連携して最適化することができるので、全体のコストの節約ができます。 再帰問い合わせあるいは副作用のある問い合わせ（すなわち揮発性の関数を含まない単純SELECTではないもの）にNOT MATERIALIZEDを適用しても無視されます。

デフォルトでは、主問い合わせ中のFROM句で正確に一度だけ使われているなら、副作用のないWITH問い合わせは主問い合わせに畳み込まれます。 これにより意味論的に不可視の二つの問い合わせレベルが共同して最適化されることを可能にします。 しかし、WITH問い合わせにMATERIALIZEDと印を付けることにより、そうした畳込みを防ぐことができます。 たとえば、プランナが悪いプランを選択するのを防ぐために最適化障壁としてWITH問い合わせを使っている場合にこれは有用です。 PostgreSQLバージョン12よりも前ではそうした畳込みは決して行われていなかったので、古いバージョン用に書かれた問い合わせはWITHが最適化障壁として働くことに依存しているかもしれません。

追加情報については7.8を参照してください。

FROM句

FROM句にはSELECTの対象となるソーステーブルを1つ以上指定します。 複数のソースが指定された場合、結果は全てのソースの直積（クロス結合）となります。 しかし、通常は（WHEREを介して）制約条件を付けて、直積のごく一部を返すように結果行を限定します。

FROM句には以下の要素を指定できます。

WHERE句

WHERE句の一般的な構文は以下の通りです（この句は省略可能です）。

WHERE condition

conditionは、評価の結果としてboolean型を返す任意の式です。 この条件を満たさない行は全て出力から取り除かれます。 全ての変数に実際の行の値を代入して、式が真を返す場合、その行は条件を満たすとみなされます。

GROUP BY句

GROUP BY句の一般的な構文は以下の通りです（この句は省略可能です）。

GROUP BY grouping_element [, ...]

GROUP BYは、グループ化のために与えられた式を評価し、結果が同じ値になった行を1つの行にまとめる機能を持ちます。 grouping_elementの内側で使われるexpressionには、入力列の名前、出力列（SELECTリスト項目）の名前/序数、あるいは入力列の値から計算される任意の式を取ることができます。 判断がつかない時は、GROUP BYの名前は出力列名ではなく入力列名として解釈されます。

グループ化の要素としてGROUPING SETS、ROLLUP、CUBEのいずれかが指定されている場合、GROUP BY句は全体でいくつかの独立したグループ化セットを定義します。 この効果は、個々のグループ化セットをGROUP BY句で定義する副問い合わせをUNION ALLするのと同等です。 グループ化セットの処理の詳細については、7.2.4を参照してください。

集約関数が使用された場合、各グループ内の全ての行を対象に計算が行われ、グループごとに別々の値が生成されます （集約関数が使われていてGROUP BYがない場合、その問い合わせは選択された全ての行からなる1つのグループを持つものとして扱われます）。 集約関数の入力となる行の集合は、集約関数の呼び出しにFILTER句を付けることで、さらに絞り込むことができます。 詳しくは4.2.7を参照してください。 FILTER句があると、その条件に適合する行だけが集約関数の入力行に取り込まれます。

GROUP BYが存在する場合、あるいは集約関数が存在する場合、集約関数内部以外で、グループ化されていない列を参照する、あるいはグループ化されていない列がグループ化された列に関数依存するSELECTリストの式は無効になります。 こうしないとグループ化されていない列について返される値は複数の値になってしまう可能性があるからです。 グループ化された列(またはその部分集合)がグループ化されていない列を含むテーブルの主キーである場合、関数従属性が存在します。

すべての集約関数は、HAVING句やSELECTリストのどの「スカラー」式よりも先に評価されることに注意してください。 これは例えば、CASE式を集約関数の評価をスキップするために使うことはできない、ということを意味します。 4.2.14を参照してください。

現在は、FOR NO KEY UPDATE、FOR UPDATE、FOR SHARE、FOR KEY SHAREをGROUP BYと合わせて使うことはできません。

HAVING句

HAVING句の一般的な構文は以下の通りです（この句は省略可能です）。

HAVING condition

conditionはWHERE句で指定するものと同じです。

HAVINGは、グループ化された行の中で、条件を満たさない行を取り除く機能を持ちます。 HAVINGとWHEREは次の点が異なります。 WHEREが、GROUP BYの適用前に個々の行に対してフィルタを掛けるのに対し、HAVINGは、GROUP BYの適用後に生成されたグループ化された行に対してフィルタをかけます。 condition内で使用する列は、集約関数内で使用される場合とグループ化されない列がグループ化される列に関数依存する場合を除き、グループ化された列を一意に参照するものでなければなりません。

HAVING句があると、GROUP BY句がなかったとしても問い合わせはグループ化された問い合わせになります。 GROUP BY句を持たない問い合わせが集約関数を含む場合と同様です。 選択された行はすべて、1つのグループを形成するものとみなされます。また、SELECTリストとHAVING句では、集約関数が出力するテーブル列しか参照することができません。 こうした問い合わせでは、HAVINGが真の場合には単一の行を、真以外の場合は0行を出力します。

現在は、FOR NO KEY UPDATE、FOR UPDATE、FOR SHARE、FOR KEY SHAREをHAVINGと合わせて使うことはできません。

WINDOW句

WINDOW句(省略可能)の一般的な構文は以下の通りです。

WINDOW window_name AS ( window_definition ) [, ...]

ここでwindow_nameは、OVER句やこの後のウィンドウ定義で参照することができる名前です。 また、window_definitionは以下の通りです。

[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

existing_window_nameを指定する場合、それはWINDOWリスト内のそれより前にある項目を参照しなければなりません。 新しいウィンドウはそのPARTITION BY句をその項目からコピーします。 ORDER BY句があった場合も同様です。 この場合、新しいウィンドウでは独自のPARTITION BY句を指定することはできません。 また、コピーされたウィンドウがORDER BYを持たない場合のみORDER BYを指定することができます。 新しいウィンドウは常に独自のフレーム句を使用します。 コピーされたウィンドウはフレーム句を指定してはなりません。

PARTITION BYリストの要素はGROUP BY句の要素とほとんど同じように解釈されます。 ただし、こちらは常に単純な式であり、出力列の名前や番号ではないことが異なります。 他にも違いがあり、これらの式は、通常のGROUP BY句では許されない、集約関数を含めることができるという点です。 グループ化および集約処理の後にウィンドウ処理が動作するため、これらでは許されています。

同様に、ORDER BYリストの要素は文レベルのORDER BY句の要素とほとんど同じように解釈されます。 ただし、この式は常に単純な式であり、出力列の名前や番号ではないことが異なります。

frame_clauseを指定すると、（すべてではありませんが）フレームに依存するウィンドウ関数用のウィンドウフレームを定義できます。 ウィンドウフレームは、問い合わせの各行(現在の行と呼ばれます)に関連する行の集合です。 frame_clauseは以下のいずれかを取ることができます。

{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]

ここでframe_startとframe_endは以下のいずれかを取ることができます。

UNBOUNDED PRECEDING
offset PRECEDING
CURRENT ROW
offset FOLLOWING
UNBOUNDED FOLLOWING

また、frame_exclusionには以下のいずれかを取ることができます。

EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS

frame_endが省略された場合、デフォルトでCURRENT ROWとなります。 frame_startはUNBOUNDED FOLLOWINGとすることができない、frame_endはUNBOUNDED PRECEDINGとすることができない、また、frame_startとframe_endのオプションの上記リストでframe_endの選択をframe_startの選択よりも手前に現れるものにはできない、という制限があります。 例えばRANGE BETWEEN CURRENT ROW AND offset PRECEDINGは許されません。

デフォルトのフレーム化オプションはRANGE UNBOUNDED PRECEDINGです。 これはRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWと同じで、 パーティションの先頭から現在の行の最後のピア（ウィンドウのORDER BY句が現在行と同等とみなす行、ORDER BYが無ければ全ての行がピア）までのすべての行をフレームとします。 一般的に、RANGEやROWS、GROUPSのモードにかかわらず、UNBOUNDED PRECEDINGはフレームがパーティションの先頭行から開始することを意味し、同様にUNBOUNDED FOLLOWINGはフレームがパーティションの最終行で終了することを意味します。 ROWSモードではCURRENT ROWはフレームが現在の行で開始または終了することを意味しますが、RANGEあるいはGROUPSモードではフレームがORDER BY順序における現在行の最初または最後のピアで開始または終了することを意味します。 offset PRECEDINGおよびoffset FOLLOWINGオプションの意味はフレームのモードによって異なります。 ROWSモードでは、offsetはフレームが現在行の何行前または何行後に開始または終了するかを示す整数です。 GROUPSモードでは、offsetはフレームが現在行のピアグループからピアグループ何個、前または後で開始または終了するかを示す整数です。 ここでピアグループとはウィンドウのORDER BY句において等価の行のグループです。 RANGEモードでは、offsetオプションを使うには、ウィンドウ定義に一つだけORDER BY列があることが必要です。 それで、整列する列の値がoffsetを超えないだけ、現在行の整列する列の値より小さい（PRECEDINGに対して）、あるいは、より大きい（FOLLOWINGに対して）行がフレームに含まれます。 この場合、offset式のデータ型は整列する列のデータ型によって決まります。 数値の整列する列に対するoffsetは一般的に整列する列と同じ型ですが、日付時刻の整列する列に対してはintervalになります。 これら全ての場合で、offsetの値は非NULLかつ非負でなければなりません。 また、offsetが単純な定数である必要はありませんが、変数や集約関数、ウィンドウ関数を含めることはできません。

frame_exclusionオブションは現在行の周辺の行を、フレーム開始とフレーム終了のオプションにより含まれるものであっても、フレームから除外することができます。 EXCLUDE CURRENT ROWはフレームから現在行を除外します。 EXCLUDE GROUPはフレームから現在行とその整列ピアを除外します。 EXCLUDE TIESは現在行自身を除いた現在行のピアをフレームから除外します。 EXCLUDE NO OTHERSは単に、現在行もそのピアも除外しないというデフォルトの振る舞いを明示的に指定します。

ORDER BY順序によりその行を一意に順序付けできない場合、ROWSモードが予期できない結果をもたらす可能性があることに注意して下さい。 RANGEおよびGROUPSモードは、ORDER BY順序におけるピアとなる行が同等に扱われる、すなわち、与えられたピアグループの全行がフレームに入るか除外されるように設計されています。

WINDOW句の目的は、問い合わせのSELECTリストまたはORDER BY句に記載されるウィンドウ関数の動作を規定することです。 これらの関数はそのOVER句において名前でWINDOW句の項目を参照することができます。 しかしWINDOW句の項目は他で参照される必要はありません。 問い合わせ内で使用されなかったものは、単に無視されます。 ウィンドウ関数呼び出しはOVER句でウィンドウ定義を直接規定することができますので、WINDOW句を全く使わずにウィンドウ関数を使用することができます。 しかしWINDOW句は、同じウィンドウ定義が複数のウィンドウ関数で必要とされる場合に入力量を省くことができます。

現在は、FOR NO KEY UPDATE、FOR UPDATE、FOR SHARE、FOR KEY SHAREをWINDOWと合わせて使うことはできません。

ウィンドウ関数に関する詳細については3.5、4.2.8、7.2.5を参照してください。

SELECTリスト

SELECTリスト（SELECTキーワードとFROMキーワードの間にあるもの）は、SELECT文の出力行を形成する式を指定するものです。 この式では、FROM句で処理後の列を参照することができます（通常は実際に参照します）。

テーブルの場合と同様に、SELECTの出力列はすべて名前を持ちます。 簡単なSELECTでは、この名前は列に表示用のラベルを付けるために使用されるだけです。 しかしSELECTが大規模な問い合わせの副問い合わせである場合、大規模な問い合わせ側で副問い合わせで生成された仮想のテーブルの列名としてこの名前が参照されます。 出力列として使用するための名前を指定するためには、列式の後にAS output_nameと記述してください。 （希望する列名がPostgreSQLのキーワード（付録Cを参照）に一致しない場合にのみASを省略することができます。 将来あり得るキーワードの追加に備えるために、常にASを記述する、あるいは、出力名を二重引用符で括ることを推奨します。） 列名を指定しない場合、名前はPostgreSQLにより自動的に付けられます。 列式が単純な列参照であれば、つけられる名前はその列の名前と同じものです。 より複雑な場合では、関数名または型名が使用されるかもしれません。さもなければ?column?のように生成される名前になるかもしれません。

ORDER BY句とGROUP BY句内で列の値を参照する時も、出力列名を使用できます。 しかし、WHEREやHAVING句では使用できません。これらでは式を書かなければなりません。

リストには、選択された行の全ての列を表す省略形として、式ではなく*と書くことができます。 また、そのテーブルに由来する列のみを表す省略形として、table_name.*と書くこともできます。 このような場合、ASにより新しい名前を指定することはできません。 出力列名はテーブルの列名と同一になります。

標準SQLによれば、出力リスト内の式は、DISTINCT、ORDER BY、LIMITを適用する前に計算することになっています。 DISTINCTを使う場合は、これは明らかに必要です。 なぜなら、そうしなければどの値がDISTINCTであるかわからないからです。 しかし、多くの場合、ORDER BYやLIMITの後で出力式を計算する方が便利です。 特に出力式が揮発性(volatile)あるいは高価な式を含んでいる場合はそうです。 この動作により、関数の評価順序はより直感的になり、出力に現れない行については評価されなくなります。 PostgreSQLでは、式がDISTINCT、ORDER BY、GROUP BYの中で参照されていない限り、ソートと制限(limit)の後にそれらの式を実際に評価します。 （この反例として、SELECT f(x) FROM tab ORDER BY 1 では明らかにf(x)をソートの前に評価しなければなりません。） 集合を返す関数を含む出力式は、ソートの後、制限の前に実際の評価が行われ、これによりLIMITが集合を返す関数の出力を制限することになります。

DISTINCT句

SELECT DISTINCTが指定されると、重複する行は全て結果セットから削除されます （重複するグループの中で1行が保持されます）。 SELECT ALLはこの反対で、全ての行が保持されます。 デフォルトはこちらです。

SELECT 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グループの中での行の優先順位を決定する追加的な式を含みます。

現在は、FOR NO KEY UPDATE、FOR UPDATE、FOR SHARE、FOR KEY SHAREをDISTINCTと合わせて使うことはできません。

UNION句

UNION句の一般的な構文は以下の通りです。

select_statement UNION [ ALL | DISTINCT ] select_statement

select_statementには、ORDER BY、LIMIT、FOR NO KEY UPDATE、FOR UPDATE、FOR SHARE、FOR KEY SHARE句を持たない任意のSELECT文が入ります （ORDER BYとLIMITは、括弧で囲めば副式として付与することができます。 括弧がない場合、これらの句は右側に置かれた入力式ではなく、UNIONの結果に対して適用されてしまいます）。

UNION演算子は、2つのSELECT文が返す行の和集合を作成します。 この和集合には、2つのSELECT文の結果集合のいずれか（または両方）に存在する行が全て含まれています。 UNIONの直接のオペランドとなる2つのSELECT文が返す列数は、同じでなければなりません。また、対応する列のデータ型には互換性が存在する必要があります。

ALLオプションが指定されていない限り、UNIONの結果には重複行は含まれません。 ALLを指定するとこのような重複除去が行われません （したがって、通常UNION ALLはUNIONよりかなり高速です。 できればALLを使用してください）。 重複行を除去するデフォルトの動作を明示的に指定するためにDISTINCTを記述することができます。

1つのSELECT文に複数のUNION演算子がある場合、括弧がない限り、それらは左から右に評価されます。

現時点では、UNIONの結果やUNIONに対する入力に、FOR NO KEY UPDATE、FOR UPDATE、FOR SHARE、FOR KEY SHAREを指定することはできません。

INTERSECT句

INTERSECT句の一般的な構文は以下の通りです。

select_statement INTERSECT [ ALL | DISTINCT ] select_statement

select_statementには、ORDER BY、LIMIT、FOR NO KEY UPDATE、FOR UPDATE、FOR SHARE、FOR KEY SHARE句を持たない、任意のSELECT文が入ります。

INTERSECTは、2つのSELECT文が返す行の積集合を計算します。 この積集合に含まれるのは、2つのSELECT文の結果集合の両方に存在する行です。

ALLオプションを指定しない限り、INTERSECTの結果に重複行は含まれません。 ALLが指定された場合、左側テーブルにm個、右側テーブルにn個の重複がある行は、結果集合ではmin(m,n)個出現します。 重複行を除去するデフォルトの動作を明示的に指定するためにDISTINCTを記述することができます。

1つのSELECT文に複数のINTERSECT演算子がある場合、括弧がない限り、それらは左から右に評価されます。 INTERSECTはUNIONよりも強い結び付きを持ちます。 つまり、A UNION B INTERSECT C はA UNION (B INTERSECT C)と解釈されます。

現時点では、INTERSECTの結果やINTERSECTに対する入力に、FOR NO KEY UPDATE、FOR UPDATE、FOR SHAREまたはFOR KEY SHAREを指定することはできません。

EXCEPT句

EXCEPT句の一般的な構文は以下の通りです。

select_statement EXCEPT [ ALL | DISTINCT ] select_statement

select_statementには、ORDER BY、LIMIT、FOR NO KEY UPDATE、FOR UPDATE、FOR SHARE、FOR KEY SHARE句を持たない、任意のSELECT文が入ります。

EXCEPTは、左側のSELECT文の結果には存在し、右側のSELECT文の結果には存在しない行の集合を生成します。

ALLオプションが指定されていない限り、EXCEPTの結果には重複行は含まれません。 ALLがある場合、左側テーブルにm個、右側テーブルにn個の重複がある行は、結果集合ではmax(m-n,0)個出現します。 重複行を除去するデフォルトの動作を明示的に指定するためにDISTINCTを記述することができます。

1つのSELECT文に複数のEXCEPT演算子がある場合、括弧がない限り、それらは左から右に評価されます。 EXCEPTの結び付きの強さはUNIONと同じです。

現時点では、EXCEPTの結果やEXCEPTに対する入力に、FOR NO KEY UPDATE、FOR UPDATE、FOR SHAREまたはFOR KEY SHAREを指定することはできません。

ORDER BY句

ORDER BY句の一般的な構文は以下の通りです（この句は省略可能です）。

ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...]

ORDER BY句を使うと、結果行を指定した式（複数可）に従ってソートすることができます。 最も左側の式を使って比較した結果、2つの行が等しいと判断された場合は、1つ右側の式を使って比較します。その結果も等しければ、さらに次の式に進みます。 指定した全ての式で等しいと判断された場合は、実装に依存した順番で返されます。

expressionには、出力列（SELECTリスト項目）の名前または序数、あるいは入力列値から形成される任意の式を取ることができます。

序数は、出力列の位置（左から右に割り当てられます）を示します。 これを使うと、一意な名前を持たない列の順序を定義することができます。 AS句を使用すれば出力列に名前を割り当てることができるので、これはどうしても必要な機能というわけではありません。

また、ORDER BY句には、SELECT出力リストに出現しない列を含む、任意の式を使用できます。 したがって、以下の文は有効です。

SELECT name FROM distributors ORDER BY code;

ただし、UNION、INTERSECT、EXCEPTの結果にORDER BYを適用する場合は、式は使用できず、出力列の名前か序数のみを指定できるという制限があります。

ORDER BYの式として出力列名と入力列名の両方に一致する単なる名前が与えられた場合、ORDER BYはそれを出力列名として扱います。 これは、同じ状況におけるGROUP BYの選択とは反対です。 この不整合は、標準SQLとの互換性を保持するために発生しています。

ORDER BY中の任意の式の後に、キーワードASC（昇順）、DESC（降順）を付加することができます(省略可能)。 指定がなければ、デフォルトでASCがあるものとして扱われます。 その他、順序を指定する演算子名をUSING句に指定する方法もあります。 順序指定演算子は何らかのB-Tree演算子族の小なりまたは大なり演算子でなければなりません。 通常、ASCはUSING <と、DESCはUSING >と同じです （ただし、ユーザ定義データ型の作成時には、デフォルトのソート順を定義することができます。また、異なる名前の演算子と対応付けすることもできます）。

NULLS LASTが指定されると、NULL値はすべての非NULL値の後にソートされます。 NULLS FIRSTが指定されると、NULL値はすべての非NULL値の前にソートされます。 どちらも指定されない場合のデフォルト動作は、明示的あるいは暗黙的なASCの場合はNULLS LAST、DESCが指定された場合はNULLS FIRSTです。 （したがって、デフォルトでは、NULLが非NULLよりも大きい値であるかのように動作します。） USINGが指定されると、デフォルトのNULLの順序は、演算子が小なり演算子か大なり演算子によって変わります。

順序付けオプションは直前の演算子にのみ適用されます。 たとえば、ORDER BY x, y DESCはORDER BY x DESC, y DESCと同一の意味ではありません。

文字型データでは、格納する列に適用された照合順序に従ってソートされます。 これは必要に応じてexpression内にCOLLATE句を含めることで上書きできます。 例えばORDER BY mycolumn COLLATE "en_US"です。 より詳細については4.2.10および23.2を参照してください。

LIMIT句

LIMIT句は2つの独立した副句から構成されます。

LIMIT { count | ALL }
OFFSET start

パラメータcountには返される行の最大数を、一方、startには行を返し始める前に飛ばす行数を指定します。 両方とも指定された場合、start行分が飛ばされ、そこから数えてcount行が返されます。

count式がNULLと評価された場合、LIMIT ALLとして、つまり制限無しとして扱われます。 startがNULLと評価された場合、OFFSET 0と同様に扱われます。

SQL:2008では同じ結果を実現する異なる構文が導入されました。 PostgreSQLでもサポートしています。 以下の構文です。

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }

この構文において、startまたはcountの値は標準SQLでは、リテラル定数、パラメータもしくは変数名を要求します。 PostgreSQLの拡張では他の表現が許容されていますが、曖昧さを防ぐために通常は括弧で囲まれる必要があるでしょう。 countをFETCH句で省略した場合、そのデフォルトは１です。 WITH TIESオプションは、結果の集合でORDER BY句に従って最後の場所で同点になる追加の行を返すのに使われます。この場合ORDER BYは必須です。 ROWおよびROWS、そしてFIRSTおよびNEXTは意味がない単語で、この句に影響を与えることはありません。 SQL標準ではOFFSET句は、FETCH句と同時に使用する場合、これより前に存在しなければなりません。 しかしPostgreSQLは厳密ではなく、どちらが先でも許されます。

LIMITを使う時は、結果行を一意な順番に強制するORDER BY句を使うとよいでしょう。 そうしないと、問い合わせ結果のどの部分が返されるのかがわかりません。 10〜20行目までを出力するとしても、どの順番で並べた時の10〜20行目なのでしょうか。 ORDER BYを指定しない限り、行が返される順番は不明です。

問い合わせプランナは問い合わせ計画を作成する時にLIMITを考慮するので、LIMITとOFFSETの指定によって異なった計画を得ることになるでしょう。計画が異なれば、異なる順番で行が返ります。 したがって、LIMIT/OFFSET値の変更によって異なる結果行を選択しようとすると、ORDER BYで順序を並び替えない限り、矛盾した結果を返すことになります。 これはバグではありません。 「SQLは、ORDER BYで順序を制御されない限り、問い合わせ結果が返す順序を約束しない」という事実の当然の帰結なのです。

厳密的に部分集合の選択を強制するORDER BYがなければ、同じLIMIT問い合わせを繰り返し実行してもテーブル行から異なる部分集合が取り出される可能性すらあります。 繰り返しますが、これは不具合ではありません。 こうした場合に確定した結果は単に保証されていないのです。

ロック処理句

FOR UPDATE、FOR NO KEY UPDATE、FOR SHAREおよびFOR KEY SHAREはロック処理句です。 これらはテーブルから行を入手する時にどのようにSELECTがその行をロックするかに影響します。

ロック処理句の一般的な構文は以下の通りです。

FOR lock_strength [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ]

ここでlock_strengthは以下のいずれかを取ることができます。

UPDATE
NO KEY UPDATE
SHARE
KEY SHARE

それぞれの行レベルロックモードについての詳しい説明は13.3.2を参照してください。

他のトランザクションのコミットを待機することなく操作を進めるには、NOWAITあるいはSKIP LOCKEDオプションを使用してください。 NOWAITでは、選択行のロックを即座に獲得できない時、文は待機せずに、エラーを報告します。 SKIP LOCKEDでは、即座にロックできない行はすべてスキップされます。 行のロックをスキップすると、一貫性のないデータが見えることになるので、一般的な目的の作業のためには適しませんが、複数の消費者がキューのようなテーブルにアクセスするときのロック競合の回避などに利用できます。 NOWAITおよびSKIP LOCKEDは行レベルロックにのみに適用される点に注意してください。 つまり、必要なROW SHAREテーブルレベルロックは通常通りの方法（ 第13章を参照）で獲得されます。 もし、テーブルレベルのロックを待機せずに獲得しなければならないのであれば、最初にLOCKのNOWAITオプションを使用してください。

ロック処理句内に特定のテーブルが指定されている場合は、そのテーブルの行のみがロックされます。 SELECT内の他のテーブルは通常通りに読み込まれます。 テーブルリストを持たないロック処理句は、その文で使用されるすべてのテーブルに影響を与えます。 ロック処理句がビューまたは副問い合わせで使用された場合、そのビューや副問い合わせで使用されるすべてのテーブルに影響を与えます。 しかしこれらの句は主問い合わせで参照されるWITH問い合わせには適用されません。 WITH問い合わせ内での行ロックを行いたい場合は、WITH問い合わせ内でロック処理句を指定してください。

異なるロック方式を異なるテーブルに指定する必要があれば、複数のロック処理句を記述することができます。 複数のロック処理句で同一のテーブルを記述した（または暗黙的に影響が与えられた）場合、最も強いものだけが指定されたかのように処理されます。 同様に、あるテーブルに影響を与える句のいずれかでNOWAITが指定された場合、そのテーブルはNOWAITとして処理されます。 それ以外の場合、あるテーブルに影響を与える句のいずれかでSKIP LOCKEDが指定されていれば、そのテーブルはSKIP LOCKEDとして処理されます。

ロック処理句は、返される行がテーブルのどの行に対応するのかが明確に識別できない場合には使用することができません。 例えば、集約には使用できません。

ロック処理句がSELECT問い合わせの最上位レベルに存在する場合、ロック対象行は問い合わせが返す行に正確に一致します。 結合問い合わせ内の場合、ロック対象行は返される結合行に関連する行となります。 さらに、スナップショットを更新した後に問い合わせ条件を満たさなくなった場合は返されなくなりますが、問い合わせのスナップショット時点で問い合わせ条件を満たす行もロックされます。 LIMITが使用された場合、制限を満たす行が返されるとロック処理は止まります。 （しかし、OFFSETにより飛ばされた行はロックされることに注意してください。） 同様に、ロック処理句がカーソル問い合わせで使用された場合、カーソルにより実際に取り込んだ行または通り過ぎた行のみがロックされます。

ロック処理句が副SELECTに存在する場合、ロック対象行は副問い合わせの外側の問い合わせに返される行となります。 外側の問い合わせからの条件が副問い合わせ実行の最適化に使用される可能性がありますので、これには副問い合わせ自体の検査が提示する行より少なくなるかもしれません。 例えば、

SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5;

は、副問い合わせ内では文字として条件が記載されていなくても、col1 = 5を持つ行のみがロックされます。

以前のリリースでは、セーブポイント以降に更新されるロックの保持は失敗しました。 例えば以下のコードです。

BEGIN;
SELECT * FROM mytable WHERE key = 1 FOR UPDATE;
SAVEPOINT s;
UPDATE mytable SET ... WHERE key = 1;
ROLLBACK TO s;

ROLLBACK TO後のFOR UPDATEロックの保持に失敗します。 これはリリース9.3で修正されました。

SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss ORDER BY column1;

TABLEコマンド

TABLE name

というコマンドは以下と同じです。

SELECT * FROM name

これは、最上位のコマンドとして、あるいは複雑な問い合わせの一部として、入力を省略する構文の一種としても使用することができます。 WITH、UNION、INTERSECT、EXCEPT、ORDER BY、LIMIT、OFFSET、FETCH、FORのロック句だけをTABLEと一緒に使うことができます。 WHERE句およびいかなる形式の集約も使うことはできません。

FROM句の省略

PostgreSQLでは、FROM句を省略することができます。 これによって、以下のように単純な式を計算させることができます。

SELECT 2+2;

 ?column?
----------
        4

他のSQLデータベースでは、このようなSELECTを行うためにはダミーの1行テーブルを使わなければならないものもあります。

FROM句の指定がない場合、問い合わせではデータベーステーブルを参照することができません。 例えば、以下の問い合わせは無効です。

SELECT distributors.* WHERE distributors.name = 'Westward';

PostgreSQLリリース8.1より前まででは、こうした形の問い合わせを受け付け、問い合わせで参照する各テーブルに対する暗黙的な項目を問い合わせのFROM句に追加していました。 これは許されなくなりました。

空のSELECTリスト

SELECTの後の出力式のリストは空でも良く、このとき列数がゼロの結果テーブルが生成されます。 これは標準SQLでは有効な構文ではありませんが、PostgreSQLは列数がゼロのテーブルを許すので、それと整合性を保つために許しています。 しかし、DISTINCTを使う時は、空のリストを使うことはできません。

ASキーワードの省略

標準SQLでは、キーワードAS(省略可能)は、新しい列名が有効な列名（つまり予約済みのどのキーワードとも異なるもの）である場合は常に、出力列名の前から省くことができます。 PostgreSQLには多少より強い制限があります。 新しい列名が予約済みか否かに関わらず何らかのキーワードに一致する場合はASが必要です。 推奨する実践方法は、今後のキーワードの追加と競合する可能性に備え、ASを使用する、または出力列名を二重引用符で括ることです。

FROM項目において標準およびPostgreSQLでは、未予約のキーワードである別名の前のASを省略することができます。 しかし、構文があいまいになるため、出力名では実践的ではありません。

ONLYと継承関係

標準SQLでは、SELECT * FROM ONLY (tab1), ONLY (tab2) WHERE ...のように、ONLYを記述する時にテーブル名の前後を括弧でくくることを要求します。 PostgreSQLではこの括弧を省略可能であるとみなしています。

PostgreSQLでは最後に*を付けることで 明示的に子テーブルを含めるというONLYではない動作を指定することができます。 標準ではこれを許していません。

（これらの点はONLYオプションをサポートするすべてのSQLコマンドで同様に適用されます。）

TABLESAMPLE句の制限

現在のところ、TABLESAMPLE句は通常のテーブルとマテリアライズドビューでのみ受け付けられます。 SQL標準では、FROM句の任意の要素について適用可能であるべきとされています。

FROM内の関数呼び出し

PostgreSQLでは、FROMリストのメンバとして直接関数呼び出しを記述することができます。 標準SQLではこうした関数呼び出しを副SELECT内に囲む必要があります。 つまりFROM func(...) aliasはおおよそFROM LATERAL (SELECT func(...)) aliasと同じです。 暗黙的にLATERALであるとみなされることに注意してください。 標準ではFROM内のUNNEST()項目にはLATERAL構文を必要とするためです。 PostgreSQLではUNNEST()を他の集合を返す関数と同じものとして扱います。

GROUP BYとORDER BYにおける利用可能な名前空間

標準SQL-92では、ORDER BY句で使用できるのは、出力列名か序数のみであり、GROUP BY句で使用できるのは、入力列名からなる式のみです。 PostgreSQLは、これらの句で両方が指定できるように拡張されています （ただし、不明瞭さがある場合は標準の解釈が使用されます）。 さらに、PostgreSQLではどちらの句にも任意の式を指定できます。 式で使われる名前は、常に出力列名ではなく入力列の名前とみなされることに注意してください。

SQL:1999以降では、SQL-92と完全には上位互換でない、多少異なる定義が採用されています。 しかし、ほとんどの場合、PostgreSQLはSQL:1999と同じ方法でORDER BYやGROUP BYを解釈します。

関数従属性

テーブルの主キーがGROUP BYリストに含まれる場合に限り、PostgreSQLは（GROUP BYで列を省くことができる）関数従属性を認識します。 標準SQLでは、認識しなければならない追加の条件を規定しています。

LIMITおよびOFFSET

LIMITおよびOFFSET句はPostgreSQL独自の構文ですが、MySQLでも使用されています。 LIMIT Clauseで説明したように、標準SQL:2008にて同じ機能のOFFSET ... FETCH {FIRST|NEXT} ...が導入されました。 この構文はIBM DB2でも使用されています。 （Oracle用に開発されたアプリケーションでは、これらの句の機能を実装するために自動生成されるrownum列を含めるという回避策を使用することが多いですが、PostgreSQLでは利用できません。）

FOR NO KEY UPDATE、FOR UPDATE、FOR SHARE、FOR KEY SHARE

FOR UPDATEは標準SQLに存在しますが、標準では、DECLARE CURSORのオプションとしてしか許されていません。 PostgreSQLでは、副SELECTなど任意のSELECTで許されます。 これは拡張です。 FOR NO KEY UPDATE、FOR SHARE、FOR KEY SHAREの亜種、およびNOWAITとSKIP LOCKEDオプションは標準にはありません。

WITH内のデータ変更文

PostgreSQLではWITH問い合わせとしてINSERT、UPDATEおよびDELETEを使用することができます。 これは標準SQLにはありません。

非標準句

DISTINCT ON ( ... )は標準SQLの拡張です。

ROWS FROM( ... )は標準SQLの拡張です。

WITHのMATERIALIZEDとNOT MATERIALIZEDオプションはSQL標準の拡張です。