SQL: IN Operator

We’ve seen that Between defines a range of values to check against for inclusion or exclusion from the result table. This is not always enough. What if we needed to check for certain values only? Values that do not always fit into a neat range. To accommodate this, SQL allows the use of the IN operator. In SQL, there are two uses of the IN keyword, and this section introduces the one that is related to the Where clause. When used in this context, we know exactly the value of the returned values we want to see for at least one of the columns.

The syntax for using the IN keyword is as follows:

SELECT column_name 
FROM derived_table 
WHERE column_name [NOT] IN ('value1', 'value2', ... ,'value_n') 

This SQL statement will return the records where column1 is value1, value2…, or value_n. The number of values in the parenthesis can be one or more, and they can be numerical or characters.

Example #1

Select * 
From EmployeeAddressTable 
Where FirstName IN ('Mary', 'Sam') 

This would return all rows where the FirstName is either Mary or Sam. Because the * is used in the select, all fields from the EmployeeAddressTable table would appear in the result set.

Example #2

SELECT * 
FROM EmployeeAddressTable 
WHERE FirstName = 'Mary' 
OR FirstName = 'Sam' 

It is equivalent to the previous statement. As you can see, using the IN function makes the statement easier to read and more efficient.

Example #3

Select * 
From EmployeeAddressTable 
Where FirstName NOT IN ('Mary', 'Sam') 

This query uses the NOT operator to retrieve all records where the employee’s lastname is neither Mary nor Sam. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

Example #4

Select * 
From EmployeeStatisticsTable 
Where Benefits in (15000, 12000) 

You can also use the IN function with numeric values. This SQL statement would return all Employees for which Benefits is either 15000 or 12000.