SQL Between Operator

In SQL, the BETWEEN operator is useful to get the values within the defined range. Generally, we will use BETWEEN operator in the WHERE clause to get the required values within the specified range.

Syntax of SQL BETWEEN Operator

Following is the syntax of between operator in the SQL server.

 

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

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

 

We will check this between operator with an example for that create “EmployeeDetails” table by using the 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 the above script, our table “EmployeeDetails” will create and return the following result.

 

Newly created employeedetails table in sql server

Now run the following examples to check the BETWEEN operator in the SQL server.

SQL Between Operator Example

The following SQL query will return all employee's details where empid in between 1 and 3.

 

SELECT * FROM EmployeeDetails WHERE empid BETWEEN 1 and 3

When we execute the above SQL between operator, we will get the following result.

 

SQL Between Operator with Example Result or Output

SQL NOT BETWEEN Operator with Example

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

 

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

 

SELECT * FROM EmployeeDetails WHERE empid NOT BETWEEN 1 and 3

When we execute the above SQL not between operator example, we will get the following result.

 

SQL Not Between Operator Example Result or Output

 

Now we will see how to use the NOT keyword with IN operator and check how it will return records. Generally, using the NOT keyword with IN operator will return all the records that do not exist in the list of values.

 

 

SQL BETWEEN Operator with Character (Text) Value Example

In SQL, if we use the BETWEEN operator with character string values, it will return all the records where the column name begins 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 the above SQL query, we will get the result for SQL between operator with a 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 begins with any letter between character string values. Suppose if we use NOT with BETWEEN operator, it will return all the records whose column name does not start between string values.

 

The following SQL query will return all the records whose location name does not begin with any letter between 'A' and 'K'.

 

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

When we execute the above SQL not between operator with character, we will get the following result.

 

SQL NOT BETWEEN Operator with Character (Text) Value Example

SQL BETWEEN Operator with Date Value Example

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

 

The 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 the 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