13.2.9 SELECT 構文

[+/-]

13.2.9.1 SELECT ... INTO 構文

13.2.9.2 JOIN 構文

13.2.9.3 インデックスヒントの構文

13.2.9.4 UNION 構文

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT は、1 つ以上のテーブルから選択された行を取得するために使用され、UNION ステートメントとサブクエリーを含めることができます。セクション13.2.9.4「UNION 構文」およびセクション13.2.10「サブクエリー構文」を参照してください。

SELECT ステートメントのもっとも一般的に使用される句は次のとおりです。

• 各 select_expr は、取得するカラムを示します。少なくとも 1 つの select_expr が存在する必要があります。

• table_references は、行を取得する 1 つまたは複数のテーブルを示します。その構文については、セクション13.2.9.2「JOIN 構文」で説明されています。

• MySQL 5.6.2 から、SELECT は、table_reference 内のテーブル名のあとにパーティションまたはサブパーティション (またはその両方) のリストを含む PARTITION キーワードを使用した明示的なパーティション選択をサポートしています (セクション13.2.9.2「JOIN 構文」を参照してください)。この場合、行はリストされているパーティションからのみ選択され、テーブルのほかのパーティションはすべて無視されます。詳細および例については、セクション19.5「パーティション選択」を参照してください。

  MySQL 5.6.6 以降では、テーブルレベルのロック (つまり、パーティションロック) を実行する MyISAM などのストレージエンジンを使用しているテーブルからの SELECT ... PARTITION では、PARTITION オプションで指定されているパーティションまたはサブパーティションのみがロックされます。

  詳細は、セクション19.6.4「パーティショニングとロック」を参照してください。

• WHERE 句 (指定されている場合) は、選択されるために行が満たす必要のある 1 つまたは複数の条件を示します。where_condition は、選択される各行に対して true に評価される式です。WHERE 句がない場合、このステートメントはすべての行を選択します。

  WHERE 式では、集約 (サマリー) 関数を除き、MySQL がサポートするすべての関数および演算子を使用できます。セクション9.5「式の構文」および第12章「関数と演算子」を参照してください。

SELECT を使用して、どのテーブルも参照せずに計算された行を取得することもできます。

例:

mysql> SELECT 1 + 1;
        -> 2

テーブルが参照されない状況では、ダミーのテーブル名として DUAL を指定することが許可されます。

mysql> SELECT 1 + 1 FROM DUAL;
        -> 2

DUAL は純粋に、すべての SELECT ステートメントに FROM や、場合によってはその他の句が存在することを要求するユーザーの便宜のために用意されています。MySQL は、これらの句を無視する可能性があります。MySQL では、テーブルが参照されない場合でも FROM DUAL は必要ありません。

一般に、使用される句は、正確に構文の説明で示されている順序で指定する必要があります。たとえば、HAVING 句は、すべての GROUP BY 句のあとで、かつすべての ORDER BY 句の前にある必要があります。例外として、INTO 句は、構文の説明で示されている位置か、または select_expr リストの直後のどちらにも現れることができます。INTO の詳細は、セクション13.2.9.1「SELECT ... INTO 構文」を参照してください。

select_expr 項のリストは、どのカラムを取得するかを示す選択リストで構成されています。これらの項はカラムや式を指定するか、または * の短縮形を使用できます。

• 1 つの修飾されていない * のみから成る選択リストは、すべてのテーブルのすべてのカラムを選択するための短縮形として使用できます。

  SELECT * FROM t1 INNER JOIN t2 ...

• tbl_name.* は、指定されたテーブルのすべてのカラムを選択するための修飾された短縮形として使用できます。

  SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ...

• 修飾されていない * を選択リスト内のほかの項目とともに使用すると、解析エラーが生成される可能性があります。この問題を回避するには、修飾された tbl_name.* 参照を使用します。

  SELECT AVG(score), t1.* FROM t1 ...

次のリストは、その他の SELECT 句に関する追加情報を示しています。

