Oracle之子查询

子查询：

用子查询能解决的问题

假想你想要写一个查询来找出挣钱比陆涛的薪水还多的人。为了解决这个问题，你需要两个查询：一

 

个找出陆涛的收入，第二个查询找出收入高于陆涛的人。

你可以用组合两个查询的方法解决这个问题，放置一个查询到另一个查询中。

内查询或子查询返回一个值给外查询或主查询。使用一个子查询相当于执行两个连续查询并且用第一个

 

查询的结果作为第二个查询的搜索值。

 

子查询语法:

SELECT select_list

FROM table

WHERE expr operator

              (SELECT select_list

              FROM table);

1.子查询(内查询) 在主查询之前执行一次

2.子查询的结果被用于主查询(外查询)

首先执行子查询 (内查询) 显示子查询返回的值，然后用内查询返回的结果执行外查询，最后，执行整个查询 (包括子查询)，显示相同的结果。

 

 

子查询可嵌套的位置：

子查询是一个SELECT 语句，它是嵌在

另一个 SELECT 语句中的子句。

使用子查询你可以用简单的语句构建功能强大的语句。当你需要从表中用依赖于表本身的数据选择行时

 

它们是非常有用的。

也可以放在

WHERE 子句 HAVING 子句 FROM 子句。

 

在语法中：

operator 包括比较条件，例如 >、= 或 IN

比较条件分为两个种类：单行运算符 (>, =, >=, <, <>, <=) 和多行运算符 (IN, ANY, ALL)。

子查询通常涉及一个嵌套的 SELECT、子-SELECT 或内 SELECT 语句。字查询通常执行一次。并且它的输出被用于完成主或外查询的查询条件。

 

另外，子查询可以被放在 CREATE VIEW 语句中、CREATE TABLE 语句、UPDATE 语句、INSERT 语句的 INTO 子句和 UPDATE 语句的 SET 子句中。

 

使用子查询的原则：

1.       子查询放在圆括号中

2.将子查询放在比较条件的右边, 可以增加可读性。

在子查询中的ORDER BY 子句不需要，除非你正在执行Top-N 分析。

Oracle8i 以前的版本中，子查询不包含 ORDER BY 子句。对一个 SELECT 语句只能用一个 ORDER BY 子句，并且如果指定了它就必须放在主 SELECT 语句的最后。从 Oracle8i 开始，ORDER BY 子句可以使用，并且在进行 Top-N 分析时是必须的。

3.在单行子查询中用单行运算符，在多行子查询中用多行运算符，

在子查询中可以使用两种比较条件：单行运算符和多行运算符。

子查询的个数：

Oracle 服务器没有强制限制子查询的数目；限制只与查询所需的缓冲区大小有关。

 

子查询的类型：

1.    单行子查询：从内 SELECT 语句只返回一行的查询

2.       多行子查询：从内 SELECT 语句返回多行的查询

3.    还有多列子查询：从内 SELECT 语句返回多列的查询。

 

单行子查询

单行子查询是从内查询返回一行的查询。在该子查询类型中用一个单行操作符

SELECT last_name, job_id

FROM employees

WHERE job_id =

(SELECT job_id

FROM employees

WHERE employee_id = 141);

 

 

SELECT  last_name, job_id, salary

FROM employees

WHERE job_id =

(SELECT  job_id  FROM  employees  WHERE  employee_id = 141)  AND  salary >(SELECT  salary  FROM  employees  WHERE  employee_id = 143);

该例子可以由三个查询块组成：外查询和两个内查询。内查询块首先被执行，产生查询结果分别为 ST_CLERK 和 2600。然后处理外查询块，并且使用内查询的返回值来完成它的查询条件。

两个内查询返回单个值 (分别是 ST_CLERK 和 2600)，所以这种 SQL 语句被称为单行子查询。

注：外和内查询可以从不同的表中取得数据。

 

在子查询中使用组函数:

SELECT  last_name,  job_id,  salary

FROM  employees

WHERE  salary =

(SELECT  MIN(salary) FROM  employees);

你可以从主查询中显示数据，该主查询使用一个带组函数的单行子查询。子查询放在圆括号中并且放在比较条件的后面。

例子显示所有其薪水等于最低薪水的雇员的 last name、job ID 和 salary。 MIN 组函数返回单个的值 (2500) 给外函数。

 

带子查询的HAVING 子句:

1.Oracle 服务器首先执行子查询

2.Oracle 服务器返回结果到主查询的HAVING 子句中

例

找出平均薪水为最低平均薪水的工作岗位。

SELECT job_id, AVG(salary)

FROM employees

GROUP BY job_id

HAVING AVG(salary) = (SELECT MIN(AVG(salary))

FROM employees

GROUP BY job_id);

 

子查询错误

使用子查询的一个常见的错误是单行子查询返回返回了多行。

SELECT employee_id, last_name

FROM employees

WHERE salary =

(SELECT MIN(salary) FROM employees  GROUP  BY  department_id);

ERROR at line 4:ORA-01427: single-rowsubqueryreturns more thanone rowERROR

子查询包含一个 GROUP BY 子句，这就暗示该子查询将返回多行，每个对应它所找到的一组，在这种情况下，子查询的结果将是 4400、6000、2500、4200、7000、17000 和 8300。

外查询得到子查询的结果 (4400、6000、2500、4200、7000、17000、8300) 并且在它的 WHERE 子句中使用这些结果。WHERE 子句包含一个等号 (=) 运算符，这是一个单行比较运算符，只能使用一个值。 = 操作符不能接受来自子查询的多个值，并且因此产生错误。

为了纠正该错误，改变 = 操作为 IN。

子查询的另一个常见问题是内查询没有返回行。

，子查询包含一个 WHERE 子句，推测起来，其目的是找名字为 Haas 的雇员，该语句是正确的，但在执行时选择无行返回。

没有名叫 Haas 的雇员，所以子查询无返回行，外查询得到子查询的结果 (null) 并且在 WHERE 子句中使用该结果，外查询找不到一个 job ID 等于 null 的雇员，所以也没有行返回。如果一个 job 存在 null 值，也没有返回行，因为比较两个空值还是空，因此 WHERE 子句的条件不为 true。

多行子查询:

多行子查询

子查询返回多行被称为多行子查询。对多行子查询要使用多行运算符而不是单行运算符。多行运算符期待多个值。

例

查找各部门收入为部门最低的那些雇员。

SELECT last_name, salary, department_id

FROM employees

WHERE salary IN (SELECT MIN(salary)

FROM employees

GROUP BY department_id);

内查询先被执行，产生一个查询结果，然后主查询块处理和使用由内查询返回的值完成它的搜索条件。事实上，在 Oracle 服务器看起来主查询象是下面这样：

SELECT last_name, salary, department_id

FROM employees

WHERE salary IN (2500, 4200, 4400, 6000, 7000, 8300, 8600, 17000);

在多行子查询中使用ANY 运算符

SELECT employee_id, last_name, job_id, salary

FROM employees

WHERE salary < ANY(SELECT salaryFROM employeesWHERE job_id = 'IT_PROG')

AND  job_id <> 'IT_PROG';

ANY 运算符 (和它的同义词， SOME 运算符) 比较一个值与一个子查询返回的每一个值。幻灯片中的例子显示不是 IT 程序员的雇员，并且这些雇员的的薪水少于IT 程序员。挣钱最多的程序员的薪水是 $9,000。

<ANY