Oracle IN

The Oracle IN operator is used to check whether the column value matches any of the values in list, that we give. We can also use a subquery instead of manually giving the list of values to compare against.

The syntax of Oracle IN operator is as follows:

SELECT column_names 
FROM table_name 
WHERE column_name IN [ value1, value2, value3 etc.]

This is an example in which we can compare whether column_name is equal to any of the values that we give in a list.

Similarly, we can also use a subquery instead of giving list of values manually.

Syntax while using IN operator with subquery is as follows:

SELECT column_names 
FROM table_name 
WHERE column_name IN [ subquery]

If the column has any values that match against the set of values we give, it will return all the rows matching those values from the table.

Lets understand this more clearly with the help of examples.

Oracle IN operator examples

We will be using Employee Table from HR schema for these examples:

1. Oracle IN operator example

Suppose we want to find all the employees who belong to either department 80, 90 or 100 then we can simply use this query:

SELECT first_name, salary, department_id
FROM employees
WHERE department_id IN (80, 90, 100);

You can think of IN operator as ‘equals to’ as it matches against a set of values instead of one single value and retrieves all the data that matches against those set of values.

Result:

This query retrieves all 43 employees who are working in departments 80, 90 or 100.

2. Oracle NOT IN operator example

Using NOT before IN operator, simply returns all the values that don’t match any of the values we give in list.

Here’s the same query we used before, but we add NOT before IN operator this time:

SELECT first_name, salary, department_id
FROM employees
WHERE department_id NOT IN (80, 90, 100);

Result:

As we can see, this time query returns all the employees who don’t belong to either 80, 90 or 100.

3.Oracle IN Subquery Example

Subquery is simply a query within a query. Lets see how we can use IN operator to match against the set of values that subquery returns.

In this example, we will find all the employees who work in a department where at least one employee is earning more than 10000.

SELECT first_name, department_id 
FROM employees
WHERE department_id IN ( select department_id from employees where salary > 10000);

If we just use the subquery in the bracket:

SELECT department_id FROM employees WHERE salary > 10000

It returns all the departments where employee salary is greater than 10000.

Now, lets consider the entire query:

Result:

If we consider the entire query, then it returns all the employees who work in departments where at least one employee is earning more than 10000.

4. Oracle NOT IN Subquery example

We will be using the same query as above, but this time we will add the NOT operator before IN.

Using this query, we are trying to find all the employees who are working in departments where not even one employee is earning more than 10000.

SELECT first_name, department_id
FROM employees
WHERE department_id NOT IN ( select department_id from employees where salary > 10000);

Result:

Using this query, we find all the employees who are working in departments where no one is earning more than 10000.

Comparison between Oracle IN and OR operators

Using the IN operator is the same as using multiple OR operators.

Consider this query which returns all the employees belonging to department 60, 70 or 80.

SELECT first_name, salary, department_id
FROM employee
WHERE department_id IN (60, 70, 80);

Result:

We also use multiple OR operators to return the same result:

See this query,

SELECT first_name, salary, department_id
FROM employees
WHERE department_id = 60
OR department_id = 70
OR department_id=80;

Result:

As we can see, the result is the same as using IN operator.

In this article, we learned in depth about the Oracle IN operator.