なからなLife

geekに憧れと敬意を抱きながら、SE、ITコンサル、商品企画、事業企画、管理会計、総務・情シス、再び受託でDB屋さんと流浪する人のブログです。

JOIN ON で絞り込み条件を入れるのと、JOIN ONの後WHERE句で絞り込み条件を入れるのとでは、結果が違う件

MySQL

MySQL Casual Advent Calendar 2016 - Qiita 12日目の記事です。


まさかの3回目。もうムリ。。。

JOIN ON句で結合条件ではなく絞込条件を書くことができることを、知らなかったのです。

具体的な名称がわからない＆検索にもヒットしにくいので、勝手に名前をつけました。


「JOINに直値」


わかりにくいと思うので、サンプルSQL書きます。

SELECT tbl_a.col1,tbl_b.col1
FROM tbl_a 
INNER JOIN tbl_b
ON  tbl_a.col1 = tbl_b.col1
AND tbl_a.col2 = 0;

JOINでは一般的に「表A JOIN 表B... ON 表A.列 = 表B.列」と書いて表同士を結合するための条件を書きますが、ここに、一方の表の絞り込み条件を書けるんです。


SQLの教科書的な書籍ではまず見かけませんし、個人的にも、自分で書こうと思ったこともなければ、周りにこういう書き方する人もいないまま十数年。


ところが、この1年で関わったMySQL案件で、この「JOINに直値」を書く人を結構な率で見かけるようになりまして。



で、最近たまによくお世話になっているMySQL-CasualのSlackにつぶやいてみたところ、「たまによくある」というレスをいくつか頂きまして。


そんなわけで、検証してみました。

実験

準備

かなり端折りますが、こんな感じでテーブルを用意します。
面倒くさいので、テーブルAとBは中身は一緒、col1列は1から始まる連番です。
col2の値には、col1の連番の値が偶数ならば0、奇数ならば1が入っています。


これを各テーブル1,000件入れてあります。サンプルとして10件ずつ表示しておきます。

mysql> show create table tbl_a\G
*************************** 1. row ***************************
       Table: tbl_a
