SQL扩展之T-SQL中的数据查询语言之连接查询

女爷i 2022-08-08 19:31 237阅读 0赞

当一个查询涉及到两个或则多个表的数据时。需要指定连接列进行连接查询。

连接查询是关系数据库中的重要查询，在T-SQL中连接查询有2中表示方式，一种是连接谓词表示形式，另一种是使用关键字JOIN表示形式。

1，连接谓词

在SELECT语句的WHERE子句中使用比较运算符给出连接条件对表进行连接，将这种表示形式称为连接谓词表示形式。其一般语法格式为：  
\[<表名1.>\] <列名1> <比较运算符> \[<表名2.>\] <列名2>  
比较运算符有：<、<=、=、>、>=、!=、<>、!<、!>  
连接谓词还有以下形式：  
\[<表名1.>\] <列名1> BETWEEN \[<表名2.>\] <列名2>AND\[<表名2.>\] <列名3>  
由于连接多个表存在公共列，为了区分是哪个表中的列，引入表名前缀指定连接列。例如，student.stno表示student表的stno列，score.stno表示score表的stno列  
经常用到的连接如下：  
● 等值连接：表之间通过比较运算符“=”连接起来，称为等值连接。  
● 非等值连接：表之间使用非等号进行连接，则称为非等值连接。  
● 自然连接：如果在目标列中去除相同的字段名，称为自然连接。  
● 自连接：将同一个表进行连接，称为自连接。

举例，查询学生的情况和选秀课程的情况：

USE stsc
      SELECT student.*, score.*
      FROM student, score
      WHERE student.stno=score.stno

上面的代码是采用的等值连接。

下面语句采用自然连接查询，能达到一样的效果：

USE stsc
       SELECT student.*, score.cno, score.grade
       FROM student, score
       WHERE student.stno=score.stno

再例如，查询选修了“微机原理”且成绩在80分以上的学生的姓名：

USE stsc
    SELECT a.stno, a.stname, b.cname, c.grade
    FROM student a, course b, score c
    WHERE a.stno=c.stno AND b.cno=c.cno AND b.cname='微机原理' AND C.grade>=80

这里设计到别名。a是表 student 的别名，b是 course 的别名，c是 score 的别名

自然连接举例：查询选修了“801”课程的成绩高于学号为“1002”的成绩的学生姓名：

USE stsc
       SELECT a.cno, a.stno, a.grade
       FROM score a, score b
       WHERE a.cno='801' AND a.grade>b.grade AND b.stno='1002' AND b.cno='801'
       ORDER BY a.grade DESC

2， 使用关键字JOIN

T-SQL扩展了以JOIN关键字指定连接的表示方式，使表的连接运算能力有了增强。JOIN连接在FROM子句的< joined\_table >中指定。

语法格式如下：

<joined_table> ::= 
    {
    <table_source> <join_type> <table_source> ON <search_condition>
     | <table_source> CROSS JOIN <table_source>
      | <joined_table>
    }

参数说明：

<join\_type>为连接类型, ON用于指定连接条件, <join\_type>的格式如下:  
\[INNER|\{LEFT|RIGHT|FULL\}\[OUTER\]\[<join\_hint>\]JOIN  
INNER表示内连接，OUTER表示外连接，CROSS表示交叉连接，此为JOIN关键字指定的连接的3种类型。

A）内链接

内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。

内连接是系统默认的，可省略INNER关键字。

举例，查询学生的情况和选秀课程的情况

USE stsc
     SELECT *
     FROM student INNER JOIN score ON student.stno=score.stno

再例如，查询选修了“102”的课程且成绩在85以上的学生的情况

USE stsc
     SELECT a.stno, a.stname, b.cno, b.grade
    FROM student a JOIN score b ON a.stno=b.stno
    WHERE b.cno='102' AND b.grade>=85

B） 外连接

在内连接的结果表，只有满足连接条件的行才能作为结果输出。外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。外连接有以下3种：  
● 左外连接（LEFT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括左表的所有行；  
● 右外连接（RIGHT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括右表的所有行；  
● 完全外连接（FULL OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括两个表的所有行。

例如，采用左外连接可查询教师的任课情况:

USE stsc
      SELECT teacher.tname, course.cname
      FROM teacher LEFT JOIN course ON (teacher.tno=course.tno)

采用右外连接查询教师的任课情况：

USE stsc
      SELECT teacher.tname, course.cname
      FROM teacher RIGHT JOIN course ON (teacher.tno=course.tno)

采用全外连接查询教师的任课情况：

USE stsc
    SELECT teacher.tname, course.cname
    FROM teacher FULL JOIN course ON (teacher.tno=course.tno)

（注意：外连接只能针对2个表）