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.

Consider the following query.

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.

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

SELECT EmpId, FirstName, LastName, Salary
FROM Employee
WHERE FirstName IN ('james','john','abdul');

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.

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.

NOT IN

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

SELECT EmpId, FirstName, LastName, Salary
FROM Employee
WHERE EmpId NOT IN (1, 3, 5);