• AS alias_name を使用して、select_expr にエイリアスを指定できます。エイリアスは式のカラム名として使用され、GROUP BY、ORDER BY、または HAVING 句で使用できます。例:

  SELECT CONCAT(last_name,', ',first_name) AS full_name
    FROM mytable ORDER BY full_name;

  select_expr にエイリアスとして識別子を指定する場合、AS キーワードはオプションです。前の例は、次のように記述することもできました。

  SELECT CONCAT(last_name,', ',first_name) full_name
    FROM mytable ORDER BY full_name;

  ただし、AS はオプションであるため、2 つの select_expr 式の間のカンマを忘れると、軽微な問題が発生する可能性があります。MySQL は、2 番目の式をエイリアスとして解釈します。たとえば、次のステートメントでは、columnb はエイリアスとして処理されます。

  SELECT columna columnb FROM mytable;

  このため、カラムのエイリアスを指定するときは AS を明示的に使用するようにすることをお勧めします。

  WHERE 句が実行されるときはまだカラム値が決定されていない可能性があるため、WHERE 句内でカラムのエイリアスを参照することは許可されません。セクションB.5.5.4「カラムエイリアスに関する問題」を参照してください。

• FROM table_references 句は、行を取得する 1 つまたは複数のテーブルを示します。複数のテーブルを指定すると、結合が実行されます。結合構文については、セクション13.2.9.2「JOIN 構文」を参照してください。指定されたテーブルごとに、オプションでエイリアスを指定できます。

  tbl_name [[AS] alias] [index_hint]

  インデックスヒントを使用すると、クエリー処理中にインデックスを選択する方法に関する情報がオプティマイザに提供されます。これらのヒントを指定するための構文については、セクション13.2.9.3「インデックスヒントの構文」を参照してください。

  代わりの方法として SET max_seeks_for_key=value を使用して、MySQL にテーブルスキャンの代わりにキースキャンを強制的に実行させることができます。セクション5.1.4「サーバーシステム変数」を参照してください。

• データベースを明示的に指定するために、デフォルトデータベース内でテーブルを tbl_name または db_name.tbl_name として参照できます。カラムを col_name、tbl_name.col_name または db_name.tbl_name.col_name として参照できます。参照があいまいにならないかぎり、カラム参照のために tbl_name または db_name.tbl_name プリフィクスを指定する必要はありません。より明示的なカラム参照形式を必要とするあいまいさの例については、セクション9.2.1「識別子の修飾子」を参照してください。

• tbl_name AS alias_name または tbl_name alias_name を使用して、テーブル参照にエイリアスを指定できます。

  SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
    WHERE t1.name = t2.name;
  
  SELECT t1.name, t2.salary FROM employee t1, info t2
    WHERE t1.name = t2.name;

• カラム名、カラムのエイリアス、またはカラム位置を使用して、出力のために選択されたカラムを ORDER BY および GROUP BY 句で参照できます。カラム位置は整数であり、1 から始まります。

  SELECT college, region, seed FROM tournament
    ORDER BY region, seed;
  
  SELECT college, region AS r, seed AS s FROM tournament
    ORDER BY r, s;
  
  SELECT college, region, seed FROM tournament
    ORDER BY 2, 3;

  逆の順序でソートするには、ソートに使用する ORDER BY 句内のカラムの名前に DESC (降順) キーワードを追加します。デフォルトは昇順です。これは、ASC キーワードを使用して明示的に指定できます。

  ORDER BY がサブクエリー内で発生し、外側のクエリー内でも適用される場合は、もっとも外側の ORDER BY が優先されます。たとえば、次のステートメントの結果は昇順ではなく、降順でソートされます。

  (SELECT ... ORDER BY a) ORDER BY a DESC;

  カラム位置の使用は、この構文が SQL 標準から削除されたため非推奨です。

• GROUP BY を使用すると、出力行は、同じカラムに対して ORDER BY を指定したかのように GROUP BY カラムに従ってソートされます。GROUP BY によって生成されるソートのオーバーヘッドを回避するには、ORDER BY NULL を追加します。

  SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;

  MySQL 5.6 における暗黙の GROUP BY ソートへの依存は、非推奨になっています。グループ化された結果の特定のソート順序を実現するには、明示的な ORDER BY 句を使用することをお勧めします。GROUP BY ソートは、たとえば、オプティマイザがもっとも効率的であると考えるどのような方法でも、グループ化を指示できるようにしたり、ソートオーバーヘッドを回避したりするためなどに、今後のリリースで変更される可能性のある MySQL 拡張機能です。

