JOIN
構文
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
, ... [FROMtable_references
[WHEREwhere_condition
] [GROUP BY {col_name
|expr
|position
} [ASC | DESC], ... [WITH ROLLUP]] [HAVINGwhere_condition
] [ORDER BY {col_name
|expr
|position
} [ASC | DESC], ...] [LIMIT {[offset
,]row_count
|row_count
OFFSEToffset
}] [PROCEDUREprocedure_name
(argument_list
)] [INTO OUTFILE 'file_name
'export_options
| INTO DUMPFILE 'file_name
' | INTO @var_name
[, @var_name
]] [FOR UPDATE | LOCK IN SHARE MODE]]
SELECT
は、1つまたは複数のテーブルから選択した行を検索するために利用され、UNION
ステートメントとサブクエリを含む事ができます。項2. 「UNION
構文」、項 「サブクエリ構文」 を参照して下さい。
一番良く利用される SELECT
ステートメントの条項はこれらです。
各 select_expr
は、検索したいカラムを指示します。少なくても1つの select_expr
が必要です。
table_references
はどのテーブルから行の検索をするかを指示します。その構文は 項1. 「JOIN
構文」 で説明されています。
WHERE
条項がもしあれば、それは行が選択される為に満たさなければいけない条件を指示します。 where_condition
は選択される行が真であるかを確認する式です。ステートメントは、もし WHERE
条項がなければ全ての行を選択します。
WHERE
条項の中では、総計 (要約) 関数以外の、MySQL がサポートする関数や演算子の全てを利用する事ができます。詳しくは 章 11. 関数と演算子 を参照してください。
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
条項が構文の説明どおりに表される事も、FROM
条項の直前に先行して表される事も両方可能であるという事です。
select_expr
に AS alias_name
を利用したエイリアスを与える事ができます。そのエイリアスは、式のカラム名として利用され、 GROUP BY
、ORDER BY
、または HAVING
条項内で利用する事ができます。例:
SELECT CONCAT(last_name,', ',first_name) AS full_name FROM mytable ORDER BY full_name;
AS
キーワードは、select_expr
をエイリアスを指定する時には任意です。前出の例はこのように書く事が可能でした。
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. 「Problems with Column Aliases」 を参照してください。
FROM table_references
はどのテーブルから行の検索をするかを指示します。もし複数のテーブルに名前をつけると、それは結合を実行するという事になります。結合構文の情報に関しては、項1. 「JOIN
構文」 を参照してください。指定された各テーブルにエイリアスを任意で指定する事ができます。
tbl_name
[[AS]alias
] [{USE|IGNORE|FORCE} INDEX (key_list
)]
どのようにインデックスを選択するかについてのオプチマイザ ヒントを与える USE INDEX
、IGNORE INDEX
、FORCE INDEX
の利用については 項1. 「JOIN
構文」 で説明しています。
MySQL がテーブル スキャンの代わりにキー スキャンを好むように仕向ける代替法として SET max_seeks_for_key=value
を利用する事ができます。詳しくは 項 「システム変数」 を参照してください。
データベースを明示的に指定する為に、デフォルト データベース内で tbl_name
、または db_name
.tbl_name
としてテーブルを参照する事ができます。col_name
、tbl_name
.col_name
、または db_name
.tbl_name
.col_name
としてカラムを参照する事ができます。参照が曖昧にならなければ、カラムの参照に tbl_name
か db_name
.tbl_name
プリフィックスを指定する必要はありません。さらに明確なカラム参照フォームを必要とする例に関しては、項 「識別子の修飾語」 を参照してください。
テーブル参照では 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
キ-ワードを利用して明示的に指定する事ができます。
カラム位置の利用は、構文が SQL スタンダードから削除された為に今後廃止される可能性があります。
もし GROUP BY
を利用すると、アウトプット行は GROUP BY
に従って、まるで同じカラムに ORDER BY
を持っていたかのようにソートされます。GROUP BY
が生成するソートのオーバーヘッドを防ぐには、ORDER BY NULL
を追加してください。
SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;
MySQL は、条項の中で名づけられたカラムの後ろに ASC
と DESC
を指定する事もできるように、GROUP BY
条項を拡張します。
SELECT a, COUNT(b) FROM test_table GROUP BY a DESC;
MySQL は、GROUP BY
条項内で言及されていないフィールドの選択を許可する為に、 GROUP BY
の利用を拡張します。もしクエリから期待通りの結果を得る事ができないのであれば、項11.11. 「GROUP BY
句との関数および修飾子の使用」 内の GROUP BY
の説明を読んでください。
GROUP BY
は WITH ROLLUP
修飾因子を許容します。詳しくは 項 「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
カラム内のカラムに与えられます。
HAVING
は、WHERE
条項内になければいけない項目に対しては利用しないでください。例えば、次のような物を書かないでください。
SELECTcol_name
FROMtbl_name
HAVINGcol_name
> 0;
代わりにこのように書いてください。
SELECTcol_name
FROMtbl_name
WHEREcol_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 は select_expr
値の中、そして FROM
条項内のテーブル カラムの中を検索する事で ORDER BY
条項内の無条件のカラムやエイリアス参照を解決します。GROUP BY
か HAVING
条項に対しては、select_expr
値内を検索する前に FROM
条項を検索します。(GROUP BY
と HAVING
に対しては、ORDER BY
に対するのと同じルールを利用した MySQL 5.0 以前の動作とは異なります。)
LIMIT
条項は SELECT
ステートメントに返された行数を制限するのに利用する事ができます。LIMIT
は、負数以外の整数定数でなければいけない、1つか2つの数値引数を取ります。(準備されたステートメントを利用している時以外)
その2つの引数のうち、最初の物は返される最初の行のオフセットを指定し、2つめの物は返される行の最高数を指定します。冒頭の行のオフセットは0です。(1ではありません)
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
構文もサポートします。
SELECT
の SELECT ... INTO OUTFILE 'file_name'
型は選択された行をファイルに書き込みます。ファイルはサーバ ソフト上に作成されるので、この構文を利用するには FILE
権限を持たなければいけません。 file_name
は、/etc/passwd
のようなファイルやデータベース テーブルが、その他の物の間で破壊されるのを防ぐ既存ファイルにはなり得ません。MySQL 5.1.6 以降のバージョンでは、character_set_filesystem
システム変数は、ファイル名の解明をコントロールします。
SELECT ... INTO OUTFILE
ステートメントはそもそも、サーバ マシン上のテキスト ファイルにテーブルをすばやく捨てさせる事を意図しています。もしサーバ ホストではなく、クライアント
ホスト上に結果ファイルを作成したければ、SELECT ... INTO OUTFILE
を利用する事はできません。その場合、クライアント ホスト上にファイルを生成する為には、代わりに mysql -e "SELECT ..." > file_name
のようなコマンドを利用しなければいけません。
SELECT ... INTO OUTFILE
は LOAD DATA INFILE
の補数です。ステートメントの export_options
部分の構文は、LOAD DATA INFILE
ステートメントと共に利用される物と同じ FIELDS
と LINES
条項で成り立っています。詳しくは 項 「LOAD DATA INFILE
構文」 を参照してください。
FIELDS ESCAPED BY
は、特別な文字をどのように書き込むのかをコントロールします。もし FIELDS ESCAPED BY
文字が空でなければ、それはアウトプット上で次の文字に先行するプリフィックスとして利用されます。
FIELDS ESCAPED BY
文字
FIELDS [OPTIONALLY] ENCLOSED BY
文字
FIELDS TERMINATED BY
と LINES TERMINATED BY
値の最初の文字
ASCII NUL
(ゼロの値のバイト;実際に拡張文字の後ろに書かれているのは、ゼロの値のバイトではなく、ASCII ‘0
’です。)
FIELDS TERMINATED BY
、ENCLOSED BY
、ESCAPED BY
、または LINES TERMINATED BY
文字は、ファイルを確実に読み返す事ができるように、拡張 されなければいけません。 ASCII NUL
は、ポケベルで見やすくする為に拡張されています。
結果のファイルは SQL 構文と一致する必要がないので、他の物は拡張される必要はありません。
もし FIELDS ESCAPED BY
文字が空なら、文字が拡張される事はなく、NULL
は \N
ではなく NULL
としてアウトプットされます。特に、もしデータ中のにフィールド値が先ほどのリストの中の文字を含んでいる場合は、空の拡張文字を指定するのは良い考えではないかも知れません。
ここに、多くのプログラムで利用されるカンマで区切られた値(CSV)のフォーマットのファイルを生成する例があります。
SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;
もし INTO OUTFILE
の代わりに INTO DUMPFILE
を利用すると、MySQL は、カラムやラインのターミネーションや、拡張操作を行う事無く、ファイルの中に行を1つだけ書き込みます。 これは、もしファイルの中に
BLOB
値を格納したいのであれば有効です。
INTO
条項は、複数のユーザ定義変数のリストに名前をつける事ができます。選択された値は変数に割り当てられます。変数の数はカラム数と一致しなければいけません。
ストアド ルーチンの中では、その変数はルーチン パラメータかローカル変数になり得ます。詳しくは 項3. 「SELECT ... INTO
ステートメント」 を参照してください。
注意:INTO OUTFILE
か INTO DUMPFILE
によって作成されたファイルは、サーバホスト上で全てのユーザによって書き込まれます。この理由は、MySQL サーバは、それを起動させているアカウントの持ち主であるユーザ以外によって所有されているファイルを作成する事はできない
という事です。(これらの理由の為に、mysqld を root
として起動する事は絶対にしてはいけません。)ですので、このファイルは内容を真似する事ができるように、誰でも修正ができる物である必要があります。
このセクションの最初の SELECT
構文の説明で、ステートメントの終わりの方の INTO
条項が表されています。FROM
条項に先行して、INTO OUTFILE
か INTO DUMPFILE
を直接利用する事も可能です。
PROCEDURE
条項は、結果セットの中にデータを処理しなければいけないプロシージャに名前をつけます。(例については、項 「Procedure Analyse」 をご覧ください。)
もしページか行ロックを利用するストレージ エンジンと FOR UPDATE
を一緒に利用するなら、クエリに検査された行は現在のトランザクションが終わるまで書き込みロックされます。 LOCK IN SHARE MODE
を利用すると、他のトランザクションが検査された行を読む事は許容しますが、それらを更新や削除する事は許容しない共通ロックを設定します。詳しくは
項5. 「SELECT ... FOR UPDATE
と SELECT ... LOCK IN SHARE MODE
ロック読み取り」 を参照してください。
SELECT
キーワードの後で、ステートメントの操作に影響を与える幾つかのオプションを利用する事ができます。
ALL
、DISTINCT
、そして DISTINCTROW
オプションは、複製行が返されるべきかどうかを指定します。もしこれらのオプションが何も与えられなければ、デフォルトは ALL
です。(全ての一致する行が返されます。)DISTINCT
と DISTINCTROW
は同義語で、結果セットから複製行を削除する指示を出します。
HIGH_PRIORITY
、STRAIGHT_JOIN
、そして SQL_
で始まるオプションは、スタンダード SQL の MySQL 拡張子です。
HIGH_PRIORITY
は SELECT
に、テーブルを更新するステートメントよりも高い優先順位を与えます。これは、スピードがとても速く、一度に実行されなければいけないクエリに対してだけ利用して下さい。読み込みの為にテーブルがロックされている間に発行された
SELECT HIGH_PRIORITY
クエリは、テーブルがフリーになるのを待っている更新ステートメントがあったとしても実行します。
HIGH_PRIORITY
は、UNION
の一部である SELECT
ステートメントと一緒には利用できません。
STRAIGHT_JOIN
は、オプチマイザが FROM
条項内にリストされている順番でテーブルに結合するよう働きかけます。もし最適化ツールが、最適ではない順番でテーブルに接合した時、クエリのスピードを早くする為にこれを利用する事ができます。詳しくは
項 「EXPLAIN
を使用して、クエリを最適化する」 を参照してください。STRAIGHT_JOIN
はまた table_references
リストの中でも利用できます。詳しくは 項1. 「JOIN
構文」 を参照してください。
SQL_BIG_RESULT
は、オプチマイザに結果セットが行を多く持っている事を教える為に GROUP BY
か DISTINCT
と共に利用する事ができます。この場合、MySQL は必要であればディスク ベースのテンポラリ テーブルを直接利用し、GROUP BY
要素上のキーを持つテンポラリ テーブルを利用してソートします。
SQL_BUFFER_RESULT
は結果がテンポラリ テーブルの中に置かれるよう働きかけます。これは、MySQL がテーブル ロックを早く解除するのを助け、クライアントに結果セットを送るのに時間がかかる場合に補助します。
SQL_BIG_RESULT
は、オプチマイザに結果セットが小さい事を教える為に GROUP BY
か DISTINCT
と共に利用する事ができます。この場合、MySQL は結果テーブルを格納する為、ソート機能を利用する代わりに高速のテンポラリ テーブルを利用します。通常これは必要ではないでしょう。
SQL_CALC_FOUND_ROWS
は全ての LIMIT
条項を無視して、結果セットの中にいくつ行があるかを計算するよう MySQL に指示します。 行数は SELECT FOUND_ROWS()
を利用して検索する事ができます。詳しくは 項 「情報関数」 を参照してください。
2
or DEMAND
の query_cache_type
値を利用している場合、SQL_CACHE
は、クエリ キャッシュの中にクエリの結果を格納するよう MySQL に指示します。 このオプションは、UNION
かサブクエリを利用するクエリに対して、クエリ中の全ての SELECT
に影響を与えます。詳しくは 項4.13. 「MySQL クエリ キャッシュ」 を参照してください。
SQL_NO_CACHE
は MySQL に対して、クエリ キャッシュ内のクエリの結果を格納しないように指示します。詳しくは 項4.13. 「MySQL クエリ キャッシュ」 を参照してください。UNION
かサブクエリを利用するクエリに対して、このオプションはクエリ中の全ての SELECT
に影響を与えます。
JOIN
構文MySQL は、SELECT
ステートメントの table_references
部分と、複合テーブル DELETE
と UPDATE
ステートメント
に対して、次の JOIN
構文をサポートします。
table_references:
table_reference
[,table_reference
] ...table_reference
:table_factor
|join_table
table_factor
:tbl_name
[[AS]alias
] [{USE|IGNORE|FORCE} INDEX (key_list
)] | (table_references
) | { OJtable_reference
LEFT OUTER JOINtable_reference
ONconditional_expr
}join_table
:table_reference
[INNER | CROSS] JOINtable_factor
[join_condition
] |table_reference
STRAIGHT_JOINtable_factor
|table_reference
STRAIGHT_JOINtable_factor
ONcondition
|table_reference
LEFT [OUTER] JOINtable_reference
join_condition
|table_reference
NATURAL [LEFT [OUTER]] JOINtable_factor
|table_reference
RIGHT [OUTER] JOINtable_reference
join_condition
|table_reference
NATURAL [RIGHT [OUTER]] JOINtable_factor
join_condition
: ONconditional_expr
| USING (column_list
)
テーブル参照は、接合式としても知られています。
table_factor
の構文は、SQL スタンダードと比較して拡張されます。後者は table_reference
だけを許容し、カッコ内のそれらのリストは許容しません。
もし table_reference
のリスト内の各カンマが内側の接合と同等であると考えると、これは保守的な拡張子という事になります。例:
SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
は次の物と同等です:
SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
MySQL では、CROSS JOIN
は INNER JOIN
と構文的に同等です。(お互いに置き換える事ができます。)スタンダード SQL では、それらは同等ではありません。INNER JOIN
は ON
条項と共に利用され、そうでなければ CROSS JOIN
が利用されます。
通常、内側結合演算だけを含む結合式内のカッコは無視する事ができます。MySQL はネスト化した接合をサポートします。(項 「入れ子結合最適化」 を参照してください。)
次のリストには、接合を書く時に考慮に入れる通常の要因が説明されています。
テーブル参照では tbl_name AS alias_name
か tbl_name alias_name
を利用してエイリアスを指定する事ができます。
SELECT t1.name, t2.salary FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name; SELECT t1.name, t2.salary FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;
INNER JOIN
と ,
(カンマ)は結合条件がない場合には意味的に同等となります。 両方とも、指示されたテーブルの間にデカルト結果を作り出します。(これは、最初のテーブル内の行1つ1つが、2番目のテーブルの行1つ1つに接合されるという事です。)
しかし、カンマ演算子の先行は、INNER JOIN
、CROSS JOIN
、LEFT JOIN
等のそれよりも少ないです。もし接合条件がある場合にカンマ接合と別のタイプの接合を混合すると、Unknown column 'col_name' in 'on clause'
という形のエラーが発生するかもしれません。この問題の対処法は、このセクションの後半で紹介します。
ON
条件文は WHERE
条項の中で利用する事ができる形の条件文です。通常、テーブルをどのように接合するのかを指定する条件には ON
条項を、結果セットの中にどの行が必要であるかを制限するには WHERE
条項を利用する必要があります。
もし LEFT JOIN
内の ON
か USING
部分内に右側のテーブルに一致する行がなければ、全てのカラムが NULL
に設定されている行が右側のテーブルに利用されます。この事実は、別のテーブル内に対応する物を持たないテーブル内の行を見つける為に利用する事ができます。
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;
この例は、table2
の中に存在しない id
値を持つ table1
内全ての行を見つけます。(table2
内に対応する行を持たない table1
内全ての行)これは、table2.id
が NOT NULL
を宣言したと仮定します。詳しくは 項 「LEFT JOIN
とRIGHT JOIN
最適化」 を参照してください。
USING(column_list)
条項は、両方のテーブルに存在しなければいけないカラムのリストに名前をつけます。もしテーブル a
と b
の両方がカラム c1
、c2
、そして c3
を含むと、次の接合は二つのテーブルの対応するカラムを比較します。
a LEFT JOIN b USING (c1,c2,c3)
2つのテーブルの NATURAL [LEFT] JOIN
は INNER JOIN
か、両方のテーブルに存在する全てのカラムに名前を付ける USING
条項を持つ LEFT JOIN
と意味的に同等になるよう定義されます。
RIGHT JOIN
は LEFT JOIN
と同じように機能します。コードがデータベース全体に移植できる状態を保つ為に、RIGHT JOIN
の代わりに LEFT JOIN
を利用する事をお勧めします。
接合構文の説明で表されている { OJ ... LEFT OUTER JOIN ...}
構文は ODBC を利用した互換性に対してだけ存在します。構文内のカールした中括弧は文字通り書き込まれる必要があります。それらは構文説明の別の部分で利用されているようなメタシンタックスではありません。
STRAIGHT_JOIN
は、左側のテーブルがいつも右側のテーブルの前に読み込まれるという事以外は JOIN
と全く同じです。 これは、接合オプチマイザがテーブルを間違った順番で置いてしまうという(数少ない)場合に利用する事ができます。
接合の例:
SELECT * FROM table1, table2; SELECT * FROM table1 INNER JOIN table2 ON table1.id=table2.id; SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; SELECT * FROM table1 LEFT JOIN table2 USING (id); SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table2.id=table3.id;
テーブルから情報を検索する時、MySQL がどのインデックスを利用するべきか、ヒントを与える事ができます。 USE INDEX (key_list)
を指定する事で、MySQL がテーブル内の行を見つける為に、有効なインデックスを1つだけ利用するように指示する事ができます。 既存構文 IGNORE INDEX (key_list)
は、MySQL がいくつかの特定のインデックスを利用しないように指示する事ができます。これらのヒントは、MySQL が可能なインデックスのリストの中から、間違ったインデックスを利用している事を、EXPLAIN
が表示した時に便利な物です。
テーブル スキャンは とても 高いと仮定されますが、USE INDEX (key_list)
のように機能する FORCE INDEX
を利用する事もできます。 言い換えると、テーブル内の行を見つける為に与えられたインデックスを利用できない場合、テーブル スキャンを利用する事ができるという事です。
USE INDEX
、IGNORE INDEX
、そして FORCE INDEX
は、MySQL がどのようにテーブルの中の行を見つけ、接合を行うのかを決定する時に、どのインデックスが利用されるのかという事にだけ影響を与えます。
それらは、ORDER BY
か GROUP BY
を解決する時にインデックスを利用するかどうかという事に影響を与えます。
USE KEY
、IGNORE KEY
、そして FORCE KEY
は USE INDEX
、IGNORE INDEX
、そして FORCE INDEX
の同義語です。
例:
SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3; SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;
接合処理は MySQL 5.0.12 で変更されました。
注意:自然接合と、外部接合異形を含む USING
を利用した接合は、SQL:2003 スタンダードに従って処理されます。その目的は、SQL:2003 に従い NATURAL JOIN
と JOIN ... USING
について、MySQL の構文と動作を提携させる事でした。 しかし、接合処理に関してのこれらの変更は、いくつかの接合に関して異なるアウトプット
カラムをもたらす可能性があります。また、古いバージョン (5.0.12 以前の物) で正しく機能していたいくつかのクエリも、スタンダードに適合する為に書き直される必要があります。
これらの変更には、主に5つの特徴があります。
MySQL が NATURAL
か USING
接合操作の結果カラムを決定する方法。(従って FROM
条項の結果という事)
選択されたカラムのリストの中への SELECT *
と SELECT tbl_name.*
の拡大。
NATURAL
か USING
接合内でのカラム名の決定。
NATURAL
か USING
接合の JOIN ... ON
への変形.
JOIN ... ON
の ON
条件内のカラム名の決定。
次のリストに、現在のバージョンと古いバージョンの接合処理の効果について比べた詳細が紹介されています。「以前は」 という言葉は 「MySQL 5.0.12 以前」 という意味です。
NATURAL
接合や USING
接合のカラムは以前と異なるかもしれません。特に、余分なアウトプット カラムはもう現れません、そして、SELECT *
拡大のカラムの順番は以前とは異なるかもしれません。
このステートメントのセットを検討してください。
CREATE TABLE t1 (i INT, j INT); CREATE TABLE t2 (k INT, j INT); INSERT INTO t1 VALUES(1,1); INSERT INTO t2 VALUES(1,1); SELECT * FROM t1 NATURAL JOIN t2; SELECT * FROM t1 JOIN t2 USING (j);
以前は、このステートメントはこのアウトプットを産出しました。
+------+------+------+------+ | i | j | k | j | +------+------+------+------+ | 1 | 1 | 1 | 1 | +------+------+------+------+ +------+------+------+------+ | i | j | k | j | +------+------+------+------+ | 1 | 1 | 1 | 1 | +------+------+------+------+
最初の SELECT
ステートメントの中で、カラム j
は両方のテーブル内に現れた為に接合カラムになります。という事は、スタンダード SQLによると、それはアウトプット内に2回ではなく1回のみ現れる必要があるという事になります。同じように、2番目の
SELECT ステートメントの中で、カラム j
は USING
条項の中で名前が付けられ、2回ではなく1回だけアウトプットの中に現れる必要があります。しかし、この両方で余分なカラムは排除されていません。また、スタンダード
SQL によると、カラムの順番は正しくありません。
そして、ステートメントはこのアウトプットを産出します。
+------+------+------+ | j | i | k | +------+------+------+ | 1 | 1 | 1 | +------+------+------+ +------+------+------+ | j | i | k | +------+------+------+ | 1 | 1 | 1 | +------+------+------+
余分なカラムは排除され、スタンダード SQL によると、このカラムの順番は正しいです。
最初に、1つ目のテーブルの順番で、2つの接合したテーブルに共通するカラムを合体させました。
次に、テーブルの順番で、最初のテーブル固有のカラムを合体させました。
最後に、テーブルの順番で、2番目のテーブル固有のカラムを合体させました。
2つの共通カラムを置き換えられる単一結果カラムは、合体操作を通して定義されました。これは、次のステートメントで、t1.a
と t2.a
の2つに対して、導き出された1つの接合カラム a
は a = COALESCE(t1.a, t2.a)
として定義される、という事です。
COALESCE(x, y) = (CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END)
もし接合操作がそれ以外の接合であれば、その接合の結果カラムは、接合されたテーブルの全てのカラムの連続で構成されます。これは以前と同じです。
外部接合に関する合体したカラム定義の結論は、合体したカラムはもし2つのうち1つのカラムがいつも NULL
であれば、非 NULL
カラムの値を含む、という事です。もしどちらのカラムも NULL
でない、または両方がそうである場合、両方の共通カラムは同じ値を持つので、どちらが合体したカラムの値として選択されるかというのは特に問題にはなりません。これを理解する簡単な方法は、外部接合の合体したカラムは
JOIN
の内側テーブルの共通カラムによって表される、と考える事です。テーブル t1(a,b)
と t2(a,c)
が次のコンテンツを持つと仮定してください。
t1 t2 ---- ---- 1 x 2 z 2 y 3 w
すると:
mysql> SELECT * FROM t1 NATURAL LEFT JOIN t2;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | x | NULL |
| 2 | y | z |
+------+------+------+
ここでは、カラム a
は t1.a
の値を含んでいます。
mysql> SELECT * FROM t1 NATURAL RIGHT JOIN t2;
+------+------+------+
| a | c | b |
+------+------+------+
| 2 | z | y |
| 3 | w | NULL |
+------+------+------+
ここでは、カラム a
は t2.a
の値を含んでいます。
これらの結果を JOIN ... ON
を利用した他の同等のクエリと比較してください。
mysql> SELECT * FROM t1 LEFT JOIN t2 ON (t1.a = t2.a);
+------+------+------+------+
| a | b | a | c |
+------+------+------+------+
| 1 | x | NULL | NULL |
| 2 | y | 2 | z |
+------+------+------+------+
mysql> SELECT * FROM t1 RIGHT JOIN t2 ON (t1.a = t2.a);
+------+------+------+------+
| a | b | a | c |
+------+------+------+------+
| 2 | y | 2 | z |
| NULL | NULL | 3 | w |
+------+------+------+------+
以前、 USING
条項は、対応するカラムを比較する ON
条項として再度書き込む事ができました。例えば、次の2つの条項は意味的に全く同じでした。
a LEFT JOIN b USING (c1,c2,c3) a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3
今はもうこの2つの条項は同じではありません。
どの行が接合条件を満たすかの判断に関しては、両方の接合は意味的に全く同じままです。
SELECT *
拡大に対してどのカラムを表示するかの判断に関しては、両方の接合は意味的に全く同じではありません。ON
接合が全てのテーブルから全てのカラムを選択するのに対して、USING
接合は対応するカラムの合体した値を選択します。先行する USING
接合に対しては、SELECT *
はこれらの値を選択します。
COALESCE(a.c1,b.c1), COALESCE(a.c2,b.c2), COALESCE(a.c3,b.c3)
ON
接合に対しては、SELECT *
が次の値を選択します。
a.c1, a.c2, a.c3, b.c1, b.c2, b.c3
内部結合では、両方のカラムが同じ値を持つので COALESCE(a.c1,b.c1)
は a.c1
か b.c1
と同じです。外部接合では(LEFT JOIN
のような)、2つのうち1つのカラムが NULL
になり得ます。そのカラムは結果から排除されます。
多方向自然接合の評価は、NATURAL
か USING
接合の結果に影響を与え、クエリの再書き込みを必要とするような、大変重要な形で異なっています。それぞれが行を1つ持つ3つのテーブル t1(a,b)
、t2(c,b)
、そして t3(a,c)
があると仮定してください。t1(1,2)
、t2(10,2)
、そして t3(7,10)
です。また、その3つのテーブル上にこの NATURAL JOIN
も持っていると仮定してください。
SELECT ... FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;
以前は、2つめの接合の左のオペランドは、ネスト化した接合 (t1 NATURAL JOIN t2)
とならなければいけない一方、t2
となると考えられていました。 その結果、t3
のカラムは t2
の中だけで共通カラムに関して確認され、そしてもし t3
が t1
を持つ共通カラムを持っていれば、これらのカラムは等価接合カラムとして利用されません。従って、以前は先行クエリは次の等価接合に変形されていました。
SELECT ... FROM t1, t2, t3 WHERE t1.b = t2.b AND t2.c = t3.c;
その接合では、もう1つの等価接合述語 (t1.a = t3.a)
がなくなっています。その結果、それはもう1つ行を作成するので、結果は空にはなりません。正しい同等のクエリはこれです。
SELECT ... FROM t1, t2, t3 WHERE t1.b = t2.b AND t2.c = t3.c AND t1.a = t3.a;
もし現在の MySQL のバージョンの中で、古いバージョンと同じクエリの結果が必要であれば、自然接合を最初の等価接合として書き換えてください。
以前は、カンマ演算子(,
)と JOIN
の両方は同じ優先順位だったので、接合式 t1, t2 JOIN t3
は ((t1, t2) JOIN t3)
として解釈されました。現在は JOIN
が高い優先順位を持つので、式は (t1, (t2 JOIN t3))
として解釈されます。この変更は、ON
条項が接合の演算子内のカラムだけを参照する事ができ、優先順位の変更はそれらの演算子が何であるかについての解釈を変えてしまうので、この条項を利用するステートメントに影響を与えます。
例:
CREATE TABLE t1 (i1 INT, j1 INT); CREATE TABLE t2 (i2 INT, j2 INT); CREATE TABLE t3 (i3 INT, j3 INT); INSERT INTO t1 VALUES(1,1); INSERT INTO t2 VALUES(1,1); INSERT INTO t3 VALUES(1,1); SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);
以前は、(t1,t2)
としての t1,t2
の暗黙のグループ分けのおかげで、SELECT
は正当でした。現在は JOIN
が優先順位を持つので ON
条項の演算子は t2
と t3
です。t1.i1
がどちらの演算子でもないので、結果は Unknown column 't1.i1' in 'on clause'
エラーになります。接合を実行させるには、ON
条項の演算子が (t1,t2)
と t3
となるように、括弧を利用して最初の2つのテーブルを明示的にグループ分けして下さい。
SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);
または、カンマ演算を利用するのを避け、その代わりに JOIN
を利用してください。
SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);
この変更は、カンマ演算子とそれよりも高い優先順位を持つ INNER JOIN
、CROSS JOIN
、LEFT JOIN
または RIGHT JOIN
を混合するステートメントにも適応します。
以前は、ON
条項はその右側で名前が付けられたテーブル内のカラムを参照する事ができました。現在は ON
条項はその演算子だけ参照する事ができます。
例:
CREATE TABLE t1 (i1 INT); CREATE TABLE t2 (i2 INT); CREATE TABLE t3 (i3 INT); SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;
以前は、SELECT
ステートメントは正当でした。現在は、i3
は、ON
条項の演算子ではない t3
内のカラムなので、ステートメントは Unknown column 'i3' in 'on clause'
エラーで失敗します。ステートメントは次のように書き換えられなければいけません。
SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);
NATURAL
か USING
接合内でのカラム名の決定は、以前とは違います。FROM
条項の外にあるカラム名に対しては、MySQL は以前と比べると上位集合であるクエリを扱います。 それは、以前は MySQL がいくつかのカラムが曖昧であるというエラーを発行したような場合でも、現在はクエリが正確に扱われるという事です。これは、現在は
MySQL が NATURAL
や USING
接合の共通カラムを単一カラムとして扱う為、クエリがそのようなカラムを参照した時、クエリ コンパイラがそれらを曖昧だとは認識しないという事実によるものです。
例:
SELECT * FROM t1 NATURAL JOIN t2 WHERE b > 1;
以前は、このクエリは ERROR 1052 (23000) を導いていました。場所条項内の、カラム 'b' が曖昧です。
.現在はそのクエリは正しい結果を導きます。
+------+------+------+ | b | c | y | +------+------+------+ | 4 | 2 | 3 | +------+------+------+
SQL:2003 スタンダードと比較した MySQL の拡張機能の1つは、スタンダードは NATURAL
や USING
接合(以前のような)の共通(合体した)カラムを修飾する事を許可しなかったのに対して、MySQL はそれを許可するという事です。
UNION
構文SELECT ... UNION [ALL | DISTINCT] SELECT ... [UNION [ALL | DISTINCT] SELECT ...]
UNION
は、結果を複数 SELECT
ステートメントから単一結果セットに結合させる為に利用されます。
最初の SELECT
ステートメントからのカラム名は、返された結果のカラム名として利用されます。各 SELECT
ステートメントの対応する位置にリストされている選択されたカラムは、同じデータ タイプを持つ必要があります。(例えば、最初のステートメントに選択された最初のカラムは、別のステートメントに選択された最初のカラムと同じタイプを持つ必要があります。)
もし、対応する SELECT
カラムのデータ タイプが一致しなければ、UNION
結果内のタイプとカラムの長さは、全ての SELECT
ステートメントによって検索された値を考慮する必要があります。例えば、次の物を検討してみてください。
mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a |
| bbbbbbbbbb |
+---------------+
(MySQL の初期のバージョンでは、最初の SELECT
のタイプと長さだけが利用され、2つ目の行は長さ1まで切り捨てられていました。)
SELECT
ステートメントは通常の選択ステートメントですが、次の制約があります。
最後の SELECT
ステートメントだけが INTO OUTFILE
を利用できます。
HIGH_PRIORITY
は、UNION
の一部である SELECT
ステートメントと一緒には利用できません。
もしそれを最初の SELECT
に指定しても、効果はありません。もしそれを後に続く SELECT
ステートメントに指定すると、構文エラーが起こります。
UNION
のデフォルトの動作は、複製行は結果から削除されるという事です。任意の DISTINCT
キーワードは、複製行の削除の指定もするので、デフォルト以外に何も効果は持ちません。任意の ALL
キーワードを利用すると、複製行の削除は行われず、結果には全ての SELECT
ステートメントからの一致する行が含まれます。
UNION ALL
と UNION DISTINCT
を同じクエリの中で混合する事ができます。混合された UNION
タイプは DISTINCT
ユニオンが全ての ALL
ユニオンをその左側に上乗せするような形で扱われます。DISTINCT
ユニオンは UNION DISTINCT
を利用して明示的に、また後に DISTINCT
や ALL
キーワードがない UNION
を利用して暗黙的に作成されます。
ORDER BY
や LIMIT
条項を、UNION
結果全体をソートしたり制限したりする為に利用するには、各 SELECT
ステートメントを括弧で囲み、最後の物の後に ORDER BY
か LIMIT
を置いて下さい。次の例は、両方の条項を利用しています。
(SELECT a FROM t1 WHERE a=10 AND B=1) UNION (SELECT a FROM t2 WHERE a=11 AND B=2) ORDER BY a LIMIT 10;
この種の ORDER BY
はテーブル名を含むカラム参照を利用する事ができません。(それは、tbl_name
.col_name
フォーマット内の名前です。)その代わりに、最初の SELECT
ステートメント内でカラム エイリアスを提供し、ORDER BY
内でそのエイリアスを参照します。(あるいは、そのカラムの位置を利用して ORDER BY
内でカラムを参照します。しかし、カラム位置の使用は今後廃止予定です。)
また、もし格納されるカラムがエイリアスされると、ORDER BY
条項は、カラム名ではなく、そのエイリアスを参照 しなければいけません。次のステートメントの1つ目の物は機能しますが、2つ目は Unknown column 'a' in 'order clause'
エラーで失敗します。
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b; (SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a;
個々の SELECT
に ORDER BY
か LIMIT
を適用するには、SELECT
を囲む括弧内に条項を置いて下さい。
(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
デフォルトの UNION
が不規則な順番の行セットを作り出す為、個々の SELECT
ステートメントへの ORDER BY
の利用は、最終結果の中で行がどのような順番で現れるのかを暗示しません。もし ORDER BY
が LIMIT
と共に現れると、検索の為に選択された行のサブセットを SELECT
に決定する為に利用されますが、それは最終的な UNION
の結果内の行の順番に影響を与えるとは限りません。もし ORDER BY
が SELECT
内に LIMIT
無しで現れても、何の効果も持たない為最適化されて切り離されます。
UNION
の結果内の行が、各 SELECT
によって1つずつ検索された行で構成されるようにする為には、各 SELECT
からソート カラムとして利用する為の追加カラムを選択し、最後の SELECT
の後に ORDER BY
を追加してください。
(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1) UNION (SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;
さらに、個々の SELECT
の結果の中でソートの順番を維持する為には、ORDER BY
条項に補助的なカラムを追加してください。
(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1) UNION (SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a;