Create Table: CREATE TABLE `tbl_a` (
  `col1` int(11) NOT NULL DEFAULT '0',
  `col2` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> show create table tbl_b\G
*************************** 1. row ***************************
       Table: tbl_b
Create Table: CREATE TABLE `tbl_b` (
  `col1` int(11) NOT NULL DEFAULT '0',
  `col2` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM tbl_a;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tbl_a order by col1 limit 10;
+------+------+
| col1 | col2 |
+------+------+
|    1 |    1 |
|    2 |    0 |
|    3 |    1 |
|    4 |    0 |
|    5 |    1 |
|    6 |    0 |
|    7 |    1 |
|    8 |    0 |
|    9 |    1 |
|   10 |    0 |
+------+------+
10 rows in set (0.00 sec)

mysql> SELECT count(*) FROM tbl_b;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tbl_b order by col1 limit 10;
+------+------+
| col1 | col2 |
+------+------+
|    1 |    1 |
|    2 |    0 |
|    3 |    1 |
|    4 |    0 |
|    5 |    1 |
|    6 |    0 |
|    7 |    1 |
|    8 |    0 |
|    9 |    1 |
|   10 |    0 |
+------+------+
10 rows in set (0.00 sec)
実験パターンと結果

「tbl_a.col1 = tbl_b.col1」で結合するとして、col2をどこで絞るかのパターンでチェックしたところ、以下のようになりました。

JOIN 絞込条件 WHEREに絞込条件 戻り件数 JOIN句に絞込条件 戻り件数
INNER JOIN tbl_a.col2 = 0 (1) 500 (2) 500
INNER JOIN tbl_b.col2 = 0 (3) 500 (4) 500
LEFT OUTER JOIN tbl_a.col2 = 0 (5) 500 (6) 1,000
LEFT OUTER JOIN tbl_b.col2 = 0 (7) 500 (8) 1,000


エビデンスは長くなるので、結果の要点を先に書きます。

  • INNER JOINでは、得られる結果に違いはないが、OUTER JOINでは結果が変わってしまう！
  • JOIN ONで絞り込み条件を書くと、「結果を絞るのではなく、結合前のテーブルのレコードを絞る」らしい。


まずわかりやすい所では、(7)と(8)の違いに要注意です。

SELECT tbl_a.col1,tbl_b.col1
FROM tbl_a
LEFT OUTER JOIN tbl_b
ON tbl_a.col1 = tbl_b.col1
WHERE tbl_b.col2 = 0;  --(7)

SELECT tbl_a.col1,tbl_b.col1
FROM tbl_a
LEFT OUTER JOIN tbl_b
ON  tbl_a.col1 = tbl_b.col1
AND tbl_b.col2 = 0;  -- (8)


(7)は、OUTER JOINした結果に対し、WHEREで絞り込むので、結果は500件（col2は0と1が半分ずつ持っているため）になります。


しかし(8)は、OUTER JOINのところで条件を入れていますが、WHEREでは絞り込み条件を入れていません。


よって、tbl_aの1,000件とtbl_bの500件のOUTER JOINになり、tbl_a.col1≠tbl_b.col1の行も抽出対象になって、1,000件が戻ってきます。





次に、(5)と(6)を見てみましょう。

SELECT tbl_a.col1,tbl_b.col1
FROM tbl_a
LEFT OUTER JOIN tbl_b
ON tbl_a.col1 = tbl_b.col1
WHERE tbl_a.col2 = 0; --(5)

SELECT tbl_a.col1,tbl_b.col1
FROM tbl_a
LEFT OUTER JOIN tbl_b
ON  tbl_a.col1 = tbl_b.col1
AND tbl_a.col2 = 0; --(6)


(7)と(8)との違いは、右表にあたるtbl_bのcol2を絞り込み条件に指定している(7)(8)に対し、左表tbl_a側のcol2を使っている点です。


先ほどの理屈で行くと、JOINに条件を加えると、結合前にtbl_aを半分に絞ってから結合するので、WHEREで絞った結果と同じ500件が返ってきそうに思います。


ですが、、、JOINで条件を書く(6)では、1,000件返ってきます。。。


ソースにあたってないので推測ですが、Nested Loopの中で、
「tbl_a.col2 = 0 なtbl_aのレコードセットを取得し、それをループさせながらtbl_b.col_1 = tbl_a.col1なレコードを検索しにいく」
ではなく、
「tbl_aは戻り値の母体として全部ループを回しつつ、tbl_a.col1 = 0の時だけ、tbl_b.col_1 = tbl_a.col1なレコードを検索しにいく」
って感じなんだと思います。



どっちが正しいとかではなく、求めている結果に応じて使い分けてね、と。



INNER JOINでもこの構文を使い分けている方がいるようですが、実験結果や実行計画からは、明確な差が見いだせませんでした。


上記の推測が正しいとすれば、左表のループの中で右表を検索しに行くロジックに入るか入らないかの差で、JOINで絞った方が少し軽くなるんじゃないかな、などと思うわけですが、試しに1,000万件で(1)(2)のINNER JOINで比較した感じ、全然違いがありませんでした。。。


実行計画をみると、8パターンすべてにおいて、左表は「rows = 1000」、右表は「rows = 1」になっています。
これも正直良くわかってません。


PRIMARYのインデックスしか用意しませんでしたが、これががセカンダリ使用の実験をしたら、少し変わるんでしょうか。



なおこれ、MySQL以外でも書ける構文ですが、Oracle・PostgreSQLでは試してません。
実験したのは「MySQL 5.6.34」です。

実験エビデンス

なお、実行計画はexplain extendedで取得しておきましたので、MySQLがどう変換しているかもご確認いただけます。
なにか気づくところがありましたら教えてください。
（急に語調が丁寧）


以下エビデンスです。


(1)INNER JOIN でtbl_a.col2をWHERE で絞込

mysql> SELECT count(*) FROM tbl_a INNER JOIN tbl_b ON tbl_a.col1 = tbl_b.col1 WHERE tbl_a.col2 = 0;
+----------+
| count(*) |
+----------+
|      500 |
+----------+
1 row in set (0.00 sec)

mysql> explain extended SELECT tbl_a.col1,tbl_b.col1 FROM tbl_a INNER JOIN tbl_b ON tbl_a.col1 = tbl_b.col1 WHERE tbl_a.col2 = 0\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_a
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_b
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test_join.tbl_a.col1
         rows: 1
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test_join`.`tbl_a`.`col1` AS `col1`,`test_join`.`tbl_b`.`col1` AS `col1` from `test_join`.`tbl_a` join `test_join`.`tbl_b` where ((`test_join`.`tbl_b`.`col1` = `test_join`.`tbl_a`.`col1`) and (`test_join`.`tbl_a`.`col2` = 0))
1 row in set (0.00 sec)


(2)INNER JOIN でtbl_a.col2をJOIN ON で絞込

mysql> SELECT count(*) FROM tbl_a INNER JOIN tbl_b ON tbl_a.col1 = tbl_b.col1 AND tbl_a.col2 = 0;
+----------+
| count(*) |
+----------+
|      500 |
+----------+
1 row in set (0.01 sec)

mysql> explain extended SELECT tbl_a.col1,tbl_b.col1 FROM tbl_a INNER JOIN tbl_b ON tbl_a.col1 = tbl_b.col1 AND tbl_a.col2 = 0\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_a
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_b
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test_join.tbl_a.col1
         rows: 1
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test_join`.`tbl_a`.`col1` AS `col1`,`test_join`.`tbl_b`.`col1` AS `col1` from `test_join`.`tbl_a` join `test_join`.`tbl_b` where ((`test_join`.`tbl_a`.`col2` = 0) and (`test_join`.`tbl_b`.`col1` = `test_join`.`tbl_a`.`col1`))
1 row in set (0.00 sec)


(3)INNER JOIN でtbl_b.col2をWHERE で絞込

mysql> SELECT count(*) FROM tbl_a INNER JOIN tbl_b ON tbl_a.col1 = tbl_b.col1 WHERE tbl_b.col2 = 0;
+----------+
| count(*) |
+----------+
|      500 |
+----------+
1 row in set (0.01 sec)

mysql> explain extended SELECT tbl_a.col1,tbl_b.col1 FROM tbl_a INNER JOIN tbl_b ON tbl_a.col1 = tbl_b.col1 WHERE tbl_b.col2 = 0\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_a
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_b
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test_join.tbl_a.col1
         rows: 1
     filtered: 100.00
        Extra: Using where
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test_join`.`tbl_a`.`col1` AS `col1`,`test_join`.`tbl_b`.`col1` AS `col1` from `test_join`.`tbl_a` join `test_join`.`tbl_b` where ((`test_join`.`tbl_b`.`col1` = `test_join`.`tbl_a`.`col1`) and (`test_join`.`tbl_b`.`col2` = 0))
1 row in set (0.00 sec)


(4)INNER JOIN でtbl_b.col2をJOIN ON で絞込

mysql> SELECT count(*) FROM tbl_a INNER JOIN tbl_b ON tbl_a.col1 = tbl_b.col1 AND tbl_b.col2 = 0;
+----------+
| count(*) |
+----------+
|      500 |
+----------+
1 row in set (0.00 sec)

mysql> explain extended SELECT tbl_a.col1,tbl_b.col1 FROM tbl_a INNER JOIN tbl_b ON tbl_a.col1 = tbl_b.col1 AND tbl_b.col2 = 0\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_a
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_b
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test_join.tbl_a.col1
         rows: 1
     filtered: 100.00
        Extra: Using where
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test_join`.`tbl_a`.`col1` AS `col1`,`test_join`.`tbl_b`.`col1` AS `col1` from `test_join`.`tbl_a` join `test_join`.`tbl_b` where ((`test_join`.`tbl_b`.`col2` = 0) and (`test_join`.`tbl_b`.`col1` = `test_join`.`tbl_a`.`col1`))
1 row in set (0.00 sec)


(5)OUTER JOIN でtbl_a.col2をWHERE で絞込

mysql> SELECT count(*) FROM tbl_a LEFT OUTER JOIN tbl_b ON tbl_a.col1 = tbl_b.col1 WHERE tbl_a.col2 = 0;
+----------+
| count(*) |
+----------+
|      500 |
+----------+
1 row in set (0.00 sec)

mysql> explain extended SELECT tbl_a.col1,tbl_b.col1 FROM tbl_a LEFT OUTER JOIN tbl_b ON tbl_a.col1 = tbl_b.col1 WHERE tbl_a.col2 = 0\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_a
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_b
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test_join.tbl_a.col1
         rows: 1
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test_join`.`tbl_a`.`col1` AS `col1`,`test_join`.`tbl_b`.`col1` AS `col1` from `test_join`.`tbl_a` left join `test_join`.`tbl_b` on((`test_join`.`tbl_b`.`col1` = `test_join`.`tbl_a`.`col1`)) where (`test_join`.`tbl_a`.`col2` = 0)
1 row in set (0.01 sec)


(6)OUTER JOIN でtbl_a.col2をJOIN ON で絞込

mysql> SELECT count(*) FROM tbl_a LEFT OUTER JOIN tbl_b ON tbl_a.col1 = tbl_b.col1 AND tbl_a.col2 = 0;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> explain extended SELECT tbl_a.col1,tbl_b.col1 FROM tbl_a LEFT OUTER JOIN tbl_b ON tbl_a.col1 = tbl_b.col1 AND tbl_a.col2 = 0\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_a
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_b
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test_join.tbl_a.col1
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test_join`.`tbl_a`.`col1` AS `col1`,`test_join`.`tbl_b`.`col1` AS `col1` from `test_join`.`tbl_a` left join `test_join`.`tbl_b` on(((`test_join`.`tbl_a`.`col1` = `test_join`.`tbl_b`.`col1`) and (`test_join`.`tbl_a`.`col2` = 0))) where 1
1 row in set (0.00 sec)


(7)OUTER JOIN でtbl_b.col2をWHERE で絞込

mysql> SELECT count(*) FROM tbl_a LEFT OUTER JOIN tbl_b ON tbl_a.col1 = tbl_b.col1 WHERE tbl_b.col2 = 0;
+----------+
| count(*) |
+----------+
|      500 |
+----------+
1 row in set (0.00 sec)

mysql> explain extended SELECT tbl_a.col1,tbl_b.col1 FROM tbl_a LEFT OUTER JOIN tbl_b ON tbl_a.col1 = tbl_b.col1 WHERE tbl_b.col2 = 0\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_a
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_b
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test_join.tbl_a.col1
         rows: 1
     filtered: 100.00
        Extra: Using where
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test_join`.`tbl_a`.`col1` AS `col1`,`test_join`.`tbl_b`.`col1` AS `col1` from `test_join`.`tbl_a` join `test_join`.`tbl_b` where ((`test_join`.`tbl_b`.`col1` = `test_join`.`tbl_a`.`col1`) and (`test_join`.`tbl_b`.`col2` = 0))
1 row in set (0.00 sec)


(8)OUTER JOIN でtbl_b.col2をJOIN ON で絞込

mysql> SELECT count(*) FROM tbl_a LEFT OUTER JOIN tbl_b ON tbl_a.col1 = tbl_b.col1 AND tbl_b.col2 = 0;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> explain extended SELECT tbl_a.col1,tbl_b.col1 FROM tbl_a LEFT OUTER JOIN tbl_b ON tbl_a.col1 = tbl_b.col1 AND tbl_b.col2 = 0\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_a
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_b
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test_join.tbl_a.col1
         rows: 1
     filtered: 100.00
        Extra: Using where
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test_join`.`tbl_a`.`col1` AS `col1`,`test_join`.`tbl_b`.`col1` AS `col1` from `test_join`.`tbl_a` left join `test_join`.`tbl_b` on(((`test_join`.`tbl_a`.`col1` = `test_join`.`tbl_b`.`col1`) and (`test_join`.`tbl_b`.`col2` = 0))) where 1
1 row in set (0.00 sec)