★PostgreSQLカンファレンス2024 12月6日開催/チケット販売中★
他のバージョンの文書 16 | 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

Name

SELECT  --  テーブルもしくはビューからの行の検索

Synopsis

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 ] [, ...] ]
    [ FOR UPDATE [ OF tablename [, ...] ] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start ]

ここで、from_item は以下のとおりです。
[ ONLY ] table_name [ * ]
    [ [ AS ] alias [ ( column_alias_list ) ] ]
|
( select )
    [ AS ] alias [ ( column_alias_list ) ]
|
from_item [ NATURAL ] join_type from_item
    [ ON join_condition | USING ( join_column_list ) ]

入力

expression

テーブルの列名もしくは式です。

output_name

AS 句を使い、出力列の別の名前を指定します。この名前は主に表示する列を名づけるために使われます。他には ORDER BY と GROUP BY 句の中の列の値を参照するのに使われることもあります。しかし output_name は WHERE もしくは HAVING 句で使うことはできません。代わりに式を書いて下さい。

from_item

テーブル参照、副 SELECT、もしくは JOIN 句です。詳細は下記を参照して下さい。

condition

真か偽の結果を返すブーリアン式です。WHERE と HAVING 句の説明は下記を参照して下さい。

select

ORDER BY、 FOR UPDATE、そして LIMIT 句を除く全ての機能での select 文です(select 式をカッコでくくれば、ORDER BY、FOR UPDATE、LIMIT も使用できます)。

FROM アイテムは下記を含むことができます。

table_name

既存のテーブルかビューの名前です。もし ONLY が指定された場合、そのテーブルのみがスキャンされます。もし ONLY が指定されない場合、テーブルと(あった場合は)それを継承するすべてのテーブルがスキャンされます。継承テーブルがスキャンされることを表すために * を付けることができますが、現在のバージョンでは、これがデフォルトになりました。(7.1 より前のリリースでは、 ONLY がデフォルトでした。)

alias

前にある table_name の代わりの名前です。別名は簡潔さもしくは自己結合(同じテーブルが複数回スキャンされる場合です)の曖昧さをなくすために使われます。テーブルの別名と一緒に、そのテーブルの複数の列の別名リストを 付け加えることもできます。

select

副 SELECT は FROM 句で使うことができます。これはその出力がこの SELECT コマンド実行時に一時テーブルとして作成されたかのように動作します。副 SELECT はカッコで囲まれなければならず、 必ず別名が提供されなければなりません。

join_type

[ INNER ] JOIN, LEFT [ OUTER ] JOIN, RIGHT [ OUTER ] JOIN, FULL [ OUTER ] JOIN, もしくは CROSS JOIN のどれかです。INNER と OUTER 結合型では、正確に一つの NATURAL, ON join_condition,もしくは USING ( join_column_list )のいずれかがなくてはいけません。CROSS JOIN では、一切なくてもかまいません。

join_condition

制約条件です。これは WHERE 条件と似ていますが、この JOIN 句で結合される二つの from_item にのみあてはまるという点が違います。

join_column_list

USING の列リスト ( a, b, ... ) は ON 条件left_table.a = right_table.a AND left_table.b = right_table.b ...の省略した形です。

出力

Rows

問い合わせ指定の結果の行の完全な集合です。

count

問い合わせが返す行の数です。

説明

SELECT は一つ以上のテーブルから行を返します。選択される侯補は WHERE 条件を満たす行です。もし WHERE が省略されると、全ての行が侯補になります。 (WHERE 句を参照してください。)

実際のところ、返される行は FROM/WHERE/GROUP BY/HAVING 句から直接作られる行ではありません。出力される行はそれぞれの選択された行の SELECT 出力式を計算することで形成されます。 * は選択された行の全ての列の省略形としての出力リストに書くことができます。更に、 table_name .* はそのテーブルのみからくる列の省略形として書くことができます。

DISTINCT は結果から重複行を削除します。 ALL (デフォルトです) は重複行を含む全ての侯補の行を返します。

DISTINCT ON はそれぞれの重複行の集合の最初の行のみを残し、指定された式に当てはまる行を消します。DISTINCT ON 式は ORDER BY アイテムと同じルールを使って解釈されます。下記を見て下さい。それぞれの集合の "最初の行"ORDER BY が望まれる行が最初に出ることを確実にしないかぎりは予測できないことに注意して下さい。例えば

        SELECT DISTINCT ON (location) location, time, report
        FROM weatherReports
        ORDER BY location, time DESC;

はそれぞれの場所の最新の気象情報を取りだします。しかしもしそれぞれの場所の時間値を降順に強制するために ORDER BY を使わなかったとしたら、それぞれの場所の予測できない時代の予報を得ることになってしまいます。

GROUP BY 句によってユーザがテーブルを、一つ以上の値に当てはまる行のグループにわけることができます。(GROUP BY句を見て下さい。)

HAVING 句によって、指定された条件にあう行のグループのみを選択することができます。(HAVING 句を見て下さい。)

ORDER BY 句は、返された行が指定された順にソートされるようにします。もし ORDER BY が与えられないと、行はシステムにとって一番作りやすい順番で返されます。(ORDER BY 句を見て下さい。)

