left join 条件区别

t1:

num | name
-----+------
1 | a
2 | b
3 | c

t2:

num | value
-----+-------
1 　 | xxx
3 　 | yyy
5 　 | zzz

1. SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';

2. SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';

这两种写法结果是会不一样的：

1. SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx'; =>

num | name | num | value
-----+------+-----+-------
1 | a | 1 　　| xxx
2 | b | 　　　|
3 | c | 　　 |

2. SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';

num | name | num | value
-----+------+-----+-------
1 　 | a | 1 | xxx

解释：(这是因为在ON子句连接之前处理，而WHERE子句在连接之后处理。)

我们先看第二种：2. SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';

这个可以分两步：

1）SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num => 假设生成了一个新的虚拟表：t12

num | name | num | value
-----+------+-----+-------
1 　 | a | 1 | xxx
2 | b | 　 |
3 | c | 3 | yyy

2) SELECT * FROM t12 WHERE t12.value = 'xxx';

num | name | num | value
-----+------+-----+-------
1 　 | a | 1 | xxx

再看第一种：1. SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';

这里已经明确规定从t2表选择的数据中value要等于'xxx'的，这是t2只有一条符合，所以这返回一条，其余的用NULL不全到LEFT JOIN 的t1表

num | name | num | value
-----+------+-----+-------
1 | a | 1 　　| xxx
2 | b | 　　　|
3 | c | 　　 |

巴特西