SQL Between Operator

In sql, BETWEEN operator is used to get the values within the defined range. Generally we will use this BETWEEN operator in WHERE clause to get required values within the defined range.

Syntax of SQL BETWEEN Operator

Following is the syntax of between operator in sql server.

 

SELECT column1, column2 FROM tablename WHERE column1 BETWEEN value1 AND value2

If you observe above SQL BETWEEN operator syntax, it will return a values whose column1 values between value1 and value2.

 

We will check this between operator with example for that create “EmployeeDetails” table by using following script in your database

 

create table EmployeeDetails(empid int, empname varchar(50),designation varchar(50),salary int,Location varchar(50))

 

insert into EmployeeDetails

values(1,'suresh','software engineer',25000,'chennai'),

(2,'rohini','AEO',15000,'chennai'),

(3,'madhavsai','business analyst',50000,'nagpur'),

(4,'mahendra','CA',75000,'guntur'),

(5,'sateesh','Doctor',65000,'guntur')

 

select * from EmployeeDetails

Once we run above script our table “EmployeeDetails” will create and result will be like as shown below

 

Newly created employeedetails table in sql server

Now run following examples to check BETWEEN operator in sql server.

SQL Between Operator Example

The following sql query will return all employees details where empid in between 1 and 3.

 

SELECT * FROM EmployeeDetails WHERE empid BETWEEN 1 and 3

When we execute above sql between operator, then we will get the result like as shown below.

 

SQL Between Operator with Example Result or Output

SQL NOT BETWEEN Operator with Example

Generally, in sql statements if we use BETWEEN operator then it will return a records whose value between the defined range. Suppose if we use NOT keyword with BETWEEN operator, then it will return a data where the column value not in between the defined range of values.

 

In following sql query we will see how to use NOT with BETWEEN operator in sql. The following query will return all the employees details whose empname not in between 1 and 3 range.

 

SELECT * FROM EmployeeDetails WHERE empid NOT BETWEEN 1 and 3

When we execute above sql not between operator example, we will get the result like as shown below.

 

SQL Not Between Operator Example Result or Output

 

Now we will see how to use NOT keyword with IN operator and will check how it will return records. Generally if we use NOT keyword with IN operator it will return all the records that does not exists in the list of values.

 

 

SQL BETWEEN Operator with Character (Text) Value Example

In sql, if we use BETWEEN operator with character string values, then it will return all the records where the column name beginning with any letter between the character string values.

 

Following is the sql query which will return all the records whose location name starts with any letter between 'A' and 'K'.

 

SELECT * FROM EmployeeDetails WHERE Location BETWEEN 'A' AND 'K'

 When we execute above sql query, we will get the result for sql between operator with character like as shown below.

 

SQL Between Operator with Characters Example Output or Result

SQL NOT BETWEEN Operator with Character (Text) Value Example

In sql, if we use BETWEEN operator with character values it will return all the records where the column name beginning with any letter between character string values. Suppose if we use NOT with BETWEEN operator, then it will return all the records whose column name not start between string values.

 

Following sql query will return all the records whose location name not begin with any letter between 'A' and 'K'.

 

SELECT * FROM EmployeeDetails WHERE Location NOT BETWEEN 'A' AND 'K'

When we execute above sql not between operator with character, then we will get the result like as shown below.

 

SQL NOT BETWEEN Operator with Character (Text) Value Example

SQL BETWEEN Operator with Date Value Example

In sql, if we use BETWEEN operator with date values, then it will return all the records between the defined range of date values. 

 

Following sql query will return all the records between defined ranges of date values ('1986-05-20', '1997-08-24').

 

SELECT * FROM EmployeeDetails WHERE joineddate BETWEEN '1986-05-20' AND '1997-08-24'

When we execute above query, we will get the result of sql between operator with date value like as shown below.

 

SQL BETWEEN Operator with Date Value Example Output or Result

 

PREVIOUS

SQL IN Operator