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

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.

sql-server-where-in-clause

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

sql-server-where-in-example

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:

Get Last 3 months records in SQL Server

How to Edit More than 200 rows in SQL Server

Import an SQL file in MySQL (Using CMD or Powershell)

Find nth highest salary in SQL Server

Return Multiple values in C# (Various ways)