他のバージョンの文書 15 | 14 | 13 | 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

SELECT

名前

SELECT, TABLE, WITH -- テーブルもしくはビューから行を検索する

概要

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ [ AS ] output_name ] [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

ここでfrom_itemは以下のいずれかです。


    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    with_query_name [ [ 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 [, ...] ) ]

またwith_queryは以下の通りです。

    with_query_name [ ( column_name [, ...] ) ] AS ( select | insert | update | delete )

TABLE [ ONLY ] table_name [ * ]

説明

SELECTは0個以上のテーブルから行を返します。 SELECTの一般的な処理は以下の通りです。

  1. WITHリスト内のすべての問い合わせが計算されます。 これらは実質、FROMリスト内から参照可能な一時テーブルとして提供されます。 FROM内で2回以上参照されるWITH問い合わせは一度のみ計算されます。 (後述のWITHを参照してください。)

  2. FROMリストにある全要素が計算されます (FROMリストの要素は実テーブルか仮想テーブルのいずれかです)。 FROMリストに複数の要素が指定された場合、それらはクロス結合されます (後述のFROMを参照してください)。

  3. WHERE句が指定された場合、条件を満たさない行は全て出力から取り除かれます (後述のWHEREを参照してください)。

  4. GROUP BY句が指定された場合、1つまたは複数の値が条件に合う行ごとにグループに組み合わせて出力されます。 HAVING句が指定された場合、指定した条件を満たさないグループは取り除かれます (後述のGROUP BYHAVINGを参照してください)。

  5. 実際には、選択された各行または行グループに対して、SELECT出力式を使用して計算した結果の行が出力されます (後述のSELECTリストを参照してください)。

  6. DISTINCTは結果から重複行を取り除きます。 DISTINCT ONは指定した全ての式に一致する行を取り除きます。 ALLでは、重複行も含め、全ての候補行を返します(これがデフォルトです。 詳しくは、後述のDISTINCTを参照してください)。

  7. UNIONINTERSECTEXCEPT演算子を使用すると、複数のSELECT文の出力を1つの結果集合にまとめることができます。 UNION演算子は、両方の結果集合に存在する行と、片方の結果集合に存在する行を全て返します。 INTERSECT演算子は、両方の結果集合に存在する行を返します。 EXCEPT演算子は、最初の結果集合にあり、2番目の結果集合にない行を返します。 ALLが指定されない限り、いずれの場合も、重複する行は取り除かれます。 無意味なDISTINCTという単語を付けて、明示的に重複行を除去することを指定することができます。 SELECT自体はALLがデフォルトですが、この場合はDISTINCTがデフォルトの動作であることに注意してください。 (後述のUNIONINTERSECTEXCEPTを参照してください。)

  8. ORDER BY句が指定された場合、返される行は指定した順番でソートされます。 ORDER BYが指定されない場合は、システムが計算過程で見つけた順番で行が返されます (後述のORDER BYを参照してください)。

  9. LIMIT(またはFETCH FIRST)あるいはOFFSET句が指定された場合、SELECT文は結果行の一部分のみを返します (詳しくは、後述のLIMITを参照してください)。

  10. FOR UPDATEまたはFOR SHARE句を指定すると、SELECT文は引き続き行われる更新に備えて選択行をロックします (詳しくは、後述のFOR UPDATE/FOR SHAREを参照してください)。

SELECTコマンド内で使われる列それぞれに対するSELECT権限が必要です。 FOR UPDATEまたはFOR SHAREを使用するには、さらに、(選択できるように各テーブルで少なくとも1列に対する)UPDATE権限が必要です。

パラメータ

WITH

WITH句により主問い合わせ内で名前により参照可能な、1つ以上の副問い合わせを指定することができます。 副問い合わせは実質的に主問い合わせの間の一時的なテーブルかビューのように動作します。 各副問い合わせはSELECTINSERTUPDATEDELETEにすることができます。 WITH内でデータ変更文(INSERTUPDATEDELETE)を記述する場合は、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問い合わせの鍵となる特性は、これらを主問い合わせが複数回参照していたとしても、主問い合わせの実行当たり一度のみ評価される点です。 特にデータ変更文は、主問い合わせがその出力のすべてまたは一部を読み取るかに関係なく、本当に一度のみ実行されることが保証されています。

主問い合わせとWITH問い合わせは(理論上)同時にすべて実行されます。 WITH内のデータ変更文によりなされた影響は、RETURNING出力を読み取る以外、問い合わせの他の部分では参照できないことを意味します。 こうしたデータ変更文が2つあり、同じ行を変更しようとした場合、その結果は明確ではありません。

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

FROM

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

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

table_name

既存のテーブルもしくはビューの名前です(スキーマ修飾名も可)。 ONLYが指定された場合、そのテーブルのみがスキャンされます。 ONLYが指定されない場合、テーブルとそれを継承する全てのテーブルがスキャンされます。

alias

別名を含むFROMアイテムの代替名です。 別名は、指定を簡潔にするため、もしくは、自己結合(同じテーブルを複数回スキャンする結合)の曖昧さをなくすために使われます。 別名が指定されている場合は、その別名によって実際のテーブル名または関数名が完全に隠されます。 例えば、FROM foo AS fと指定されている場合、以降のSELECT文ではこのFROMアイテムをfooではなくfとして参照する必要があります。 テーブルの別名があれば、そのテーブルの複数の列の名前を置き換える列の別名リストを記述することができます。

select

FROM句では、副SELECTを使うことができます。 SELECTコマンドの実行中、副SELECTの出力は一時テーブルであるかのように動作します。 副SELECTは括弧で囲まれなければなりません。また、必ず別名を与えておかなければなりません。 VALUESコマンドをここで使用することもできます。

with_query_name

WITH問い合わせは、問い合わせの名前があたかもテーブル名であるかのように、名前を記述することで参照されます。 (実際にはWITH問い合わせは主問い合わせの対象とするテーブルと同じ名前の実テーブルを隠蔽します。 必要ならばテーブル名をスキーマ修飾することで同じ名前の実テーブルを参照することができます。) テーブルと同様の方法で別名を提供することができます。

function_name

FROM句では、関数呼び出しを使用することができます (これは特に関数が結果セットを返す場合に有用ですが、任意の関数を使用することもできます)。 SELECTコマンドの実行中は、この関数の結果は一時テーブルであるかのように動作します。 また、別名を使用することもできます。 別名が指定されていれば、さらに列の別名リストを指定して、関数の複合型の戻り値の属性に対する代替名を提供することもできます。 関数がrecordデータ型を返すと定義されている場合は、別名すなわちASキーワードと、それに続くcolumn_name data_type [, ... ])という形式の列定義リストが必要です。 列定義リストは、関数によって返される実際の列の数およびデータ型に一致していなければなりません。

