例:找出每个部门中薪水最高的雇员,显示姓和薪水。 SELECT last_name,salary_amount FROM employee ee WHERE salary_amount = (SELECT MAX (salary_amount) FROM employee em WHERE ee.department_number = em.department_number);
再举一个例子:查找薪水高于部门平均薪水的所有员工,显示姓和薪水。 SELECT last_name ,salary_amount (Format ''$$$,$99.99'') FROM employee ee WHERE salary_amount > (SELECT AVG (salary_amount) FROM employee em WHERE ee.department_number = em.department_number);
该子查询的执行过程如下:
1.读取employee表的一条记录
2.执行子查询,得到该记录的雇员的所在部门的平均工资
3.比较该雇员的薪水和部门平均薪水
4.如果雇员薪水比部门平均薪水高,就输出该行信息
5.转到1
用相关子查询的例子: 哪些代码的工作没有雇员? SELECT job_code FROM job WHERE job_code NOT IN (SELECT job_code FROM employee) ; 全部取出后 判定是否存在
如果使用NOT EXISTS表达式,可以写成: SELECT job_code FROM job WHERE NOT EXISTS (SELECT * FROM employee ee WHERE ee.job_code = job.job_code);