Home » SQL » Operator Between in SQL Server

Operator Between in SQL Server

Introduction: Operator Between in SQL

In this tutorial, you will learn how to use the SQL Server BETWEEN operator to specify a range to test. The BETWEEN in SQL result the values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included.

If you are using range then you need to use AND logical operator, So that it will full fill both condition and gives the data between the range / number.

BETWEEN in SQL always used with WHERE clause that is called the condition. So where ever you have condition you can apply BETWEEN operator to get the range records as result.

Syntax:

SELECT 
      column_name
FROM 
      table_name
WHERE 
      column_name BETWEEN value1 AND value2;

In this syntax:

  • First, specify the column to test.
  • Second, place the Value1 and value2 between the BETWEEN and the AND keywords. The value1, Value2 and the expression to test must have the same data type.

Example #1: Between in SQL

Below example for “Order Quantity” between 25 to 35. If you are using range in BETWEEN then you need to use AND logical operator, So that it will full fill both condition and gives the data between the range / number

SELECT
      [Order ID], [Order Quantity]
FROM
      [dbo].[Orders_Tbl]
WHERE
      [Order Quantity] BETWEEN 25 AND 30

Result:

BETWEEN in sql

Example #2: Between in SQL

Lets take another example for Discount columns. below Query shows the result of discount in range 20% to 30% only, and it ahs used AND logical operator to make the records visible by Query.

SELECT
      [Order_ID], [Discount]
FROM
      [dbo].[Orders_Tbl]
WHERE
      Discount BETWEEN .02 AND .03
ORDER BY
      Discount

Example #3:

To exclude BETWEEN use NOT before between. If you are using range in BETWEEN then you need to use AND logical operator, So that it will full fill both condition and gives the data between the range / number

SELECT
       [Order ID], [Order Quantity]
FROM
       [dbo].[Orders_Tbl]
WHERE
       [Order Quantity] NOT BETWEEN 25 AND 30

Result:

BETWEEN in sql