join_type

以下のいずれかです。

  • [ INNER ] JOIN

  • LEFT [ OUTER ] JOIN

  • RIGHT [ OUTER ] JOIN

  • FULL [ OUTER ] JOIN

  • CROSS JOIN

INNERおよびOUTER結合型では、結合条件、すなわち、NATURAL, ON join_conditionUSING (join_column [, ...])のいずれか1つのみを指定する必要があります。 それぞれの意味は後述します。 CROSS JOINでは、これらの句を記述しなくても構いません。

JOIN句は、2つのFROMアイテムを結び付けます。 入れ子の順番を決めるために、必要ならば括弧を使用してください。 括弧がないと、JOINは左から右へ入れ子にします。 どのような場合でもJOINは、カンマで分けられたFROM項目よりも強い結び付きを持ちます。

CROSS JOININNER JOINは直積を1つ生成します。これは、FROMの最上位で2つの項目を結合した結果と同一です。 しかし、(指定すれば)結合条件によって制限をかけることができます。 CROSS JOININNER JOIN ON (true)と等価であり、条件によって削除される行はありません。 これらの結合型は記述上の便宜のためだけに用意されています。 したがって、通常のFROMWHEREを実行しなければ何も行いません。

LEFT OUTER JOINは、条件に合う直積の全ての行(つまり、その結合条件を満たす全ての組み合わせ)に加え、左側テーブルの中で、右側テーブルには結合条件を満たす行が存在しなかった行のコピーも返します。 この左側テーブルの行を結合結果のテーブルの幅に拡張するために、右側テーブルが入る列にはNULL値が挿入されます。 マッチする行を決める時は、JOIN句自身の条件のみが考慮されることに注意してください。 他の外部結合条件は後で適用されます。

逆に、RIGHT OUTER JOINは、全ての結合行と、左側テーブルに当てはまるものがなかった右側の行(左側はNULLで拡張されています)の1行ずつを返します。 左右のテーブルを入れ替えればLEFT OUTER JOINに変換できるので、RIGHT OUTER JOINは記述上の便宜を図るため用意されているに過ぎません。

FULL OUTER JOINは、全ての結合行に加え、一致しなかった左側の行(右側はNULLで拡張)、一致しなかった右側の行(左側はNULLで拡張)を全て返します。

