内连接
内连接也叫连接,是最早的一种连接,最早被称为普通连接或自然连接。内连接是从结果中删除其他被连接表中没有匹配行的所有行,所以内连接可能会丢失信息。内连接使用比较运算符根据每个表共有的列的值匹配两个表中的行。(典型的连接运算,使用像 = 或 <> 之类的比较运算符)。包括相等连接和自然连接。
内连接的语法:
SELECT fieldlist FROM table1 [INNER] join table2 ON table1.column=table2.column
一个表中的行和与另外一个表中的行匹配连接。表中的数据决定了如何对这些行进行组合。从每一个表中选取一行,根据这些列的值是否相同,组合方式分为一对一、多对一和多对多的关系。
- 1.一对一关系
当连接的两个表,两个连接列的值完全相同,则两个表连接相当于一对一的关系。
下面在“pubs”数据库中“titles”和“titleauthor”两个表中,根据相同列“title_id”进行内连接。在查询分析器中输入的SQL语句如下:
select * from titles inner join titleauthor on titles.title_id= titleauthor.title_id
实现的过程如图1所示。
图1 内连接时一对一的关系
说明:表“titles”和“titleauthor”都是SQL Server自带的数据库“pubs”的表。由于两个表中的title_id列都是具有惟一值的索引,所以两个表的内连接属于一对一的关系。
- 2.多对一关系
当连接的两个表,其中一个表要连接的列出现重复值,另外一个表的值是惟一的。这时连接的两个表之间的出现了多对一的关系。
下面“student”表中的“学号”具有惟一值,而“grade”成绩表中的“学号”有重复值。现在实现这两个表的内连接。在查询分析器中输入的SQL语句如下:
select * from grade join student on student.学号=grade.学号?
实现的过程如图2所示。
图2 内连接时多对一的关系
- 3.多对多关系
当连接的两个表,要连接的列都出现重复值,这时连接的两个表之间出现多对多的关系。
本例是SQL Server自带的数据库“pubs”,其中的两个表“titles”和“roysched”中的“royalty”列都有重复值,现在实现这两个表的内连接。在查询分析器中输入的SQL语句如下:
select * from titles inner join roysched on titles.royalty=roysched.royalty
实现的过程如图3所示。
图3 内连接时多对多的关系
外连接
外连接则扩充了内连接的功能,会把内连接中删除表源中的一些保留下来,由于保留下来的行不同,把外连接分为左外连接、右外连接和全外连接这3种连接。在FROM子句中指定外连接时,可以由下列几组关键字中的一组指定:
LEFT JOIN 或 LEFT OUTER JOIN。
左向外连接的结果集包括LEFT OUTER子句中指定的左表的所有行,而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值。
RIGHT JOIN 或 RIGHT OUTER JOIN。
右向外连接是左向外连接的反向连接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。
FULL JOIN 或 FULL OUTER JOIN。
完整外部连接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。
交叉连接。交叉连接返回左表中的所有行,左表中的每一行与右表中的所有行组合。交叉连接也称作笛卡尔积。
- 1.左外连接
左外连接保留了第一个表的所有行,但只包含第二个表与第一表匹配的行。第二个表相应的空行被放入NULL值。
左外连接的语法:
SELECT fieldlist FROM table1 left join table2 ON table1.column=table2.column
下面把“student”表和“grade”表左外连接,第一个表“student”有不满足连接条件的行。在查询分析器中输入的SQL语句如下:
select * from student left join grade on student.学号=grade.学号
实现的过程如图1所示。
- 2.右外连接
右外连接保留了第二个表的所有行,但只包含第一个表与第二个表匹配的行。第一个表相应空行被入NULL值。
右外连接的语法:
use student
SELECT fieldlist FROM table1 right join table2 ON table1.column=table2.column
下面把“grade”表和“course”表右外连接,第二个表“course”有不满足连接条件的行。在查询分析器中输入的SQL语句如下:
use student
select * from grade right join course on course.课程代号=grade.课程代号
实现的过程如图2所示。
- 3.全外连接
全外连接会把两个表所有的行都显示在结果表中。
全外连接的语法:
use student
SELECT fieldlist FROM table1 full join table2 ON table1.column=table2.column
把“grade”表和“course”表实现全连接,两个表都有不满足连接条件的行。在查询分析器中输入的SQL语句如下:
use student
select * from grade full join course on course.课程代号=grade.课程代号
实现的过程如图3所示。
有两个表A和表B。
表A结构如下:
Aid:int;标识种子,主键,自增ID
Aname:varchar
数据情况,即用select * from A出来的记录情况如下图1所示:
表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所得出的记录集也是一样的。
出自:http://hi.baidu.com/crm1989/item/dfa9982b423dedc0ee10f188
出自:http://nvd11.blog.163.com/blog/static/200018312201281112515595/
出自:http://pangaoyuan.iteye.com/blog/713177