AppDividend
Latest Code Tutorials

SQL BETWEEN AND IN OPERATOR Tutorial With Example

0

SQL BETWEEN AND IN OPERATOR Tutorial With Example is today’s topic. SQL between operator is used for selecting values within the specified range. The values can be text, date, numbers, or other SQL datatypes that can be used with select, update, insert, or delete statement. The records will be returned only if there exists any value within the range of values as specified in statements. SQL between operator is inclusive, i.e., both starting and end points values are included.

SQL BETWEEN AND IN OPERATOR

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

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

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

#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 is used for check 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.

 

SQL BETWEEN

#EXPLANATION

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

#QUERY: (NOT BETWEEN NUMERIC VALUES)

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

See the following output.

 

NOT BETWEEN NUMERIC VALUES

#EXPLANATION

The above query resulted in displaying the records of an employee whose salary is not in between 20000 to 50000.

#QUERY: (BETWEEN TEXT VALUES)

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

See the following output.

 

BETWEEN TEXT VALUES

#EXPLANATION

The above query resulted in displaying 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 another 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

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

It is also used for removing the need of multiple OR condition in SELECT, INSERT, UPDATE, or DELETE statement. 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 a name of the table.
  3. Where column_name is used for applying condition on column_name.
  4. IN is the operator which is 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

 

IN FOR NUMERIC VALUES

#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

 

NOT IN FOR NUMERIC VALUES

#QUERY: (IN TEXT VALUES)

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

#Output

 

SQL IN Operator

#EXPLANATION

The above query resulted in displaying 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

 

NOT IN TEXT VALUES

#EXPLANATION

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

#BETWEEN AND IN OPERATOR

In this both BETWEEN and IN operator 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

 

BETWEEN AND IN OPERATOR

#Explanation

Here in this query details of an employee has 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.

Finally, SQL BETWEEN AND IN OPERATOR Tutorial With Example is over.

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.