Oracle Les06_子查询.ppt

子查询 目标 通过本章学习，您将可以: 描述子查询可以解决的问题 定义子查询。 列出子查询的类型。 书写单行子查询和多行字查询。 使用子查询解决问题 谁的工资比 Abel 高? 子查询语法 子查询 (内查询) 在主查询之前一次执行完成。 子查询的结果被主查询使用 (外查询)。 子查询 注意事项 子查询要包含在括号内。 将子查询放在比较条件的右侧。 除非进行Top-N 分析，否则不要在子查询中使用ORDER BY 子句。 单行操作符对应单行子查询，多行操作符对应多行子查询。 子查询类型 单行子查询 只返回一行。 使用单行比较操作符。 执行单行子查询 在子查询中使用组函数 子查询中的 HAVING 子句 首先执行子查询。 向主查询中的HAVING 子句返回结果。 非法使用子查询 子查询中的空值问题 多行子查询 返回多行。 使用多行比较操作符。 在多行子查询中使用 ANY 操作符 在多行子查询中使用 ALL 操作符 子查询中的空值问题 总结 通过本章学习，您已经学会: 在什么时候遇到什么问题应该使用子查询。 在查询是基于未知的值时应使用子查询。 The HAVING Clause with Subqueries You can use subqueries not only in the WHERE clause, but also in the HAVING clause. The Oracle server executes the subquery, and the results are returned into the HAVING clause of the main query. The SQL statement on the slide displays all the departments that have a minimum salary greater than that of department 50. Example Find the job with the lowest average salary. SELECT job_id, AVG(salary) FROM employees GROUP BY job_id HAVING AVG(salary) = (SELECT MIN(AVG(salary)) FROM employees GROUP BY job_id); … Errors with Subqueries One common error with subqueries is more than one row returned for a single-row subquery. In the SQL statement on the slide, the subquery contains a GROUP BY clause, which implies that the subquery will return multiple rows, one for each group it finds. In this case, the result of the subquery will be 4400, 6000, 2500, 4200, 7000, 17000, and 8300. The outer query takes the results of the subquery (4400, 6000, 2500, 4200, 7000, 17000, 8300) and uses these results in its WHERE clause. The WHERE clause contains an equal (=) operator, a single-row comparison operator expecting only one value. The = operator cannot accept more than one value from the subquery and therefore generates the error. To correct this error, change the = operator to IN. Problems with Subqueries A common problem with subqueries is no rows being returned by the inner query. I