1.join,left jioin, right join, inner join,full join区别
join的默认方法为inner join
关于inner join, left join, right join, full join的区别举个例子就清楚了
首先有两个表a和b
select * from a;
+----+-----------+
| ID | aNum |
+----+-----------+
| 1 | a20050111 |
| 2 | a20050112 |
| 3 | a20050113 |
| 4 | a20050114 |
| 5 | a20050115 |
+----+-----------+
select * from b;
+----+-------------+
| ID | bName |
+----+-------------+
| 1 | 2006032401 |
| 2 | 2006032402 |
| 3 | 2006032403 |
| 4 | 2006032404 |
| 8 | 2006032408 |
+----+-------------+
left join的情况
select * from a left join b on a.ID=b.ID;
+----+-----------+------+-------------+
| ID | aNum | ID | bName |
+----+-----------+------+-------------+
| 1 | a20050111 | 1 | 2006032401 |
| 2 | a20050112 | 2 | 2006032402 |
| 3 | a20050113 | 3 | 2006032403 |
| 4 | a20050114 | 4 | 2006032404 |
| 5 | a20050115 | NULL | NULL |
+----+-----------+------+-------------+
right join的情况
select * from a right join b on a.ID=b.ID;
+------+-----------+----+-------------+
| ID | aNum | ID | bName |
+------+-----------+----+-------------+
| 1 | a20050111 | 1 | 2006032401 |
| 2 | a20050112 | 2 | 2006032402 |
| 3 | a20050113 | 3 | 2006032403 |
| 4 | a20050114 | 4 | 2006032404 |
| NULL | NULL | 8 | 2006032408 |
+------+-----------+----+-------------+
join(inner join)的情况
+----+-----------+----+-------------+
| ID | aNum | ID | bName |
+----+-----------+----+-------------+
| 1 | a20050111 | 1 | 2006032401 |
| 2 | a20050112 | 2 | 2006032402 |
| 3 | a20050113 | 3 | 2006032403 |
| 4 | a20050114 | 4 | 2006032404 |
+----+-----------+----+-------------+
full join(因为 版本不支持full join,使用union实现了full join操作)
select * from a left join b on a.ID=b.ID union select * from a right join b on a.ID=b.ID;
+------+-----------+------+-------------+
| ID | aNum | ID | bName |
+------+-----------+------+-------------+
| 1 | a20050111 | 1 | 2006032401 |
| 2 | a20050112 | 2 | 2006032402 |
| 3 | a20050113 | 3 | 2006032403 |
| 4 | a20050114 | 4 | 2006032404 |
| 5 | a20050115 | NULL | NULL |
| NULL | NULL | 8 | 2006032408 |
+------+-----------+------+-------------+
我们可以发现inner join是只显示a.ID=b.ID的部分。
left join除了显示a.ID=b.ID的部分,还显示了a.ID中有但是b表中没有与之对应的项目
right join反之,除了显示a.ID=b.ID的部分,还显示了b.ID中有而a表中没有与之对应的项目
full join中,显示没有对应的所有项目