BETWEEN Clause Vs AND

BETWEEN Clause Vs [ >= AND <= ]

Introduction

In this article I am trying to discuss related to BETWEEN clause and >= AND <= comparisons operators and which one is best.
Most of the junior developer has some bad concepts related to BETWEEN clause and they told, not to use it. In this article I am demon staring the BETWEEN clause and how good it is.
What is the Difference

Let’s take an example to understand it properly.

Step-1 [ Create the Base Table ]

CREATE TABLE my_TestTab
       (ID    INT      NOT NULL IDENTITY(1,1) PRIMARY KEY,
        VAL   DATETIME NOT NULL);
Step-2 [ Inserting Records ]

INSERT INTO my_TestTab
      (VAL)
VALUES(’05-01-2012′),
      (’05-07-2012′),
      (’05-11-2012′),
      (’05-15-2012′),
      (’05-22-2012′),
      (’05-23-2012′),
      (’05-25-2012′),
      (’05-27-2012′),
      (’05-28-2012′);
Step-3 [ Now use Between Clause ]

SELECT *
FROM   my_TestTab
WHERE  VAL BETWEEN  ’05-01-2012′ AND  ’05-28-2012′;

Output:

ID            VAL
1              2012-05-01 00:00:00.000
2              2012-05-07 00:00:00.000
3              2012-05-11 00:00:00.000
4              2012-05-15 00:00:00.000
5              2012-05-22 00:00:00.000
6              2012-05-23 00:00:00.000
7              2012-05-25 00:00:00.000
8              2012-05-27 00:00:00.000
9              2012-05-28 00:00:00.000
Step-4 [ Now using >= AND <= ]

SELECT *
FROM   my_TestTab
WHERE  VAL >=’05-01-2012′ AND VAL<=’05-28-2012′; 

Output:

ID            VAL
1              2012-05-01 00:00:00.000
2              2012-05-07 00:00:00.000
3              2012-05-11 00:00:00.000
4              2012-05-15 00:00:00.000
5              2012-05-22 00:00:00.000
6              2012-05-23 00:00:00.000
7              2012-05-25 00:00:00.000
8              2012-05-27 00:00:00.000
9              2012-05-28 00:00:00.000
To observe the difference between Step-3 and Step-4 just execute the SQL of Step-3 Again and see the actual execution plan.
SELECT *
FROM   my_TestTab
WHERE  VAL BETWEEN  ’05-01-2012′ AND  ’05-28-2012′;

If we see the execution plan we find that

SELECT * FROM  my_TestTab WHERE  [VAL] >= @1 AND [VAL]<=@2

So there is no difference between Step-3 and Step-4. Actually internally the BETWEEN clause is converted to >= and <= logical operators.
Summary

As per me using BETWEEN clause is much easier then the >= and <= operators and it looks great in complex query.
It actually depends on developer and there writing style of T-SQL.


Posted by: MR. JOYDEEP DAS