SQL - IN Operator

The IN operator is used to specify the list of values or sub query in the WHERE clause. A sub-query or list of values must be specified in the parenthesis e.g. IN (value1, value2, ...) or IN (Select query).

Syntax:

SELECT column1, column2,..
FROM table
WHERE column IN (value1, value2, value3,...)

-- or

SELECT column1, column2,..
FROM table
WHERE column IN (SELECT query)

For the demo purpose, we will use the following tables in all examples.

Employee Table
EmpId FirstName LastName Email Salary DeptId
1 'John' 'King' '[email protected]' 33000 1
2 'James' 'Bond' 1
3 'Neena' 'Kochhar' '[email protected]' 17000 2
4 'Lex' 'De Haan' '[email protected]' 15000 1
5 'Amit' 'Patel' 18000 3
6 'Abdul' 'Kalam' '[email protected]' 25000 4
Department Table
DeptId Name
1 'Finance'
2 'HR'
3 'Sales'
4 'Admin'

Consider the following query.

SQL Script: IN Operator
SELECT EmpId, FirstName, LastName, Salary
FROM Employee
WHERE EmpId IN (1, 3, 5, 6)

The above query will return records where EmpId is 1 or 3 or 5 or 6. The above query will display the following result.

EmpId FirstName LastName Salary
1 'John' 'King' 33000
3 'Neena' 'Kochhar' 17000
5 'Amit' 'Patel' 18000
6 'Abdul' 'Kalam' 25000

The following query uses the string values with the IN operator.

SQL Script: IN Operator
SELECT EmpId, FirstName, LastName, Salary
FROM Employee
WHERE FirstName IN ('james','john','abdul');
EmpId FirstName LastName Salary
1 'John' 'King' 33000
2 'James' 'Bond'
6 'Abdul' 'Kalam' 25000

Note that wildcard characters '%', '_', etc. cannot be used with the string values.

Sub-query with IN Operator

You can use the subquery with the IN operator that returns records from the single column. The subquery cannot include more than one column in the SELECT column list.

SQL Script: IN Operator
SELECT EmpId, FirstName, LastName, DeptId
FROM Employee
WHERE DeptId IN (SELECT DeptId from Department WHERE DeptId > 2);

In the above query, the sub-query SELECT DeptId from Department WHERE DeptId > 2 returns two DeptId, 3 and 4. So, now the query would be like SELECT EmpId, FirstName, LastName, Salary FROM Employee WHERE DeptId in (3, 4);. The following is the result.

EmpId FirstName LastName DeptId
5 'Amit' 'Patel' 3
6 'Abdul' 'Kalam' 4

NOT IN

Use the NOT operator with the IN operator to filter records that do not fall in the specified values.

SQL Script: NOT IN Operator
SELECT EmpId, FirstName, LastName, Salary
FROM Employee
WHERE EmpId NOT IN (1, 3, 5);
EmpId FirstName LastName Salary
2 'James' 'Bond'
4 'Lex' 'De Haan' 15000
6 'Abdul' 'Kalam' 25000