最近在审核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 ,如需转载请自行联系原作者