The BETWEEN Clause

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s