SQL Between: Best Way to Retrieve Desired Range of Values

What Is Between in SQL?

The SQL Between operator is used to test whether an expression is within a range of values. This operator is inclusive, so it includes the start and end values of the range. The values can be of textual, numeric type, or dates.

This operator can be used with SELECT, INSERT, UPDATE, and DELETE command.

To get a clearer picture of this operator, let’s get an insight into its syntax.

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

The Syntax for Using the SQL Between Operator

This operator is most commonly used with the SELECT command. The syntax of this is as follows:

• The columns to be retrieved are specified in the SELECT statement.
• The table the columns are being retrieved from is specified in the FROM statement.
• The BETWEEN operator is used in the WHERE clause. The column we want to apply the range condition on is specified in the “column_name” parameter. The starting value of the range of values is specified in the “range_start” parameter and the ending value in the “range_end” parameter.

For example, let’s take a sample “Employeedetails” table:

From this table, if we want to retrieve the ID and name of the employees with ID in the range of 4 to 7, we’ll use the following query:

This will result in the following:

As we can see, the result contains ID values 4 and 7 as well, as Between is inclusive.

Using the Not Operator With Between

We can also use the NOT operator with the BETWEEN operator to select the values that do not belong to the specified range. This is achieved by using the NOT BETWEEN keyword instead of the BETWEEN keyword in the above syntax. 

For example, from the sample table, if we want to retrieve all the employees' ID and salary, with the salary not in the range 30000 to 80000, we’ll use the following query:

This will result in the following:

Using Between With Numeric Values

We can use the ORDER BY statement to sort the result based on some columns.

For example, from the sample table, to retrieve ID, name, and salary of all the employees with salary ranging from 50000 to 78000, and sort this result based on salary:

This will result in the following:

Using Between With Date Values

When using Between with dates, we need to remember to enclose the date in single inverted commas, as otherwise, the query returns a syntax error. 

• From the “Employeedetails” table, the aim is to retrieve the ID, name, and date of birth of employees born in the years 1994 to 1997:

This will result in the following:

• It is imperative to specify the correct values for the starting and ending positions of the range.

Let’s see what happens when we don’t do that:

As we can see, the query mentioned above returns an empty set as the range values were specified incorrectly.

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

Using Between With Text 

The Between operator can also be used with character data types. While using this on text, we need to remember to enclose the data in single inverted commas, as otherwise, the query returns a syntax error.

• From the sample tables, the aim is to retrieve all the employees' ID and name with names belonging to the range ‘Arun’ to ‘Rahul’:

This will result in the following:

• Let’s see what happens when we don’t enclose the data in inverted commas:

As we can see, the query returns a syntax error.

Using Not Between on Values

From the sample table, the aim is to retrieve the ID, name, and DOB of all the employees not born in the range 1st  January 1997 to 12th  November 2000:

This will result in the following:

As we can see, all the rows that don’t belong to the range specified are returned.

Using Between With the SQL In Operator

The SQL In operator is utilized to specify multiple values in a WHERE clause and is an alternative for multiple OR conditions. 

The syntax for using this operator with BETWEEN is:

• The IN operator can be used to check whether the values selected from the column specified in the column_2 parameter belong to the values we want, that can be specified in the parentheses after the IN keyword.
• We can also select the values that do not belong to the values specified in the parentheses by using the NOT IN keyword instead of the IN keyword.

Let’s see some examples of this.

• From the sample table, the aim is to retrieve the ID, name, and salary of all the employees with name in the range ‘A’ to ‘R’ and ID belonging to the values 3, 4, 5, or 6:

This will result in the following:

• Let’s see what happens when we use the NOT IN keyword.

To retrieve the ID, name, and DOB of employees with ID in the range 3 to 9 and names other than ‘Arun’, ‘Rahul’, and ‘Hannah’:

This will result in the following:

With this, we reach the end of this article about the SQL Between operator.

Gain expertise in the latest Business analytics tools and techniques with the Post Graduate Program in Business Analysis. Enroll now!

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

Next Steps

If you remember all the SQL Between operator rules and syntax, you can customize your queries to retrieve the type of information that is needed. This tool, along with other SQL tools, enables us to write different kinds of essential queries.

Now that you know about the Between operator, it is time for you to start using this along with other commands, clauses, and operators to query data and move forward in your journey to become an expert in SQL. If you liked this article and want to get certified, you must check out our SQL Certification Course as it covers the A-Z of SQL as well.

Do you have any questions for us? Mention them in the comments section of this “SQL Between: The Best Way to Retrieve Desired Range of Values” article, and we’ll have our experts in the field answer them for you.