SQL-多表关联查询详解

为了在工作中能更顺利的使用多表关联查询，今天这篇博客就写这个内容了。
在讲解多表关联查询之前，先生成测试表。
登录scott用户，运行以下语句生成测试表。

create table ex1 as select * from emp;
create table ex2 as select * from dept;
insert into ex1(empno,ename,job) values(8888,'EXBONG','EXWORD');
insert into ex1(empno,ename,job) values(9999,'EXZHANG','EXWORD');
insert into ex2(deptno,dname,loc) values(50,'ABONG','BEIJING');

现在看下测试表的内容:


由上图可以看出，ex1表有16行数据，ex2表有5行数据。

多表关联查询

表别名

• 给表起一个短一点的名字
• 在多表关联查询时，如果多个表之间存在同名的列，则必须使用表别名来限定列的使用。
• 在from子句中给表起别名，现起现用。
• 由于from子句最先执行，故一旦在from子句中为表指定了别名，则必须在剩下的子句中都使用表别名（不同名的列可以不用表别名限定，但最好用），不允许再使用原来的名字。

1. 等值连接

• 等值连接的列数=左表列数+右表列数
• 不要求两个表具有相同的列，只看值是否相等

select e.empno,e.ename,e.deptno,d.deptno,d.dname from ex1 e,ex2 d where e.deptno=d.deptno;

左表和右表满足where子句的有14行，故等值连接的查询结果为14行。

2. 内连接

• 内连接的结果均满足on子句的连接条件。

select e.empno,e.ename,e.deptno,d.deptno,d.dname from ex1 e inner join ex2 d on e.deptno=d.deptno;

ex1有16行数据，ex2有5行数据，满足连接条件的只有14行数据，故内连接的结果只有14行数据。

3. 外连接

左外连接

• 左连接的结果不仅包含满足连接条件的数据行，还包括左表的不满足连接条件的数据行。

select e.empno,e.ename,e.deptno,d.deptno,d.dname from ex1 e left join ex2 d on e.deptno=d.deptno;

满足连接条件的有14行，左表中不满足连接条件的有2行，故左连接的查询结果应有16行。

右外连接

• 右连接的结果不仅包含满足连接条件的数据行，还包括右表的不满足连接条件的数据行。

select e.empno,e.ename,e.deptno,d.deptno,d.dname from ex1 e right join ex2 d on e.deptno=d.deptno;

满足连接条件的有14行，右表中有2行不满足连接条件，故右连接的查询结果应有16行。

完全外连接

• 完全外连接的结果不仅包含满足连接条件的数据行，还包括左表和右表中不满足连接条件的数据行。

select e.empno,e.ename,e.deptno,d.deptno,d.dname from ex1 e full join ex2 d on e.deptno=d.deptno;

满足连接条件的有14行，左表和右表中分别有2行不满足连接条件，故完全外连接的查询结果应该有18行。

4. 自然连接

• 内连接、外连接的列数=左表列数+右表列数
• 内连接、外连接可以使用别名
• 自然连接的列数=左表列数+右表列数-左表和右表中名字相同的列数（去除重复列）
• 自然连接不能使用别名，不需要on子句
• 自然连接要求连接的两个表必须要有相同的列名

select * from ex1 natural join ex2;

左表和右表中名字相同的列为deptno，故以该列进行连接。满足连接条件的行数为14行，故自然连接的查询结果为14行。

5. 自连接

• 若一个表的某两列存在上下级关系，可以通过自连接获取。

select e1.empno,e1.ename,e1.mgr,e2.ename from ex1 e1 inner join ex1 e2 on e1.mgr=e2.empno;

存在上下级关系的行数只有13行，故自连接的查询结果为13行数据。

6. 交叉连接（笛卡尔连接）

• 不需要任何连接条件的连接
• 不需要on子句

select * from ex1 e cross join ex2 d;

左表有16行数据，右表有5行数据，任意连接即有16*5=80行数据。