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

今天在寫 Oracle 查詢時，直覺把時間區間寫成 between sysdate and sysdate - 1，結果完全查不到資料。原本還懷疑是不是 DB 對日期型別有什麼怪脾氣，回頭看文件才發現，BETWEEN 的上下界順序真的不能亂放。

BETWEEN 的本質

BETWEEN 不是魔法語法，它本質上就是：

1

expr BETWEEN min_value AND max_value

等價於：

1

min_value <= expr AND expr <= max_value

所以如果你把小的值放右邊、大的值放左邊，區間就會變成空集合。

最容易踩雷的例子

像這句：

1

where create_time between sysdate and sysdate - 1

意思其實是：

1

sysdate <= create_time and create_time <= sysdate - 1

這在正常情況下根本不可能成立，所以查不到資料是合理的。

正確應該寫成：

1

where create_time between sysdate - 1 and sysdate

Oracle 文件怎麼說

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 也是一樣

MySQL 官方文件同樣說明，BETWEEN 等價於 min <= expr AND expr <= max。

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.

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

我自己的習慣

後來我在寫時間區間時，會盡量避免讓式子左右都帶運算，這樣比較不容易眼殘。例如：

1
2

where create_time >= :start_time
	and create_time <= :end_time

這種寫法雖然比 BETWEEN 長一點，但可讀性更高，也比較適合後續維護。

小提醒

1. BETWEEN 是包含上下界的。
2. 日期時間欄位要注意是否含時分秒。
3. 如果是查某一天資料，通常直接用 >= 起始時間 搭 < 下一天 會更穩。

小結

BETWEEN 本身沒有問題，真正的問題是我們太容易把它當自然語言在看。只要記得它等價於兩個比較式，就不太會再被上下界順序陰到。