ON join_condition

join_conditionは、結合においてどの行が一致するかを指定する、boolean型の値を返す式です(WHERE句に類似しています)。

USING ( join_column [, ...] )

USING ( a, b, ... )句はON left_table.a = right_table.a AND left_table.b = right_table.b ...の省略形です。 USINGは等価な列の両方ではなく片方のみが結合の出力に含まれることを意味します。

NATURAL

NATURALは、2つのテーブル内の同じ名前を持つ行を全て指定したUSINGリストの省略形です。

WHERE

WHERE句は通常以下の形式となります(この句は省略可能です)。

WHERE condition

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

GROUP BY

GROUP BY句は通常以下の形式となります(この句は省略可能です)。

GROUP BY expression [, ...]

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

集約関数が使用された場合、各グループ内の全ての行を対象に計算が行われ、結果としてグループごとの値が生成されます (一方GROUP BYがなければ、集約関数は選択された全ての行を対象に計算を行い、1つの値を生成します)。 GROUP BYが存在する場合、集約関数内部以外で、グループ化されていない列を参照する場合やグループ化されていない列がグループ化された列に関数依存する場合、SELECTリストは無効になります。 こうしないとグループ化されていない列について返される値は複数の値になってしまう可能性があるからです。 グループ化された列(またはその部分集合)がグループ化されていない列を含むテーブルのプライマリキーである場合、関数依存が存在します。

HAVING

HAVINGは通常以下の形になります(この句は省略可能です)。

HAVING condition

conditionWHERE句で指定するものと同じです。

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

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

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を持たない場合のみORDER BYを指定することができます。 新しいウィンドウは常に独自のフレーム句を使用します。 コピーされたウィンドウはフレーム句を指定してはなりません。

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

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

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

[ RANGE | ROWS ] frame_start
[ RANGE | ROWS ] BETWEEN frame_start AND frame_end

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

UNBOUNDED PRECEDING
value PRECEDING
CURRENT ROW
value FOLLOWING
UNBOUNDED FOLLOWING

frame_endが省略された場合デフォルトでCURRENT ROWとなります。 frame_startUNBOUNDED FOLLOWINGとすることができない、frame_endUNBOUNDED PRECEDINGとすることができない、および、上のリストでframe_endの選択をframe_startの選択より先に行うことができないという制限があります。 例えばRANGE BETWEEN CURRENT ROW AND value PRECEDINGは許されません。

デフォルトのフレーム化オプションはRANGE UNBOUNDED PRECEDINGです。 これはRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWと同じで、 パーティションの先頭からORDER BY順序における現在の行の最後のピアまでのすべての行をフレームとします(ORDER BYがなければすべての行を意味します)。 一般的にUNBOUNDED PRECEDINGはフレームがパーティションの先頭から始まることを意味し、同様にUNBOUNDED FOLLOWINGはフレームがパーティションの最終行で終わることを意味します(RANGEモードかROWSかは関係ありません)。 ROWSモードでは、CURRENT ROWはフレームが現在の行で始まる、または終わることを意味しますが、RANGEモードでは、フレームが現在の行のORDER BY順序における最初のピアまたは最後のピアで始まる、または終わることを意味します。 現時点ではvalue PRECEDINGおよびvalue FOLLOWINGという場合わけはROWSモードだけで許されます。 これらは、現在の行の何行前または何行後にフレームが始まるまたは終わることを示します。 valueは整数式でなければならず、変数、集約関数、ウィンドウ関数を含めることはできません。 この値はNULLまたは負を取ることはできません。 しかし、現在の行自身を選択するゼロを取ることができます。

ORDER BY順序によりその行を一意に順序付けできない場合、ROWSが予期できない結果をもたらす可能性があることに注意して下さい。 RANGEは、ORDER BY順序におけるピアとなる行が同等に扱われる、つまり任意の2つのピアはフレーム内に両方とも存在するか、存在しないかのいずれかとなることが確実になるように設計されています。

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

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

SELECTリスト

SELECTリスト(SELECTFROMの間にあるキーワード)は、SELECT文の出力行を形成する式を指定するものです。 この式では、FROM句で処理後の列を参照することができます(通常は実際に参照します)。 AS output_nameを使用すると、出力列に元の名前とは別の名前を付けることができます。

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

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

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

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

UNION

UNIONは通常以下の形式となります。

select_statement UNION [ ALL | DISTINCT ] select_statement

