SQL入门:相关子查询

　　允许引用先前提到的任何表的子查询称为相关子查询。我们也说该子查询具有对主查询中表的相关引用。
　　
　　下列示例是一个不相关子查询，该子查询列出部门 'A00' 中薪水超过该部门平均薪水的雇员的雇员编号和姓名：
　　
　　
　　 SELECT EMPNO, LASTNAME
　　　　FROM EMPLOYEE
　　　　WHERE WORKDEPT = 'A00'
　　　　　AND SALARY > (SELECT AVG(SALARY)
　　　　　　　　　　　　　 FROM EMPLOYEE
　　　　　　　　　　　　　 WHERE WORKDEPT = 'A00')
　　
　　如果想要知道每个部门的平均薪水，则需要对每个部门计算一次子查询。对在外层查询中标识的表的每一行，各使用一次 SQL 的相关功能（该能力允许您编写重复执行的子查询），就可做到这一点。此类型的相关子查询用来计算外层表的每一行的某个特性，该特性是在子查询中计算谓词所需要的。
　　
　　此示例显示薪水高于部门平均薪水的所有雇员：
　　
　　
　　 SELECT E1.EMPNO, E1.LASTNAME, E1.WORKDEPT
　　　　FROM EMPLOYEE E1
　　　　WHERE SALARY > (SELECT AVG(SALARY)
　　　　　　　　　　　　　 FROM EMPLOYEE E2
　　　　　　　　　　　　　 WHERE E2.WORKDEPT = E1.WORKDEPT)
　　　　ORDER BY E1.WORKDEPT
　　
　　在此查询中，对每个部门计算一次子查询。结果为：
　　　
　　要编写带有相关子查询的查询，使用与带有子查询的普通外部查询相同的基本格式。然而，在外部查询的 FROM 子句中，只是在表名后面放置一个相关名。于是子查询可能包含由该相关名限定的列引用。例如，如果 E1 是相关名，则 E1.WORKDEPT 表示外部查询中表的当前行的工作部门值。在外部查询中对表的每一行（概念上）重新计算子查询。
　　
　　通过使用相关子查询，可以使系统为您工作并减少需要在应用程序中编写的代码量。
　　
　　DB2 中允许非限定相关引用。例如，表 EMPLOYEE 有一个命名为 LASTNAME 的列，表 SALES 有一个命名为 SALES_PERSON 的列，但没有命名为 LASTNAME 的列。
　　
　　
　　 SELECT LASTNAME, FIRSTNME, COMM
　　　　FROM EMPLOYEE
　　　　WHERE 3 > (SELECT AVG(SALES)
　　　　　　　　　　　FROM SALES
　　　　　　　　　　　WHERE LASTNAME = SALES_PERSON)
　　
　　在此示例中，系统检查最内层的 FROM 子句，以获取 LASTNAME 列。如果未找到 LASTNAME 列，则系统检查次最内层的 FROM 子句（此情况下为外部 FROM 子句）。虽然不总是必要的，还是建议限定相关引用以改进查询的可读性并确保获取想要的结果。
　　
　　
　　实现相关子查询
　　
　　
　　想何时使用相关子查询？列函数的使用有时是一条线索。
　　
　　假定您想要列出教育级别高于部门平均值的雇员。
　　
　　首先，您必须确定选择列表项。问题为 "List the employees"。这隐含着来自 EMPLOYEE 表中的 EMPNO 应该足以唯一标识雇员。该问题也将 EDLEVEL 和雇员的部门 WORKDEPT 说明为条件。当问题未明确要求显示列时，在选择列表中包括这些列将会有助于说明解法。现在可构造查询的一部分：
　　
　　 SELECT LASTNAME, WORKDEPT, EDLEVEL
　　　　FROM EMPLOYEE
　　
　　接着需要搜索条件（WHERE子句）。问题语句为 "...whose level of education is higher than the average for that employee's department"。这意味着对于表中每个雇员，必须计算该雇员所在部门的平均教育级别。此语句适合相关子查询的说明。正在对每行计算某个特性（当前雇员所在部门的平均教育级别）。 EMPLOYEE 表需要相关名：
　　
　　 SELECT LASTNAME, WORKDEPT, EDLEVEL
　　　　FROM EMPLOYEE E1
　　
　　需要的子查询较简单。该子查询计算每个部门的平均教育级别。完整的 SQL 语句为：
　　
　　 SELECT LASTNAME, WORKDEPT, EDLEVEL
　　　　FROM EMPLOYEE E1
　　　　WHERE EDLEVEL > (SELECT AVG(EDLEVEL)
　　　　　　　　　　　　　　FROM EMPLOYEE　E2
　　　　　　　　　　　　　　WHERE E2.WORKDEPT = E1.WORKDEPT)
　　
　　结果为：
　　　
　　
　　假定不列出雇员的部门编号，则应列出部门名称。需要的信息(DEPTNAME)在独立表(DEPARTMENT)中。定义相关变量的外层查询也可以是连接查询 （参见从多个表中选择数据以了解详情）点击查看。
　　
　　当在外层查询中使用连接时，列出要在 FROM 子句中连接的表，并将相关名放在这些表名的任何一个表名旁边。
　　
　　要修改查询以列出部门名称而不是部门编号，在选择列表中用 DEPTNAME 替换 WORKDEPT。 FROM 子句现在也必须包括 DEPARTMENT 表，并且 WHERE 子句必须表示适当的连接条件。
　　
　　以下是修改的查询：
　　
　　 SELECT LASTNAME, DEPTNAME, EDLEVEL
　　　　FROM EMPLOYEE E1, DEPARTMENT
　　　　WHERE E1.WORKDEPT = DEPARTMENT.DEPTNO
　　　　AND EDLEVEL > (SELECT AVG(EDLEVEL)
　　　　　　　　　　　　　FROM EMPLOYEE E2
　　　　　　　　　　　　　WHERE E2.WORKDEPT = E1.WORKDEPT)
　　
　　上例显示，必须在包含相关子查询的某个查询的 FROM 子句中定义用于子查询中的相关名。然而，这种包含可能涉及若干层嵌套。
　　
　　假定某些部门只有几个雇员，因此这些部门的平均教育级别可能是错误的。可以决定，为了使平均教育级别在用于比较雇员时是有意义的数字，一个部门中必须至少有 5 个雇员。因此现在必须列出教育级别高于雇员所在部门平均值的雇员，并只考虑至少有 5 个雇员的部门。
　　
　　该问题暗含另一个子查询，因为对于外层查询中每个雇员来说，必须计算该雇员所在部门的雇员总数：
　　
　　 SELECT COUNT(*)
　　　　FROM EMPLOYEE E3
　　　　WHERE E3.WORKDEPT = E1.WORKDEPT
　　
　　仅当计数大于或等于 5 时才计算平均值：
　　
　　 SELECT AVG(EDLEVEL)
　　　　FROM EMPLOYEE E2
　　　　WHERE E2.WORKDEPT = E1.WORKDEPT
　　　　AND 5 <= (SELECT COUNT(*)
　　　　　　　　　　 FROM EMPLOYEE　E3
　　　　　　　　　　 WHERE E3.WORKDEPT = E1.WORKDEPT)
　　
　　最后，只包括其教育级别高于部门平均值的那些雇员：
　　
　　 SELECT LASTNAME, DEPTNAME, EDLEVEL
　　　　FROM EMPLOYEE E1, DEPARTMENT
　　　　WHERE E1.WORKDEPT = DEPARTMENT.DEPTNO
　　　　AND EDLEVEL >
　　　　(SELECT AVG(EDLEVEL)
　　　　　　FROM EMPLOYEE E2
　　　　　　WHERE E2.WORKDEPT = E1.WORKDEPT
　　　　　　AND 5 <=
　　　　　　(SELECT COUNT(*)
　　　　　　　　FROM EMPLOYEE E3
　　　　　　　　WHERE E3.WORKDEPT = E1.WORKDEPT))
　　
　　此语句产生下列结果：