SQL Between Operator overview and examples

We extract data from SQL Server tables along with various conditions. Usually, we have data in large amounts and SQL Between operator helps to extract a specific range of data from this huge data. For example, suppose we want to know the product sales between Jan 2019 to May 2019. In this case, we can use this operator in a SQL query. In this article, we will explore the SQL Between operator and its usage scenarios.

The Syntax of SQL Between operator

We use SQL Between operator in the Where clause for selecting a range of values. The syntax for SQL Between is as follows

• Test_Expression: It is the expression or column on which we need to define a range
• Min_value(expression): We define a minimum range for the between operator. Its data type should be the same as of test_expression
• Max_value)expression): It is the maximum range for the between operator. It should also be the same data type as of min_value(expression) and test_expression

In my example, product sales between Jan 2019 to May 2019, we have min_value as Jan 2019 and max_value as May 2019.

The SQL Between operator returns TRUE if the Test_expression value is greater than or equal to the value of min_value(expression) and less than or equal to the value of max_value ( expression).

If the condition is not satisfied, it returns FALSE.

Usually, developers confuse on whether the result will be inclusive or exclusive of the specified range. For example, does the result set include (in product sales between Jan 2019 to May 2019) both Jan 2019 and May 2019 sales data and exclude Jan 2019 and May 2019. We will look at this with the next section of examples.

Let’s prepare a sample table and insert data into it.

I am using ApexSQL Generate as shown in the following screenshot. You can specify a custom date range to select appropriate data.

Once we generate the date, you can check the sample data in the table.

Example 1: SQL Between operator with Numeric values

We can specify numeric values in the SQL Between operator. Suppose we want to get data from Productdata table for ProductID between 101 and 105.

Execute the following query to get the data. You can specify numeric value directly in between operator without any single quote.