SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
? ? ? ? ? ? ? ? ? FROM? employees
? ? ? ? ? ? ? ? ? WHERE? department_id
? ? ? ? ? ? ? ? ? ? ? ? = d.department_id);
⑦相关更新
UPDATE table1 alias1
SET? ? column = (SELECT expression
? ? ? ? ? ? ? ? FROM? table2 alias2
? ? ? ? ? ? ? ? WHERE? alias1.column =? ?
? ? ? ? ? ? ? ? ? ? ? ? alias2.column);
使用相关子查询依据一个表中的数据更新另一个表的数据
⑧相关删除
DELETE FROM table1 alias1
?WHERE? column operator
?(SELECT expression
? FROM? table2 alias2
? WHERE? alias1.column = alias2.column);
⑨WITH 子句
使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块
WITH 子句将该子句中的语句块执行一次并存储到用户的临时表空间中
使用 WITH 子句可以提高查询效率
例：查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息
WITH dept_costs? AS (
? SELECT? d.department_name, SUM(e.salary) AS dept_total
? FROM? ? employees e, departments d
? WHERE? e.department_id = d.department_id
? GROUP BY d.department_name),
avg_cost? ? AS (
? SELECT SUM(dept_total)/COUNT(*) AS dept_avg
? FROM? dept_costs)
SELECT *
FROM? dept_costs
WHERE? dept_total >
? ? ? ? (SELECT dept_avg
? ? ? ? FROM avg_cost)
ORDER BY department_name;