SELECT

FROM 句

FROM 句は一つ以上のソーステーブルを SELECT に指定します。もし複数のソースが指定されると、結果は概念的に全てのソースの全ての行の直積になります。 しかし通常は制約条件は直積の小さなサブセットを制限するために追加されます。

FROM アイテムが単純なテーブル名の場合、それは暗黙的にそのテーブルのサブテーブル(継承する子)からの行も含みます。ONLY はそのテーブルのサブテーブルからの行を抑圧します。PostgreSQL 7.1 以前ではこれはデフォルトの結果で、サブテーブルの追加はテーブル名に * を付けて行なわれていました。 この古い方法はコマンド SET SQL_Inheritance TO OFF で使うことができます。

FROM アイテムはカッコで囲まれたサブセレクト(別名句がサブセレクトには必要なことに注意して下さい。)であってもかまいません。これは単一の問い合わせの複数レベルのグループ分け、集約、若しくはソートをする唯一の方法なので、非常に便利な機能です。

FROM アイテムはテーブル関数にすることもできます (どのような関数でも使えますが、複数の行および列を返す関数が一般的)。 関数は、特定の引数値によって呼び出され、その出力はテーブルの場合と同じようにスキャンされます。

場合によっては、呼び出される方法によって異なる列セットを返せるテーブル関数を定義しておくと便利です。 これを行うには、疑似データ型 record を返すようテーブル関数を宣言します。 このような関数を FROM アイテムで使用する場合は、この関数の後に別名またはキーワード AS を単独で記述し、さらに、括弧で囲んだ列名とデータ型を記述します。 これにより、問い合わせ時複合データ型定義が可能になります。 この複合データ型定義は、関数から実際に返される複合データ型とマッチしていなければなりません。マッチしていなければ、実行時にエラーが報告されます。

最後に、FROM アイテムは二つのより単純な FROM アイテムを組み合われるJOIN 句であることもあります。(ネストの順番を決める必要がある場合カッコを使います。)

CROSS JOIN もしくは INNER JOIN は単純な直積で、FROM の最上レベルにその二つのアイテムをリストするのと同じものです。CROSS JOIN は INNER JOIN ON (TRUE) と等しく、制約ではどの行も削除されません。これらの結合型は記述上の便宜のためだけであり、これらは何もしないので単純な FROM と WHERE は使えません。

LEFT OUTER JOIN は条件にあう直積の全ての行(つまり、その ON 条件を渡す全ての組み合わされた行)、そして ON 条件を満たす右側の行を持たない左側のテーブルの各々の行のコピーを返します。 この左側の行は右側の行に NULL 値を挿入することで結合されたテーブルの全幅に拡張されます。 どの行がマッチしているかを決める間、JOIN 自身の ON か USING 条件のみが考慮されることに注意してください。他の ON か WHERE 条件は後で適用されます。

逆に、RIGHT OUTER JOIN は全ての結合された行と、それぞれの当てはまるものがなかった右側の行(左側は null で拡張されています)に一行ずつを返します。左と右の入力を入れ換えることで LEFT OUTER JOIN に変換することもできるため、これはただの記述上の便宜でしかありません。

FULL OUTER JOIN は全ての結合された行と、当てはまるものがなかった左側の行(右側が null で拡張されている)と、あてはまるものがなかった右側の行(左側が null で拡張されている)を返します。

CROSS JOIN を除く全ての JOIN 型では、ON join_condition,USING ( join_column_list ),もしくは NATURAL のどれかを正確に書かなければいけません。ON はもっとも一般的な場合です。結合されるべき二つのテーブルに関連するどのような制約式でも書くことができます。 USING 列リスト( a, b, ... ) は ON 条件left_table.a = right_table.a AND left_table.b = right_table.b ...の簡略バージョンです。更に USING は、JOIN 出力に含まれるそれぞれのペアの同等の列のうちの両方ではなく一つだけが含まれることを意味します。NATURAL はテーブルのなかの似たような名前を持つ全ての列を挙げる USING リストの簡略バージョンです。

WHERE 句

オプションの WHERE 条件の一般形は次のようになります。

WHERE boolean_expr
    

boolean_expr はブール値として評価できる表現であればどのような組み合わせでも構成することができます。多くの場合、次のようになります。

expr cond_op expr
    

もしくは

log_op expr
    

ここで、cond_op は、 =, <, <=, >, >= or <> のいずれか、ALL,ANY,IN,LIKEのような条件演算子、もしくは、ローカルに定義された演算子をとることができます。 また、log_op は AND, OR, NOT のいずれかをとることができます。SELECT は WHERE 条件が TRUE を返さないすべての行を無視します。

GROUP BY句

GROUP BY はこの句のアプリケーションから作られるグループ化されたテーブルを指定します。

GROUP BY expression [, ...]
    

