第4讲 子查询(补充)
子查询
例如：
示例：如何显示与smith在同一部门的所有员工
select * from emp where deptno = (select deptno from emp where ename=‘SMITH’);
多行子查询
语法格式： WHERE 表达式 [NOT] IN (子查询) 例如：查询和部门10号的工作相同的雇员的姓名、岗位、 工资、部门号 select * from emp where job in (select distinct job from emp where deptno=10);
在多行子查询中使用all 例：查询工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
多列子查询
单行子查询是指只返回单行、单列数据，多行子查询是指返 回单列多行数据，都是针对单列而言的，而多列子查询是指 查询返回多个列数据的子查询语句。 例如：查询和smith的部门和岗位完全一致的雇员信息 //smith的部门号和职位 select deptno，job from emp where ename=‘SMITH’ ； select * from emp where (deptno，job)=(select deptno ，job from emp where enamel=‘SMITH’);
在多行子查询中使用any 例：查询工资比部门30的任意一个员工的工资高的姓名、工资、部门号 select ename，sal，deptno from emp where sal>any(select sal from emp where deptno=30); select ename，sal，deptno from emp where sal>(select min(sal) from emp where deptno=30);
04_01_Oracle子查询
子查询的使用规则
表示运算符既可以是单行运算符（如>、=、>=等）,也可以是多 行运算符，如IN、ANY、ALL、SOME等。 子查询必须用括号括起来，否则无法判断子查询语句的开始和 结束。 子查询只能出现在WHERE子句中比较运算符的右边。 不能在子查询语句中使用ORDER BY子句。 子查询允许嵌套多层。 在子查询中可以使用单行运算符和多行运算符。
Oracle子查询时，称之为子查询。 子查询可以嵌套，嵌套子查询的求解次序是由里向 外，即先处理子查询，然后将其查询的结果用于其 父查询。使用嵌套子查询的方法，可以用一系列简 单的查询构成复杂的查询，从而明显增强SQL语句的 功能，Oracle数据库没有限制在FROM子句中的嵌套层数，可
相关子查询
相关子查询是指引用了父查询中某些表或某些列的子查 询(但父查询不能引用子查询中的表或列)。
示例5：显示每个部门最高工资的雇员编号、雇员姓名、职位、部门号和工资。 SELECT EMPNO,ENAME,JOB,DEPTNO,SAL FROM EMP E WHERE EXISTS (SELECT MAX(SAL) FROM EMP EM WHERE E.DEPTNO =EM.DEPTNO); 示例6：显示工作在NEW YORK的雇员信息。 SELECT * FROM 员工表 E WHERE EXISTS (SELECT * FROM 部门表 D WHERE E.DEPTNO = D.DEPTNO AND D.LOC=‘NEW YORK’); 示例7：找出所有名字长度为2的学生。 select * from stu s1 where exists (select * from stu s2 where length(name)=2 and s1.sid=s2.sid);
子查询语法
子查询语法子查询语法是数据库查询语言中的一个重要部分，它可以在一个查询语句中嵌套另一个查询语句，用于获取更复杂的查询结果。
本文将介绍子查询语法的基本用法和常见应用场景。
一、子查询语法的基本用法子查询语法可以在SELECT、FROM、WHERE等子句中使用，用于实现更复杂的查询逻辑。
以下是子查询语法的基本用法：1. 在SELECT子句中使用子查询：SELECT column_nameFROM table_nameWHERE column_name = (SELECT column_name FROM table_name WHERE condition);2. 在FROM子句中使用子查询：SELECT column_nameFROM (SELECT column_name FROM table_name WHERE condition) AS alias_name;3. 在WHERE子句中使用子查询：SELECT column_nameFROM table_nameWHERE column_name IN (SELECT column_name FROM table_nameWHERE condition);1. 子查询与聚合函数的结合使用：SELECT column_nameFROM table_nameWHERE column_name > (SELECT AVG(column_name) FROM table_name);2. 子查询与EXISTS关键字的结合使用：SELECT column_nameFROM table_name t1WHERE EXISTS (SELECT column_name FROM table_name t2 WHERE t2.column_name = t1.column_name);3. 子查询与ANY/ALL关键字的结合使用：SELECT column_nameFROM table_nameWHERE column_name > ALL (SELECT column_name FROM table_name WHERE condition);4. 子查询与JOIN语句的结合使用：SELECT column_nameFROM table_name t1INNER JOIN (SELECT column_name FROM table_name WHEREcondition) t2ON t1.column_name = t2.column_name;5. 子查询与UPDATE语句的结合使用：UPDATE table_nameSET column_name = (SELECT column_name FROM table_name WHERE condition)WHERE condition;6. 子查询与DELETE语句的结合使用：DELETE FROM table_nameWHERE column_name IN (SELECT column_name FROM table_name WHERE condition);三、子查询语法的注意事项1. 子查询返回的结果集只能有一列，如果需要返回多列结果，可以使用JOIN语句；2. 子查询的性能较低，如果能使用其他方式替代子查询，应尽量避免使用子查询；3. 子查询嵌套层数过多会导致查询效率低下，应尽量简化查询逻辑。
子查询_精品文档
子查询子查询（Subquery）是一种在SQL查询语句中嵌套执行的查询。
它允许我们使用嵌套的查询语句来从查询结果中取得更详细的信息，或者用它作为其他查询语句的一部分来过滤数据。
子查询在数据库查询和数据分析中起到了很重要的作用，它可以提供更高效、更准确的结果。
在使用子查询之前，我们需要了解一些基本的语法规则和使用方式。
首先，子查询通常出现在查询的SELECT、FROM、WHERE等子句中，可以嵌套多层。
并且，子查询可以返回单个值、单个列或者多个列的结果集。
对于返回单个值的子查询，我们可以在查询语句的WHERE子句中使用它来进行条件判断。
例如，我们可以使用子查询来查询销售额高于平均销售额的产品：```SELECT product_nameFROM productsWHERE product_sales > (SELECT AVG(product_sales) FROM products)```这个查询将返回销售额高于平均销售额的产品名称。
子查询也可以返回单个列的结果集。
我们可以在查询语句的SELECT子句或FROM子句中使用这种子查询。
比如，我们可以使用子查询来获取每个部门的员工数量：```SELECT department_name, (SELECT COUNT(*) FROM employees WHERE departments.department_id = employees.department_id) AS employee_countFROM departments```这个查询将返回每个部门的员工数量。
除此之外，子查询还可以返回多个列的结果集。
在这种情况下，我们可以将子查询作为派生表（Derived table）来使用。
派生表是一种临时表格，可以像普通表格一样在查询中使用。
例如，我们可以使用子查询来获取每个部门的销售额：```SELECT departments.department_name, sales.sub_totalFROM departmentsINNER JOIN (SELECT department_id, SUM(order_total) AS sub_total FROM orders GROUP BY department_id) AS salesON departments.department_id = sales.department_id```这个查询将返回每个部门的销售额。
第四章 SQL查询(子查询)
嵌套子查询（之一） 嵌套子查询（之一）
把子查询作为派生表
可以用子查询产生一个派生的表，用于代 替 FROM 子句中的表。
USE northwind select T.productname from(select productname, unitprice from products) as T where T.productname = ‘chai’ GO
嵌套子查询（之三） 嵌套子查询（之三）
举例：查询1998年1月1日，签订单的雇 员的姓名。
USE northwind select distinct lastname,firstname from employees as e inner join orders as o on e.employeeid = o.employeeid where o.orderdate = '1998-1-1' GO 用联接的 方式实现
嵌套子查询（之二） 嵌套子查询（之二）
在 Transact-SQL 中，所有使用表达式的地方，都 可以使用子查询代替 当子查询被用作表达式时，子查询可以认为是被 当作一个表达式处理并计算。
举例：查询产品名称为’chai’的产品价格与所有产品的平均价格的差价。 USE northwind select productname,(unitprice – (select avg(unitprice) from products)) as ‘diff’ from products where productname = 'chai' GO
嵌套子查询（之三）（续 嵌套子查询（之三）（续） ）（
子查询可以用于提供一系列的值，这些值可 以出现在 WHERE 子句中的 IN 关键字中。
7-2 第4章 数据库的查询 子查询
（2）比较子查询
其中，expression为要进行比较的表达式，subquery是 子查询。ALL、SOME和ANY说明对比较运算的限制。 ALL指定表达式要与子查询结果集中的每个值都进行比较， 当表达式与每个值都满足比较的关系时，才返回TRUE，否 则返回FALSE； SOME或ANY是同义词，表示表达式只要与子查询结果集 中的某个值满足比较的关系时，就返回TRUE，否则返回 FALSE。
子查询
【例2】 查找在XSCJ数据库中选修了课程号为206的课程的学生的姓名、 学号。
SELECT 姓名,学号 FROM XS WHERE 学号 IN ( SELECT 学号 FROM XS_KC WHERE 课程号 = '206' );
子查询
【练习2】 查找分数为90的学生的姓名、学号。
子查询
expression { < | <= | = | > | >= | != | <> } { ALL | SOME | ANY } ( subquery )
（2）比较子查询
其中，expression为要进行比较的表达式，subquery是 子查询。ALL、SOME和ANY说明对比较运算的限制。 如果子查询的结果集只返回一行数据时，可以通过比较运 算符直接比较。
子查询
【例4】查找年龄最大的学生姓名
select 姓名,出生时间 from xs where 出生时间= ( select min(出生时间) from xs );
子 查 询
回TRUE，否则返回FALSE。若使用 了NOT选项，则返回值相反。
嵌套查询的执行顺序是首先执行括 号中的子查询即内查询，产生一个结 果集，然后在结果集中再执行外查询， 因此子查询要放在括号中。
〖例4-46〗查询调度过AX1320车的 所有调度员姓名、职务和电话。 SELECT 姓名, 职务, 电话
FROM jsy WHERE EXISTS (SELECT 主驾
FROM xc WHERE 主驾=jsy. 图4.47 驾照号 AND 出车单号=‘7013’)
该例子查询的条件表达式里使用了 外查询的列值，查询过程是这样的： 外查询从jsy表第一行开始扫描，子 查询用外查询表第一行的列值计算其 条件表达式，得到一个子查询结果— 空，外查询根据这个结果计算自己的 条件表达式值，得到结果—FALSE，
再根据这个结果判断第一行是否被选 择—否。然后扫描外查询表jsy表的 第二行，扫描第二行的情况与第一行 一样。当扫描外查询表jsy表的第三 行时，子查询用外查询表第三行的列 值计算其条件表达式，得到一个子
查询结果—非空，外查询根据这个结 果计算自己的条件表达式值，得到结 果—TRUE，再根据这个结果判断第 三行是否被选择—是。然后扫描外查 询表jsy表的第四行，同样的过程依 次进行，直至外查询表最后一行扫描 完毕。可以看出在EXISTS关键字子
SQL_server
3. 使用比较运算符
使用比较运算符可将表达式的值与 子查询结果进行比较运算，其格式为:
expression{< | > | = | <= | >= | <> | !> | !< | != } | {ALL | SOME | ANY }(subquery)
子查询的用法
子查询是一种在SQL语句中嵌套另一个SQL语句的查询方式。
子查询可以在SELECT、INSERT、UPDATE或DELETE语句中使用，以根据内部查询的结果执行外部查询。
以下是子查询的一些常见用法：1.在SELECT语句中使用子查询：sqlSELECT column1, column2FROM table1WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);上述查询将从table1中选择满足table2中条件的column1和column2。
2.在INSERT语句中使用子查询：sqlINSERT INTO table1 (column1, column2)SELECT column1, column2FROM table2WHERE condition;上述查询将从table2中选择满足条件的记录，并将结果插入到table1中。
3.在UPDATE语句中使用子查询：sqlUPDATE table1SET column1 = value1, column2 = value2WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);上述查询将更新table1中满足table2中条件的记录的column1和column2列。
4.在DELETE语句中使用子查询：sqlDELETE FROM table1WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);上述查询将从table1中删除满足table2中条件的记录。
需要注意的是，子查询的返回结果可以是单个值、多行单列或多行多列，具体取决于外部查询的要求。
另外，子查询可以嵌套在主查询的任何位置，以根据内部查询的结果执行相应的操作。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
16/13
最后，只包括其教育级别高于部门平均值的那些雇员： SELECT LASTNAME, DEPTNAME, EDLEVEL FROM EMPLOYEE E1, DEPARTMENT WHERE E1.WORKDEPT = DEPARTMENT.DEPTNO AND EDLEVEL > (SELECT AVG SELECT AVG(EDLEVEL) FROM EMPLOYEE E2 WHERE E2.WORKDEPT = E1.WORKDEPT AND 5 <= (SELECT COUNT SELECT COUNT(*) FROM EMPLOYEE E3 WHERE E3.WORKDEPT = E1.WORKDEPT))
SELECT LASTNAME, WORKDEPT, EDLEVEL FROM EMPLOYEE
9/13
接着需要搜索条件（WHERE子句）。问题语句为 "...whose level of education is higher than the average for that employee's department"。这意味着对于表中每个雇员， 必须计算该雇员所在部门的平均教育级别。此语句适合相关 子查询的说明。正在对每行计算某个特性（当前雇员所在部 门的平均教育级别）。 EMPLOYEE 表需要相关名：
20/13
表达式 > ALL （全查询） 如果该表达式大于由全查询 返回的每个单值，则该谓词为真。如果全查询未返回值， 则该谓词为真。如果指定的关系至少对一个值为假，则 结果为假。注意：<>ALL 定量谓词相当于 NOT IN 谓词。
21/13
下列示例使用子查询和> ALL 比较来寻找收入超过所有 经理的所有雇员的姓名和职业： SELECT LASTNAME, JOB FROM EMPLOYEE WHERE SALARY > ALL (SELECT SALARY FROM EMPLOYEE SELECT WHERE JOB='MANAGER')
SELECT LASTNAME, WORKDEPT, EDLEVEL FROM EMPLOYEE E1
10/13
需要的子查询较简单。该子查询计算每个部门的平均教育级 别。完整的 SQL 语句为：
SELECT LASTNAME, WORKDEPT, EDLEVEL EMPLOYEE E1 WHERE EDLEVEL > (SELECT AVG SELECT AVG(EDLEVEL) FROM EMPLOYEE E2 WHERE E2.WORKDEPT = E1.WORKDEPT) )
12/13
以下是修改的查询： SELECT LASTNAME, DEPTNAME, EDLEVEL FROM EMPLOYEE E1, DEPARTMENT WHERE E1.WORKDEPT = DEPARTMENT.DEPTNO AND EDLEVEL > (SELECT SELECT AVG(EDLEVEL) AVG FROM EMPLOYEE E2 WHERE E2.WORKDEPT = E1.WORKDEPT)
17/13
使用 EXISTS 谓词
可使用子查询来测试满足某个条件的行的存在性。在此 情况下，谓词 EXISTS 或 NOT EXISTS 将子查询链接到 外层查询。 当用 EXISTS 谓词将子查询链接到外层查询时，该子查 询不返回值。相反，如果子查询的回答集包含一个或更 多个行，则 EXISTS 谓词为真；如果回答集不包含任何 行，则 EXISTS 谓词为假。
7/13
在此示例中，系统检查最内层的 FROM 子句，以获取 LASTNAME 列。如果未找到 LASTNAME 列，则系统检查次 最内层的 FROM 子句（此情况下为外部 FROM 子句）。 虽然不总是必要的，还是建议限定相关引用以改进查询 的可读性并确保获取想要的结果。
8/13
实现相关子查询
15/13
该问题暗含另一个子查询，因为对于外层查询中每个雇员来说，必须 该问题暗含另一个子查询 计算该雇员所在部门的雇员总数： SELECT COUNT(*) FROM EMPLOYEE E3 WHERE E3.WORKDEPT = E1.WORKDEPT 仅当计数大于或等于 5 时才计算平均值： AVG(EDLEVEL) SELECT AVG FROM EMPLOYEE E2 WHERE E2.WORKDEPT = E1.WORKDEPT AND 5 <= (SELECT COUNT SELECT COUNT(*) FROM EMPLOYEE E3 WHERE E3.WORKDEPT = E1.WORKDEPT)
22/13
表达式 > ANY （全查询） 如果表达式至少大于由全查 询返回的值之一，则该谓词为真。如果全查询未返回值， 则该谓词为假。注意：=ANY 定量运算符相当于 IN 谓词。 表达式 > SOME（全查询） SOME 与 ANY 同义。
23/13
运算符 >ALL <ALL <ANY >ANY =ANY
18/13
通常将 EXISTS 谓词与相关子查询一起使用。下面示例 列出当前在项目(PROJECT) 表中没有项的部门： SELECT DEPTNO, DEPTNAME FROM DEPARTMENT X WHERE NOT EXISTS (SELECT * FROM PROJECT WHERE SELECT DEPTNO = X.DEPTNO) ORDER BY DEPTNO
5/13
要编写带有相关子查询的查询，使用与带有子查询的普 通外部查询相同的基本格式。然而，在外部查询的 FROM 子句中，只是在表名后面放置一个相关名 在表名后面放置一个相关名。于是子查询 在表名后面放置一个相关名 可能包含由该相关名限定的列引用。例如，如果 E1 是 相关名，则 E1.WORKDEPT 表示外部查询中表的当前行的 工作部门值。在外部查询中对表的每一行（概念上）重 新计算子查询。
13/13
上例显示，必须在包含相关子查询的某个查询的 FROM 子句中定义用于子查询中的相关名。然而，这种包含可 能涉及若干层嵌套。
14/13
例3：假定某些部门只有几个雇员，因此这些部门的平均 教育级别可能是错误的。可以决定，为了使平均教育级 别在用于比较雇员时是有意义的数字，一个部门中必须 至少有 5 个雇员。因此现在必须列出教育级别高于雇员 所在部门平均值的雇员，并只考虑至少有 5 个雇员的部 门。
可通过在外层查询的 WHERE 子句中使用 AND 和 OR 将 EXISTS 和 NOT EXISTS 谓词与其他谓词连接起来。
19/13
定量谓词
定量谓词将一个值和值的集合进行比较。如果全查询返 回多个值，则必须通过附加后缀 ALL、ANY 或 SOME 来 修改谓词中的比较运算符。这些后缀确定如何在外层谓 词中处理返回的这组值。使用>比较运算符作为示例（下 面的注释也适用于其他运算符）：
说明 大于子查询返回的最大值 小于子查询返回的最小值 小于子查询返回的最大值 大于子查询返回的最小值 等于子查询返回的任何值（于IN相同）
6/13
通过使用相关子查询，可以使系统为您工作并减少需要 在应用程序中编写的代码量。 中允许非限定相关引用。 DB2 中允许非限定相关引用。例如，表 EMPLOYEE 有一 个命名为 LASTNAME 的列，表 SALES 有一个命名为 SALES_PERSON 的列，但没有命名为 LASTNAME 的列。 SELECT LASTNAME, FIRSTNME, COMM FROM EMPLOYEE WHERE 3 > (SELECT AVG(SALES) FROM SALES WHERE LASTNAME = SALES_PERSON)
4/13
SELECT E1.EMPNO, STNAME, E1.WORKDEPT FROM EMPLOYEE E1 WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE E2 WHERE E2.WORKDEPT = E1.WORKDEPT) ORDER BY E1.WORKDEPT
想何时使用相关子查询？列函数的使用有时是一条线索。 例1：假定您想要列出教育级别高于部门平均值的雇员。 假定您想要列出教育级别高于部门平均值的雇员。
首先，您必须确定选择列表项。问题为 "List the employees"。这隐含着来自 EMPLOYEE 表中的 EMPNO 应该足 以唯一标识雇员。该问题也将 EDLEVEL 和雇员的部门 WORKDEPT 说明为条件。当问题未明确要求显示列时，在选择 列表中包括这些列将会有助于说明解法。现在可构造查询的 一部分：
子查询（补充） 子查询（补充到的任何表的子查询称为相关子查询。 我们也说该子查询具有对主查询中表的相关引用。 我们也说该子查询具有对主查询中表的相关引用。
3/13
此示例显示薪水高于部门平均薪水的所有雇员：
如果想要知道每个部门的平均薪水，则需要对每个部门计算 一次子查询。对在外层查询中标识的表的每一行，各使用一 次 SQL 的相关功能（该能力允许您编写重复执行的子查询）， 就可做到这一点。此类型的相关子查询用来计算外层表的每 一行的某个特性，该特性是在子查询中计算谓词所需要的。
FROM
11/13
例2：假定不列出雇员的部门编号，则应列出部门名称。 需要的信息(DEPTNAME)在独立表(DEPARTMENT)中。定义 定义 相关变量的外层查询也可以是连接查询。 相关变量的外层查询也可以是连接查询。 当在外层查询中使用连接时，列出要在 FROM 子句中连 接的表，并将相关名放在这些表名的任何一个表名旁边。 要修改查询以列出部门名称而不是部门编号，在选择列 表中用 DEPTNAME 替换 WORKDEPT。 FROM 子句现在也必 须包括 DEPARTMENT 表，并且 WHERE 子句必须表示适当 的连接条件。