• MySQL では GROUP BY 句が拡張され、この句で指定されているカラムのあとに ASC と DESC も指定できるようになっています。

  SELECT a, COUNT(b) FROM test_table GROUP BY a DESC;

• MySQL では、GROUP BY の使用が、GROUP BY 句で指定されていないフィールドの選択を許可するように拡張されています。クエリーから期待する結果が得られない場合は、セクション12.19「GROUP BY 句で使用される関数と修飾子」にある GROUP BY の説明を参照してください。

• GROUP BY では、WITH ROLLUP 修飾子が許可されます。セクション12.19.2「GROUP BY 修飾子」を参照してください。

• HAVING 句は、ほぼ最後 (項目がクライアントに送信される直前) に最適化なしで適用されます。(LIMIT は HAVING のあとに適用されます。)

  SQL 標準では、HAVING は GROUP BY 句内のカラムか、または集約関数で使用されるカラムしか参照できません。ただし、MySQL ではこの動作への拡張がサポートされており、HAVING が SELECT リスト内のカラムや外側サブクエリー内のカラムを参照することも許可されます。

  HAVING 句があいまいなカラムを参照している場合は、警告が発生します。次のステートメントにある col2 は、エイリアスとカラム名の両方として使用されているため、あいまいです。

  SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

  標準 SQL の動作の方が優先されるため、HAVING のカラム名が GROUP BY で使用されると同時に、出力カラムリスト内のエイリアスが指定されたカラムとしても使用されている場合は、GROUP BY カラム内のカラムが優先されます。

• WHERE 句に含めるべき項目には HAVING を使用しないでください。たとえば、次のように記述しないでください。

  SELECT col_name FROM tbl_name HAVING col_name > 0;

  代わりに、次のように記述してください。

  SELECT col_name FROM tbl_name WHERE col_name > 0;

• HAVING 句は、WHERE 句が参照できない集約関数を参照できます。

  SELECT user, MAX(salary) FROM users
    GROUP BY user HAVING MAX(salary) > 10;

  (これは、一部の古いバージョンの MySQL では機能しませんでした。)

• MySQL では、重複したカラム名が許可されます。つまり、同じ名前を持つ複数の select_expr が存在できます。これは、標準 SQL の拡張です。MySQL では GROUP BY や HAVING が select_expr 値を参照することも許可されるため、これにより、あいまいさが発生する場合があります。

  SELECT 12 AS a, a FROM t GROUP BY a;

  このステートメントでは、どちらのカラムの名前も a です。グループ化のために正しいカラムが使用されるようにするために、select_expr ごとに異なる名前を使用してください。

• MySQL は、ORDER BY 句内の修飾されていないカラムまたはエイリアス参照を、まず select_expr 値、次に FROM 句内のテーブルのカラム内を検索することによって解決します。GROUP BY または HAVING 句の場合は、select_expr 値内を検索する前に FROM 句を検索します。(GROUP BY と HAVING について、これは、ORDER BY) の場合と同じルールを使用していた MySQL 5.0 より前の動作とは異なります。

