【转】ALL与max 何时等价 -OCP-047--83

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 

  

注意：

如果子查询返回的结果为空时，all  与max 的写法不等价

不一样的结果

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