select_statementには、ORDER BYLIMITFOR UPDATEFOR SHARE句を持たない任意のSELECT文が入ります (ORDER BYLIMITは、括弧で囲めば複式として付与することができます。 括弧がない場合、これらの句は右側に置かれた入力式ではなく、UNIONの結果に対して適用されてしまいます)。

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

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

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

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

INTERSECT

INTERSECTは通常以下の形式となります。

select_statement INTERSECT [ ALL | DISTINCT ] select_statement

select_statementには、ORDER BYLIMITFOR UPDATEFOR SHARE句を持たない、任意のSELECT文が入ります。

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

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

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

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

EXCEPT

EXCEPTは通常以下の形式となります。

select_statement EXCEPT [ ALL | DISTINCT ] select_statement

select_statementには、ORDER BYLIMITFOR UPDATEFOR SHARE句を持たない、任意のSELECT文が入ります。

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

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

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

現時点では、EXCEPTの結果やEXCEPTに対する入力に、FOR UPDATEまたはFOR 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;

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

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

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

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

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

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

この構文において、startまたはcountに単一整数定数以外を記述するためには、括弧でくくって記述しなければなりません。 countFETCH句で省略した場合、そのデフォルトは1です。 ROWおよびROWS、そしてFIRSTおよびNEXTは意味がない単語で、この句に影響を与えることはありません。 標準に従うとOFFSET句は、FETCH句と同時に使用する場合、これより前に存在しなければなりません。 しかしPostgreSQLは厳密ではなく、どちらが先でも許されます。

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

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

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

FOR UPDATE/FOR SHARE

FOR UPDATEは以下の形式となります。

FOR UPDATE [ OF table_name [, ...] ] [ NOWAIT ]

深く関連するFOR SHARE句は以下の形式となります。

FOR SHARE [ OF table_name [, ...] ] [ NOWAIT ]

FOR UPDATEを使用すると、問い合わせによって検索された行が更新用にロックされます。 これにより、現行のトランザクションが終了するまでは、これらの行が他のトランザクションによって変更されたり削除されたりすることがなくなります。 つまり、現行のトランザクションが終了するまでは、他のトランザクションがこれらの行に対してUPDATEDELETESELECT FOR UPDATEを試行しても拒否されます。 また、他のトランザクションからのUPDATEDELETESELECT FOR UPDATEによって選択した行がロックされている場合、SELECT FOR UPDATEを実行しようとすると、SELECT FOR UPDATEはそのトランザクションが終了するのを待ってから、その後行をロックして更新された行を返します(行が削除された場合は返しません)。 しかしREPEATABLE READまたはSERIALIZABLEトランザクションの内部では、ロック対象の行がトランザクション開始時から変更されていた場合、エラーが発生します。 第13章を参照してください。

FOR SHAREも同様に振舞いますが、入手する行に対し排他的ロックを獲得するのではなく共有ロックを獲得する点が異なります。 共有ロックにより、他トランザクションによるその行に対するUPDATEDELETESELECT FOR UPDATE操作はブロックされます。 しかし、他トランザクションによるSELECT FOR SHARE操作を防ぎません。

他のトランザクションのコミットを待機することなく操作を進めるには、NOWAITオプションを使用してください。 NOWAITでは、選択行のロックを即座に獲得できない時、文は待機せずに、エラーを報告します。 NOWAITは行レベルロックにのみに適用される点に注意してください。 つまり、必要なROW SHAREテーブルレベルロックは通常通りの方法( 第13章を参照)で獲得されます。 もし、テーブルレベルのロックを待機せずに獲得しなければならないのであれば、最初にLOCKNOWAITオプションを使用してください。

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

異なるロック方式を異なるテーブルに指定する必要があれば、複数のFOR UPDATEFOR SHARE句を記述することができます。 FOR UPDATE句とFOR SHARE句の両方で、同一のテーブルを記述した(または暗黙的に影響が与えられた)場合、FOR UPDATEとして処理されます。 同様に、あるテーブルに影響を与える句のいずれかでNOWAITが指定された場合、そのテーブルはNOWAITとして処理されます。

FOR UPDATEおよびFOR SHAREは、返される行がテーブルのどの行に対応するのかが明確に識別できない場合には使用することができません。 例えば、集約には使用できません。

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

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

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

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

注意

行をロックしてから後のセーブポイントやPL/pgSQL例外ブロックでその行を編集することは避けてください。 後のロールバックでロックが失われてしまいます。 以下に例を示します。

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

ROLLBACK後、行は、セーブポイント前のロックされた更新前の状態に戻らず、実質ロックされません。 現在のトランザクションでロックされた行が更新されたり削除されたりした場合や、共有ロックが排他ロックに向上した場合、これは危険です。 すべての場合において、以前のロック状態が忘れられています。 トランザクションがその後、元のロックコマンドと続く更新の間の状態にロールバックした場合、その行はロックされていない状態として現れます。 これは実装上の問題であり、将来のPostgreSQLリリースで修正する予定です。

注意

ORDER BY句とFOR UPDATE/SHARE句を使用した、READ COMMITTEDトランザクション隔離レベルで実行するSELECTコマンドでは、順序通りにならない行を返す可能性があります。 ORDER BYが最初に適用されるためです。 このコマンドは結果をソートしますが、その後、1行または複数の行のロック獲得がブロックされる可能性があります。 このSELECTのブロックが解除された時点で、順序付け対象の列値の一部が変更されているかもしれません。 これによりこうした行が(元の列値という観点では順序通りではありますが、)順序通りに現れません。 必要に応じて、これは以下のように副問い合わせ内にFOR UPDATE/SHARE句を記述することで、回避することができます。

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

これは、最上位レベルにおけるFOR UPDATEは実際に返される行のみをロックするのに対して、結果としてmytableのすべての行をロックすることに注意してください。 これは、特にORDER BYLIMITやその他の制限と組み合わせている場合、性能上大きな違いを生み出す可能性があります。 このため、この技法は、順序付け対象の列に対する同時実行の更新が想定され、かつ、厳密にソートされた結果が要求される場合にのみ推奨されます。

REPEATABLE READまたはSERIALIZABLEトランザクション隔離レベルでは、('40001'というSQLSTATEを持つ)シリアライゼーション失敗が発生します。 このためこれらの隔離レベルでは順序外の行を受け取る可能性はありません。

TABLEコマンド

TABLE name

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

SELECT * FROM name

これは、最上位のコマンドとしても複雑な問い合わせの一部として入力を省略する構文の一種としても使用することができます。

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

次に、結果を2番目の列(name)の内容に基づいてソートする方法を2つ例示します。

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

次の例は、distributorsテーブルとactorsテーブルの和集合を取得する方法を示しています。さらに、両方のテーブルで結果をWという文字で始まる行のみに限定しています。 重複しない行のみが必要なので、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

以下の例では簡単なWITH句の使用方法を示します。

WITH t AS (
    SELECT random() as x FROM generate_series(1, 3)
  )
SELECT * FROM t
UNION ALL
SELECT * FROM t

         x          
--------------------
  0.534150459803641
  0.520092216785997
 0.0735620250925422
  0.534150459803641
  0.520092216785997
 0.0735620250925422

WITH問い合わせが一度だけ評価されることに注意してください。 このため3つのランダムな値の2つの集合を得ることになります。

以下の例ではWITH RECURSIVEを使用して、直接の部下しか表示しないテーブルから、従業員Maryの(直接または間接的な)部下とその間接度を見つけ出します。

WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS (
    SELECT 1, employee_name, manager_name
    FROM employee
    WHERE manager_name = 'Mary'
  UNION ALL
    SELECT er.distance + 1, e.employee_name, e.manager_name
    FROM employee_recursive er, employee e
    WHERE er.employee_name = e.manager_name
  )
SELECT distance, employee_name FROM employee_recursive;

初期条件、続いてUNION、さらに問い合わせの再帰部分という再起問い合わせの典型的な構文に注意してください。 問い合わせの再帰部分は最終的にはタプルを返さないことを確実にしてください。 さもないと問い合わせは無限にループします。 (より多くの例については項7.8を参照してください。)

互換性

当然ながら、SELECT文は標準SQLと互換性があります。 しかし、拡張機能や実現されていない機能もいくつかあります。

FROM句の省略

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

SELECT 2+2;

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

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

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

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

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

ASキーワードの省略

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

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

ONLYと括弧

標準SQLでは、SELECT * FROM ONLY (tab1), ONLY (tab2) WHERE ...のように、ONLYに続くテーブル名の前後を括弧でくくることを要求します。 PostgreSQLではこれもサポートしますが、括弧を省略することもできます。 (この点はONLYオプションをサポートするすべてのSQLコマンドで同様に適用されます。)

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

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

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

関数依存性

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

WINDOW句の制限

標準SQLではウィンドウ用のframe_clauseオプションをさらに提供します。 現在のPostgreSQLでは上述のオプションのみをサポートします。

LIMITおよびOFFSET

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

FOR UPDATEおよびFOR SHARE

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

WITH内のデータ変更文

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

非標準句

DISTINCT ON句は標準SQLでは定義されていません。