多表查询（二）-----表的连接类型

1、表的连接类型可以分为五种：自连接，自然连接，内连接，外连接，交叉连接。下面分别说说下面几种连接类型。

2、自连接

自连接是指表与自身进行连接，这就需要用到表的别名。

eg1：在student表中查询存在不及格课程的学生的姓名，所在系，所有课程及其成绩信息。

方法一、分两步，第一步查询出不及格学生的学号作为子查询；第二步，根据学号查出学生的姓名，所在系，所有课程的名称和成绩信息。

方法二、采用自连接，代码如下：

select distinct s1.sname,s1.dname,s1.cno,s1.mark from student as s1,student as s2 where s1.sno = s2.sno and s2.mark<60 order by s1.sname 

执行结果：

 

3、自然连接（natua join ）

自然连接是一种特殊的等价连接，它将表中具有相同名称的列自动进行匹配。因为是自动连接，所以不需要在where子句中指定连接条件。

自然连接

SQL  server查询语言不支持自然连接

自然连接的特点：

• 自动匹配具有相同名称的列，不能认为指定哪些列进行匹配
• 连接后的结果中相同名称的列只出现一次。

4、内连接（inner join）

内连接分为等值连接和不等值连接两种，下面是其中的等值连接示意图:

SQL SERVER支持内连接查询.

eg1：从student表和teacher表中查询学生姓名，所在系，所修的所有课程的课程后以及开课教师姓名。我们可以从过where子句实现，不过在这里我们通过inner join内连接两个表来实现。

select sname,s.dname,s.cno,tname from student as s inner join teacher as t on s.cno = t.cno order by sname 

不等连接：在连接条件中，使用其他比较运算符。包括：>=,>,<=,<,!>,!<,<>

 

eg2:

查询学生修的非本息教师开设的课程信息，包括学生学号，姓名，所在系，课程号，成绩，以及开课教师的姓名信息。
select sname,s.dname,s.cno,mark,tname from student as s inner join teacher as t on s.dname<> t.dname and s.cno = t.cno order by sname

说明：在默认的情况下， dbms将多表查询按inner join来执行，除非指定outer join ，即上面的例子中将inner join换成join也将得到同样的结果。

eg3：

用inner join实现多表连接

根据teacher，student，course表，查询所有学生的姓名， 所在系， 所修课程， 考试时间，课程成绩及教授教师姓名。

select sname,student.dname,cname,ctest,mark,tname from student inner join course on student.cno = course.cno inner join teacher on student.cno = teacher.cno order by sname 

 

5、外连接

外连接分为左外连接left(outer)join，右外连接right(outer)join 和全外连接full (outer )join。

 

eg1：左连接student表和course表， 查询所有学生的学号，姓名，课程代码，课程名称，考试时间和成绩信息

select sno,sname,s.cno,cname,ctest,mark from student as s left outer join course as c on s.cno = c.cno order by sname 

注意：在sql server2000 中， 可以在where子句中使用“*=”符号实现左外连接，用“=*”实现右外连接。但是2005以后的版本都不再支持这两个符号实现外连接。

关于右外连接和全外连接不再举例详述。

 

6、交叉连接（cross join）

由上图可以看出交叉连接其实就是在做两个表（记录集合）的笛卡尔积。也就是说下面两条查询语句是完全等价的。

select * from table1,table2 select * from table cross join table2 

eg1:

交叉连接表student表和course表，查询所有学生的学号， 姓名，课程代码， 课程名称，考试时间和成绩信息

select sno,sname,s.cno,c.cname,ctest,mark from student as s cross join course as c where s.cno = c.cno

注意：cross join 做的是两个表的笛卡尔积，不能是cross join ...on的形式，只能在where子句中定义查询条件。

 

7、union join

使用union join来进行多表连接，它并不对表中的数据进行任何匹配处理，而只是把来自一个源表的行与另一个源表的行进行联合起来，生成的结果表中包括第一个表中的所有行和列和另一个表中的所有行和列。缺少的属性值用null来表示

下面是union join的示意图

当想要像单表那样使用来自多个表的所有行时，使用union join就显得非常便利。

说明：在SQL sever中，不支持union join，这里也就无法给出实例了。