SQL Between

The SQL Between is a way of looking for a range of values when you are trying to filter in a SQL Select statement.

So let’s take at an Employee table with the following query:

select * from employee; 

Here are the results:

This employee table has 14 rows.  I want to filter this table to find all of the salaries that are greater than or equal to 1100.00 and less than or equal to 1600.00.

I will use the following query to do that:

select * from employee where salary >= 1100 and salary <= 1600; 

Here are the results:

Notice how we get the salary of 1100 and the salary of 1600.  This is because I used the greater than or equal to and the less than or equal to.

I could reword what I want with the following sentence.  I want to filter this table to find all of the salaries that are between 1100.00 and 1600.00.

Then I can write the query like this:

select * from employee where salary between 1100 and 1600; 

This query will give you the same results as before, but it uses the BETWEEN clause.

Some things to note about the BETWEEN clause.

First, you only put the column name in there 1 time vs. 2 times with the first query.

Also notice that the BETWEEN clause includes the Endpoints.  So, if you look at the query above, we are including in the results a Salary of 1100 and a Salary of 1600.

Now, if you are wondering if one way is better than the other?  Whether you should use the BETWEEN clause or the ‘>=’ and ‘<=’?

I would have to say that it is really just a preference. I prefer to use the BETWEEN clause when I can because it helps with the readability of the query.

As you get into more complex queries, you will start to find ways to keep your query as simple and readable as possible so that you don’t loose your mind.

(Trust me, queries can become really complex fast!)

That’s it for the BETWEEN clause.

Drop me a note in the comments section if you have any questions, comments, or suggestions.