开发手册 欢迎您！
软件开发者资料库

用于解决查询的子查询

解决查询的子查询 - Oracle数据库11g管理员认证助理，OPN认证专家考试的准备指南。本指南可帮助您逐步在线准备证书考试，并且完全免费。此认证旨在使您获得赢得基于Oracle SQL的项目的简短列表。 Oracle技术认证是一种有价值的，行业认可的凭证，表明已经证明的知识水平和技能。

子查询最好定义为查询中的查询.通过子查询，您可以编写查询，为查询在运行时执行时实际开发的条件选择数据行.更正式地说，它是在另一个SELECT语句的一个子句中使用SELECT语句.实际上，子查询可以包含在另一个子查询中，该子查询位于另一个子查询中，依此类推.子查询也可以嵌套在INSERT，UPDATE和DELETE语句中.子查询必须括在括号内.

子查询可以在允许表达式的任何地方使用，前提是它返回单个值.这意味着返回单个值的子查询也可以作为FROM子句列表中的对象列出.这称为内联视图，因为当子查询用作FROM子句的一部分时，它被视为虚拟表或视图.子查询可以放在主查询的FROM子句，WHERE子句或HAVING子句中.

Oracle允许在WHERE子句中最多嵌套255个子查询级别.在FROM子句中表示的嵌套子查询没有限制.实际上，255级别的限制根本不是一个限制，因为很少遇到嵌套超过三个或四个级别的子查询.

子查询SELECT语句与用于开始常规或外部查询的SELECT语句非常相似.子查询的完整语法是:

( SELECT [DISTINCT] subquery_select_parameter  FROM {table_name | view_name}               {table_name | view_name} ...  [WHERE search_conditions]  [GROUP BY column_name [,column_name ] ...]  [HAVING search_conditions] )

子查询类型

单行子查询:返回单行输出的子查询.当在WHERE条件中使用时，它们标记单行比较运算符的用法.

多行子查询:返回多行输出的子查询.他们使用多行比较运算符，如IN，ANY，ALL.也可以有子查询返回多个列.

相关子查询:相关子查询依赖于外部查询提供的数据.此类子查询还包括子查询使用EXISTS运算符来测试是否存在满足指定条件的数据行.

单行子查询

使用单行子查询时外部查询的结果基于单个未知值.虽然此查询类型正式称为"单行"，但该名称意味着查询返回多个列 - 但只返回一行结果.但是，单行子查询只能返回一行结果，只包含一列到外部查询.

在下面的SELECT查询中，内部SQL只返回一行，即最小值公司的薪水.它反过来使用这个值来比较所有员工的薪水，只显示那些工资等于最低工资的人.

SELECT first_name, salary, department_idFROM employeesWHERE salary = (SELECT MIN (salary) FROM employees);

当需要根据某些条件限制查询的组结果时，使用HAVING子句.如果必须将子查询的结果与组函数进行比较，则必须将内部查询嵌套在外部查询的HAVING子句中.

SELECT department_id, MIN (salary)FROM employeesGROUP BY department_idHAVING MIN (salary)  < (SELECT AVG (salary)FROM employees)

多行子查询

多行子查询是嵌套查询，可以向父查询返回多行结果. WHERE和HAVING子句中最常使用多行子查询.由于它返回多行，因此必须由集合比较运算符(IN，ALL，ANY)处理.虽然IN运算符与前面章节中讨论的含义相同，但ANY运算符将指定值与子查询返回的每个值进行比较. ALL将值与子查询返回的每个值进行比较.

下面的查询显示单行子查询返回多行时的错误.

SELECTfirst_name, department_idFROM employeesWHERE department_id = (SELECT department_idFROM employeesWHERE LOCATION_ID = 100)department_id = (select               *ERROR at line 4:ORA-01427: single-row subquery returns more than one row

多行运算符的使用

  • [> ALL ]超过子查询返回的最高值

  • [

  • [

  • [> ANY]超过子查询返回的最低值

  • [= ANY]等于子查询返回的任何值(与IN相同)

上面的SQL可以使用IN运算符重写，如下所示.

 SELECT first_name，department_id  FROM employees  WHERE department_id IN(SELECT department_id  FROM departments  WHERE LOCATION_ID = 100)

请注意，在上述查询中，IN匹配从子查询返回的部门ID，将其与主查询中的部门ID进行比较，并返回满足条件的员工姓名.

对于上述查询，联接可能是更好的解决方案，但为了说明的目的，已经在其中使用了子查询.

相关子查询

相反常规子查询，其中外部查询依赖于内部查询提供的值，相关子查询是内部查询依赖于外部查询提供的值的子查询.这意味着在相关子查询中，内部查询将重复执行一次，对于外部查询可能选择的每一行.

相关子查询可以生成回答复杂管理问题的结果表.

考虑下面的SELECT查询.与先前考虑的子查询不同，此SELECT语句中的子查询无法独立于主查询进行解析.请注意，外部查询指定从employee表中选择别名为e1的行.内部查询将employee表的employee department number列(DepartmentNumber)与别名e2比较为别名表名e1的相同列.

 SELECT EMPLOYEE_ID，salary，department_id  FROM employees E  WHERE salary>(SELECT AVG(salary) FROM EMP T  WHERE E.department_id = T.department_id)

多列子查询

多列子查询向外部查询返回多个列，并且可以列在外部查询的FROM，WHERE或HAVING子句中.例如，以下查询显示员工的当前薪水在1000和2000范围内并在部门10或20工作的员工的历史详细信息.

 SELECT first_name，job_id，salary  FROM emp_history  WHERE(salary，department_id)in(SELECT salary，department_id  FROM employees  WHERE salary BETWEEN 1000 and 2000  AND department_id BETWEEN 10和20) ORDER BY first_name;

当在外部查询的FROM子句中使用多列子查询时，它创建一个临时表，可以由外部查询的其他子句引用.此临时表更正式地称为内联视图.子查询的结果与FROM子句中的任何其他表一样对待.如果临时表包含分组数据，则分组的子集将被视为表中的单独数据行.请考虑以下查询中的FROM子句.子查询形成的内联视图是主查询的数据源.

SELECT * FROM (SELECT salary, department_idFROM employees WHERE salary BETWEEN 1000 and 2000);