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.

Published by Parvinder Nijjar

I blog at ParvTheITGeek.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: