左连接,右连接,内连接,外连接

 

left join   :左连接,返回左表中所有的记录以及右表中连接字段相等的记录。
right join :右连接,返回右表中所有的记录以及左表中连接字段相等的记录。
inner join: 内连接,又叫等值连接,只返回两个表中连接字段相等的行。
full join:外连接,返回两个表中的行:left join + right join
cross join:结果是笛卡尔积,就是第一个表的行数乘以第二个表的行数。

declare @a table(a int,b int)
declare @b table(a int,b int)

insert @a values(1,1)
insert @a values(2,2)
insert @b values(1,1)
insert @b values(3,3)
select * from @a 
select * from @b
--左:
select * from @a Aa left join @b Bb on Aa.a=Bb.a
--右:
select * from @a Aa right join @b Bb on Aa.a=Bb.a
--内
select * from @a Aa inner join @b Bb on Aa.a=Bb.a
--外:
select * from @a Aa full join @b Bb on Aa.a=Bb.a
--交叉连接
select * from @a  cross join @b

sql内连接和外连接的区别

在之前,我对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 JOIN ON A.Aid=B.Bnameid
运行结果如下图4所示:



图4:内连接数据

其实select from A,B where A.Aid=B.Bnameid与Select from JOIN ON A.Aid=B.Bnameid的运行结果是一样的。
2.外连接:外连接分为两种,一种是左连接(Left JOIN)和右连接(Right JOIN)
  (1)左连接(Left JOIN):即图3公共部分记录集C+表A记录集A1。     
     语句如下:select from Left JOIN ON A.Aid=B.Bnameid
     运行结果如下图5所示:



图5:左连接数据

     说明:
           在语句中,A在B的左边,并且是Left Join,所以其运算方式为:A左连接B的记录=图3公共部分记录集C+表A记录集A1
           在图3中即记录集C中的存在的Aid为:2           
           图1中即表A所有记录集A中存在的Aid为:1 9
           表A记录集A1中存在的Aid=(图1中即A表中所有Aid)-(图3中即记录集C中存在的Aid),最终得出为:1 9
           由此得出图5中A左连接B的记录=图3公共部分记录集C+表A记录集A1,
           最终得出的结果图5中可以看出Bnameid及Bid非NULL的记录都为图3公共部分记录集C中的记录;Bnameid及Bid为NULL的Aid为1 9的四笔记录就是表A记录集A1中存在的Aid。

  (2)右连接(Right JOIN):即图3公共部分记录集C+表B记录集B1。
     语句如下:select from Right JOIN ON A.Aid=B.Bnameid
     运行结果如下图6所示:



图6:右连接数据

     说明:
           在语句中,A在B的左边,并且是Right Join,所以其运算方式为:A右连接B的记录=图3公共部分记录集C+表B记录集B1
           在图3中即记录集C中的存在的Aid为:2           
           图2中即表B所有记录集B中存在的Bnameid为:2 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 Left JOIN ON A.Aid=B.Bnameid
select from Right JOIN ON A.Aid=B.Bnameid

其实对图3左右翻转一下就可以得出以下结论:
select from Left JOIN ON A.Aid=B.Bnameid和select from Right JOIN ON A.Aid=B.Bnameid所得出的记录集是一样的

select from Right JOIN ON A.Aid=B.Bnameid和select from Left JOIN 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的笛卡尔积表示所有可能的选课情况。