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);
广告