SQL - 子查询解决查询问题

上一页
下一页

子查询最好定义为查询中的查询。子查询使您能够编写查询，这些查询根据在查询运行时实际开发的条件选择数据行。更正式地说，它是在另一个 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_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)

多行子查询

多行子查询是嵌套查询，可以向父查询返回多行结果。多行子查询最常用于 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

多行运算符的使用

  • [> ALL] 大于子查询返回的最高值

  • [< ALL] 小于子查询返回的最低值

  • [< ANY] 小于子查询返回的最高值

  • [> ANY] 大于子查询返回的最低值

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

可以使用 IN 运算符将上述 SQL 重写如下。

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 语句中的子查询不能独立于主查询解析。请注意，外部查询指定从具有别名 e1 的员工表中选择行。内部查询将员工表的员工部门编号列 (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 and 20)
ORDER BY first_name;

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

SELECT * 
FROM (SELECT salary, department_id
	FROM employees
 	WHERE salary BETWEEN 1000 and 2000);
打印页面
上一页
下一页
广告