6.多表连接（SQL基础）-CSDN博客

本文链接：https://blog.csdn.net/ruyigongfang/article/details/141432393

1.内连接(inner join...on)

关键字：[inner] join ... on 关联条件

定义:按照关联条件将两张表关联，只显示匹配成功（满足关联条件）的数据，匹配不成功的不显示

语法:

select distinct 查询展示的内容

from 表名1 inner join 表名2 on 关联条件

inner join 表名3 on 关联条件 ...

where 分组前的过滤条件

group by 分组的内容 having 分组后的过滤条件

order by 排序的内容 asc|desc;

执行顺序：from - on -join -where - group by -having -select -distinct -order by

2.外连接

左外连接(left join...on)

关键字:left [outer] join ... on 关联条件

定义:将两张表按照关联条件进行关联，左表作为主表，左表的数据要全部展示，匹配不成功的数据，右表（匹配表）以空值填充。

语法：表名1 left join 表名2 on 关联条件 ---left join 前边的表为主表，左表

条件放在 on 和 where 后边的区别

1.on后边是关联条件，where后边是过滤条件，对已经关联好的表进行过滤。

2.条件放在on后边，如果是左（右）连接，左（右）表数据全部展示，右（左）表匹配不成功的时候以空值填充。

如果是内连接，放在on和 where 后边没有区别。

右外连接(right join...on)

关键字:right [outer] join ... on 关联条件

全外连接(full join...on)

关键字:full join ... on 关联条件

定义:将两张表按照关联条件进行关联，两张表的数据都会全部展示，匹配不成功的数据，对应的表以空值填充

