SQL Teaser - Between Operator in SQL Server

Let see some interesting facts of SQL Server's between operator:

create table VenkatTable(id int identity(1,1) primary key clustered, MyColumn int)
insert VenkatTable(MyColumn) select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8

--Statement 1
select count(*) from VenkatTable where MyColumn between 3 and 5

Ouput is 3

--Statement 2
select count(*) from VenkatTable where MyColumn between 5 and 3

Ouput is 0
The reason behind the different output is,
Generally, Between indicates the data between the left value and right value. But it's not the case in SQL Server.
This compiler will check for the syntax and
1. It will check the left value should be less than the right value.
2. If its the case, it will return "True" else it will return "False"
3. If the result is true then it will fetch the actual data from the database.
4. If the result is false then it wont fetch any data and give the data as null.
Happy Learning!!!
Regards,
Venkatesan Prabu .J