子查询

1、  子查询：嵌套层数最多255

（1）       单行子查询

1、  WHERE子句里的子查询

SELECT  TITLE,COST  FROM BOOKS  WHERE COST>

(SELECT  COST  FROM BOOKS  WHERE  TITLE=’CHRISLEE’)

AND  CATEGORY=’COMPUTER’;

2、  HAVING子句中的子查询

SELECT CATEGORY,AVG(RETAIL-COST) FROM BOOKS GROUP BY CATEGORY

HAVING AVG(RETAIL-COST)>

(SELECT AVG(RETAIL-COST)  FROM BOOKS WHERE CATEGORY=’computer’);

3、  SELECT子句中的子查询

SELECT TITLE,RETAIL ,(SELECT AVG(RETAIL)  FROM  BOOKS) FROM BOOKS;

 

（2）       多行子查询

1、  IN运算符——例：显示每一种图书中最贵的图书

SELECT TITLE,RETAIL,CATEGORY FROM BOOKS WHERE RETAIL  IN

(SELECT  MAX(retail) FROM BOOKS GROUP BY CATEGORY) ORDER BY CATEGORY;

2、  ALL/ANY运算符

Ø>ALL:大于子查询返回的最大值。例：查找零售价超过cooking种类最贵的图书的书

SELECT TITLE,RETAIL,CATEGORY FROM BOOKS WHERE RETAIL>ALL

(SELECT RETAIL FROM BOOKS WHERE CATEGORY=’COOKING’);

Ø<ALL:小于子查询返回的最小值。例：查找价格低于cooking种类中最便宜的书的书

 SELECT TITLE,RETAIL,CATEGORY FROM BOOKS WHERE RETAIL<ALL

(SELECT RETAIL FROM BOOKS WHERE CATEGORY=’COOKING’);

Ø>ANY:大于子查询返回的最小值。例：查找价格高于cooking种类中最便宜的书的书

SELECT TITLE,RETAIL,CATEGORY FROM BOOKS WHERE RETAIL>ANY

(SELECT RETAIL FROM BOOKS WHERE CATEGORY=’COOKING’);

Ø<ANY:小于子查询返回的最大值。例：查找价格低于cooking种类中最贵的书的书

SELECT TITLE,RETAIL,CATEGORY FROM BOOKS WHERE RETAIL<ANY

(SELECT RETAIL FROM BOOKS WHERE CATEGORY=’COOKING’);

Ø=ANY:等于子查询返回的任何值。相当于IN

3、  EXISTS运算符：确定一个字查询是否存在一个条件，这个运算的结果是一个布尔值，存在为true，不存在为false。例：查询最近(没有)订购的所有图书

SELECT  TITLE FROM BOOKS WHERE  (NOT)  EXISTS

(SELECT ISBN FROM ORDERITEMS WHERE BOOKS.ISBN=ORDERITEMS.ISBN);

4、  HAVING子句中的多行子查询。例：确定任何客户最近下达的订单的总应付款项是否超过客户所在州的下达的所有订单的平均应付款项

SELECT ORDER#,SUM(RETAIL*QUANTITY)

FROM ORDERS NATURAL JOIN ORDERITEMS NATURAL JOIN BOOKS GROUP BY ORDER#

HAVING SUM(RETAIL*QUANTITY)>ANY(SELECT  AVG(SUM(RETAIL*QUANTITY))

FROM ORDERS NATURAL JOIN ORDERITEMS NATURAL JOIN BOOKS GROUP BY SHIPDATE);

 

（3）       多列子查询

1、  FROM子句中的多列子查询：子查询实际上将生成一个外部查询的其他子句可以引用的临时表。例：查找books表中零售价高于该种类平均零售价的所有图书列表

SELECT  TITLE,RETAIL,CATAVERAGE FROM BOOKS NATURAL JOIN

(SELECT CATEGORY,AVG(RETAIL) CATAVERAGE FROM BOOKS GROUP BY CATEGORY)

WHERE RETAIL>CATAVERAGE;

2、  WHERE子句中的多列子查询：列列表必须包含在括号中；在WHERE子句中列出列名称的顺序必须在子查询的SELECT子句列出他们的顺序。例：每一个种类中最贵的图书列表

SELECT TITLE,RETAIL,CATEGORY FROM BOOKS WHERE(CATEGORY,RETAIL) IN

(SELECT CATEGORY,MAX(RETAIL) FROM BOOKS GROUP BY CATEGORY) ORDER BY CATEGORY;

 

规则：子查询中不能有ORDER BY分组语句；自身就是一个完整的查询；如果将子查询在外部查询的WHERE或HAVING子句中，那么该子句只能位于比较运算符的“右边”。