数据查询语言（Data Query Language，DQL）全部语法举例（二）多表查询

查询用表及单表查询见：

数据查询语言（Data Query Language，DQL）全部语法举例（一）单表查询

https://blog.csdn.net/u010852547/article/details/135707602?spm=1001.2014.3001.5501

4、多表查询

1） 内连接

内连接（INNER JOIN）基于指定的连接条件，只返回两个表中满足条件的匹配记录。可以指定一个或多个连接条件。在使用内连接时应该谨慎考虑查询的需求和数据完整性。如果需要保留未匹配的行，可以使用左外连接或右外连接。

这里的“匹配”指的是两个表之间的连接条件。比如，两个表中的某个字段具有相同的值，或者是某个字段与另一个字段之间的关系满足特定条件。当连接条件满足时，相应的行将被认为是匹配的，并返回在结果集中。

  • 查询员工编号、姓名、部门编号、部门名称
select e.empno, e.ename, e.deptno, d.dname from emp e inner join dept d on e.deptno=d.deptno;
-- 内连接：inner join   on子句

条件：
(1）筛选条件：where, having 
(2）连接条件：on 
筛选条件和连接条件是分开书写的   

2）外连接

以一张表为基表，该表的内容会全部显示，再加上两张表匹配的内容。

外连接会显示不匹配的行，因为外连接返回左表或右表中所有满足连接条件的行，以及未找到匹配的行。左外连接中，即使右表中没有匹配的行，左表中的所有行仍然会出现在结果集中，右表的相关列将填充为NULL。右外连接同理。

这种特性使得外连接在处理不完全的数据集或处理具有相关关系的数据时非常有用。通过使用外连接，可以获取更完整的数据集，并处理缺失的匹配行。在使用外连接时，要仔细考虑如何处理NULL值。在结果集中，未找到匹配的行对应的列将填充为NULL，这可能会对后续的数据处理和分析造成影响。

左外连接（LEFT OUTER JOIN）返回左表中的所有记录以及右表中与左表匹配的记录。如果右表中没有与左表匹配的记录，则结果集中对应的字段将为NULL。

  • 查询员工编号、姓名、部门编号、部门名称（以员工表为基表）
select e.empno, e.ename, e.deptno, d.dname  from emp e left outer join dept d on e.deptno = d.deptno; 
-- 查询结果为14条数据
-- 如果有的员工没有部门，也会显示该条数据

右外连接（RIGHT OUTER JOIN）返回右表中的所有记录以及左表中与右表匹配的记录。如果左表中没有与右表匹配的记录，则结果集中对应的字段将为NULL。

  • 查询员工编号、姓名、部门编号、部门名称（以部门表为基表）
select e.empno, e.ename, d.deptno, d.dname from emp e right outer join dept d on e.deptno = d.deptno;
-- 查询结果为15条数据，包含40号部门（该部门无员工）的数据

如果数据中存在空值或缺失值，需要考虑这些值对连接结果的影响。内连接会排除空值行，而外连接则会保留空值行。

  • 查询员工编号、姓名、工资、部门编号、部门名称、工资等级（三表查询）
select e.ename, e.sal, e.empno, e.deptno, d.dname,s.*
from emp e right outer join dept d on e.deptno=d.deptno
inner join salgrade s on e.sal between s.losal and s.hisal  
-- 第二个连接条件防止笛卡尔积产生错误记录
-- 三表及以上可以先写2个表的连接，再连接第三个表

3）取并集    

取并集是将两个或多个结果集合并为一个新的结果集的操作。取并集可以将多个条件下的结果集合并起来，从而获得更完整的数据。操作中，需要确保参与合并的结果集具有相同的结构，即列数和对应的数据类型必须一致。

  • 将【查询员工编号、姓名、部门编号、部门名称（以员工表为基表）的查询结果集】与【查询员工编号、姓名、部门编号、部门名称（以部门表为基表）的查询结果集】合并为一个结果集，去重
select e.empno, e.ename, e.deptno, d.dname  from emp e left outer join dept d on e.deptno = d.deptno
union select e.empno, e.ename, d.deptno, d.dname from emp e right outer join dept d on e.deptno = d.deptno;
-- 取并集：union 去除重复的行，只保留唯一的行。
-- 查询结果为15条数据
-- union连接的2个select语句与上一条2）外连接-左外连接、右外连接的2个举例相同
  • 将【查询员工编号、姓名、部门编号、部门名称（以员工表为基表）的查询结果集】与【查询员工编号、姓名、部门编号、部门名称（以部门表为基表）的查询结果集】合并为一个结果集，不去重
select e.empno, e.ename, e.deptno, d.dname  from emp e left outer join dept d on e.deptno = d.deptno
union all select e.empno, e.ename, d.deptno, d.dname from emp e right outer join dept d on e.deptno = d.deptno;
-- 取并集：union all，不去重
-- 查询结果为29条数据（14+15）

4）自连接

自连接是一种特殊的SQL连接，它在一个表上执行两次或多次，以便分析该表的不同部分之间的关系。自连接通常用于处理具有层次结构的数据，例如组织结构、树形结构等。

自连接使用别名来标识一个表，然后将这个表自身进行连接。在自连接中，表被视为两个不同的表，并分别用不同的别名来标识。

  • 查询员工的编号、姓名、上级编号、上级的姓名（用内连接）
select e1.empno 员工编号,e1.ename 员工姓名, e1.mgr 上级编号, e2.ename 上级姓名
from emp e1 inner join emp e2 on e1.mgr=e2.empno; 
-- 查询结果为13条记录
  • 查询员工的编号、姓名、上级编号、上级的姓名（用左外连接）
select e1.empno 员工编号,e1.ename 员工姓名, e1.mgr 上级编号, e2.ename 上级姓名
from emp e1 left outer join emp e2 on e1.mgr=e2.empno; 
-- 查询结果为14条数据（KING的上级为空）

5）子查询

子查询是一种嵌套在其他查询中的查询（语句中包含多个SELECT）。它可以在一个查询中执行，并返回一个结果集，该结果集可以作为外部查询的输入。

子查询可以在SELECT（将内层查询结果当做外层查询的条件）、FROM（将子查询的结果作为一个虚拟表来使用）或WHERE子句（根据子查询的结果对外部查询的结果进行过滤）中使用。

5.1）不相关子查询

子查询可以独立运行。先运行子查询，再运行外查询。

按子查询的返回结果是单行或多行，分为单行子查询和多行子查询。

 单行子查询：

  • 查询工资比CLARK高的雇员
select * from emp where sal>(select sal from emp where ename='CLARK' );
  • 查询工资高于平均工资的雇员名字和工资
select ename, sal from emp where sal> (select avg(sal)from emp);
  • 查询和CLARK同一部门且比他工资低的雇员名字和工资
select ename, sal from emp where deptno= (select deptno from emp where ename='CLARK') 
and sal< (select sal from emp where ename='CLARK');
  • 查询职务和SCOTT相同且比SCOTT雇佣时间早的雇员信息  
select * from emp where job=(select job from emp where ename='SCOTT') 
and hiredate<(select hiredate from emp where ename='SCOTT');

多行子查询：

  • 查询部门20中 职务与部门10中的雇员职务相同的 雇员信息。
select * from emp where deptno=20 and job in (select job from emp where deptno=10);
  • 查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。
-- 方法1：
select empno,ename,sal from emp where sal> all (select sal from emp where job = 'SALESMAN');
-- 子查询的运行结果是多行，为多行子查询

-- 方法2：
select empno,ename,sal from emp where sal>(select max(sal) from emp where job = 'SALESMAN');
-- 子查询的运行结果为单行，为单行子查询
  • 查询工资低于任意一个“CLERK”的工资的雇员信息。  
select * from emp where sal< any(select sal from emp where job = 'CLERK')
and job!='CLERK';
-- 多行子查询

select * from emp where sal< (select max(sal) from emp where job = 'CLERK')
and job!='CLERK';
-- 单行子查询
5.2）相关子查询

子查询不可以独立运行。先运行外查询，再运行子查询。

  • 查询本部门最高工资的员工
select * from emp e where sal=(select max(sal)from emp where deptno=e.deptno) order by deptno;
-- 外查询和子查询是怎样连接的？2个查询都用到了相同的emptno
-- 子查询里用到了外部的emp.deptno，子查询无法独立运行，因此为相关子查询
-- 优点：简洁、功能强大

-- 如果使用不相关子查询实现：
select * from emp where deptno=10 and sal=(select max(sal) from emp where deptno=10)
union select * from emp where deptno=20 and sal=(select max(sal) from emp where deptno=20)
union select * from emp where deptno=30 and sal=(select max(sal) from emp where deptno=30)
-- 需要多行语句，使用union取并集，并且不知道部门号的数据将无法查询
  • 查询工资高于其所在岗位的平均工资的那些员工
select * from emp e where sal>(select avg(sal) from emp where job=e.job) order by job;