• LIMIT 句を使用すると、SELECT ステートメントによって返される行数を制約できます。LIMIT は 1 つまたは 2 つの数値引数を受け取ります。これは、どちらも負ではない整定数である必要があります。ただし、次の例外があります。

  • 準備済みステートメント内では、? プレースホルダマーカーを使用して LIMIT パラメータを指定できます。

  • ストアドプログラム内では、整数値のルーチンパラメータまたはローカル変数を使用して LIMIT パラメータを指定できます。

  引数が 2 つの場合、最初の引数は返す先頭行のオフセットを指定し、2 番目の引数は返す行の最大数を指定します。最初の行のオフセットは (1 ではなく) 0 です。

  SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

  特定のオフセットから結果セットの最後までのすべての行を取得するために、2 番目のパラメータにある程度大きい数字を使用できます。次のステートメントは、96 行目から最後の行までのすべての行を取得します。

  SELECT * FROM tbl LIMIT 95,18446744073709551615;

  引数が 1 つの場合、この値は、結果セットの先頭から返す行数を指定します。

  SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows

  つまり、LIMIT row_count は LIMIT 0, row_count と同等です。

  準備済みステートメントでは、プレースホルダを使用できます。次のステートメントは、tbl テーブルの 1 行を返します。

  SET @a=1;
  PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
  EXECUTE STMT USING @a;

  次のステートメントは、tbl テーブルの 2 行目から 6 行目までを返します。

  SET @skip=1; SET @numrows=5;
  PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';
  EXECUTE STMT USING @skip, @numrows;

  PostgreSQL との互換性のために、MySQL は LIMIT row_count OFFSET offset 構文もサポートしています。

  LIMIT がサブクエリー内に現れ、また外部クエリーでも適用される場合は、もっとも外側の LIMIT が優先されます。たとえば、次のステートメントは、1 行ではなく 2 行を生成します。

  (SELECT ... LIMIT 1) LIMIT 2;

• PROCEDURE 句は、結果セット内のデータを処理するプロシージャーを指定します。例については、セクション8.4.2.4「PROCEDURE ANALYSE の使用」を参照してください。ここでは、テーブルサイズの削減に役立つ可能性のある最適なカラムデータ型に関する提案を得るために使用できるプロシージャーである ANALYSE について説明しています。

• SELECT の SELECT ... INTO 形式を使用すると、クエリー結果をファイルに書き込んだり、変数に格納したりできます。詳細は、セクション13.2.9.1「SELECT ... INTO 構文」を参照してください。

