程式狂想筆記

一個攻城師奮鬥史

0%

SQL Between ... AND ... 順序有差

今天寫 Oracle 時候發現 between sysdate and sysdate -1 竟然不能查詢
原來看一下文件,順序真的是有差的

Oracle

is the value of the boolean expression:
expr2 <= expr1 AND expr1 <= expr3
If expr3 < expr2, then the interval is empty. If expr1 is NULL, then the result is NULL. If expr1 is not NULL, then the value is FALSE in the ordinary case and TRUE when the keyword NOT is used.

Oracle BETWEEN Conditions

MySQL

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. Otherwise type conversion takes place according to the rules described in Section 12.3, “Type Conversion in Expression Evaluation”, but applied to all the three arguments.

MySQL :: MySQL 8.0 Reference Manual :: 12.4.2 Comparison Functions and Operators