第二部分：SQL多表连接(SQL92 and SQL99)

SQL多表连接

SQL92

等值连接

等值连接是指两个数据库表在做完笛卡儿积之后，选择两个表中相同的的字段，令其相等，建立起来的等值连接。
注意：如果两个数据表中选择的是相同的字段，注意要加上对应所在的表名作区分

select ename,emp.deptno,dname from emp,dept where ename='ALLEN' and emp.deptno=dept.deptno;
另一种方法便是使用别名，两种方式运行结果相同
 select e.ename,e.deptno,d.dname from emp e,dept d where e.ename='ALLEN' and e.deptno=d.deptno;

运行结果

不等值连接

不等值连接则是两张没有相同字段的数据表，但是其中一张表可以跟另一张表中的数据建立不等关系

例如 dept表中的sal具体的金额可以对应到salgrade中的薪资等级，可以筛选员工工资在哪个等级
select * from emp e ,salgrade s where e.sal>=s.losal and e.sal<=s.hisal;

运行如下

自连接

数据库表自己跟自己的连接

select e1.ename,e1.job,e1.sal,e2.ename from emp e1,emp e2 where e1.MGR=e2.EMPNO;
运行结果

外连接

SQL92的外连接包括左外连接和右外连接，左外连接就是(+)在右边，查询左边数据为null的数据，同理，右外连接就是(+) 在左边，查询左边对应数据为null的数据，
现在有如下两张表：
表1：emp表，记录员工信息，其中SMITH DEPTNO为空

表2 dept部门表，这个表记录着所有的部门信息，目前部门编号55是新增加的空部门

示例1： 查询员工姓名，工作，薪资，部门名称及没有部门的员工信息

select * from emp e,dept d where e.deptno = d.deptno(+) order by e.deptno;
说明: (+)在右边，说明e.deptno为空，也就是职工表中所在部门为空

运行结果

示例2: 查询员工姓名，工作，薪资，部门名称及没有员工信息的部门

 select * from emp e,dept d where e.deptno(+) = d.deptno order by e.deptno;
 说明：现在(+)在左边，也就是说明 d.deptno为空，没有员工信息的部门表

SQL99

其中如下两种方式做笛卡儿积的效果是一样的

  select * from emp,dept;
  select * from emp cross join dept;  --使用cross join关键字

自然连接

自然连接会按照所有同名同值字段自动进行笛卡儿积操作，也就意味着如果两个表中不止有一个同名同值的字段的话，会对全部字段进行笛卡儿积操作

 select * from emp natural join dept;

所以自然连接存在如下两个缺点

如果只想按照部分字段筛选怎么办--------------------------------------使用内连接

字段名不同，但是值相同进行等值筛选怎么办-----------------------

内连接

在两个表中有多个字段相同，可以使用内连接+using语句的方式指定对特定字段进行笛卡儿积操作 或者是on关键字建立等值连接操作

语法1：select 内容 from 表名 inner join 表名 using(字段名，字段名...)
示例：select * from emp inner join dept using(deptno);
语法2：select 内容 from 表名 inner join 表名 on 连接条件 where普通筛选条件
示例：select * from emp inner join dept on emp.deptno=dept.deptno;

运行结果

外连接

基本上实现的查询跟SQL92的查询一致，只不过在SQL查询语句的书写上不太一样

左外连接

两个连接表，以左为主，所以左表数据会全部显示

语法：select 内容 from 表名 left join 表名 on 连接条件 where 筛选条件;
示例： select * from emp e left join dept d on e.deptno =d.deptno;  运行效果同SQL92的结果

右外连接

两个连接表，以右为主，所以右表数据会全部显示

语法：select 内容 from 表名 right join 表名 on 连接条件 where 筛选条件;
示例： select * from emp e right join dept d on e.deptno =d.deptno;  运行效果同SQL92的结果

全外连接

两个连接表中的等值字段全部会显示出来，两个表的空字段也会全部显示出来.

语法：select 内容 from 表名 fulljoin 表名 on 连接条件 where 筛选条件;
示例： select * from emp e full join dept d on e.deptno =d.deptno;

运行结果

自连接

自连接内容跟SQL92基本一致,只是SQL99选择inner join on语法进行自连接

语法： select 内容 from 表名 inner join 表名 on 连接条件 
示例： select e1.*,e2.ename from emp e1 inner join emp e2 on e1.Mgr = e2.Empno;

SQL92&SQL99实现三表联合查询

SQL92写法

沿用笛卡儿积的方式将三个表连接起来,然后筛选条件
特点：易于读写，难于维护
缺点：SQL语句结构不清晰

select e.ename,d.dname,c.cname 
from emp e,dept d,city c 
where e.deptno=d.deptno and d.loc =c.cid;

SQL99写法

SQL99的三表连接的写法就是连续使用inner join on语法连接对应字段然后筛选

select e.ename,d.dname,c.cname 
from emp e i
nner join dept d 
on e.deptno=d.deptno
inner join city c 
on d.loc=c.cid;