A subquery is a SELECT statement that is embedded in a clause of another SQL statement, called the parent statement.
The subquery (inner query) returns a value that is used by the parent statement. Using a nested subquery is equivalent to performing two sequential queries and using the result of the inner query as the search value in the outer query (main query).
:: To provide values for conditions in WHERE, HAVING, and START WITH clauses of SELECT statements
:: To define the set of rows to be inserted into the target table of an INSERT or CREATE TABLE statement
:: To define the set of rows to be included in a view or snapshot in a CREATE VIEW or CREATE SNAPSHOT statement
:: To define one or more values to be assigned to existing rows in an UPDATE statement
:: To define a table to be operated on by a containing query. (You do this by placing the subquery in the FROM clause. This can be done in INSERT, UPDATE, and DELETE statements as well.)
Note: A subquery is evaluated once for the entire parent statement.
先执行内查询,返回值给外查询,再执行主查询.
You can build powerful statements out of simple ones by using subqueries. Subqueries can be very useful when you need to select rows from a table with a
condition that depends on the data in the table itself or some other table. Subqueries are very useful for writing SQL statements that need values based on one or more unknown conditional values.
The subquery is often referred to as a nested SELECT, sub-SELECT, or inner SELECT statement. The inner and outer queries can retrieve data from either the same table or different tables.(叫做内嵌select,子select,内部select)
■多列子查询
Syntax
SELECT column,column, ...
FROM table
WHERE (column,column, ...) IN
(SELECT column,column, ...
FROM table
WHERE condition);
主查询的每行与一个多行多列子查询的值比较
Main query <----------|
WHERE(manager_id,department_id) IN <-------| |
| |
<-----|| |
|| |
Subquery || |
100 90 _______|| |
102 60 ________| |
124 50 ____________|
Multiple-Column Subqueries
So far you have written single-row subqueries and multiple-row subqueries where only one column is returned by the inner SELECT statement and this is used to evaluate the expression in the parent select statement. If you want to compare two or more columns, you must write a compound WHERE clause using logical operators(如果想要比较两行或更多行你要使用逻辑运算符写一个混合的WHERE子句). Using multiple-column subqueries, you can combine duplicate WHERE conditions into a single WHERE clause.(使用混合列子查询可以把多个WHERE条件合到一个WHERE子句.)
The graphic in the slide illustrates that the values of the MANAGER_ID and DEPARTMENT_ID from the main query are being compared with the MANAGER_ID and DEPARTMENT_ID values retrieved by the subquery. Since the number of columns that are being compared are more than one, the example qualifies as a multiple-column subquery.
■列比较
在一个多列子查询中的列比较能够被：
:: 成对地比较
:: 非成对的比较
①成对比较子查询
显示雇员的细节，这些雇员被同一个经理管理，并且，工作在同一个部门，具有 EMPLOYEE_ID 178 或 174
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
(SELECT manager_id, department_id
FROM employees
WHERE employee_id IN (178,174))
AND employee_id NOT IN (178,174);
SQL> select manager_id,department_id
2 from employees
3 where employee_id in (178,174);
MANAGER_ID DEPARTMENT_ID
---------- -------------
149 80
149
SQL> SELECT employee_id, manager_id, department_id
2 FROM employees
3 WHERE (manager_id, department_id) IN
4 (SELECT manager_id, department_id
5 FROM employees
6 WHERE employee_id IN (178,174))
7 ;
EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
----------- ---------- -------------
179 149 80
177 149 80
176 149 80
175 149 80
174 149 80
SQL> SELECT employee_id, manager_id, department_id
2 FROM employees
3 WHERE (manager_id, department_id) IN
4 (SELECT manager_id, department_id
5 FROM employees
6 WHERE employee_id IN (178,174))
7 AND employee_id NOT IN (178,174);
EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
----------- ---------- -------------
179 149 80
177 149 80
176 149 80
175 149 80
Pairwise Comparison Subquery
The example in the slide is that of a multiple-column subquery because the subquery returns more than one column(子查询返回值多于一行). It compares the values in the MANAGER_ID column and the DEPARTMENT_ID column of each row in the EMPLOYEES table with the values in the MANAGER_ID column and the DEPARTMENT_ID column for the employees with the EMPLOYEE_ID 178 or 174.
First, the subquery to retrieve the MANAGER_ID and DEPARTMENT_ID values for the employees with the EMPLOYEE_ID 178 or 174 is executed. These values are compared with the MANAGER_ID column and the DEPARTMENT_ID column of each row in the EMPLOYEES table. If the values match, the row is displayed. In the
output, the records of the employees with the EMPLOYEE_ID 178 or 174 will not be displayed. The output of the query in the slide follows.
②非成对比较子查询
SELECT employee_id, manager_id, department_id 3/
FROM employees
WHERE manager_id IN
(SELECT manager_id 1/
FROM employees
WHERE employee_id IN (174,141))
AND department_id IN
(SELECT department_id 2/
FROM employees
WHERE employee_id IN (174,141))
AND employee_id NOT IN(174,141);
返回的department_id值和manager_id值与departments表中的每一行进行比较.
要两个值同时都满足才display.
Nonpairwise Comparison Subquery
The example shows a nonpairwise comparison of the columns. It displays the EMPLOYEE_ID, MANAGER_ID, and DEPARTMENT_ID of any employee whose manager ID matches any of the manager IDs of employees whose employee IDs are either 174 or 141 and DEPARTMENT_ID match any of the department IDs of employees whose employee IDs are either 174 or 141.
First, the subquery to retrieve the MANAGER_ID values for the employees with the EMPLOYEE_ID 174 or 141 is executed. Similarly, the second subquery to retrieve the DEPARTMENT_ID values for the employees with the EMPLOYEE_ID 174 or 141 is executed. the retrived values of the MANAGER_ID and DEPARTMENT_ID columns are compared with the MANAGER_ID and DEPARTMENT_ID column for each row in the EMPLOYEES table. If the MANAGER_ID column of the row in the EMPLOYEES table matches with any of the values of the MANAGER_ID retrieved by the inner subquery and if the DEPARTMENT_ID column of the row in the EMPLOYEES table matches with any of the values of the DEPARTMENT_ID retrieved by the second subquery, the record is displayed. The output of the query in the slide follows.
■在 FROM 子句中使用子查询
SELECT a.last_name, a.salary,
a.department_id, b.salavg //必须是b 表中的'列'
FROM employees a, (SELECT department_id,
AVG(salary) salavg
FROM employees
GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary > b.salavg;
Using a Subquery in the FROM Clause
You can use a subquery in the FROM clause of a SELECT statement, which is very similar to how views are used. A subquery in the FROM clause of a SELECT statement is also called an inline view(内部视图). A subquery in the FROM clause of a SELECT statement defines a data source for that particular SELECT statement, and only that SELECT statement. The example on the slide displays employee last names, salaries, department numbers, and average salaries for all the employees who earn more than the average salary in their department. The subquery in the FROM clause is named b, and the outer query references the SALAVG column using this alias.
■分级子查询表达式
:: 一个分级子查询表达式是一个从一行中返回确切的一个列值的子查询
:: 在 Oracle8i 中，分级子查询仅在一些有限情况的情况下被支持，例如：
- SELECT 语句 (FROM 和 WHERE 子句)
- 在一个 INSERT 语句中的VALUES 表中
:: 在 Oracle9i 中，分级子查询能够被用于：
- DECODE and CASE 的条件和表达式部分
- 除 GROUP BY 以外所有的 SELECT 子句
Scalar Subqueries in SQL
A subquery that returns exactly one column value from one row is also referred to as a scalar subquery(一个分级子查询表达式是一个从一行中返回确切的一个列值的子查询.) Multiple-column subqueries written to compare two or more columns, using a compound WHERE clause and logical operators, do not qualify as scalar subqueries.
The value of the scalar subquery expression is the value of the select list item of the subquery. If the subquery returns 0 rows, the value of the scalar subquery expression is NULL. If the subquery returns more than one row, the Oracle Server returns an error如果子查询返回0行,分级子查询表达式是NULL,如果子查询返回多行,Oracle Server返回ERROR. . The Oracle Server has always supported the usage of a scalar subquery in a SELECT statement. The usage of scalar subqueries has been enhanced in Oracle9i. You can now use scalar subqueries in:
- Condition and expression part of DECODE and CASE
- All clauses of SELECT except GROUP BY
- In the left-hand side of the operator in the SET clause and WHERE clause of UPDATE statement
However, scalar subqueries are not valid expressions in the following places:
- As default values for columns and hash expressions for clusters
- In the RETURNING clause of DML statements
- As the basis of a function-based index 基本函数索引
- In GROUP BY clauses, CHECK constraints, WHEN conditions
- HAVING clauses
- In START WITH and CONNECT BY clauses
- In statements that are unrelated to queries, such as CREATE PROFILE
eg:
在CASE表达式的分级子查询.
SELECT employee_id, last_name,
(CASE 20 <----|
WHEN department_id = |
(SELECT deaprtment_id FROM deaprtments
WHERE location_id=1800)
THEN 'Canada' ELSE 'USA' END) location
FROM employees;
...
...
EMPLOYEE_ID LAST_NAME LOCATI
----------- ------------------------- ------
199 Grant USA
200 Whalen USA
201 Hartstein Canada
202 Fay Canada
203 Mavris USA
204 Baer USA
...
eg:
在 ORDER BY 子查询中的分级子查询
SELECT employee_id, last_name
FROM employees e //两个表
ORDER BY (SELECT department_name //用deaprtments这个表的department_name排序
FROM departments d
WHERE e.department_id = d.department_id);
■相关的子查询
相关子查询被用于 row-by-row 处理。对外查询的每一行，每个子查询被执行一次
GET
|---- > candidate row from outer query //从外查询中获得候选行.
| |
| EXECUTE
| inner query using candidate row value //从内查询中获得候选行.
| |
| USE
|----values from inner query to qualify //
or disqualify candidate row
Correlated Subqueries
The Oracle Server performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement.
Nested Subqueries Versus Correlated Subqueries
With a normal nested subquery, the inner SELECT query runs first and executes once, returning values to be used by the main query(这是对于一般的查询). A correlated subquery, however, executes once for each candidate row considered by the outer query. In other words, the inner query is driven by the outer query.
Nested Subquery Execution
- The inner query executes first and finds a value. 先内查询
- The outer query executes once, using the value from the inner query.//再外查询
Correlated Subquery Execution .
- Get a candidate row (fetched by the outer query).//从外查询中获得行
- Execute the inner query using the value of the candidate row.
用外查询获得的行执行内查询.
- Use the values resulting from the inner query to qualify or disqualify the candidate.使用从内查询中返回的值限定或不限定行.
- Repeat until no candidate row remains. //重复做直到没有行余下.
eg:
SELECT column1,column2, ...
FROM table1 outer
WHERE column1 operator
( SELECT column1,column2
FROM table2
WHERE expr1= //要有一个关联
outer.expr2);
子查询参考在父查询中的表的一个列
A correlated subquery is one way of reading every row in a table and comparing values in each row against related data. It is used whenever a subquery must return a different result or set of results for each candidate row considered by the main query. In other words, you use a correlated subquery to answer a multipart question whose answer depends on the value in each row processed by the parent statement.
The Oracle Server performs a correlated subquery when the subquery references a column from a table in the parent query. (当一个子查询参考父查询表返回的列.)
Note: You can use the ANY and ALL operators in a correlated subquery.
eg:
找出所有的雇员，他们挣的薪水高于该部门的平均薪水
SELECT last_name, salary, department_id
FROM employees outer
WHERE salary >
|---> (SELECT AVG(salary)
FROM employees
WHERE department_id =
outer.department_id) ;
※外查询中的行每被处理一次，内查询就求值一次
Using Correlated Subqueries
The example in the slide determines which employees earn more than the average salary of their department. In this case, the correlated subquery specifically computes the average salary for each department.
Because both the outer query and inner query use the EMPLOYEES table in the FROM clause, an alias is given to EMPLOYEES in the outer SELECT statement, for clarity. Not only does the alias make the entire SELECT statement more readable, but without the alias the query would not work properly, because the inner statement would not be able to distinguish the inner table column from the outer table column.
eg:
显示雇员的详细信息，这些雇员至少变换过两次工作
SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2 <= (SELECT COUNT(*)
FROM job_history
WHERE employee_id = e.employee_id);
■使用 EXISTS 操作
:: EXISTS 操作对在子查询的结果集中存在的行进行检验
:: 如果一个子查询行值被找到：
- 在内查询中的搜索不再继续
- 条件被标记为 TRUE
:: 如果一个子查询行值未找到：
- 条件被标记为 FALSE
- 在内查询中的搜索继续
如果子查询返回至少一行,则操作返回TRUE,如果没有值返回,则返回FALSE
eg:
查找至少有一个雇员的经理
SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS ( SELECT 'X' //如果返回X,则TRUE,否则FALSE.最后看是不是TRUE,即返回X
FROM employees 的即为满足条件的.
WHERE manager_id =
outer.employee_id);
A IN construct can be used as an alternative for a EXISTS operator, as shown in the following example:
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL);
■使用 NOT EXISTS 操作
找出所有的没有任何雇员的部门
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
FROM employees
WHERE department_id //要有一个联联
= d.department_id);
Alternative Solution
A NOT IN construct can be used as an alternative for a NOT EXISTS operator, as shown in the following example.
SELECT department_id, department_name
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees);
However, NOT IN evaluates to FALSE if any member of the set is a NULL value. 如果集合的任何成员是NULL值,NOT IN 的值是FALSE.因此,即使departments表中满足WHERE条件的行,你的查询将不会返回任何行.
■相关 UPDATE
UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column =
alias2.column);
用一个相关子查询来更新在一个表中的行，该表基于另一个表中的行
Correlated UPDATE
In the case of the UPDATE statement, you can use a correlated subquery to update rows in one table based on rows from another table.
eg:
:: 用一个附加的列来存储部门名称，反向规格化 EMPLOYEES 表
:: 用相关更新填充表
ALTER TABLE employees
ADD(department_name VARCHAR2(30));
UPDATE employees e
SET department_name =
(SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id); //要有一个关联
■相关 DELETE
DELETE FROM table1 alias1
WHERE column operator
(SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
用一个相关子查询删除表中的行，该表基于另一个表中的行
eg:
用一个相关子查询删除哪些在 EMPLOYEES 表和 EMP_HISTORY 表中的 employee_id 列值相同的行
DELETE FROM employees E
WHERE employee_id =
(SELECT employee_id
FROM emp_history
WHERE employee_id = E.employee_id);
■■WITH子句
:: 当一个查询块在一个复杂的查询中出现多次时，使用 WITH 子句，能够在 SELECT 语句中多次使用相同查询块
:: WITH 子句取回查询块的结果，并且将它存在用户的临时表空间中
:: WITH 子句可以改善性能
eg:
WITH 子句: 例子
用 WITH 子句，写一个查询来显示部门名称和该部门的合计薪水，哪些人的合计薪水高于各部门的平均薪水
WITH
dept_costs AS (
SELECT d.department_name, SUM(e.salary) AS dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) AS dept_avg
FROM dept_costs)
SELECT *
FROM dept_costs
WHERE dept_total >
(SELECT dept_avg
FROM avg_cost)
ORDER BY department_name;
