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.
Following is the syntax of between operator in the SQL server.
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.
Once we run the above script, our table “EmployeeDetails” will create and return the following result.
Now run the following examples to check the BETWEEN operator in the SQL server.
The following SQL query will return all employee's details where empid in between 1 and 3.
When we execute the above SQL between operator, we will get the following result.
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.
When we execute the above SQL not between operator example, we will get the following result.
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.
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.'
When we execute the above SQL query, we will get the result for SQL between operator with a character like as shown below.
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'.
When we execute the above SQL not between operator with character, we will get the following result.
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').
When we execute the above query, we will get the result of SQL between operator with date value like as shown below.