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);