SQL Between: The Best Way to Retrieve Desired Range of Values

SQL provides us with many tools that help in retrieving useful information from all kinds of data. Sometimes, we need to retrieve a range of values from all the values of a table’s columns. For example, to retrieve the information about all employees of a company, born in a particular decade. The SQL Between operator helps in performing these activities and is an integral part of this query language.

In this article about the SQL Between operator, we’ll be covering the following topics:

  • What is Between in SQL?
  • The Syntax for Using The SQL Between Operator
  • Using The Not Operator With Between
  • Using Between With Numeric Values
  • Using Between With Date Values
  • Using Between With Text Values
  • Using Not Between on Values
  • Using Between With The SQL In Operator

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.

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

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:

SELECT column_names

FROM table_name 

WHERE column_name BETWEEN range_start AND range_end;

  • 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:

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:

on_numeric_data

This will result in the following:

on_numeric_data_output

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:

not_between_number-SQL_Between

This will result in the following:

not_between_number_output

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:

numeric_with_order_by-SQL_Between

This will result in the following:

numeric_with_order_by_output

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:

with_date_value-SQL_Between

This will result in the following:

with_date_value_output

  • 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:

wrong_order_date-SQL_Between

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

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’:

with_text-SQL_Between.

This will result in the following:

with_text_output

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

with_text_error.

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:

not_between_date-SQL_Between

This will result in the following:

not_between_date_output

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:

SELECT column_names

FROM table_name

WHERE column_1 BETWEEN range_start AND range_end

AND column_2 IN(value_1, value_2, value_3,...value_n);

  • 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:

with_SQL_in-SQL_Between

This will result in the following:

with_SQL_in_output.

  • 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’:

with_SQL_not_in-SQL_Between.

This will result in the following:

with_SQL_not_in_output.

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!

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 Business Analyst Master’s Program 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.

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.