mysql内连接和外连接的区别

在之前，我对MSSQL中的内连接和外连接所得出的数据集不是很清楚。这几天重新温习了一下SQL的书本，现在的思路应该是很清楚了，现在把自己的理解发出来给大家温习下。希望和我一样对SQL的连接语句不太理解的朋友能够有所帮助.

有两个表A和表B。
表A结构如下：
Aid：int；标识种子，主键，自增ID
Aname：varchar

数据情况，即用select 

*  from  A出来的记录情况如下图1所示：

图1:A表数据

表B结构如下：

Bid：int；标识种子，主键，自增ID

Bnameid：int

数据情况，即用select  *  from  B出来的记录情况如下图2所示：

图2:B表数据

为了把Bid和Aid加以区分，不让大家有误解，所以把Bid的起始种子设置为100。

有SQL基本知识的人都知道，两个表要做连接，就必须有个连接字段，从上表中的数据可以看出，在A表中的Aid和B表中的Bnameid就是两个连接字段。

下图3说明了连接的所有记录集之间的关系：

图3:连接关系图

现在我们对内连接和外连接一一讲解。

1.内连接：利用内连接可获取两表的公共部分的记录，即图3的记录集C

语句如下：Select  *  from  A  JOIN  B  ON  A.Aid=B.Bnameid

运行结果如下图4所示：

图4:内连接数据

其实select  *  from  A,B  where  A.Aid=B.Bnameid与Select  *  from  A  JOIN  B  ON  A.Aid=B.Bnameid的运行结果是一样的。

2.外连接：外连接分为两种，一种是左连接（Left  JOIN）和右连接（Right  JOIN）

    (1)左连接（Left  JOIN）：即图3公共部分记录集C＋表A记录集A1。         

          语句如下：select  *  from  A  Left  JOIN  B  ON  A.Aid=B.Bnameid

          运行结果如下图5所示：

图5:左连接数据

          说明：

                      在语句中，A在B的左边，并且是Left  Join，所以其运算方式为：A左连接B的记录=图3公共部分记录集C＋表A记录集A1

                      在图3中即记录集C中的存在的Aid为：2  3  6  7  8                     

                      图1中即表A所有记录集A中存在的Aid为：1  2  3  4  5  6  7  8  9

                      表A记录集A1中存在的Aid=(图1中即A表中所有Aid)-(图3中即记录集C中存在的Aid)，最终得出为：1  4  5  9

                      由此得出图5中A左连接B的记录=图3公共部分记录集C＋表A记录集A1,

                      最终得出的结果图5中可以看出Bnameid及Bid非NULL的记录都为图3公共部分记录集C中的记录；Bnameid及Bid为NULL的Aid为1  4  5  9的四笔记录就是表A记录集A1中存在的Aid。

    (2)右连接（Right  JOIN）：即图3公共部分记录集C＋表B记录集B1。

          语句如下：select  *  from  A  Right  JOIN  B  ON  A.Aid=B.Bnameid

          运行结果如下图6所示：

图6:右连接数据

          说明：

                      在语句中，A在B的左边，并且是Right  Join，所以其运算方式为：A右连接B的记录=图3公共部分记录集C＋表B记录集B1

                      在图3中即记录集C中的存在的Aid为：2  3  6  7  8                     

                      图2中即表B所有记录集B中存在的Bnameid为：2  3  6  7  8  11

                      表B记录集B1中存在的Bnameid=(图2中即B表中所有Bnameid)-(图3中即记录集C中存在的Aid)，最终得出为：11

                      由此得出图6中A右连接B的记录=图3公共部分记录集C＋表B记录集B1,

                      最终得出的结果图6中可以看出Aid及Aname非NULL的记录都为图3公共部分记录集C中的记录；Aid及Aname为NULL的Aid为11的记录就是表B记录集B1中存在的Bnameid。

         

总结：

通过上面的运算解说，相信很多人已经想到，上面的情况（包括图3的关系图）说明的都只是A在B的左边的情况，

以下语句B在A的右边的又会出现什么情况呢？？

select  *  from  B  Left  JOIN  A  ON  A.Aid=B.Bnameid

select  *  from  B  Right  JOIN  A  ON  A.Aid=B.Bnameid

其实对图3左右翻转一下就可以得出以下结论：

select  *  from  B  Left  JOIN  A  ON  A.Aid=B.Bnameid和select  *  from  A  Right  JOIN  B  ON  A.Aid=B.Bnameid所得出的记录集是一样的

而

select  *  from  B  Right  JOIN  A  ON  A.Aid=B.Bnameid和select  *  from  A  Left  JOIN  B  ON  A.Aid=B.Bnameid所得出的记录集也是一样的。

假设一个数据库中有两张表，一张是学生表StudentInfo，一张是班级表ClassInfo，两张表之间用ClassId字段进行关联。

如果用内连接，正常的写法是这样的：

Select StudentInfo.*,ClassInfo.* from StudentInfo join ClassInfo on StudentInfo.ClassId=ClassInfo.ClassId

用这种写法不会出现笛卡尔积，但是内连接是允许省略连接条件的，也就是可以省略掉on后面的内容，所以如果写成这样：

Select StudentInfo.*,ClassInfo.* from StudentInfo,ClassInfo

也是可以通过编译的，但是这样一来就产生了一个笛卡尔积（关于笛卡尔积的内容见文章最后注解）。

但是因为怕产生笛卡尔积而拒绝使用内链接是不正确的，只要不省略掉连接条件，是不会产生笛卡尔积的。

具体采用内连接还是外连接要 看具体问题的需要。内连接的特性是只显示符合连接条件的记录，以刚才的语句为例，查询结果只显示学生表和班级表中ClassId相等的记录。举例来说，如 果有条学生记录的ClassId字段为空，那么用内链接将不会显示这条学生记录，因为在班级表里面是找不到对应的ClassId的。

而外连 接就不一样，以左外连接为例（右外连接类似），它除了显示符合连接条件的记录以外，还会显示所有左表中的记录（右外连接就是所有右表中的记录）。还是以刚 才的例子说明，如果学生表中有条学生记录的ClassId字段为空，用学生表左外连接班级表，那么是会显示这条学生记录的。

所以如果要求只显示已有分班的学生记录，那么就应该用内连接查询；如果要求查询所有学生记录，那么就应该采用外连接查询。

笛卡尔积：
假 设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。 可以扩展到多个集合的情况。类似的例子有，如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选课情况。