Membership Condition Using the IN Operator

Topic Introduction: In this tutorial, we will see the Membership Condition Using the IN Operator on Oracle SQL. The IN operator allows us to specify multiple values in a WHERE clause. It is also used for Sub Query in the WHERE clause. Basically, The IN operator is internally evaluated by the Oracle server as a set of OR conditions, such as a specific column/expression=value1 or column/expression=value2 or column/expression=value3. Therefore, using the IN operator has no performance benefits and is used only for logical simplicity.

Syntex

Where column/expression in (value/expression,value/expression,value/expression)

Where column/expression in (sub query)

Example 1

select employee_id,last_name,hire_date,salary,department_id

from employees

where department_id in (30,40,50);

Example 2

select employee_id,last_name,hire_date,salary,department_id

from employees

where last_name in ('King','Urman','Davies');

Example 3

select employee_id,last_name,hire_date,salary,department_id

from employees

where employee_id in (select employee_id from employees where department_id = 100);

Explanation of Queries

In example 1 I want to find out that all employees of department IDs 30 or 40 or 50 using the IN operator. here we can use more values in where the condition. 

In example 2 I want to find out the employees whose name is King or Urman or Davies using the IN operator. 

In example 3 we used Sub Query after IN operator to find out the employee list whose department id is 100.

Note 1. If characters or dates are used in the condition values, they must be enclosed with single quotation marks ('').