SQL BETWEEN: Retrieve Desired Range of Values with Examples

NOT is a logical operator in SQL that you can place ahead of any conditional statement to select rows for which that statement is not true.

Alternatively, we can use the NOT operator with the BETWEEN operator to select values that do not fall within the specified range. This is done using the NOT BETWEEN keyword rather than the BETWEEN keyword in the above syntax. 

For example, Using the SQL Server Between from the sample table, we can retrieve all the employees' ID and salary, with the salary not in the range 30000$ to 60000$:

This will be the output of the following:

In this SQL between operator, you can use the ORDER BY statement to sort the result in some columns. 

For instance, the SQL keyword between is used in the sample table, to retrieve the ID, name, and salary of all employees with wages ranging from 50,000 to 80,000:

This will result in the following:

When using SQL date between, we need to remember to include the date in a single inverted comma; otherwise, the query returns a syntax error. 

Here is a classic example of SQL between two dates. From the table "Employment details", the objective is to recover the identity, the name, and the date of birth of employees born in the years 1994 to 1997:

This will be the output after the use of SQL Between DateTime is:

It is essential to specify the correct values for the beginning and end positions of the range.

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

As we can see, the SQL Date Between mentioned above returns a blank set because the range values were incorrectly specified.

A Between operator in SQL can be extremely useful. But, if you are seriously looking to increase your domain knowledge, check out this comprehensive SQL Career Path.

The SQL Between operator can also be used with character data types. Using this on the text, we have to remember to enclose the data in a single inverted comma, otherwise, the request returns a syntax error.

From the sample tables, the SQL keyword Between is used to retrieve all the employees' IDs and names with names belonging to the range ‘PARKER’ to ‘MADISON’:

This will be the output of the Between in MySQL: 

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.

You have just learned a between operator in SQL. Before moving ahead, evaluate your skills with this easy 2 mins SQL Quiz now!

The SQL Not Between operator is used to get the values as part of the result set which is outside of the range specified by the SQL Between operator.

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

This will be the output of the following:

As we can see, all rows that do not fall in the specified range are returned.

SQL is an extensive domain, if you’re looking for your first job in data, it turns out knowing SQL is even more critical. Check out our Comprehensive Guide on SQL to get a better insight.

The SQL In operator is used to specify several values in a WHERE clause and is an alternate for several OR conditions. 

The syntax for using this operator with SQL Between is:

The SQL In operator can be used to check if the values selected in the column specified in the column_2 parameter belong to the desired values, which can be specified in parentheses following the IN keyword. We can also select values that do not belong to the values given in parentheses by using the keyword Not In instead of the keyword In.

Let’s see some examples of this.

From the sample table, the aim is to retrieve the ID, name, and wages of all the employees with names in the range ‘A’ to ‘J’ and ID belonging to the values 1, 2, 4, or 8:

This will be the output of 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 2 to 8 and names other than ‘ADELYN’, ‘FRANK‘, and ‘JULIUS’.

This will be the output of the following:   

Also, don't forget to join our professional JanBask SQL Community to connect with industry experts and professionals.

The following example shows the MySQL Date Between usage for ranges.

The next example uses >= and <= operators and will display the same value.

However, using just operators will result in fewer rows. Indeed, the SQL Between operator is inclusive, and more than that and less than operators are not.

SQL Between is a best practice and requires less coding as compared to the >= and <= operators. It is more user-friendly and you don't need to specify the column to compare twice.

Note: For a range with max and minimum value, use SQL Between whenever possible. Also, if you are a beginner, looking to start your career in SQL. Check out How to Become SQL Developer?

We can use multiple between operators too. Its syntax is:

By using the aforementioned syntax, we can use several SQL Between operators.

Scenario: Get the name of the employee between the ages of 26 and 30 and the salary between  50 to 65 thousand dollars. 

Output of using Between in MySQL: ADELYN

With this, we have reached the end of this blog about the SQL Between operator. But, if you are a job aspirant, then below are the most in-demand SQL Skill of 2022 that can boost your career to the next level.