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.
