SQL BETWEEN Operator

The SQL Between Operator displays the records (or rows) whose values are in between the given values. The syntax of the Between operator is

--  Syntax
SELECT [Column Names]
FROM [Source]
WHERE [Column Name] BETWEEN Value1 AND Value2

--We can also write the above statement
SELECT [Column Names]
FROM [Source]
WHERE [Column Name] >= Value1 AND
      [Column Name] <= Value2

For example, If you want to find Sales from 18 May 2015 to 19 June 2015. Or If you want the Amazon website to display the products whose price range from1000 to 2500, then internally, we have to use this SQL Server Between operator. For this example, We are going to use the below-shown data

SQL Between Operator On Numeric Data Example

The following SQL between operator query finds all the Customers present in the Customers table whose [Yearly Income] is in the middle of 50000 and 70000

SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
FROM [Customer]
WHERE [YearlyIncome] BETWEEN 50000 AND 70000

The following query will find all the existing Customers in the Customers table whose Last Name is in the middle of Carlson and Ruiz

SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
FROM [Customer]
WHERE [LastName] BETWEEN 'Carlson' AND 'Ruiz'
ORDER BY [LastName]

TIP: In SQL Server, We can also use single character instead of writing the complete name.

SQL Not BETWEEN Example

We can also use the NOT Keyword along with this operator. For example, the following Not between query will find all the Customers present in a table whose [Yearly Income] is not betwixt 50000 and 70000

SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
FROM [Customer]
WHERE [YearlyIncome] NOT BETWEEN 50000 AND 70000