Using the SQL Between Operator: Some Examples

Using the SQL Between Operator: Some Examples
Page content

The good thing about SQL, or Structured Query Language, is that its syntax is based and structured almost as similar to spoken English. This is already an advantage for any English speaking person who wants to easily pick up on the syntax. Take, for example, the SQL BETWEEN operator. According to my English dictionary, the word between means; concerning the relationship of two things or people. In SQL, the BETWEEN operator does exactly that.

Role of the BETWEEN Operator

You might wonder what place the BETWEEN operator has in SQL. The thing here is that the very nature of SQL is that it certifies the very same definition of BETWEEN that as described in the English dictionary. SQL is used to manage data stored in relational database management systems. The data in these kinds of databases is relational by nature and SQL is used to run queries on these databases and find relationships between data in the same table, different tables or even different databases.

If this is the case, then it seems there is no place for the BETWEEN operator. It could be true. You can design an entire system based on massive database schematics and write hundreds of SQL statements to manage everything without using the BETWEEN operator. Even though this operator is not critically important, it sure is convenient. The BETWEEN operator more or less plays the role of simplifying or optimizing your SQL queries.

A Simple Relationship Case

Take this case for example. I run a modeling agency and my client has specifically asked for models that are above 21 years of age but no older than 25. No problem, I have my models’ records stored quite well in my database. A standard SQL query should be able to help me filter out the ones that qualify. So how would such a query look? There is no single answer to this question. Just like in any spoken language you can describe a situation using virtually any number of words. The same goes for SQL.

Choice of Queries

To get a snapshot of our models within the specified age, any of the following queries will give us the desired result:

SELECT name FROM models WHERE age > 20 and age < 26;

or

SELECT name FROM models WHERE age >= 21 and age <= 25;

or

SELECT name FROM models WHERE age = 21 OR age = 22 OR age = 23 OR age = 24 OR age = 25;

You can see as I progress through each SQL query, I have deliberately made things more complicated yet I still get the same result. Looking at all the three queries above, there is a fourth query that makes things much simpler.

Using the BETWEEN Operator

Taking another look at the first of the three queries above:

SELECT name FROM models WHERE age > 20 and age < 26;

We can simplify it further by doing this:

SELECT name FROM models WHERE age BETWEEN 21 AND 25;

As seen here, the second query is easier to read and maintain even though it is exactly the same length as the previous one. From this you can see how the SQL BETWEEN operator can be used to simplify your SQL statements.

Important Information

The BETWEEN operator can also be used to extract ranges within character strings, dates, timestamps and general numbers. SQL itself is a case insensitive language. Therefore, in the examples above, the upper case keywords will still work if typed in lower case.

References