JOIN ON で絞り込み条件を入れるのと、JOIN ONの後WHERE句で絞り込み条件を入れるのとでは、結果が違う件
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」です。