Oracle-26-内连接（等值、不等值连接、自然连接）&外连接（左外、右外、全连接）&using子句

一、连接分类：

1.内连接

（1）等值连接：在连接中使用等号（=）操作就是等值连接。

比如查询部门编号为30的员工编号、姓名、部门名称。

①

SQL>select empno, ename, dname from emp, dept

      2 where emp.deptno = dept.deptno and dept.deptno = 30;

②

SQL>select empno, ename, dname from emp

      2 inner join dept on emp.deptno= dept.depton where dept.deptno = 30;

 

（2）不等值连接：主要用除了等号之外的操作符，比如：<>、>、<、>=、<=、LIKE、IN、BETWEEN…AND。

比如查询工资为1500以上的员工所属部门和所在的具体地点。

①

SQL>select distinct dname, loc from emp, dept

      2 where emp.deptno = dept.deptno and sal >1500;

②

SQL>select distinct dname, loc from emp inner join dept on

       2 emp.deptno = dept.deptno where sal >1500;

 

（3）自然连接：基于两个表的同名的一个或多个列。

【注意】自然连接是根据两个表中同名的列而进行连接的，当列不同名时，自然连接将失去意义。且语法中没有on。

比如：select empno,sname, deptno, loc from natural join dept.

可以少写匹配条件，不需要写a.sno=b.sno这种。

 

2.外连接：使用关键字outer join。用于检索一个表的所有记录和另一个表中的匹配行。

语法格式：

SELECT column_name, column_name [, column_name] FROM

table_name [LEFT / RIGHT /FULL] OUTER JOIN table_nameON

table_name.ref_column_name join_operator table_name.

ref_column_name

（1）左外连接：LEFT OUTER JOIN左外连接就是在结果中除了满足连接条件之外的行，还包括LEFT OUTER JOIN左侧表的所有行。

比如：

①

select ename, emp.deptno “emp表”, dept.deptno “dept表”, loc from left outer join emp on emp.deptno =dept.deptno;

②

select ename, emp.deptno “emp表”, dept.deptno “dept表”，loc from emp, dept where dept.deptno = emp.deptno(+)

【注意】第二个语句外连接的连接运算符为(+)。该连接运算符可以在等号的左面也可以在等号的右面。但一定要放在缺少相应信息的那一面。（比如下图中sc表没有s002匹配数据，那么+就放在sc表那一面）

 

【简单说明左外连接的意思】

首先有student和sc两张表：

输入SQL语句：

select a.sno, sname, cno, score from student a left outer join sc c on a.sno = c.sno

然后查询过程如下：

①对于student表中s001，查到名字为zhang，拿着s001去sc表 ，发现选了c001课程且得分为98，那么s001-zhang-c001-98为第1条查询到的数据，如下表；

②同理查到s001-zhang-c002-100为第2条数据，如下表；

③对于student表中s002-li，在sc表中没有对应数据，那么查询结果中显示null

（总结起来就是：两表有匹配数据的显示，没有匹配数据的填入null）

【A left outer join B ——必须把左表A表所有行匹配出来，无限满足左表，如果左表中相应数据在右表没有匹配数据，那么在对应位置上填入null，详见上图和例4】

 

（2）右外连接：RIGHT OUTER JOIN

与左外连接同理，无限满足右表，即根据右表中数据去左表搜索，如果没有匹配数据，填入null

 

（3）全连接：FULL OUTER JOIN

 

3.自连接，返回连接到同一表中的行

 

4.交叉连接

 

二、Using子句

如果两个表中有同名的列，可以在联接语句里用Using子句。

比如：

select empno,ename, deptno, loc from emp join dept using(deptno);

【注意】语法中没有on。

 

例1：练习内连接中的等值连接。

解：注意下图中的SQL命令，用的是inner join：

 

例2：练习内连接中的自然连接

解：

好处是不需要写匹配条件，如a.sno= b.sno这些不需要写，这里直接student natural join sc即可，就是直接将student表和sc表相同的列关联起来。

 

例3：练习Using子句

解：student表和sc表有相同列名sno，所以直接using(sno)即可。

 

例4：练习左外连接

解：当前student表中数据：

当前sc表中数据：

那么用左外连接查询：

发现依然是“无限满足左表student”，也就是对于左表中s012，在右表sc中没有匹配数据，那么查询结果相应位置填入null。