SQL BETWEEN AND IN OPERATORS: Complete Guide

SQL BETWEEN operator is almost like the SQL IN operators used sequentially.

SQL BETWEEN AND IN OPERATORS

SQL BETWEEN Operator is used with a WHERE clause for providing the range of values. The values can be numeric, text values, and dates. The BETWEEN operator is inclusive: begin and end values are included.

SQL between operator is used for selecting values within the specified range. The values can be text, date, numbers, or other SQL data types used with select, update, insert, or delete statements.

The records will be returned only if any value exists within the range of values specified in statements. SQL between operators is inclusive, i.e., both starting and endpoint values are included.

The values defined as a part of the BETWEEN range are inclusive. For example, the values mentioned in the range and start and end values are included.

Syntax

Select column(s) from table_name 
where column_name between value1 and value2.

Parameters

1. Column(s) is the name of the columns you want to print.
2. Table_name is the name of the table.
3. Where column_name is used for applying condition on column_name.
4. Between is the operator, which checks values within that range.
5. Value1 and Value2 are the starting and ending value.

Let’s begin with the example.

Table_1: Employee

Emp_id	Emp_name	City	State	Salary
101	Rohit	Patna	Bihar	30000
201	Shivam	Jalandhar	Punjab	20000
301	Karan	Allahabad	Uttar Pradesh	40000
401	Suraj	Kolkata	West Bengal	60000
501	Akash	Vizag	Andhra Pradesh	70000

 

QUERY: (BETWEEN NUMERIC VALUES)

Select * from employee 
where salary between 20000 and 50000;

See the following picture.

EXPLANATION

The above query displayed the records of an employee whose salary is between 20000 to 50000.

QUERY: (NOT BETWEEN NUMERIC VALUES)

Select * from employee 
where salary NOT BETWEEN 20000 and 50000;

See the following output.

EXPLANATION

The above query displayed the records of an employee whose salary is not between 20000 and 50000.

QUERY: (BETWEEN TEXT VALUES)

select * from employee 
where city between 'Allahabad' and 'Patna' order by city;

See the following output.

EXPLANATION

The above query displayed the records of an employee whose city is not in range, as mentioned in the query. The range is measured using ASCII values.

SQL BETWEEN Dates Example

Let’s see the following example.

SELECT * FROM Orders
WHERE OrderDate BETWEEN #10/09/1993# AND #25/06/2019#;

The output from the above query will be millions of rows because it will fetch the rows between those dates, including those dates.

SQL IN Operator

SQL IN OPERATOR is a logical operator that is used to check whether particular values exist within the declared set or not.

The IN operator is also used for removing the need for multiple OR conditions in SELECT, INSERT, UPDATE, or DELETE statements. NOT IN can be used for excluding the rows from the set.

Syntax

Select column(s) from table_name 
where column_name IN(Value_sets);

Parameters

1. Column(s) is the name of the columns you want to print.
2. Table_name is the name of the table.
3. Where column_name is used for applying condition on column_name.
4. IN is the operator used to check whether the values are present in that value_sets.
5. Value1 and Value2 are the starting and ending value.

Let’s begin with the example.

Table_1: Employee

Emp_id	Emp_name	City	State	Salary
101	Rohit	Patna	Bihar	30000
201	Shivam	Jalandhar	Punjab	20000
301	Karan	Allahabad	Uttar Pradesh	40000
401	Suraj	Kolkata	West Bengal	60000
501	Akash	Vizag	Andhra Pradesh	70000

 

QUERY: (IN FOR NUMERIC VALUES)

select * from employee 
where salary IN(20000,30000,40000);

Output

Explanation

This query displayed the records whose salary is 20000, 30000, or 40000.

QUERY: (NOT IN FOR NUMERIC VALUES)

select * from employee 
where salary NOT IN(20000,30000,40000);

Output

QUERY: (IN TEXT VALUES)

select * from employee 
where city  IN('Kolkata', 'Vizag');

Output

Explanation

The above query displayed the records of an employee whose city is in KOLKATA and VIZAG.

QUERY: (NOT IN TEXT VALUES)

select * from employee 
where city NOT IN('Kolkata', 'Vizag');

Output

Explanation

The above query displayed the records of an employee whose city is not in KOLKATA and VIZAG.

SQL BETWEEN AND IN OPERATOR

In this, both BETWEEN and IN operators can be used for generating result sets.

Table_1: Employee

Emp_id	Emp_name	City	State	Salary
101	Rohit	Patna	Bihar	30000
201	Shivam	Jalandhar	Punjab	20000
301	Karan	Allahabad	Uttar Pradesh	40000
401	Suraj	Kolkata	West Bengal	60000
501	Akash	Vizag	Andhra Pradesh	70000

 

QUERY

Select * from employee 
where emp_id between 201 and 501 and salary IN(40000,50000,60000);

Output

Explanation

Here in this query, details of an employee have displayed whose id was in the range from 201 and 501 and also whose salary was in the value_sets, i.e., 40000, 50000, and 60000.

That’s it for this tutorial.