【转】ALL与max 何时等价

　　83. View the Exhibit and examine the structure of the ORDERS table.
You have to display ORDER_ID, ORDER_DATE, and CUSTOMER_ID for all those
orders that were placed after the last order placed by the customer whose
CUSTOMER_ID is 101.Which query would give you the desired output?
A. SELECT order_id, order_date FROM orders
WHERE order_date > ALL (SELECT MAX(order_date)
FROM orders ) AND
customer_id = 101;
B. SELECT order_id, order_date FROM orders
WHERE order_date > ANY (SELECT order_date
FROM orders WHERE customer_id = 101);
C. SELECT order_id, order_date FROM orders
WHERE order_date > ALL (SELECT order_date
FROM orders WHERE customer_id = 101);
D. SELECT order_id, order_date FROM orders
WHERE order_date IN (SELECT order_date
FROM orders
WHERE customer_id = 101);
Answer: C

　　eg:
　　any表示满足其中之一
　　All 表示满足其 其中所有的查询结果
　　使用max函数：等价转换 All sql

http://onexin.iyunv.com/source/plugin/onexin_bigdata/file:///C:/Users/dingling/AppData/Roaming/Tencent/Users/1018880332/QQ/WinTemp/RichOle/23O2QCU_U9]$$%3%6050%7BL_~0.jpg
　　
　　注意：
　　如果子查询返回的结果为空时，all  与max 的写法不等价

　　不一样的结果

　　由于表中不存在customer_id=111111的记录，子查询返回的值为空，此时，all 的语句就不能用max来替换。需要注意。