运算符和"="之间的SQL差异& “< ="操作员 [英] Difference in SQL Between operator and ">=" & "<=" operator
问题描述
我们正在使用SQL查询基于dateFrom和dateTo字段进行搜索.为此，我使用"大于等于(> =)"和"小于等于(< =)"运算符在日期字段上进行搜索.在某个地方，我还发现我们也可以使用SQL" Between "运算符执行相同的操作.只是想确认当我们使用" Between "运算符和我们使用"(> =&< =)"运算符时有什么区别.
现代数据库附带了非常智能的查询执行优化器.它们的主要功能之一是查询转换. 逻辑等效表达式通常可以相互转换.例如正如Anthony所建议的那样，如果BETWEEN不仅被实现为语法糖，则BETWEEN运算符可以由Oracle(和MySQL)重写为两个AND连接的比较，反之亦然.
因此，即使在性能上有所不同，您也可以放心，Oracle很有可能会选择更好的选项.
这意味着您可以自由选择偏好设置，例如因为可读性.
注意:在逻辑上是等效的，这并不总是显而易见的.当涉及到转换EXISTS，IN，NOT EXISTS，NOT IN ...时，查询转换规则变得更加复杂.有关更多详细信息，请阅读规范(谓词之间的第8.3章):
http://www.contrib.andrew.cmu.edu /~shadow/sql/sql1992.txt
We are using a SQL query to search on the basis of dateFrom and dateTo fields. for that i am using "greater than equal to(>=)" and "less than equal to(<=)" operator to search on the date fields. somewhere i also find that we can also use SQL "Between" operator to do the same. just wanted to confirm that is there any difference when we use "Between" operator and when we used "(>= & <=)" operator.
Modern databases ship with very intelligent query execution optimisers. One of their main features is query transformation. Logically equivalent expressions can usually be transformed into each other. e.g. as Anthony suggested, the BETWEEN operator can be rewritten by Oracle (and MySQL) as two AND-connected comparisons, and vice versa, if BETWEEN isn't just implemented as syntactic sugar.
So even if there would be a difference (in performance), you can be assured that Oracle will very likely choose the better option.
This means that you can freely choose your preference, e.g. because of readability.
Note: it is not always obvious, what's logically equivalent. Query transformation rules become more complex when it comes to transforming EXISTS, IN, NOT EXISTS, NOT IN... But in this case, they are. For more details read the specification (chapter 8.3 between predicate):
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
这篇关于运算符和"="之间的SQL差异& “< ="操作员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
