from多张表和left join

发表于 | 分类于 
1
2
3
4
5
6
select * from a, b where a.id = 1 and a.id = b.id
隐式内连接，只有匹配的行


select * from a left join b on a.id=b.id where a.id = 1
以A表所有记录为基础，连接A、B两表，条件是a.id=b.id where a.id = 1，没有满足的条件B表内容为NULL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
mysql> select * from a;
+------+------+
| id   | col  |
+------+------+
|    1 |   11 |
|    2 |   12 |
|    3 |   13 |
+------+------+
3 rows in set (0.00 sec)
 
mysql> select * from b;
+------+------+
| id   | col  |
+------+------+
|    2 |   22 |
|    3 |   23 |
|    5 |   25 |
+------+------+
3 rows in set (0.00 sec)
 
mysql>
mysql> select * from a,b where a.id=b.id;
+------+------+------+------+
| id   | col  | id   | col  |
+------+------+------+------+
|    2 |   12 |    2 |   22 |
|    3 |   13 |    3 |   23 |
+------+------+------+------+
2 rows in set (0.08 sec)
 
mysql> select * from a left join b on a.id=b.id;
+------+------+------+------+
| id   | col  | id   | col  |
+------+------+------+------+
|    1 |   11 | NULL | NULL |
|    2 |   12 |    2 |   22 |
|    3 |   13 |    3 |   23 |
+------+------+------+------+
3 rows in set (0.00 sec)