SELECT 問い合わせは UNION, INTERSECT, そして EXCEPT 演算子を使って組み合わせることができます。もしこれらの演算子の順番を決める必要があるときはカッコを使います。

UNION 演算子は、関連する問い合わせが返す行の集まりを計算します。重複行は ALL が指定されない限り削除されます。(UNION 句を見て下さい。)

INTERSECT 演算子は両方の問い合わせに共通する行を計算します。重複行は ALL が指定されない限り削除されます。(INTERSECT 句を見て下さい)

EXCEPT 演算子は最初の問い合わせからは返され二番目の問い合わせからは返されない行を計算します。重複行は ALL が指定されない限り削除されます。(EXCEPT 句を見て下さい。)

FOR UPDATE 句は SELECT 文が、選択された行の排他的ロックを実行することを許可します。

LIMIT 句はユーザが返すべき問い合わせによって作られる行のサブセットを可能にします。(LIMIT 句を見て下さい。)

テーブルの値を読むためには SELECT 権限を持っていなければなりません。(GRANT/REVOKE文を見て下さい。 )

FROM 句

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

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

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

最後に、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 は、=, <, <=, >, >= ,<> のいずれか、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, FOR UPDATE, もしくはLIMIT 句を持たない任意の select 式を指定できます。(ORDER BY と LIMIT は select がカッコで囲まれていると副式に付けることができます。カッコなしでは、これらの句は右側の入力式ではなく UNION の結果に適用するものとして判断されます。

UNION 演算子は、関連する問い合わせが返す行の集合(和集合)を計算します。UNION の直接の演算項目を表す二つの 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, FOR UPDATE, もしくは LIMIT 句を持たない任意の 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, FOR UPDATE, もしくは LIMIT 句を持たない任意の 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 で順番を制御しない限り問い合わせの結果を特定の順番で返すことを約束しないという事実の本質的な結果なのです。

使用方法

テーブル 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
 Une Femme est une Femme   | 102 | Jean Luc Godard  | 1961-03-12 | Romantic
 Vertigo                   | 103 | Paramount        | 1958-11-14 | Action
 Becket                    | 103 | Paramount        | 1964-02-03 | Drama
 48 Hrs                    | 103 | Paramount        | 1982-10-22 | Action
 War and Peace             | 104 | Mosfilm          | 1967-02-12 | Drama
 West Side Story           | 105 | United Artists   | 1961-01-03 | Musical
 Bananas                   | 105 | United Artists   | 1971-07-13 | Comedy
 Yojimbo                   | 106 | Toho             | 1961-06-16 | Drama
 There's a Girl in my Soup | 107 | Columbia         | 1970-06-11 | Comedy
 Taxi Driver               | 107 | Columbia         | 1975-05-15 | Action
 Absence of Malice         | 107 | Columbia         | 1981-11-15 | Action
 Storia di una donna       | 108 | Westward         | 1970-08-15 | Romantic
 The King and I            | 109 | 20th Century Fox | 1956-08-11 | Musical
 Das Boot                  | 110 | Bavaria Atelier  | 1981-11-11 | Drama
 Bed Knobs and Broomsticks | 111 | Walt Disney      |            | Musical
(17 rows)

全ての映画の列 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
(5 rows)

全ての映画の列 len を合計し kind 列によって結果をグループ化し、合計が 5 時間より少ないグループの合計を表示します。

SELECT kind, SUM(len) AS total
    FROM films
    GROUP BY kind
    HAVING SUM(len) < INTERVAL '5 hour';

 kind     | total
----------+-------
 Comedy   | 02:58
 Romantic | 04:38
(2 rows)

以下の二つの例は個別の結果を二番目の列 (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
(13 rows)

この例は、結果をそれぞれのテーブルで W で始まるものだけに制限して、どのようにテーブル distributorsactors の結合を獲得するかを表しています。特定の行のみが必要なので、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

互換性

拡張

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

SELECT 2+2;

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

他の DBMS は、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 句も持つ問い合わせで使われる場合警告を出します。

SQL92

SELECT 句

SQL92 標準では、オプションのキーワード AS はただのノイズなので、意味に影響することなく省略することが可能です。PostgreSQL パーサは出力列の名前を変更する際にこのキーワードを要求します。 なぜなら型の拡張機能を考えるとこのコンテキストでは構文解析の不明瞭さにつながるからです。しかし、FROM アイテムでは AS はオプションです。

DISTINCT ON 文は SQL92 の一部ではありません。LIMIT も OFFSET もそうです。

SQL92 では ORDER BY 句は結果の列名か順番の数のみを使うことができ、GROUP BY 句は入力列名のみを使うことができます。PostgreSQL はこれらの句がそれぞれ他の選択も認めるように拡張します(しかしもし不明瞭さがある場合は標準の解釈を使います)。 PostgreSQL はどちらの句も任意の式を指定するよう認めています。式で使われる名前は常に結果の列名ではなく入力列名として見なされることに注意してください。

UNION/INTERSECT/EXCEPT 句

SQL92 の UNION/INTERSECT/EXCEPT の構文では CORRESPONDING BY オプションを追加することができます。

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

CORRESPONDING BY 句は PostgreSQL ではサポートされていません。