相关子查询
到现在为止我们所写出的子查询都是独立的它们都没有涉及到其它的子查询相
关子查询可以接受外部的引用从而得到一些令人惊奇的结果请看下边的这个查询
INPUT
SELECT * FROM ORDERS O WHERE 'ROAD BIKE' =
(SELECT DESCRIPTION FROM PART P
WHERE P.PARTNUM = O.PARTNUM)
OUTPUT
ORDEREDON NAME PARTNUM QUANTITY REMARKS
19-MAY-1996 TRUE WHEEL 76 3 PAID
SQL 21 日自学通(V1.0) 翻译人笨猪
147
ORDEREDON NAME PARTNUM QUANTITY REMARKS
19-MAY-1996 TRUE WHEEL 76 3 PAID
17-JAN-1996 BIKE SPEC 76 11 PAID
17-JAN-1996 LE SHOPPE 76 5 PAID
1-JUL-1996 AAA BIKE 76 4 PAID
11-JUL-1996 JACKS BIKE 76 14 PAID
该查询实际上执行了类似下边的归并操作
INPUT
SELECT O.ORDEREDON O.NAME O.PARTNUM O.QUANTITY O.REMARKS
FROM ORDERS O PART P WHERE P.PARTNUM = O.PARTNUM
AND P.DESCRIPTION = 'ROAD BIKE'
OUTPUT
ORDEREDON NAME PARTNUM QUANTITY REMARKS
19-MAY-1996 TRUE WHEEL 76 3 PAID
1-JUL-1996 AAA BIKE 76 4 PAID
17-JAN-1996 LE SHOPPE 76 5 PAID
17-JAN-1996 BIKE SPEC 76 11 PAID
11-JUL-1996 JACKS BIKE 76 14 PAID
分析
事实上除了命令不同以外结果是一样的相关查询的执行情况与归并非常相似
这个相关查询查找了在子查询中指定的内容在本例中相关查询的由于语句所确定
WHERE P.PARTNUM = O.PARTNUM
当在子查询内部对P.PARTNUM 和子查询外部的O.PARTNUM 进行比较时由于
O.PARTNUM 对于每一行均有一个不同的值因此相关查询对每一行都执行了这一语句
下面是ORDERS 表的详细内容
INPUT/OUTPUT:
SELECT * FROM ORDERS
ORDEREDON NAME PARTNUM QUANTITY REMARKS
15-MAY-1996 TRUE WHEEL 23 6 PAID
19-MAY-1996 TRUE WHEEL 76 3 PAID
2-SEP-1996 TRUE WHEEL 10 1 PAID
30-JUN-1996 TRUE WHEEL 42 8 PAID
30-JUN-1996 BIKE SPEC 54 10 PAID
SQL 21 日自学通(V1.0) 翻译人笨猪
148
ORDEREDON NAME PARTNUM QUANTITY REMARKS
30-MAY-1996 BIKE SPEC 10 2 PAID
30-MAY-1996 BIKE SPEC 23 8 PAID
17-JAN-1996 BIKE SPEC 76 11 PAID
17-JAN-1996 LE SHOPPE 76 5 PAID
1-JUN-1996 LE SHOPPE 10 3 PAID
1-JUN-1996 AAA BIKE 10 1 PAID
1-JUL-1996 AAA BIKE 76 4 PAID
1-JUL-1996 AAA BIKE 46 14 PAID
11-JUL-1996 JACKS BIKE 76 14 PAID
它的内容对应着下边的查询
SELECT DESCRIPTION FROM PART P WHERE P.PARTNUM = O.PARTNUM
分析
该操作将返回符合条件P.PARTNUM = O.PARTNUM 的PART 表中的每一个
DESCRIPTION 之后DESCRIPTION 又与下边的语句进行比较操作
WHERE ROAD BIKE =
由于每一行都进行了检查所以这个相关查询可能会返回不止一条内容不要以为返
回多列在WHERE 中就是允许的了它仍会被拒绝本例中只所以会运行是因为
DESCRIPTION 是内容是唯一的如下例你试图将PRICE 与ROAD BIKE 进行比较
那么你将会收到如下信息
INPUT/OUTPUT
SELECT * FROM ORDERS O WHERE 'ROAD BIKE' =
(SELECT PRICE FROM PART P WHERE P.PARTNUM = O.PARTNUM)
conversion error from string "ROAD BIKE"
这是又一个不能运行的例子
SELECT * FROM ORDERS O WHERE 'ROAD BIKE' =
(SELECT * FROM PART P WHERE P.PARTNUM = O.PARTNUM)
分析
在WINDOWS 操作系统中将会导致一个一般保护性错误SQL 引擎无法用=来关联所
有的行
相关查询也可以使用GROUP BY 和HAVING 子句下边的查询是查找特定PART 的
总价并对其按PARTNUM 进行分组
SQL 21 日自学通(V1.0) 翻译人笨猪
149
INPUT/OUTPUT
SELECT O.PARTNUM SUM O.QUANTITY*P.PRICE COUNT PARTNUM
FROM ORDERS O PART P WHERE P.PARTNUM = O.PARTNUM GROUP BY O.PARTNUM
HAVING SUM O.QUANTITY*P.PRICE > SELECT AVG O1.QUANTITY*P1.PRICE
FROM PART P1 ORDERS O1
WHERE P1.PARTNUM = O1.PARTNUM
AND P1.PARTNUM = O.PARTNUM)
PARTNUM SUM COUNT
10 8400.00 4
23 4906.30 2
76 19610.00 5
分析
子查询中不能只有
AVG(O1.QUANTITY*P1.PRICE)
因为子查询与主查询之间需要下边的关联语句
AND P1.PARTNUM = O.PARTNUM
将会计算每一组的平均值然后再与HAVING SUM(O.QUANTITY*P.PRICE)>进行比较
技巧当在相关查询中使用GROUP BY 和HAVING 子句时在HAVING 子句中的列必需
在SELECT 或GROUP BY 子句中存在否则你将会收到一行非法引用的信息因为这时
与子查询对应的是每一组而不是每一行对于组你无法进行比较操作
EXISTS ANY ALL 的使用
EXISTS ANY 和ALL 关键字的用法不像它看上去那么直观如果子查询返回的内容
为非空时EXISTS 返回TRUE 否则返回FALSE 例如
INPUT/OUTPUT
SELECT NAME ORDEREDON FROM ORDERS WHERE EXISTS
(SELECT * FROM ORDERS WHERE NAME ='TRUE WHEEL')
NAME ORDEREDON
TRUE WHEEL 15-MAY-1996
TRUE WHEEL 19-MAY-1996
SQL 21 日自学通(V1.0) 翻译人笨猪
150
NAME ORDEREDON
TRUE WHEEL 2-SEP-1996
TRUE WHEEL 30-JUN-1996
BIKE SPEC 30-JUN-1996
BIKE SPEC 30-MAY-1996
BIKE SPEC 30-MAY-1996
BIKE SPEC 17-JAN-1996
LE SHOPPE 17-JAN-1996
LE SHOPPE 1-JUN-1996
AAA BIKE 1-JUN-1996
AAA BIKE 1-JUL-1996
AAA BIKE 1-JUL-1996
JACKS BIKE 11-JUL-1996
分析
与你所想的并不一样在EXISTS 中的子查询在这个例子中只返回一个值因为从子
查询中返回的行数至少有一行EXIST 返回为TRUE 这就使得表中的所有记录都被显示
了出来如果你把查询改成下边的形式你将不会得到任何结果
SELECT NAME ORDEREDON FROM ORDERS
WHERE EXISTS SELECT * FROM ORDERS WHERE NAME ='MOSTLY HARMLESS')
分析
EXISTS 求得的结果为FALSE 因为MOSTLY HARMLESS 并不是NAME 中的内容
注注意在EXIST 所属的子查询中使用了SELECT * EXIST 并不管返回了多少列
你可以使用EXIST 来检查查询是否确实存在输出从而实现对查询确实有结果才输出
的控制
如果你在相关查询中使用EXISTS 关键字它将会检查你所指出的每一种情况例如
INPUT/OUTPUT
SELECT NAME ORDEREDON FROM ORDERS O WHERE EXISTS
(SELECT * FROM CUSTOMER C WHERE STATE ='NE' AND C.NAME = O.NAME)
NAME ORDEREDON
TRUE WHEEL 15-MAY-1996
TRUE WHEEL 19-MAY-1996
TRUE WHEEL 2-SEP-1996
SQL 21 日自学通(V1.0) 翻译人笨猪
151
NAME ORDEREDON
TRUE WHEEL 30-JUN-1996
AAA BIKE 1-JUN-1996
AAA BIKE 1-JUL-1996
AAA BIKE 1-JUL-1996
与上例相比只有微小的改动不相关的查询返回了所有在内布拉斯加州售出的已有订
单的自行车下边的子查询将返回与CUSTOMER 和ORDERS 相关的所有记录
(SELECT * FROM CUSTOMER C WHERE STATE ='NE' AND C.NAME = O.NAME)
分析
对于相关的记录如果STATE 为NE 则EXISTS 返回为TRUE 否则返回FALSE
与EXISTS 相关的关键字有ALL ANY 和SOME ANY与SOME 具有同样的功能
乐观的人认为它给用户提供了一种选择而悲观的人则认为它使得条件更加复杂化见下
例
INPUT
SELECT NAME ORDEREDON FROM ORDERS WHERE NAME = ANY
(SELECT NAME FROM ORDERS WHERE NAME ='TRUE WHEEL')
OUTPUT
NAME ORDEREDON
TRUE WHEEL 15-MAY-1996
TRUE WHEEL 19-MAY-1996
TRUE WHEEL 2-SEP-1996
TRUE WHEEL 30-JUN-1996
分析
ANY 与子查询中的每一行与主查询进行比较并对子查询中的每一行返回一个TRUE
值
(SELECT NAME FROM ORDERS WHERE NAME ='TRUE WHEEL')
将ANY用SOME 替换将会得到同样的结果
INPUT/OUTPUT
SELECT NAME ORDEREDON FROM ORDERS WHERE NAME = SOME
(SELECT NAME FROM ORDERS WHERE NAME ='TRUE WHEEL')
NAME ORDEREDON
TRUE WHEEL 15-MAY-1996
SQL 21 日自学通(V1.0) 翻译人笨猪
152
NAME ORDEREDON
TRUE WHEEL 19-MAY-1996
TRUE WHEEL 2-SEP-1996
TRUE WHEEL 30-JUN-1996
分析
你可能注意到了它与IN 有些类似使用IN 的相同查询语句如下
INPUT/OUTPUT
SELECT NAME ORDEREDON FROM ORDERS WHERE NAME IN
(SELECT NAME FROM ORDERS WHERE NAME ='TRUE WHEEL')
NAME ORDEREDON
TRUE WHEEL 15-MAY-1996
TRUE WHEEL 19-MAY-1996
TRUE WHEEL 2-SEP-1996
TRUE WHEEL 30-JUN-1996
分析
你已经看到了IN 返回的结果与SOME 或ANY 是相同的是不是它就没有用了呢
当然不是IN 能像下边这样使用吗
INPUT/OUTPUT
SELECT NAME ORDEREDON FROM ORDERS WHERE NAME > ANY
(SELECT NAME FROM ORDERS WHERE NAME ='JACKS BIKE')
NAME ORDEREDON
TRUE WHEEL 15-MAY-1996
TRUE WHEEL 19-MAY-1996
TRUE WHEEL 2-SEP-1996
TRUE WHEEL 30-JUN-1996
LE SHOPPE 17-JAN-1996
LE SHOPPE 1-JUN-1996
回答是否定的IN 只相当于多个等号的作用而ANY 和SOME 则可以使用其它的比
较运算符如大于或小于它是你的一个新增工具
ALL 关键字的作用在于子查询中的所有结果均满足条件时它才会返回TRUE 奇怪吗
ALL 常起双重否定的作用见下例
INPUT/OUTPUT
SELECT NAME ORDEREDON FROM ORDERS WHERE NAME <> ALL
SQL 21 日自学通(V1.0) 翻译人笨猪
153
(SELECT NAME FROM ORDERS WHERE NAME ='JACKS BIKE')
NAME ORDEREDON
TRUE WHEEL 15-MAY-1996
TRUE WHEEL 19-MAY-1996
TRUE WHEEL 2-SEP-1996
TRUE WHEEL 30-JUN-1996
BIKE SPEC 30-JUN-1996
BIKE SPEC 30-MAY-1996
BIKE SPEC 30-MAY-1996
BIKE SPEC 17-JAN-1996
LE SHOPPE 17-JAN-1996
LE SHOPPE 1-JUN-1996
AAA BIKE 1-JUN-1996
AAA BIKE 1-JUL-1996
AAA BIKE 1-JUL-1996
分析
该语句返回了除JACKS BIKE 的所有人<>ALL 只有当左边的内容不存在于右边时才
会返回TRUE 值

