IN Function in Oracle SQL – PLSQL

The IN function in Oracle SQL / PLSQL is like using multiple OR conditions at once.

Oracle SQL / PLSQL Syntax for IN function in Oracle SQL SELECT Statement is:

SELECT column_name(s)
FROM table_name
WHERE column_name IN(value1, value2 . . . valueN);

Or

Oracle SQL / PLSQL Syntax for IN function in Oracle SQL INSERT Statement is:

INSERT INTO table_name
VALUES(column_name1
,colum_name2
,column_name3
.
.
column_nameN)
WHERE column_name IN(value1, value2 . . . valueN);

Or

Oracle SQL / PLSQL Syntax for IN function in Oracle SQL UPDATE Statement is:

UPDATE table_name
SET (column_name1 = value/expression
,column_name2 = value/expression
, column_name3 = value/expression
.
.
)
WHERE column_name IN(value1, value2 . . . valueN);

Or

Oracle SQL / PLSQL Syntax for IN function in Oracle SQL DELETE Statement is:

DELETE FROM table_name
WHERE column_name IN(value1, value2 . . . valueN);

Let’s take an example for understanding:

Suppose have a table named ‘employee’ in the database as shown below.

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Example 1: Using Oracle SQL / PLSQL IN Function in SQL SELECT Statement

Suppose we want to see the records of employee(s) working either in ‘Sales’ or ‘IT’ department.

The same can be achieved as:

SELECT *
FROM employee
WHERE department IN ('Sales','IT');

The result of the above query will be:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
105	Emp E	32000	Sales	10

The example of Oracle SQL IN Function above shows that we have retrieved the records pertaining to employee(s) working either in ‘Sales’ or ‘IT’ department using IN function in WHERE clause

---

Example 2: Using Oracle SQL / PLSQL IN Function with numbers.

IN function can also be used with numbers.

Let’s assume that we have a new table namely ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Suppose we want to view all the records of employees who are having Salary = ‘20000’, or Salary = ‘32000’.

We can achieve the same as using the SQL SELECT Statement with SQL IN Function as:

SELECT *
FROM employee
WHERE salary IN (20000,32000);

Once we have run the above code following will be the result:

Employee_ID	Employee_Name	Salary	Department	Commission
102	Emp B	20000	IT	20
105	Emp E	32000	Sales	10

By using the IN Function in the example above we have successfully retrieved the records pertaining to employees who having Salary = ‘20000’, or Salary = ‘32000’.

---

Example 3:

Oracle SQL /IN function can also be used with other conditions.

Let’s assume that we have a new table namely ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Suppose we want to view all the records of employees who are having Salary = ‘20000’, or Salary = ‘32000’ and working in ‘Sales’ department.

We can achieve the same using Oracle SQL IN Function in SELECT SQL Statement with WHERE Clause as:

SELECT *
FROM employee
WHERE department = 'Sales'
AND salary IN (20000,32000);

Once we have run the above code following will be the result:

Employee_ID	Employee_Name	Salary	Department	Commission
105	Emp E	32000	Sales	10

Above example shows Oracle SQL /PLSQL IN Function successfully to retrieving the records pertaining to employees who having Salary = ‘20000’, or Salary = ‘32000’ and working in ‘Sales’ department using the IN function in combination with WHERE clause.

---