• ページまたは行ロックを使用するストレージエンジンで FOR UPDATE を使用した場合、クエリーによって検査される行は、現在のトランザクションの最後まで書き込みがロックされます。LOCK IN SHARE MODE を使用すると、その検査される行のほかのトランザクションによる読み取りは許可するが、その更新または削除を許可しない共有ロックが設定されます。セクション14.2.5「ロック読み取り (SELECT ... FOR UPDATE および SELECT ... LOCK IN SHARE MODE)」を参照してください。

  さらに、CREATE TABLE new_table SELECT ... FROM old_table ... などのステートメントで SELECT の一部として FOR UPDATE を使用することはできません。(それを行おうとすると、このステートメントはエラー Can't update table 'old_table' while 'new_table' is being created で拒否されます。)これは、CREATE TABLE ... SELECT ステートメントが作成されているテーブル以外のテーブルで変更を行うことを許可していた、MySQL 5.5 およびそれ以前からの動作の変更です。

SELECT キーワードのあとに、このステートメントの操作に影響を与えるいくつかのオプションを使用できます。HIGH_PRIORITY、STRAIGHT_JOIN、および SQL_ で始まるオプションは、標準 SQL への MySQL 拡張です。

• ALL および DISTINCT オプションは、重複した行を返すかどうかを指定します。ALL (デフォルト) は、重複を含め、一致するすべての行を返すように指定します。DISTINCT は、重複した行の結果セットからの削除を指定します。両方のオプションを指定するとエラーになります。DISTINCTROW は DISTINCT のシノニムです。

• HIGH_PRIORITY は SELECT に、テーブルを更新するステートメントより高い優先度を与えます。これは、非常に高速であり、かつただちに実行する必要のあるクエリーにのみ使用するようにしてください。テーブルが読み取りに対してロックされている間に発行された SELECT HIGH_PRIORITY クエリーは、そのテーブルが未使用になるのを待機している更新ステートメントが存在する場合でも実行されます。これは、テーブルレベルロックのみを使用するストレージエンジン (MyISAM、MEMORY、および MERGE) にのみ影響を与えます。

  HIGH_PRIORITY を、UNION の一部である SELECT ステートメントで使用することはできません。

• STRAIGHT_JOIN は、オプティマイザに、テーブルを FROM 句にリストされている順序で強制的に結合させます。オプティマイザがテーブルを最適でない順序で結合する場合は、これを使用してクエリーを高速化できます。STRAIGHT_JOIN はまた、table_references リストでも使用できます。セクション13.2.9.2「JOIN 構文」を参照してください。

  STRAIGHT_JOIN は、オプティマイザが const テーブルまたは system テーブルとして処理するテーブルには適用されません。このようなテーブルは単一行を生成し、クエリー実行の最適化フェーズ中に読み取られます。また、そのカラムへの参照は、クエリー実行が続行される前に適切なカラム値で置き換えられます。これらのテーブルは、EXPLAIN によって表示されるクエリー計画に最初に表示されます。「セクション8.8.1「EXPLAIN によるクエリーの最適化」」を参照してください。この例外は、外部結合の NULL で補完された側で使用されている const テーブルまたは system テーブル (つまり、LEFT JOIN の右側のテーブルまたは RIGHT JOIN の左側のテーブル) には適用されない可能性があります。

• SQL_BIG_RESULT または SQL_SMALL_RESULT は、結果セットの行数がそれぞれ多いこと、または少ないことをオプティマイザに通知するために GROUP BY または DISTINCT とともに使用できます。SQL_BIG_RESULT の場合、MySQL は、必要に応じてディスクベースの一時テーブルを直接使用し、ソートでは GROUP BY 要素に関するキーで一時テーブルを使用することを優先します。SQL_SMALL_RESULT の場合、MySQL はソートを使用する代わりに、高速な一時テーブルを使用して結果のテーブルを格納します。これは、通常は必要ないはずです。

• SQL_BUFFER_RESULT は、結果を強制的に一時テーブルに配置します。これは、MySQL がテーブルロックを早期に解放する場合や、結果セットをクライアントに送信するのに長い時間がかかる場合に役立ちます。このオプションは、サブクエリーまたは後続の UNION ではなく、トップレベルの SELECT ステートメントでのみ使用できます。

• SQL_CALC_FOUND_ROWS は MySQL に、LIMIT 句をすべて無視して、結果セットに含まれる行数を計算するよう指示します。そのあと、行数は SELECT FOUND_ROWS() を使用して取得できます。セクション12.14「情報関数」を参照してください。

• SQL_CACHE および SQL_NO_CACHE オプションは、クエリーキャッシュ内のクエリー結果のキャッシュに影響を与えます (セクション8.9.3「MySQL クエリーキャッシュ」を参照してください)。SQL_CACHE は MySQL に、結果がキャッシュ可能であり、query_cache_type システム変数の値が 2 または DEMAND である場合は、結果をクエリーキャッシュに格納するよう指示します。SQL_NO_CACHE を指定すると、サーバーはクエリーキャッシュを使用しません。それは、結果がすでにキャッシュされているかどうかを確認するためにクエリーキャッシュをチェックせず、クエリー結果もキャッシュしません。(パーサーの制限のため、スペース文字の前後に SQL_NO_CACHE キーワードを付ける必要があります。改行などのスペース以外では、結果がすでにキャッシュされているかどうかを確認するために、サーバーにクエリーキャッシュをチェックさせます。)

  ビューの場合、SQL_NO_CACHE は、クエリー内のいずれかの SELECT に現れた場合に適用されます。キャッシュ可能なクエリーでは、SQL_CACHE は、そのクエリーによって参照されるビューの最初の SELECT に現れた場合に適用されます。

  MySQL 5.6 では、これらの 2 つのオプションは相互に排他的であり、両方が指定された場合はエラーが発生します。また、これらのオプションは、サブクエリー (FROM 句内のサブクエリーを含む) や、最初の SELECT を除く和集合内の SELECT ステートメント内では許可されません。

MySQL 5.6.6 より前は、テーブルレベルのロックを採用した MyISAM などのストレージエンジンを使用しているパーティション化されたテーブルからの SELECT によって、そのテーブルのすべてのパーティションがロックされました。これは、SELECT ... PARTITION クエリーにも当てはまりました。(これは、行レベルロックを採用した InnoDB などのストレージエンジンでは発生しておらず、現在も発生しません。)MySQL 5.6.6 以降では、MySQL はパーティションロックプルーニングを使用します。これにより、SELECT ステートメントの WHERE 句に一致する行を含むパーティションだけが実際にロックされるようになります。詳細は、セクション19.6.4「パーティショニングとロック」を参照してください。