Sql Certificate 简明教程

SQL - Subqueries to Solve Queries

子查询最好定义为查询内的查询。子查询允许你编写这样的查询：选择满足在运行时执行查询时实际制定的条件的数据行。更正式地说，它是在其他 SELECT 语句的从句之一内使用 SELECT 语句。事实上，子查询可以包含在另一个子查询内，另一个子查询内，依此类推。子查询还可以嵌套在 INSERT、UPDATE 和 DELETE 语句内。子查询必须用圆括号括起来。

A subquery is best defined as a query within a query. Subqueries enable you to write queries that select data rows for criteria that are actually developed while the query is executing at run time. More formally, it is the use of a SELECT statement inside one of the clauses of another SELECT statement. In fact, a subquery can be contained inside another subquery, which is inside another subquery, and so forth. A subquery can also be nested inside INSERT, UPDATE, and DELETE statements. Subqueries must be enclosed within parentheses.

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

A subquery can be used any place where an expression is allowed providing it returns a single value. This means that a subquery that returns a single value can also be listed as an object in a FROM clause listing. This is termed an inline view because when a subquery is used as part of a FROM clause, it is treated like a virtual table or view. Subquery can be placed either in FROM clause, WHERE clause or HAVING clause of the main query.

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

Oracle allows a maximum nesting of 255 subquery levels in a WHERE clause. There is no limit for nesting subqueries expressed in a FROM clause.In practice, the limit of 255 levels is not really a limit at all because it is rare to encounter subqueries nested beyond three or four levels.

子查询 SELECT 语句与用于开始常规或外部查询的 SELECT 语句非常相似。子查询的完整语法如下：

A subquery SELECT statement is very similar to the SELECT statement used to begin a regular or outer query.The complete syntax of a subquery is:

( 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 条件中使用时标记单行比较运算符的使用。

*Single Row Sub Query: *Sub query which returns single row output. They mark the usage of single row comparison operators, when used in WHERE conditions.

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

*Multiple row sub query: *Sub query returning multiple row output. They make use of multiple row comparison operators like IN, ANY, ALL. There can be sub queries returning multiple columns also.

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

Correlated Sub Query: Correlated subqueries depend on data provided by the outer query.This type of subquery also includes subqueries that use the EXISTS operator to test the existence of data rows satisfying specified criteria.

Single Row Sub Query

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

A single-row subquery is used when the outer query’s results are based on a single, unknown value. Although this query type is formally called "single-row," the name implies that the query returns multiple columns-but only one row of results. However, a single-row subquery can return only one row of results consisting of only one column to the outer query.

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

In the below SELECT query, inner SQL returns only one row i.e. the minimum salary for the company. It in turn uses this value to compare salary of all the employees and displays only those, whose salary is equal to minimum salary.

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

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

A HAVING clause is used when the group results of a query need to be restricted based on some condition. If a subquery’s result must be compared with a group function, you must nest the inner query in the outer query’s HAVING clause.

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 则将值与子查询返回的每个值进行比较。

Multiple-row subqueries are nested queries that can return more than one row of results to the parent query. Multiple-row subqueries are used most commonly in WHERE and HAVING clauses. Since it returns multiple rows,it must be handled by set comparison operators (IN, ALL, ANY).While IN operator holds the same meaning as discussed in earlier chapter, ANY operator compares a specified value to each value returned by the sub query while ALL compares a value to every value returned by a sub query.

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

Below query shows the error when single row sub query returns multiple rows.

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

  1. [> ALL] More than the highest value returned by the subquery

  2. [< ALL] Less than the lowest value returned by the subquery

  3. [< ANY] Less than the highest value returned by the subquery

  4. [> ANY] More than the lowest value returned by the subquery

  5. [= ANY] Equal to any value returned by the subquery (same as IN)

上面的 SQL 可以使用 IN 运算符改写成下面这样的形式。

Above SQL can be rewritten using IN operator like below.

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

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

Note in the above query, IN matches department ids returned from the sub query,compares it with that in the main query and returns employee’s name who satisfy the condition.

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

A join would be better solution for above query, but for purpose of illustration, sub query has been used in it.

Correlated Sub Query

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

As opposed to a regular subquery, where the outer query depends on values provided by the inner query,a correlated subquery is one where the inner query depends on values provided by the outer query. This means that in a correlated subquery,the inner query is executed repeatedly, once for each row that might be selected by the outer query.

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

Correlated subqueries can produce result tables that answer complex management questions.

考虑下面的 SELECT 查询。与之前考虑的子查询不同，此 SELECT 语句中的子查询不能独立于主查询解决。请注意，外部查询指定从具有别名 e1 的 employee 表中选择行。内部查询将 employee 表的员工部门号列（DepartmentNumber）与别名 e2 与别名表名 e1 的相同列进行比较。

Consider the below SELECT query. Unlike the subqueries previously considered, the subquery in this SELECT statement cannot be resolved independently of the main query. Notice that the outer query specifies that rows are selected from the employee table with an alias name of e1. The inner query compares the employee department number column (DepartmentNumber) of the employee table with alias e2 to the same column for the alias table name 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 范围内的员工的历史详细信息。

A multiple-column subquery returns more than one column to the outer query and can be listed in the outer query’s FROM, WHERE, or HAVING clause. For example, the below query shows the employee’s historical details for the ones whose current salary is in range of 1000 and 2000 and working in department 10 or 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 and 20)
ORDER BY first_name;

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

When a multiple-column subquery is used in the outer query’s FROM clause, it creates a temporary table that can be referenced by other clauses of the outer query. This temporary table is more formally called an inline view. The subquery’s results are treated like any other table in the FROM clause. If the temporary table contains grouped data, the grouped subsets are treated as separate rows of data in a table. Consider the FROM clause in the below query. The inline view formed by the subquery is the data source for the main query.

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