GROUP BY は、グループ化された列と同じ値を共有する全ての選択された行を一つの行に凝縮します。もしあれば、集約関数はそれぞれのグループを作る全ての行を渡って計算され、それぞれのグループに個別の値を作ります(GROUP BY なしでは集約は全ての選択された行に渡って計算される単一の値を作ります)。GROUP BY が存在する場合、集約関数内以外では SELECT 出力式がグループ分けされていない列を参照することは妥当ではありません。 なぜならグループ化されていない列には一つ以上の返却可能な値があるからです。

GROUP BY アイテムは入力列名、もしくは出力列の順序の番号の名前(SELECT 式)、もしくは入力列の値に形成される任意の式が可能です。不明瞭な場合は、GROUP BY 名は出力列名ではなく入力列名として解釈されます。

HAVING 句

オプションである、HAVING 条件は一般的に次の形式です。

HAVING boolean_expr
    

ここで、boolean_expr は WHERE 句で指定するものと同一です。

HAVING によってboolean_exprを満たさない行を削除した結果からグループ化されたテーブルを作ることを指示します。HAVING は WHERE とは違います。WHERE は個別の行を GROUP BYのアプリケーションの前に濾過しますが、HAVING は GROUP BYによって作られるグループ行を濾過します。

boolean_expr で参照される各列は、参照が集約関数内で現れない限り明瞭にグループ化する列を参照します。

ORDER BY 句

ORDER BY expression [ ASC | DESC | USING operator ] [, ...]
    

ORDER BY アイテムは名前、もしくは出力列(SELECT 式)の順序の番号、もしくは入力列の値から形成される任意の式を指定することができます。あいまいな場合は、ORDER BY の 名前は出力列名として解釈されます。

順序の番号は、結果の列の(左から右への)順番の位置を参照します。 この機能は固有の名前を持たない列に基づいた順番を定義することを可能にします。これが絶対に必要だという訳ではありません。 なぜなら AS 句を使って結果の列に名前を与えることはいつでも可能だからです。 例えば下記のようになります。

SELECT title, date_prod + 1 AS newlen FROM films ORDER BY newlen;
    

SELECT の結果リストに現れないフィールドを含む任意の式(SQL92 の拡張)を ORDER BY することも可能です。したがって下記の文は正当です。

SELECT name FROM distributors ORDER BY code;
    

UNION、 INTERSECT、 もしくは EXCEPT 問い合わせの結果に適用する ORDER BY 句では、 式ではなく出力列の名前か番号のみを指定できるので、この機能は使えません。

もし ORDER BY アイテムが結果の列名と入力列名の両方に当てはまる単純な名前だった場合、ORDER BY はそれを結果列名として解釈します。これは GROUP BY が同じ状況で行なう選択とは逆です。この不一致は標準 SQL92 からくるものです。

オプションとして、ORDER BY 句の各列名の後にキーワード DESC （降順) もしくは ASC （昇順)を付けることができます。 指定がなければ、デフォルトで ASC になります。代わりに特定の順番演算子の名前を指定することもできます。 ASC は USING < と等しく、DESC は USING > と等しいです。

null 値はその領域内の他のあらゆる値よりも高くソートされます。言い替えると、順方向のソートではnull値は末尾に、逆方向のソートではnull値は先頭に現れます。

文字データ型のデータは、データベースクラスタの初期化時に設定されたローカル指定の照合順に従ってソートされます。

UNION 句

table_query UNION [ ALL ] table_query
    [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start ]
    

ここで、table_query は、ORDER BY, LIMIT, もしくは FOR UPDATE 句を持たない任意の select 式を指定できます。(ORDER BY と LIMIT は select がカッコで囲まれていると副式に付けることができます。カッコなしでは、これらの句は右側の入力式ではなく UNION の結果に適用するものとして判断されます。)

UNION 演算子は、関連する問い合わせが返す行の集合(和集合)を計算します。 UNION の直接の演算項目を表す 2 つの SELECT 文は同じ数の列を作らなければならず、対応する列は互換性のあるデータ型でなければなりません。

UNION の結果は ALL オプションが指定されない限りどのような重複行も含みません。ALL は重複行を削除しません。

同じ SELECT 文にある複数の UNION 演算子は、カッコで別の順が示されない限り左から右に評価されます。

今のところ、FOR UPDATE は UNION の結果もしくは UNION の入力値のどちらにも指定することができません。

INTERSECT 句

table_query INTERSECT [ ALL ] table_query
    [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start ]
    

ここで、table_query は、ORDER BY, LIMIT, もしくは FOR UPDATE 句を持たない任意の select 式を指定できます。

INTERSECT は UNION と似ていますが、どちらかに現れる行ではなく両方の問い合わせ出力に現れる行のみを作るという点が違います。

INTERSECT の結果は ALL オプションが指定されない限りどのような重複行も含みません。ALL では、 L に m の重複を持ち、R に n の重複を持つ行は min(m,n) 回現れます。

同じ SELECT 文の中の複数の INTERSECT 演算子は、カッコで別の指定がない限り左から右に評価されます。INTERSECT は UNION より更に固く結びつけます。 つまりA UNION B INTERSECT C はカッコで別の指定がない限り A UNION (B INTERSECT C) として読まれます。

EXCEPT 句