3.交叉连接(相乘）

定义:两张表的数据交叉相乘，不需要关联条件，等同于笛卡尔积

例子:

select * from emp,dept where emp.deptno=dept.deptno; ---等价于内连接

select * from emp,dept where emp.deptno=dept.deptno(+);---等价于左连接

select * from emp,dept where emp.deptno(+)=dept.deptno;---等价于右连接

select * from emp,dept;----交叉连接、笛卡尔积的简写

4.特殊连接

4.1自然连接

定义:两张表自动寻找关联字段，将两张表进行关联，只显示匹配成功的数据，去掉重复字段

关键字:natural join

4.2 自连接

没有关键字

定义:是表连接的一种特殊形式，一张表自己和自己关联，没有关键字

4.3 不等值连接

定义:也是表连接的一种特殊形式，关联条件里边不用等号

5.各种连接的特点

内连接：按照关联条件关联，将两表中都符合关联条件（匹配成功的）的数据展示出来；

左外连接：按照关联条件关联，左表作为主表，将左表所有数据展示出来，右表（匹配表）中不满足关联条件的以空值填充；

右外连接：按照关联条件关联，右表作为主表，将右表所有数据展示出来，左表（匹配表）中不满足关联条件的以空值填充；

全外连接：将两张表按照关联条件关联，两张表的数据全部展示，匹配不成功的数据，对应的表以空值填充；

笛卡尔连接：没有关联条件，左表中的每一条数据都与右表中的每一条数据做了一次关联

例题：输出员工编号为 7654 的员工的名字和工资等级

select t1.ename, t3.grade
  from emp t1
  left join salgrade t2
    on t1.sal between t2.losal and t2.hisal
where t1.empno=7654;

练习

--1、列出与"SCOTT" 从事相同工作的所有雇员

select t2.ename
  from emp t1 ---scott所在的表
 inner join emp t2  ---和scott岗位相同人所在的表
    on t1.job = t2.job
   and t1.ename = 'SCOTT' and t1.ename<>t2.ename;
   
select *
  from emp t1
 inner join (select job from emp where ename = 'SCOTT') t2
    on t1.job = t2.job and t1.ename<>'SCOTT';

--2、列出所有员工的姓名及其直接上级的姓名

select t1.ename, t2.ename
  from emp t1
  left join emp t2
    on t1.mgr = t2.empno;

--3、列出工资高于公司平均水平的所有员工

select t1.ename
  from emp t1
 inner join (select avg(sal) pjgz from emp) t2
    on sal > pjgz;

--4、列出入职日期早于其直接上级的所有雇员

select t1.ename
  from emp t1
 inner join emp t2
    on t1.mgr = t2.empno
   and t1.hiredate < t2.hiredate;
--5、查询工资大于10号部门平均工资的员工信息
select *
  from emp t1
 inner join (select avg(sal) pjgz from emp where deptno = 10)
    on t1.sal > pjgz;

--6.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称

select t2.ename, t2.sal, t3.dname
  from emp t1 --SMITH 所在的表
 inner join emp t2  --工资比SMITH高的人
    on t2.sal > t1.sal and t1.ename = 'SMITH'
 inner join dept t3  --工资比SMITH高的人的部门信息
    on t3.deptno = t2.deptno  and loc = 'CHICAGO';
    
select t1.ename, t1.sal, t2.dname
  from emp t1
 inner join dept t2
    on t1.deptno = t2.deptno
   and t1.sal > (select sal from emp where ename = 'SMITH')
   and t2.loc = 'CHICAGO';


select t1.ename,t1.sal,t2.dname
from emp t1
inner join (select * from dept where loc = 'CHICAGO') t2
on t1.deptno=t2.deptno
inner join (select sal from emp where ename='SMITH') t3
on t1.sal>t3.sal;

--7.查询部门人数大于所有部门平均人数的部门编号，部门名称，部门人数

--方法一： 先关联再分组
select t1.deptno, t2.dname, count(1)
  from emp t1
  join dept t2
    on t1.deptno = t2.deptno
 group by t1.deptno,t2.dname
having count(1) > (select count(1) from emp)/ (select count(1) from dept);
    
--方法二：先分组判断人数 再关联部门表
select t1.deptno, t1.dname, t2.rs
  from dept t1
 inner join (select deptno, count(1) as rs
               from emp
              group by deptno
             having count(1) > (select count(1) from emp) / (select count(1)
                                                              from dept)) t2
    on t1.deptno = t2.deptno; --3.5

--方法三：
select *
  from (select deptno, count(1) bmrs from emp group by deptno) t1
 inner join dept t2
    on t1.deptno = t2.deptno
  join (select avg(count(1)) rs from emp group by deptno) t3
    on t1.bmrs > t3.rs;

--方法四：
select distinct t1.deptno, dname,(select count(1) from emp where deptno= t1.deptno)
  from emp t1
  join dept t2
    on t1.deptno = t2.deptno
 where (select count(1) from emp where deptno= t1.deptno) >
       (select avg(count(1)) from emp group by deptno);

select distinct t1.deptno,
                dname,
                (select count(1) from emp where deptno = t1.deptno)
  from emp t1
 inner join dept t2
    on t1.deptno = t2.deptno
 where (select count(1) from emp where deptno = t1.deptno) >
       ((select count(1) from emp) /
       (select count(1) from (select distinct deptno from emp)));
              
--所有部门平均人数:
select (select count(1) from emp)/(select count(1) from dept) from dual;
select avg(count(1)) from emp group by deptno  --->4.6
select avg(c) from (select count(1) c from emp group by deptno);

--8.列出至少有三个员工的所有部门和部门信息。

select  t2.*
  from emp t1
  left join dept t2
  on t1.deptno = t2.deptno
 group by t2.deptno,t2.dname,t2.loc
having count(1) >= 3;

select t2.deptno,t2.dname,t2.loc,count(1)
from emp t1
inner join dept t2
on t1.deptno=t2.deptno
group by t2.deptno,t2.dname,t2.loc
having count(1)>=3;

select t1.*
  from dept t1
  join (select deptno from emp group by deptno having count(1) >= 3) t2
    on t1.deptno = t2.deptno

select distinct t2.*, (select count(1) from emp where deptno = t1.deptno)
  from emp t1
 inner join dept t2
    on t1.deptno = t2.deptno
 where (select count(1) from emp where deptno = t1.deptno) >= 3;

--9.列出职位为“CLERK”的员工姓名和其所在部门名称，部门人数。

select ename, dname, (select count(1) from emp where deptno = t1.deptno)
  from emp t1
 inner join dept t2
    on t1.deptno = t2.deptno
 where job = 'CLERK';
 
select t1.ename,t3.dname,t2.rs
from emp t1
inner join (select deptno,count(1) rs from emp group by deptno)  t2
on t1.deptno=t2.deptno
inner join dept t3
on t1.deptno = t3.deptno
where t1.job = 'CLERK';

多表连接综合练习：