子查询:子查询是一种把查询的结果作为参数返回给另一个查询的一种查询。子查询可以让你将多个查询绑定在一起。
两表的共有字段是partnum, 假如你不知道这个字段,但是你又想用 PART 表的 description 字段来工作 这时可以使用子查询语句。
select * FROM ORDERS
where PARTNUM =
(select partnum from PART where DESCRIPTION like "ROAD%");
1、在子查询中使用汇总函数
像 SUM、AVG、COUNT、MIN 和 MAX 等汇总函数均返回单一的数值。
计算定单的平均金额:
select AVG(O.QUANTITY * P.PRICE)
from ORDERS O, PART P
where O.PARTNUM = P.PARTNUM;
这条语句只返回一个平均值,如果你想找一下都有哪些定单的金额高于平均值的话,可以将上述语句使用子查询 。
select O.NAME,O.ORDEREDON,O.QUANTITY * P.PRICE TOTAL
from ORDERS O ,PART P
where O.PARTNUM = P.PARTNUM
and
O.QUANTITY * P.PRICE> (SELECT AVG(O.QUANTITY * P.PRICE)
from ORDERS O,PART P
where O.PARTNUM = P.PARTNUM);
2、子查询的嵌套
嵌套就是将一个子查询嵌入到另一个子查询中去。子查询的嵌套深度根据你的需要而定。
例如:如果你想给那些花费超过平均价格的客户发一个特别的通知。
select ALL C.NAME,C.ADDRESS,C.STATE,C.ZIP
from CUSTOMER C
where C.NAME in
(select O.NAME from ORDERS O, PART P
where O.PARTNUM = P.PARTNUM
and
O.QUANTITY * P.PRICE> (select AVG(O.QUANTITY * P.PRICE)
from ORDERS O,PART P
where O.PARTNUM = P.PARTNUM));
3、相关子查询
相关子查询可以接受外部的引用。
select * from ORDERS O where 'ROAD BIKE' =
(select DESCRIPTION from PART P
where P.PARTNUM = O.PARTNUM);
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';
除了命令不同以外上述两个语句运行结果是一样的,相关查询的执行情况与归并非常相似。
where P.PARTNUM = O.PARTNUM ;当在子查询内部对 P.PARTNUM 和子查询外部的 O.PARTNUM 进行比较时,由于 O.PARTNUM 对于每一行均有一个不同的值,因此相关查询对每一行都执行了这一语句。
相关查询也可以使用 GROUP BY 和 HAVING 子句,当在相关查询中使用 GROUP BY 和 HAVING 子句时,在 HAVING 子句中的列必需在 SELECT 或 GROUP BY 子句中存在,否则你将会收到一行非法引用的信息 ,因为这时与子查询对应的是每一组而不是每一行,对于组你无法进行比较操作。
4、EXISTS、ANY、ALL 的使用
如果子查询返回的内容为非空时 EXISTS 返回 TRUE 否则返回 FALSE。
例如:查询姓名为“TRUE WHEEL”的订单日期。
select NAME,ORDEREDON
from ORDERS
where EXISTS
(select * from ORDERS where NAME ='TRUE WHEEL');
在 EXISTS 中的子查询在这个例子中只返回一个值,因为从子查询中返回的行数至少有一行, EXIST 返回为 TRUE, 表中的所有记录都被显示了出来。
如果改为:
select NAME,ORDEREDON
from ORDERS
where EXISTS
(select * from ORDERS where NAME ='XIAO XIAO');
则EXISTS求得的结果为FALSE,NAME里边并没有“XIAO XIAO”这一行。
注:在 EXIST 所属的子查询中使用了 SELECT * ,EXIST 并不管返回了多少行。行的数量取决于查询的表的行数。
如果你在相关查询中使用 EXISTS 关键字,它将会检查你所指出的每一种情况。
与 EXISTS 相关的关键字有 ALL 、ANY 和 SOME ,ANY与 SOME 具有同样的功能 乐观的人认为它给用户提供了一种选择。
select NAME ,ORDEREDON
from ORDERS
where NAME = ANY
(select NAME from ORDERS where NAME ='TRUE WHEEL');
ANY与子查询的每一行与主查询进行比较,并对子查询的每一行返回一个TRUE值。
将any和some替换,将会的到相同的结果。in只能进行等值查询不能进行比较。
ALL关键字的作用在于子查询中的所有结果均满足才会返回TRUE。ALL常起双重否定的作用。
select NAME,ORDEREDON
from ORDERS
where NAME <> ALL
(select NAME from ORDERS where NAME ='JACKS BIKE');
该语句返回除了JACKS BIKE的所有人,<>ALL只有当左边的内容不存在右边时才会返回TRUE值。