MySQL 5.6 Developer試験対策 4 結合、副問合せおよびUNION
MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com
MySQL 5.6 リファレンスマニュアル
- MySQLのコマンドにおけるJOINを識別し、説明する。MySQLのコマンドにおけるJOINを使用する
- MySQLのコマンドにおける副問合せについて説明する。副問合せを使用する
- UNION句を使用して操作を実行する
MySQLのコマンドにおけるJOINを識別し、説明する。MySQLのコマンドにおけるJOINを使用する
\h join Name: 'JOIN' Description: MySQL supports the following JOIN syntax for the table_references part of SELECT statements and multiple-table DELETE and UPDATE statements: table_references: escaped_table_reference [, escaped_table_reference] ... escaped_table_reference: table_reference | { OJ table_reference } table_reference: table_factor | joined_table table_factor: tbl_name [PARTITION (partition_names)] [[AS] alias] [index_hint_list] | table_subquery [AS] alias | ( table_references ) joined_table: table_reference [INNER | CROSS] JOIN table_factor [join_specification] | table_reference STRAIGHT_JOIN table_factor | table_reference STRAIGHT_JOIN table_factor ON search_condition | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor join_specification: ON search_condition | USING (join_column_list) join_column_list: column_name [, column_name] ... index_hint_list: index_hint [, index_hint] ... index_hint: USE {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list]) | {IGNORE|FORCE} {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] (index_list) index_list: index_name [, index_name] ... A table reference is also known as a join expression. A table reference (when it refers to a partitioned table) may contain a PARTITION option, including a list of comma-separated partitions, subpartitions, or both. This option follows the name of the table and precedes any alias declaration. The effect of this option is that rows are selected only from the listed partitions or subpartitions. Any partitions or subpartitions not named in the list are ignored. For more information and examples, see https://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html. The syntax of table_factor is extended in MySQL in comparison with standard SQL. The standard accepts only table_reference, not a list of them inside a pair of parentheses. This is a conservative extension if each comma in a list of table_reference items is considered as equivalent to an inner join. For example: SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c) is equivalent to: 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) In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise. In general, parentheses can be ignored in join expressions containing only inner join operations. MySQL also supports nested joins. See https://dev.mysql.com/doc/refman/5.6/en/nested-join-optimization.html. Index hints can be specified to affect how the MySQL optimizer makes use of indexes. For more information, see https://dev.mysql.com/doc/refman/5.6/en/index-hints.html. The optimizer_switch system variable is another way to influence optimizer use of indexes. See https://dev.mysql.com/doc/refman/5.6/en/switchable-optimizations.html. URL: https://dev.mysql.com/doc/refman/5.6/en/join.html Examples: SELECT left_tbl.* FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id WHERE right_tbl.id IS NULL;
- インデックスヒントを指定可能
- 【所感】完全なコストベースじゃないんですね
Index hints can be specified to affect how the MySQL optimizer makes use of indexes. For more information, see https://dev.mysql.com/doc/refman/5.6/en/index-hints.html. The optimizer_switch system variable is another way to influence optimizer use of indexes. See https://dev.mysql.com/doc/refman/5.6/en/switchable-optimizations.html.
- MySQLにおいてJOIN系は全部
syntactic equivalents
In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.
SELECT * FROM tbl; +------+------+ | id | col | +------+------+ | 1 | 1 | | 2 | 1 | | 2 | 2 | +------+------+ 3 rows in set (0.00 sec)
- MySQLだと
CROSS JOIN
でONを使えたりする
SELECT * FROM tbl a CROSS JOIN tbl b ON a.id=b.col; +------+------+------+------+ | id | col | id | col | +------+------+------+------+ | 1 | 1 | 1 | 1 | | 1 | 1 | 2 | 1 | | 2 | 1 | 2 | 2 | | 2 | 2 | 2 | 2 | +------+------+------+------+ 4 rows in set (0.00 sec) SELECT * FROM tbl a CROSS JOIN tbl b WHERE a.id=b.col; +------+------+------+------+ | id | col | id | col | +------+------+------+------+ | 1 | 1 | 1 | 1 | | 1 | 1 | 2 | 1 | | 2 | 1 | 2 | 2 | | 2 | 2 | 2 | 2 | +------+------+------+------+ 4 rows in set (0.01 sec)
- INNER JOINでON句を指定しなければ単に直積演算になったりする
SELECT * FROM tbl a INNER JOIN tbl b; +------+------+------+------+ | id | col | id | col | +------+------+------+------+ | 1 | 1 | 1 | 1 | | 2 | 1 | 1 | 1 | | 2 | 2 | 1 | 1 | | 1 | 1 | 2 | 1 | | 2 | 1 | 2 | 1 | | 2 | 2 | 2 | 1 | | 1 | 1 | 2 | 2 | | 2 | 1 | 2 | 2 | | 2 | 2 | 2 | 2 | +------+------+------+------+ 9 rows in set (0.00 sec)
STRAIGHT_JOIN
- 常に左側のリレーションが先に読み込まれるINNER JOIN
MySQLのコマンドにおける副問合せについて説明する。副問合せを使用する
[https://dev.mysql.com/doc/refman/5.6/ja/subqueries.html
スカラサブクエリ
サブクエリの評価結果が1行1列の場合、それはスカラサブクエリ
SELECT (SELECT 1); +------------+ | (SELECT 1) | +------------+ | 1 | +------------+ 1 row in set (0.00 sec)
1列じゃないとだめ
SELECT (SELECT 1,2); ERROR 1241 (21000): Operand should contain 1 column(s)
1行でないのもだめ
SELECT(SELECT 1 UNION SELECT 1); +---------------------------+ | (SELECT 1 UNION SELECT 1) | +---------------------------+ | 1 | +---------------------------+ 1 row in set (0.00 sec) SELECT(SELECT 1 UNION ALL SELECT 1); ERROR 1242 (21000): Subquery returns more than 1 row
サブクエリを使用した比較
SELECT * FROM tbl; +------+ | col | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+ 5 rows in set (0.00 sec)
- スカラサブクエリ
SELECT col FROM tbl WHERE col=(SELECT 1); +------+ | col | +------+ | 1 | +------+ 1 row in set (0.00 sec)
- スカラじゃないサブクエリ
- 複数行
ANY
SELECT col FROM tbl WHERE col=ANY(SELECT 1 UNION SELECT 2); +------+ | col | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec)
IN
SELECT col FROM tbl WHERE col IN (SELECT 1 UNION SELECT 2); +------+ | col | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec)
- 行サブクエリ
- 1行のやつ
SELECT a.col,b.col FROM tbl a,tbl b WHERE (a.col,b.col)=(SELECT 2,3); +------+------+ | col | col | +------+------+ | 2 | 3 | +------+------+ 1 row in set (0.00 sec)
SOME
とANY
はおなじNOT IN
は<> ALL
のエイリアス- ド・モルガン的な
相関サブクエリ
- 相関サブクエリは結合で書き換えると速くなることがある
UNION句を使用して操作を実行する
- 和集合演算
- MySQLに差集合演算はない。ざんねん