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.
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.
WHERE column_name BETWEEN value1 AND value2;
Let’s create a database to understand BETWEEN & IN Operator in SQL.
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;
Using BETWEEN with Numeric Values
List all the Employee’s Names who is having salary between 30000 and 45000.
SELECT Name FROM Emp WHERE Salary BETWEEN 30000 AND 45000;
Using BETWEEN with Date Values
Find all the Employees an Age Between 22 to 24.
SELECT Name FROM Emp where Age BETWEEN '22' AND '24';
Using the NOT Operator with BETWEEN
Find all the Employee names whose salary is not in the range of 30000 and 45000.
SELECT Name FROM Emp WHERE Salary NOT BETWEEN 30000 AND 45000;
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.
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.
SELECT Name FROM Emp WHERE Salary IN (30000, 40000, 25000);
Find the Fname and Lname of all the Employees who has a Salary not equal to 25000 or 30000.
SELECT Name FROM Emp WHERE Salary NOT IN (25000, 30000);
This article is contributed by Anuj Chauhan. If you like GeeksforGeeks and would like to contribute, you can also write an article using write.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please write comments if you find anything incorrect, or if you want to share more information about the topic discussed above.
Please Login to comment...