[大数据]SQL多表连接查询

????????多表连接查询是指查询同时涉及两个或两个以上的表，连接查询是关系数据库中最主要的数据查，表与表之间的连接分为交叉连接、内连接、自连接、外连接。外连接又分为3种，即左外连接、右外连接和全外连接

? ? ? ? 连接查询的类型可以在select语句的from子句种指定，也可以在where子句中指定

交叉连接

? ? ? ? 交叉连接又称笛卡儿连接，是指两个表之间做笛卡儿积操作，得到结果集的行数是两个表的行数的乘积。交叉连接的一般格式如下

select [all|distinct] [别名,]<选项1> [AS<显示列名>] [,[别名.]<选项2>[AS<显示列名>][,....]]
from<表名1>[别名1],<表名2>[别名2];

需要连接查询的表明在from子句中指定，表明之间用英文逗号隔开

例如

成绩表（sc）和课程表关系表（course）进行交叉连接

select A.*,B.*
from course A,sc B;

? ? ? ? ?此处为了简化表名，分别给两个表指定了别名A和B。但是，一旦表明指定了别名，在该命令中，都必须用别名代替表明

内连接

? ? ? ? 内连接的一般格式如下

select [all|distinct] [别名.]<选项1>[AS<显示列名>] [,[别名.]<选项2>[AS<显示列名>]<,....>]
from <表明1> [别名1]，<表明2>[别名2][,....]
[where <条件表达式>];

?或者

select [all|distinct] [别名.]<选项1>[AS<显示列名>] [,[别名.]<选项2>[AS<显示列名>]<,....>]
from <表名1>[别名1] inner join <表名2>[别名2]on<连接条件表达式>
[where <条件表达式>];

其中，第一种格式的连接类型在where子句中指定，第2种格式的连接类型在from子句种指定

另外，连接条件是指在连接查询种连接两个表的条件。连接条件表达式的一般格式如下

[<表名1>]<别名1.列名> <比较运算符> [<表名2>]<别名2.列名>

比较运算符可以使用等号‘=’,此时称作等值连接，也可以使用不等比较运算符，包括>、 <、>=、<=、!>、!<、<>等，此时为不等值连接

例如

查询每个学生及其选修课的情况

因为学生的基本情况存放在student表中，选课情况存放在sc表中，所以查询过程涉及上述两个表。这两个表是通过公共字段sno实现内连接

select A.*,B.*
from student A,sc B
where A.sno=B.sno;

或者

select  A.*,B.*
from student A inner join sc B on A.sno=B.sno;

若在等值连接中把目标列中的重复字段去掉，则称为自然连接

用自然连接完成查询

select student.sno,sanme,ssex,sbirthday,sdept,cno,degree
from student,sc
where student.sno=sc.sno;

sno前的表名不能省略，因为sno是student和sc共有的属性，所以必须添加上表名前缀

输出所有女生的学号、姓名、课程号及成绩?

select A.sno,sname,cno,degree
from student A,sc B
where A.sno=B.sno and ssex='女';

自连接

? ? ? ? 连接操作不只在表之间进行，一张表内可以进行自身连接操作，即将同一个表的不同行连接起来。自连接可以看作一张表的两个副本之间的连接。在自连接中，必须为表指定两个别名，使之在逻辑上称为两张表

自连接一般格式如下

select [all|distinct] [别名.]<选项1>[AS<显示列名>] [,[别名.]<选项2>[AS显示列名][,...]]
from<表名1>[别名1],<表名1>[别名2][,...]
where <连接条件表达式> [and<条件表达式>];

例如

查询同时选修了c01和c04课程的学生学号

select A.sno
from sc A,sc B
where A.sno=B.sno and A.cno='c01' and B.cno='c04';

查询与张三在同一个系的学生的学号、姓名、和所在系

select B.sno,B.sname,B.sdept
from student A,student B
where A.sdept=B.sdept and A.sanme='张三'and B.sname!='张三';

?外连接

? ? ? ? 在自然连接中，只有在两个表中匹配的行才能在结果集中出现。而外连接中可以只限制一个表，而对另外一个表不加限制（所有的行都出现在结果集中）

? ? ? ? 外连接分为左外连接、右外连接和全外连接。左外连接是对连接条件中左边的表不加限制，即在结果集中保留连接表达方式左边表中的非匹配记录；右外连接是对右边的表不加限制，即在结果集中保留连接表达式右边表中的非匹配记录；全外连接对两个表都不加限制，两个表中所有的行都会包括在结果集中

外连接的一般格式如下

select [all|distinct] [别名.]<选项1>[AS<显示列明>] [,[别名.]<选项2> [AS<显示列名>][,...]]
from <表名1>left|right|full[outer]join <表名2>
on <表名1.列名1>=<表名2.列名2>

例如

由于2005030301和2005020202没有选修课程，所以在查询结果中没有这两个学生的信息，但有时候在查询结果中也需要显示这样的信息，这就需要使用外连接查询

利用左外连接查询改写例

select student.sno,sname,ssex,sbirthday,sdept,cno,degree
from student left join sc
on student.sno=sc.sno