Tutorial Gateway

SQL BETWEEN Operator

by

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

For example, If you want to find the Sales between 18 May 2015 to 19 June 2015 or If you want the Amazon website to display the products whose price range between 1000 to 2500 then, internally we have to use this SQL Between operator.

SQL Between Operator Syntax

The basic syntax of the Between operator in SQL Server can be written as:

-- SQL Server Between Operator 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
  • Columns: It allows us to choose the number of columns from the tables. It may be One or more.
  • Source: One or more tables present in the Database. SQL JOINs are used to join multiple table.
  • Values: Here we have to provide the values or expression we want to check against the Column Name. Select Statement will display the records present in between the Value1 and Value2 Including them.

In this article we will show you, How to use the Between Operator in SQL Server 2014 with examples. For this, We are going to use the below shown data

SQL BETWEEN Operator

SQL Between Operator On Numeric Data Example

The following query will find all the Customers present in the Customers table whose [Yearly Income] is between 50000 and 70000

-- SQL Server Between Operator Example
SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
FROM [Customer]
WHERE [YearlyIncome] BETWEEN 50000 AND 70000

OUTPUT

SQL BETWEEN Operator 1

SQL Between Operator On Text Data Example

The following query will find all the Customers present in the Customers table whose Last Name is between Carlson and Ruiz

-- SQL Server Between Operator Example
SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
FROM [Customer]
WHERE [LastName] BETWEEN 'Carlson' AND 'Ruiz'
ORDER BY [LastName]

TIP: We can also use single character instead of writing the complete name.

OUTPUT

SQL BETWEEN Operator 2

SQL NOT BETWEEN Example

We can also use the NOT Keyword along with the Between operator in Sql Server.

For example, the following SQL Not between operator query will find all the Customers present in the Customers table whose [Yearly Income] is not between 50000 and 70000

-- SQL Server Not Between Operator Example
SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
FROM [Customer]
WHERE [YearlyIncome] NOT BETWEEN 50000 AND 70000

OUTPUT

SQL BETWEEN Operator 3

Thank You for Visiting Our Blog

Placed Under: SQL