最近在审核SQL的时候，发现一些开发经常犯这个错误，下面我举个简单的例子，说明一下：
查找出A表和B表id相同的记录（并且把B表没有的id记录也查询出来），且name名字等于'b'的记录。
表数据：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
mysql> select * from t1; +----+------+| id | name |+----+------+| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | b |
+----+------+6 rows in set (0.00 sec)
mysql> select * from t2; +----+------+| id | name |+----+------+| 1 | a |
| 2 | b |
| 3 | cc |
| 4 | e |
| 5 | b |
+----+------+5 rows in set (0.00 sec)
|
错误写法：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> select * from t1 left join t2 on t1.id=t2.id and t1.name='b';
+----+------+------+------+| id | name | id | name |+----+------+------+------+| 2 | b | 2 | b |
| 1 | a | NULL | NULL |
| 3 | c | NULL | NULL |
| 4 | d | NULL | NULL |
| 5 | e | NULL | NULL |
| 6 | b | NULL | NULL |
+----+------+------+------+6 rows in set (0.00 sec)
|
正确写法：
|
1
2
3
4
5
6
7
8
|
mysql> select * from t1 left join t2 on t1.id=t2.id where t1.name='b';
+----+------+------+------+| id | name | id | name |+----+------+------+------+| 2 | b | 2 | b |
| 6 | b | NULL | NULL |
+----+------+------+------+2 rows in set (0.00 sec)
|
呵呵，可以当一道面试题了。
本文转自hcymysql51CTO博客，原文链接：http://blog.51cto.com/hcymysql/1363428 ，如需转载请自行联系原作者