总结
今天我们做了不少关于子查询的练习你已经学习了使用SQL 中的重要的一部分你
也已经处理了SQL 中最为困难的一部分— — 相关查询相关查询会在查询与子查询之间建
立一个关系并对子查询中的每一个实例加以关系限制不要为查询的长度所制约你可
以马上从它们中把子查询找出来
问与答
问在一些SQL 实例中对于同一个问题给出了多个解决的方法这是否会导致混乱
答不事实上不是这样多种方法得到相同的结果可以使你的查询更简练这是SQL
的优点
SQL 21 日自学通(V1.0) 翻译人笨猪
154
校练场
1 在嵌套查询部分有一个例子中子查询返回了以下几个数值
LE SHOPPE
BIKE SPEC
LE SHOPPE
BIKE SPEC
JACKS BIKE
其中有一些结果是重复的为什么在最终的结果中没有出现重复
2 下面的话是对还是错
1 汇总函数如SUM AVG COUNT MAX MIN 都返回多个数值
2 子查询最多允许嵌套两层
3 相关查询是完全的独立查询
3 下边的子查询中哪一个是使用ORDERS 表和PART 表工作的
INPUT/OUTPUT
SQL> SELECT * FROM PART;
PARTNUM DESCRIPTION PRICE
54 PEDALS 54.25
42 SEATS 24.50
46 TIRES 15.25
23 MOUNTAIN BIKE 350.45
76 ROAD BIKE 530.00
10 TANDEM 1200.00
INPUT/OUTPUT
SQL> SELECT * FROM ORDERS
ORDEREDON NAME PARTNUM QUANITY REMARKS
15-MAY-96 TRUE WHEEL 23 6 PAID
19-MAY-96 TRUE WHEEL 76 3 PAID
2-SEP-96 TRUE WHEEL 10 1 PAID
30-JUN-96 BIKE SPEC 54 10 PAID
30-MAY-96 BIKE SPEC 10 2 PAID
30-MAY-96 BIKE SPEC 23 8 PAID
17-JAN-96 BIKE SPEC 76 11 PAID
17-JAN-96 LE SHOPPE 76 5 PAID
SQL 21 日自学通(V1.0) 翻译人笨猪
155
1-JUN-96 LE SHOPPE 10 3 PAID
1-JUN-96 AAA BIKE 10 1 PAID
1-JUN-96 AAA BIKE 76 4 PAID
1-JUN-96 AAA BIKE 46 14 PAID
11-JUL-96 JACKS BIKE 76 14 PAID
A SQL> SELECT * FROM ORDERS WHERE PARTNUM =
SELECT PARTNUM FROM PART
WHERE DESCRIPTION = 'TRUE WHEEL';
B SQL> SELECT PARTNUM FROM ORDERS WHERE PARTNUM =
(SELECT * FROM PART
WHERE DESCRIPTION = 'LE SHOPPE');
C SQL> SELECT NAME, PARTNUM FROM ORDERS WHERE EXISTS
(SELECT * FROM ORDERS
WHERE NAME = 'TRUE WHEEL')
练习
应用ORDERS 表来写一个查询返回所以字母顺序排列在JACKS BIKE 之后的NAMES
和ODEREDON 数据

第一周回顾
在第一周中我们简要地回顾了一下SQL 的历史第一周的重要是在SQL 的SELECT
语句上下边给出的内容是关于SELECT 语句的语法与我们学习的前后对照情况表
l SELECT [DISTINCT | ALL] (Day 2)--Columns (Day 1), Functions (Day 4)
l FROM (Day 2)--Tables or Views (Day 1), Aggregate Functions (Day 4)
l WHERE (Day 5)--Condition (Day 3), Join (Day 6), Subquery (Day 7)
l GROUP BY (Day 5)--Columns (Day 3)
l HAVING (Day 5)--Aggregate Function (Day 4)
l UNION | INTERSECT (Day 3)--(Placed between two SELECT statements)
l ORDER BY (Day 5)--Columns (Day 1)
假如你需要在你的程序中建立成千上万个查询的话你会发现它们中的80%将会以
SELECT 开头而余下的20%将在第二周中提到
预览
在第二周中我们将会学习到数据库管理的新技能我们将会学习如何
l 建立和删除一个表
l 为你的朋友提供访问权限和有效地防止你的敌人
l 更新和删除表中的数据

第一周学完了，请看《SQL 21 日自学通－2》