Sql Certificate 简明教程

SQL - Subqueries to Solve Queries

子查询最好定义为查询内的查询。子查询允许你编写这样的查询：选择满足在运行时执行查询时实际制定的条件的数据行。更正式地说，它是在其他 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] )

Types of Subqueries

*单行子查询：*返回单行输出的子查询。在 WHERE 条件中使用时标记单行比较运算符的使用。

*多行子查询：*返回多行输出的子查询。它们使用多行比较运算符，如 IN、ANY、ALL。也可以有多个列返回的子查询。

Correlated Sub Query: 相关子查询取决于外部查询提供的数据。此类子查询还包括使用 EXISTS 运算符测试满足指定条件的数据行是否存在。

Single Row Sub Query

单行子查询用于外部查询结果基于单个未知值的情况。虽然此查询类型正式称为“单行”，但该名称暗示查询返回多列，但仅有一行结果。但是，单行子查询只能向外部查询返回仅由一列组成的一行结果。

在下面的 SELECT 查询中，内部 SQL 只返回一行，即公司的最低工资。然后它使用此值比较所有员工的工资，并仅显示工资等于最低工资的员工。

SELECT first_name, salary, department_id
FROM employees
WHERE salary = (SELECT MIN (salary)
		FROM employees);

HAVING 从句用于需要基于某些条件限制查询的组结果的情况。如果必须将子查询的结果与组函数进行比较，则必须将内部查询嵌套在外部查询的 HAVING 从句中。

SELECT department_id, MIN (salary)
FROM employees
GROUP BY department_id
HAVING MIN (salary)  < (SELECT AVG (salary)
			FROM employees)

Multiple Row Sub Query

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

下面的查询显示了单行子查询返回多行时出现的错误。

SELECT	first_name, department_id
FROM employees
WHERE department_id = (SELECT department_id
			FROM employees
			WHERE LOCATION_ID = 100)
department_id = (select
               *
ERROR at line 4:
ORA-01427: single-row subquery returns more than one row

Usage of Multiple Row operators

[> ALL] 大于子查询返回的最大值
[< ALL] 小于子查询返回的最小值
[< ANY] 小于子查询返回的最大值
[> 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 进行比较，并返回满足条件的员工姓名。

联接对于上面的查询将是更好的解决方案，但为了说明问题，其中使用了子查询。

Correlated Sub Query

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

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

考虑下面的 SELECT 查询。与之前考虑的子查询不同，此 SELECT 语句中的子查询不能独立于主查询解决。请注意，外部查询指定从具有别名 e1 的 employee 表中选择行。内部查询将 employee 表的员工部门号列（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)

Multiple Column Sub Query

多列子查询向外部查询返回多于一列，并且可以列在外部查询的 FROM、WHERE 或 HAVING 子句中。例如，下面的查询显示了部门 10 或 20 中的当前工资在 1000 至 2000 范围内的员工的历史详细信息。

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 and 20)
ORDER BY first_name;

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

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