Back to: Oracle Tutorials for Beginners and Professionals
IN Operator in Oracle with Examples
In this article, I am going to discuss IN Operator in Oracle with Examples. Please read our previous article, where we discussed Logical Operators (AND, OR, NOT) in Oracle with Examples. At the end of this article, you will understand IN, and NOT IN Operators as well as the Difference between IN and OR Operator in Oracle with examples.
IN Operator in Oracle:
The IN Operator in Oracle is used to search for specified values that match any value in the set of multiple values it accepts. The “IN” operator evaluates multiple values on a single data column. It displays the data row if any one of the given values is matched with the data column value. If none of the values matches, the SQL statement won’t return that data row.
We generally use the IN operator with WHERE clause to compare column or variable values with a set of multiple values. The “IN” operator is written as the word ‘IN’ followed by multiple values separated by a comma inside brackets.
Syntax: Expression IN (value1, value2,.. valuen); where expression specifies the value to test and value1, value2, or value n are the mentioned values to be tested against expressions. If any of the values match then it evaluates to true otherwise false.
Understanding IN Operator in Oracle:
Let us understand IN Operator in Oracle with Examples. We are going to use the following Employee table to understand the IN operator.
Please execute the below SQL query to drop the existing Employee table and create a new Employee table with the required sample data. We also set the linesize to get the output in the above format.
SET linesize 300; DROP Table Employee; CREATE TABLE Employee ( Id INT PRIMARY KEY, Name VARCHAR(15), Department VARCHAR(10), Salary NUMBER(8, 2), Gender VARCHAR(10), Age INT, City VARCHAR(10) ); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1001, 'John', 'IT', 35000, 'Male', 25, 'London'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1002, 'Smith', 'HR', 45000, 'Female', 27, 'Mumbai'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1003, 'James', 'Finance', 50000, 'Male', 28, 'Delhi'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike', 'Finance', 50000, 'Male', 28, 'London'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda', 'HR', 75000, 'Female', 26, 'Mumbai'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1006, 'Anurag', 'IT', 35000, 'Male', 25, 'London'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1007, 'Priyanla', 'HR', 45000, 'Female', 27, 'Mumbai'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1008, 'Sambit', 'IT', 50000, 'Male', 28, 'London'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1009, 'Pranaya', 'IT', 50000, 'Male', 28, 'London'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1010, 'Hina', 'HR', 75000, 'Female', 26, 'Mumbai');
IN Operator Example in Oracle:
Suppose, our business requirement is to fetch all the employees from the Employee table whose department is either IT or HR, then we need to write the SELECT SQL Query using IN Operator as shown below.
SELECT * FROM Employee WHERE Department IN (‘IT’, ‘HR’);
Once you execute the above SELECT SQL Query, then you will get the following result set which includes only the IT and HR departments employees.
Note: Returns true if the value is available in the given list of values. Supports with all types of data (data types).
NOT IN Operator Example in MySQL:
The NOT IN Operator in Oracle is just the opposite of IN Operator. The IN operator in Oracle takes a set of values and then returns the records whose column values are matched with the values it has. But if you use the NOT keyword along with the IN operator in Oracle, then it will return data where the column value is not in the set of values. For example, the following SQL query will return all the employees from the employee table where the Department is not in HR and Finance. In other words, we can say that the following SQL Query is going to return only the IT department employees.
SELECT * FROM Employee WHERE Department NOT IN (‘HR’, ‘Finance’);
When you execute the above SELECT statement, then you will get the following result set which includes only the IT department employees.
Advantages of IN Operator over OR Operator in Oracle:
Although both IN and OR going to provide the same results, IN condition is more preferable because it has the minimum number of codes as compared to OR condition. For example, the following SQL query returns all the employees who belong to the IT or HR department using the Oracle OR Operator.
SELECT * FROM Employee WHERE (Department = ‘IT’ OR Department = ‘HR’);
The following SQL Query also returns all the employees who belong to the IT or HR department using the Oracle IN Operator.
SELECT * FROM Employee WHERE Department IN (‘IT’, ‘HR’);
In both the SQL Query, you will get the same results as shown in the below image.
As you can see, with the OR operator we need to mention the column name multiple times to check multiple conditions whereas using IN Operator we need to mention the column name only once with all the values to be mentioned inside round brackets. So, in the case of IN Operator, the codes are less as compared to the OR operator.
Example: Fetch all the employees whose age is either 25 or 26
Our requirement is to fetch all the employees whose age is either 25 or 26. Then using IN operator we can get the results very easily. The following SQL Query will retrieve all the employees from the Employee table where the employee age is either 25 or 26.
SELECT * FROM Employee WHERE Age IN (25, 26);
Once you execute the above query, you will get the following output.
In Operator Inside DML statements:
In oracle, it is also possible to use the IN operator inside the DML (Update and Delete) statements. Let us understand this with an example. Suppose, we want to increase the Salary by 200 for the employees whose Id is 1002, 1004, and 1006, then we can write an UPDATE DML statement using IN operator as shown below.
UPDATE Employee SET Salary=Salary+200 WHERE ID IN (1002, 1004, 1006);
Suppose, you want to delete the employees whose Id is 1003 and 1005, then we can write the DELETE statement using IN operator as follows.
DELETE FROM Employee WHERE ID IN (1003, 1005);
Note: In the same way, we can also use the NOT IN operator with the DML statement and this is a task for you to check yourself and submit your code in the comment section. If you still getting some issues then let me know by putting your query in the comment section.
In the next article, I am going to discuss Between Operator in Oracle with Examples. Here, in this article, I try to explain IN Operator in Oracle with Examples and I hope you enjoy this IN Operator in Oracle with Examples article. If you have any queries regarding the Oracle IN Operator, then please let us know by putting your query in the comment section.