I’m watching Microsoft Virtual Academy’s Database Fundamental’s class – the Introduction to Core Database Concepts section, and there is an example of the BETWEEN clause using the AdventureWorks 2014 database:
SELECT BusinessEntityID, JobTitle, VacationHours
FROM HumanResources.Employee
WHERE VacationHours BETWEEN 75 AND 100;
I ran this query in my database, and retrieved 75 rows.The lowest number of hours in the VacationHours field is 75, and the highest is 99.
I get confused with what the word “BETWEEN” means in queries. In Access, I avoided using “Between” in favor of “> x and <y” in the criteria of a field, so that I knew exactly what I was asking for. Looking at the data returned in this query, I find myself asking again – is 100 included in “BETWEEN 75 AND 100”?
I check to see if the table has any values equal to 100 in the VacationHours field:
SELECT BusinessEntityID, JobTitle, VacationHours
FROM HumanResources.Employee
WHERE VacationHours = 100;
No data was returned, so that first query wasn’t excluding values of 100 necessarily – they just weren’t there to begin with.
Since I know the value 99 is in this field, I alter the query:
SELECT BusinessEntityID, JobTitle, VacationHours
FROM HumanResources.Employee
WHERE VacationHours BETWEEN 75 AND 99;
I again retrieved 75 rows, lowest number of hours in the VacationHours field is 75, and the highest is 99 – the same as the initial query results. I think I can reasonably conclude from this that “BETWEEN” includes that end number.
This seems easy enough for integers, and maybe with dates that are not datetimes(?), but if this were a decimal field, I don’t know what would happen to a value of 100.1 in the first query, or a value of 99.1 in the last. More to investigate.
SQL Garden 