table_query EXCEPT [ ALL ] table_query
    [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start ]
    

ここで、table_query は、ORDER BY, LIMIT, もしくは FOR UPDATE 句を持たない任意の select 式を指定できます。

EXCEPT は UNION と似ていますが、右問い合わせの出力ではなく左問い合わせの出力に現れる行を作るという点で違います。

EXCEPT の結果は ALL オプションが指定されない限りどのような重複行も含みません。ALL では、L に m の重複を持ちR に n の重複を持つ行は max(m-n,0) 回現れます。

同じ SELECT 文の中の複数の EXCEPT 演算項目は、カッコがそれ以外を指定しない限り左から右に評価されます。EXCEPT は UNION と同じレベルで結びつけられます。

LIMIT 句

    LIMIT { count | ALL }
    OFFSET start
    

count は返される行の最大数を指定し、start は行を返しはじめる前に飛ばす行の数を指定します。

LIMIT はこの問い合わせが生成する行の一部のみを取りだすことを可能にします。もし制限数が与えられると、それ以上の行は返されません。もしオフセットが与えられると、返す行が始まる前にそれだけの行がとばされます。

LIMIT を使う時は、結果の行を一意な順番に強制するORDER BY 句を使うのが良い考えです。そうしないと、問い合わせの行の予測不可能なサブセットを得ることになります。 10 から 20 行目までを出そうとしているかもしれませんが、どの順番の 10 から 20 行目までなのでしょうか?ORDER BYを指定しない限りどの順番だかわからないのです。

PostgreSQL 7.0 からは、問い合わせオプティマイザは問い合わせプランを作成する時に LIMIT を 考慮しますので、LIMIT と OFFSET に何を使用するかによって(異なる行の順番を生む)異なったプランを得ることになるでしょう。したがって、問い合わせの、異なるサブセットを選択するために異なる LIMIT/OFFSET の値を使うと、ORDER BY で予測可能な結果を強制しない限り、矛盾した結果を返します。これはバグではありません。 これは SQL が ORDER BY で順番を制御しない限り問い合わせの結果を特定の順番で返すことを約束しないという事実の本質的な結果なのです。

FOR UPDATE 句

    FOR UPDATE [ OF tablename [, ...] ]
    

FOR UPDATE を使用すると問い合わせによって検索された行が更新用にロックされます。 これにより、現行のトランザクションが終了するまではこれらの行が他のトランザクションによって変更されたり削除されたりすることがなくなります。つまり、現行のトランザクションが終了するまでは、他のトランザクションがこれらの行に対して UPDATE、DELETE、または SELECT FOR UPDATE を試行しても拒否されるのです。 また、他のトランザクションからの UPDATE、DELETE、または SELECT FOR UPDATE によって選択されている行がロックされている場合に SELECT FOR UPDATE を実行しようとすると、SELECT FOR UPDATE は前のトランザクションが終了するのを待ち、その後更新された行をロックして返します (行が削除された場合は返しません)。 詳細については、ユーザガイドの同時実行に関する章を参照してください。

FOR UPDATE 内に特定のテーブルが指定されている場合は、そのテーブル (複数の場合もあり) の行のみがロックされ、SELECT 内の他のテーブルは通常どおりに読み込まれるだけです。

FOR UPDATE は、返される行が含まれている個々のテーブルを明確に指定できない場合には使用することができません。例えば、集約には使用できません。

FOR UPDATE は、バージョン 7.3 より前のアプリケーションとの互換性のために LIMIT の前に書くことができます。 しかしその場合でも実際には LIMIT の後に実行されるので、LIMIT の後に書くことをお勧めします。

拡張

PostgreSQL では問い合わせでFROM 句を省略できます。 これはもとの PostQUEL 問い合わせ言語の機能が保存されていることによるものです。 このようにして単純な式の計算を簡単に行うことができます。

SELECT 2+2;

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

他の SQL データベースは、select from を行なうためのダミーの一行テーブルを使う以外はこれを行なうことができないものがあります。これほど明確ではない使用法としては一つ以上のテーブルからselect することを次のように省略して書くことができます。

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

 did | name
-----+----------
 108 | Westward
  

これは暗黙の FROM アイテムが、問い合わせで参照されるけれども FROM では記述されないそれぞれのテーブルに追加されるので、動作します。これは便利な省略形ですが、間違って使いやすいです。 例えば、次の問い合わせ

SELECT distributors.* FROM distributors d;
  

はおそらく間違いです。 ユーザは

SELECT d.* FROM distributors d;
  

を意味していて、実際に得られる下記のような制約のない結合

SELECT distributors.* FROM distributors d, distributors distributors;
  

を意味していたわけではないでしょう。このような間違いの発見を助けるために PostgreSQL 7.1 とそれ以降では、もし暗黙の FROM 機能が明示的な FROM 句も持つ問い合わせで使われる場合警告を出します。

テーブル関数機能は、PostgreSQL の拡張です。

SQL92

 
table_query UNION [ALL]
    [CORRESPONDING [BY (column [,...])]]
    table_query