MySQL学习:left join, right join, inner join

本文章来给各位同学介绍我的MySQL学习:left join, right join, inner join一些用法与解决,有需要了解联合查询的朋友可参考.

表A如下:

1. aIDaNum
2. 1a20110111
3. 2a20110112
4. 3a20110113
5. 4a20110114
6. 5a20110115

表B如下:

1. bIDbName
2. 1bName0111
3. 2bName0112
4. 3bName0113
5. 4bName0114
6. 8bName0118

一、left join … on

MySQL语句:SELECT * FROM a LEFT JOIN b ON a.aID = b.bID

结果如下:

1. aIDaNumbIDbName
2. 1a201101111bName0111
3. 2a201101122bName0112
4. 3a201101133bName0113
5. 4a201101144bName0114
6. 5a20110115NULLNULL

说明:left join是以A表的记录为基础,A可以看成左表,B可以看成右表,left join是以左表为准的,换句话说,左表（A）的记录将会全部表示出来,而右表（B）只会显示复合搜索条件的记录,例子中为:A.aID = B.bID.

B表记录不足的地方均为NULL.

二、right join … on

MySQL语句如下:SELECT * FROM a RIGHT JOIN b ON a.aID = b.bID

结果如下:

1. aIDaNumbIDbName
2. 1a201101111bName0111
3. 2a201101122bName0112
4. 3a201101133bName0113
5. 4a201101144bName0114
6. NULLNULL8bName0118

说明:right join 和 left join的结果刚好相反，这次是以右表（B）为基础的，A表不足的地方用NULL填充。

三、inner join … on

MySQL语句如下:SELECT * FROM a INNER JOIN b ON a.aID = b.bID

等同于:SELECT * FROM a, b WHERE a.aID = b.bID

结果如下:

1. aIDaNumbIDbName
2. 1a201101111bName0111
3. 2a201101122bName0112
4. 3a201101133bName0113
5. 4a201101144bName0114--phpfensi.com

说明:inner join并不以谁为基础,它只是显示符合条件的记录,LEFT JOIN操作用于在任何的FROME字句中.