MySQLに限りませんが、JOINでCASEを使うことで、結合条件を変えることができます。
駆動表（外部表）または内部表のいずれかが**「複数のサブタイプをまとめたテーブル」**である場合にそのようなことをしたくなるのだと思いますが、このとき、**結合処理にINDEXは使われるのか？**というのが今回のテーマです。
※MySQL 5.7.21で検証しました。
駆動表（外部表）にtype列がある（＝複数のサブタイプを持つ）ケース
以下のようなテーブルを作成します。
※カラムは可能な限り削ってあります。
駆動表にサブタイプ
mysql> CREATE DATABASE join_case_test;
Query OK, 1 row affected (0.00 sec)
mysql> USE join_case_test;
Database changed
mysql> CREATE TABLE join_outer (id int unsigned NOT NULL AUTO_INCREMENT, type int unsigned NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE join_inner (id int unsigned NOT NULL AUTO_INCREMENT, key1 int unsigned NOT NULL, key2 int unsigned NOT NULL, PRIMARY KEY (id), INDEX (key1), INDEX (key2)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)
join_outerテーブルにデータを入れるSQL（10,000行分）
SET AUTOCOMMIT=0;
INSERT INTO join_case_test.join_outer SET type = FLOOR(RAND() * 2 + 1);
INSERT INTO join_case_test.join_outer SET type = FLOOR(RAND() * 2 + 1);
INSERT INTO join_case_test.join_outer SET type = FLOOR(RAND() * 2 + 1);
（中略）
INSERT INTO join_case_test.join_outer SET type = FLOOR(RAND() * 2 + 1);
COMMIT;
join_innerテーブルにデータを入れるSQL（10,000行分）
SET AUTOCOMMIT=0;
INSERT INTO join_case_test.join_inner SET key1 = FLOOR(RAND() * 10000 + 1), key2 = FLOOR(RAND() * 10000 + 1);
INSERT INTO join_case_test.join_inner SET key1 = FLOOR(RAND() * 10000 + 1), key2 = FLOOR(RAND() * 10000 + 1);
INSERT INTO join_case_test.join_inner SET key1 = FLOOR(RAND() * 10000 + 1), key2 = FLOOR(RAND() * 10000 + 1);
（中略）
INSERT INTO join_case_test.join_inner SET key1 = FLOOR(RAND() * 10000 + 1), key2 = FLOOR(RAND() * 10000 + 1);
COMMIT;
テーブルデータ確認
mysql> SELECT * FROM join_outer LIMIT 10;
+----+------+
| id | type |
+----+------+
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
| 6 | 1 |
| 7 | 1 |
| 8 | 2 |
| 9 | 2 |
| 10 | 1 |
+----+------+
10 rows in set (0.00 sec)
mysql> SELECT * FROM join_inner LIMIT 10;
+----+------+------+
| id | key1 | key2 |
+----+------+------+
| 1 | 7042 | 9458 |
| 2 | 6162 | 2437 |
| 3 | 3699 | 1185 |
| 4 | 4825 | 570 |
| 5 | 8373 | 155 |
| 6 | 5657 | 7818 |
| 7 | 2118 | 7137 |
| 8 | 9331 | 5241 |
| 9 | 8213 | 5339 |
| 10 | 2058 | 4273 |
+----+------+------+
10 rows in set (0.00 sec)
EXPLAINで確認してみます。
まずは、通常のJOINから。
EXPLAIN確認／通常JOIN
mysql> EXPLAIN SELECT * FROM join_outer o INNER JOIN join_inner i ON o.id = i.key1 WHERE o.id BETWEEN 1 AND 100;
+----+-------------+-------+------------+-------+---------------+---------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+---------------------+------+----------+-------------+
| 1 | SIMPLE | o | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 100 | 100.00 | Using where |
| 1 | SIMPLE | i | NULL | ref | key1 | key1 | 4 | join_case_test.o.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+---------------------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)
mysql> EXPLAIN SELECT * FROM join_outer o INNER JOIN join_inner i ON o.id = i.key2 WHERE o.id BETWEEN 1 AND 100;
+----+-------------+-------+------------+-------+---------------+---------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+---------------------+------+----------+-------------+
| 1 | SIMPLE | o | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 100 | 100.00 | Using where |
| 1 | SIMPLE | i | NULL | ref | key2 | key2 | 4 | join_case_test.o.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+---------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
INDEXは効いています。
続いて、CASEでtypeによる条件分岐を行ってみます。
EXPLAIN確認／JOINでCASE
mysql> EXPLAIN SELECT * FROM join_outer o INNER JOIN join_inner i ON o.id = (CASE WHEN o.type = 1 THEN i.key1 ELSE i.key2 END) WHERE o.id BETWEEN 1 AND 100;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
| 1 | SIMPLE | o | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 100 | 100.00 | Using where |
| 1 | SIMPLE | i | NULL | ALL | NULL | NULL | NULL | NULL | 10195 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM join_outer o, join_inner i WHERE o.id = (CASE WHEN o.type = 1 THEN i.key1 ELSE i.key2 END) AND o.id BETWEEN 1 AND 100;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
| 1 | SIMPLE | o | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 100 | 100.00 | Using where |
| 1 | SIMPLE | i | NULL | ALL | NULL | NULL | NULL | NULL | 10195 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM join_outer o LEFT JOIN join_inner i ON o.id = (CASE WHEN o.type = 1 THEN i.key1 ELSE i.key2 END) WHERE o.id BETWEEN 1 AND 100;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
| 1 | SIMPLE | o | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 100 | 100.00 | Using where |
| 1 | SIMPLE | i | NULL | ALL | NULL | NULL | NULL | NULL | 10195 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
INNER JOINと、それをJOINを使わない等結合に書き換えたもの、LEFT JOINのいずれも、内部表のINDEXは使われません。
内部表にtype列がある（＝複数のサブタイプを持つ）ケース
今度は、サブタイプを持つ内部表を作成します。
内部表にサブタイプ
mysql> CREATE TABLE join_inner2 (id int unsigned NOT NULL AUTO_INCREMENT, type int unsigned NOT NULL, key1 int unsigned NOT NULL, key2 int unsigned NOT NULL, PRIMARY KEY (id), INDEX (key1), INDEX (key2)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
join_inner2テーブルにデータを入れるSQL（10,000行分）
SET AUTOCOMMIT=0;
INSERT INTO join_case_test.join_inner2 SET type = FLOOR(RAND() * 2 + 1), key1 = FLOOR(RAND() * 10000 + 1), key2 = FLOOR(RAND() * 10000 + 1);
INSERT INTO join_case_test.join_inner2 SET type = FLOOR(RAND() * 2 + 1), key1 = FLOOR(RAND() * 10000 + 1), key2 = FLOOR(RAND() * 10000 + 1);
INSERT INTO join_case_test.join_inner2 SET type = FLOOR(RAND() * 2 + 1), key1 = FLOOR(RAND() * 10000 + 1), key2 = FLOOR(RAND() * 10000 + 1);
（中略）
INSERT INTO join_case_test.join_inner2 SET type = FLOOR(RAND() * 2 + 1), key1 = FLOOR(RAND() * 10000 + 1), key2 = FLOOR(RAND() * 10000 + 1);
COMMIT;
テーブルデータ確認
mysql> SELECT * FROM join_inner2 LIMIT 10;
+----+------+------+------+
| id | type | key1 | key2 |
+----+------+------+------+
| 1 | 1 | 8481 | 2557 |
| 2 | 2 | 9025 | 3108 |
| 3 | 2 | 3004 | 9621 |
| 4 | 2 | 6608 | 5755 |
| 5 | 2 | 7492 | 605 |
| 6 | 1 | 923 | 2973 |
| 7 | 1 | 1556 | 1494 |
| 8 | 1 | 9521 | 9203 |
| 9 | 2 | 9650 | 5895 |
| 10 | 1 | 4940 | 3122 |
+----+------+------+------+
10 rows in set (0.00 sec)
EXPLAINで確認してみます。
EXPLAIN確認／JOINでCASEその2
mysql> EXPLAIN SELECT * FROM join_outer o INNER JOIN join_inner2 i ON o.id = (CASE WHEN i.type = 1 THEN i.key1 ELSE i.key2 END) WHERE o.id BETWEEN 1 AND 100;
+----+-------------+-------+------------+--------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | i | NULL | ALL | NULL | NULL | NULL | NULL | 10294 | 100.00 | NULL |
| 1 | SIMPLE | o | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM join_outer o LEFT JOIN join_inner2 i ON o.id = (CASE WHEN i.type = 1 THEN i.key1 ELSE i.key2 END) WHERE o.id BETWEEN 1 AND 100;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
| 1 | SIMPLE | o | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 100 | 100.00 | Using where |
| 1 | SIMPLE | i | NULL | ALL | NULL | NULL | NULL | NULL | 10294 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
INNER JOINの場合は駆動表と内部表が入れ替わりましたが、入れ替わった後の駆動表（元の内部表）はINDEXが効いていません。
LEFT JOINの場合は先のケース（駆動表にサブタイプ）と同じで、やはり内部表のINDEXが効いていません。
※USE INDEXやFORCE INDEXを指定しても変化はありませんでした。
まとめ
クエリとしては正しく結果の抽出ができるのですが、INDEXが効果的に使われないため、データが増えたときにパフォーマンスの悪化が予想されます。
また、それを避けるために「同一テーブルからtype毎にSELECTしたものをUNION」という、見るからに効率の悪いクエリを書くことになったりもします（INDEXまたは複合INDEXにtype列を加えないといけなくなることも）。
そもそも、このようなクエリを書く以前に、テーブル設計の段階で**「本当に1つのテーブルに複数のサブタイプをまとめて実装すべきか」**をよく検討したほうが良いでしょう。