相关子查询的执行过程理解

主要参考博客:
https://blog.csdn.net/yellowelephant/article/details/88064645?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_title-3&spm=1001.2101.3001.4242

案例表：

##创建员工信息表
create table emp(
	empno int(4) primary key,
	ename varchar(20),
	job varchar(10),
	mgr int(4),
	hiredate date,
	sal double(7,2),
	comm double(7,2),
	deptno int(2)
);

##添加员工测试数据
insert into emp values(7369,'SIMTH','CLERK',7902,'1980-12-17',800,null,1);
insert into emp values(7499,'ALLEN','SALESMAN',7968,'1981-02-02',1250,null,1);
insert into emp values(7521,'WARD','SALESMAN',7968,'1981-02-22',1500,0,1);
insert into emp values(7566,'JONES','MANAGER',7389,'1981-04-02',1600,400,2);
insert into emp values(7654,'MARTIN','SALESMAN',7968,'1981-09-28',1300,300,2);
insert into emp values(7698,'BLAKE','MANAGER',7389,'1981-05-01',1250,200,2);
insert into emp values(7782,'CLARK','MANAGER',7389,'1981-07-17',1250,100,3);
insert into emp values(7788,'SCOTT','ANALYST',7566,'1987-05-17',1750,50,3);
insert into emp values(7839,'KING','PRESIDENT',null,'1981-09-17',3000,0,3);
insert into emp values(7844,'TURNER','SALESMAN',7698,'1987-12-17',2500,null,4);
insert into emp values(7900,'ADAMS','CLERK',7788,'1981-03-17',2750,null,4);
insert into emp values(7902,'JAMES','CLERK',7698,'1981-07-17',1250,null,4);
insert into emp values(7934,'FORD','ANALYST',7566,'1982-06-17',1750,null,4);

完成业务要求：查询本部门最高工资的员工

--查询本部门最高工资的员工 
select * from emp where sal = (select max(sal) from emp where deptno = e.deptno);

执行过程：

1）首先执行父查询select * from emp获得所有的deptno数据；
2）从depton所有数据中获取第一条数据，带入子查询（select max(sal) from emp where deptno = e.deptno），获取到第一条deptno数据对应的max(sal)；
3）执行父查询的where条件，即匹配第一条deptno下的所有sa等于max(sal)的emp信息；
4）继续执行deptno中的第二条数据，继续执行以上过程；
5）将筛选的数据进行汇总呈现；