LEFT JOINのONに書くかWHEREに書くかで結果セットが変わるやつのサンプル

TL;DR

• リテラルと比較する条件のこと
  • INNER JOINならONでもWHEREでもどっちでも同じ結果になる、マッチしない行は影も形も出てこない
  • OUTER JOINならONに書いた時は「それにマッチしなければNULL詰めの行が返る」、WHEREに書いた場合は「それにマッチしない行は表示されない(= INNER JOINと一緒)」
• LEFT JOINの左右どちらのテーブルでもこうなるのはちょっと面白い(?)
  • ゆっくり考えればわかるんだけど、2度見してしまった。。

---

CREATE TABLE t1 (num int);
INSERT INTO t1 VALUES (1), (2), (3);

-- INNER JOIN で WHERE
SELECT * FROM t1 JOIN t1 AS t2
ON t1.num = t2.num
WHERE t2.num IN (1, 3);
+------+------+
| num  | num  |
+------+------+
|    1 |    1 |
|    3 |    3 |
+------+------+
2 rows in set (0.00 sec)

-- INNER JOIN で ON
SELECT * FROM t1 JOIN t1 AS t2
ON t1.num = t2.num AND t2.num IN (1, 3);
+------+------+
| num  | num  |
+------+------+
|    1 |    1 |
|    3 |    3 |
+------+------+
2 rows in set (0.00 sec)

-- LEFT JOIN の右側で WHERE
SELECT * FROM t1 LEFT JOIN t1 AS t2
ON t1.num = t2.num
WHERE t2.num IN (1, 3);
+------+------+
| num  | num  |
+------+------+
|    1 |    1 |
|    3 |    3 |
+------+------+
2 rows in set (0.00 sec)

-- LEFT JOIN の右側で ON
SELECT * FROM t1 LEFT JOIN t1 AS t2
ON t1.num = t2.num AND t2.num IN (1, 3);
+------+------+
| num  | num  |
+------+------+
|    1 |    1 |
|    2 | NULL |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)

-- LEFT JOIN の左側で WHERE
SELECT * FROM t1 LEFT JOIN t1 AS t2
ON t1.num = t2.num
WHERE t1.num IN (1, 3);
+------+------+
| num  | num  |
+------+------+
|    1 |    1 |
|    3 |    3 |
+------+------+
2 rows in set (0.00 sec)

-- LEFT JOIN の左側で ON
SELECT * FROM t1 LEFT JOIN t1 AS t2
ON t1.num = t2.num AND t1.num IN (1, 3);
+------+------+
| num  | num  |
+------+------+
|    1 |    1 |
|    2 | NULL |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)