In SQL Server, you have WHERE IN clause, which can specify multiple possible values for a column and returns values that match values in a list. So in this article, we will see working examples of WHERE IN and WHERE IN NOT in SQL Server.
Where IN SQL Server query works as a shorthand for multiple OR Conditions.
Syntax of WHERE IN is as below
SELECT column-names FROM table-name WHERE column-name IN (values)
For example, we have the below-given table, which has all states of India and US.
And we want to find the State name "Rajasthan" or "Michigan", then SQL Query for it would be
SELECT * FROM [CountryState].[dbo].[State] Where StateName In ('Rajasthan', ' Michigan')
Output would be
Where in Sub-Query
We can also use WHERE IN clause in SQL Server, sub-query.
For example, if we will first select only India Country States, which has CountrID = 2
SELECT * FROM [CountryState].[dbo].[State] Where StateName In (SELECT StateName FROM [CountryState].[dbo].[State] Where CountryID = 1)
Above Query will return all states of India.
Where In Not
Similar to WHERE IN, we can also use WHERE IN NOT, basically, it can be used if you want to get State names, other than those mentioned in the query.
Example: We want to get all states other than "Rajasthan", "Michigan", "Gujrat", then sql server would be as below
SELECT * FROM [CountryState].[dbo].[State] Where StateName NOT In ('Rajasthan', ' Michigan', 'Gujrat')
This will return 94 rows.
You may also like to read: