SQL | BETWEEN & IN Operator

Pre-requisites: SQL Operators

Operators are the foundation of any programming language. We can define operators as symbols that help us to perform specific mathematical and logical computations on operands. In other words, we can say that an operator operates the operands. 

In this article, we will see  BETWEEN & IN Operator of SQL. 

Between Operator

The SQL BETWEEN condition allows you to easily test if an expression is within a range of values (inclusive). The values can be text, date, or numbers. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement. The SQL BETWEEN Condition will return the records where the expression is within the range of value1 and value2.

Syntax: 

SELECT column_name(s)

FROM table_name

WHERE column_name BETWEEN value1 AND value2;

Let’s create a database to understand BETWEEN & IN Operator in SQL.

Query:

CREATE TABLE Emp(
    EmpID INT PRIMARY KEY,
    Name VARCHAR(50),
    Country VARCHAR(50),
    Age int(2),
  Salary int(10)
);
-- Insert some sample data into the Customers table
INSERT INTO Emp (EmpID, Name,Country, Age, Salary)
VALUES (1, 'Shubham',  'India','23','30000'),
       (2, 'Aman ',  'Australia','21','45000'),
       (3, 'Naveen', 'Sri lanka','24','40000'),
       (4, 'Aditya',  'Austria','21','35000'),
       (5, 'Nishant', 'Spain','22','25000');
       Select * from Emp; 
 

Output:

 

Using BETWEEN with Numeric Values

List all the Employee’s Names who is having salary between 30000 and 45000. 

Query:

SELECT Name
FROM Emp
WHERE Salary
BETWEEN 30000 AND 45000;

Output: 

 

Using BETWEEN with Date Values

Find all the Employees an Age Between 22 to 24.

Query:

SELECT Name
FROM Emp
where Age
BETWEEN '22' AND '24';

Output: 
 

 

Using the NOT Operator with BETWEEN 

Find all the Employee names whose salary is not in the range of 30000 and 45000. 

Query:

SELECT Name
FROM Emp 
WHERE Salary
NOT BETWEEN 30000 AND 45000;

Output: 

 

IN Operator

IN operator allows you to easily test if the expression matches any value in the list of values. It is used to remove the need for multiple OR conditions in SELECT, INSERT, UPDATE, or DELETE. You can also use NOT IN to exclude the rows in your list. We should note that any kind of duplicate entry will be retained. 

Syntax: 

SELECT column_name(s)

FROM table_name

WHERE column_name IN (list_of_values);

Find the Fname, and Lname of the Employees who have a Salary equal to 30000, 40000, or 25000. 

 Query:

SELECT Name
FROM Emp
WHERE Salary IN (30000, 40000, 25000);

Output: 
 

 

Find the Fname and Lname of all the Employees who has a Salary not equal to 25000 or 30000.

Query:

SELECT Name
FROM Emp
WHERE Salary NOT IN (25000, 30000);

Output: