ALL与max 何时等价

来源：互联网发布：淘宝刷枪是真的吗编辑：程序博客网时间：2024/06/11 19:46

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来替换。需要注意。