Relational Database Systems

What is difference between “BETWEEN” and >= with

A Database Engineer asked me, What is difference between "BETWEEN" and >= with <= operators? I explained him my answer to prove point but than later realized that others may have same question so thought of putting this article to answer same question that you may have.

So, first of all, both between and >= with <= are logically same. Lets look at below queries to prove this point:

Query with Between operator

use AdventureWorks
GO
SELECT SalesOrderID,
       SalesOrderDetailID,
       CarrierTrackingNumber,
       OrderQty,
       ProductID,
       SpecialOfferID,
       UnitPrice,
       UnitPriceDiscount,
       LineTotal,
       rowguid,
       ModifiedDate
FROM Sales.SalesOrderDetail
WHERE SalesOrderID between 43000 and 44000

Execution Plan

Query with >= with <= operator

use AdventureWorks
GO

SELECT SalesOrderID,
       SalesOrderDetailID,
       CarrierTrackingNumber,
       OrderQty,
       ProductID,
       SpecialOfferID,
       UnitPrice,
       UnitPriceDiscount,
       LineTotal,
       rowguid,
       ModifiedDate
FROM Sales.SalesOrderDetail
WHERE SalesOrderID >= 43000
AND   SalesOrderID <= 44000

Execution Plan

Now lets compare both execution plan, you will find NO difference but that's not a point to say "both are logically same". Main reason is in Seek Predicates section, where both query using same operators >= and <=.  Seek Predicates that defines how query is navigating to fetch data requested thru query and has start and end.  When query optimizer first checks for appropriate index to select data for specified range. Both Queries above using "Range Seek Query" query path because column used in where condition has clustered index defined.

Summary

"Between" and >= with <= both are logically same. You will not be able to see "Seek Predicates" in case of Index Scan, you will not be able to see operators used (>= and <=). Hence, In order to prove this point, you need to have proper index available for columns used in query so that Query optimizer can find suitable index to perform seek operation.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

To Top