Search for a list of values using the IN command in SQL — Parv The IT Geek

Have you ever been presented with a list of values which need to be found in a column or wondered how to search for a list of exact names, addresses, numbers without using wildcard searches or ranges.

Well SQL has the answer and it is the IN command.

USE AdventureWorks 
GO 
SELECT * 
FROM HumanResources.Department

The above query produces 16 rows of information when there are no clauses filtering the results.

If you wanted to know how many departments were in the Manufacturing group you would use the below query.

SELECT * 
FROM HumanResources.Department 
WHERE GroupName ='Manufacturing'

If you wanted to know how many departments were in the Inventory Management group you would use the below query.

SELECT * 
FROM HumanResources.Department 
WHERE GroupName ='Inventory Management'

You could combine both queries as show below using the OR command to filter for more than one value.

SELECT * 
FROM HumanResources.Department 
WHERE GroupName ='Inventory Management' 
OR GroupName ='Manufacturing'

However, though it does achieve our desired result there are is a better method which I shall show you below now using the IN command.

SELECT * 
FROM HumanResources.Department 
WHERE GroupName IN ('Inventory Management','Manufacturing')

The above query produces the same results but is far superior in its readability and ease of use. We can easily see the filter and there are fewer commands which measns less to go wrong and less to type.

Experiment with numbers, dates and words on the database to familiarise your self with this new query.

Originally published at https://parvtheitgeek.com on January 23, 2014.