SQL IN Operator

The IN Operator in SQL Server is used to restrict the number of rows (or records) returned by the SELECT Statement. The IN Operator check the given expression or Column name against the Values inside the IN Operator, If there is at least one match then SELECT Statement will return the records

TIP: If you want to check the expression against Multiple values then use IN Operator.

SQL Where IN Operator Syntax

The basic syntax of the IN operator in SQL Server can be written as:

SELECT [Column Names]
FROM [Source]
WHERE [Column Name] IN (Value1, Value2, ...., ValueN)

--We can also use SQL OR operator to write the above statement
SELECT [Column Names]
FROM [Source]
WHERE [Column Name] = Value1 OR
      [Column Name] = Value2 OR
       .... OR
      [Column Name] = ValueN

• Columns: It allows us to choose the number of columns from the tables. It may be One or more.
• Source: One or more tables present in the Database. SQL JOINs are used to join multiple tables.
• Values: Here we have to provide the values or expression we want to check against the Column Name. If the column name matches with any Value then only records will be displayed.

In this article, we will show you, How to use the IN Operator in SQL Server with examples. For this, We are going to use the below-shown data

SQL IN Example 1

The following query will find all the Employees present in the Employees table whose [Yearly Income] is either 70000 or 80000

-- SQL Server IN Example
USE [SQL Tutorial]
GO
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE YearlyIncome IN (70000, 80000)

OUTPUT

Let us show you one more example for better understanding.

SQL IN Operator Numerical Example 2

The following query returns the Employees whose Yearly Income is either 70000, 80000, or 90000

-- SQL Server IN Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE YearlyIncome IN (70000, 80000, 90000)

OUTPUT

SQL Where IN String Example 1

You can use any data type column inside the IN operator.

The following query will find all the Employees present in the Employees table whose Occupation is either Management or Clerical

-- SQL Server IN Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE [Occupation] IN ('Management', 'Clerical')

OUTPUT

SQL Where IN String Example 2

This example returns the employees whose Occupation is either Management, Professional or Clerical

-- SQL Server IN Example
USE [SQL Tutorial]
GO
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE [Occupation] IN ('Management', 
			'Professional', 'Clerical')

OUTPUT

IN Operator in SQL With Multiple Conditions

SQL Server allows us to use multiple operators in single WHERE Clause. In this example, we are going to use IN Operator (Arithmetic Operator) and OR Operator (Logical Operator) in a single statement.

The following query returns Employees whose Occupation is either Management or Professional Or their Yearly Income should equal to 50000

-- SQL Server IN Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE [Occupation] IN ('Management', 'Clerical')
  OR YearlyIncome = 50000

OUTPUT

SQL IN Operator With Order By Clause

In SQL Server, you can use this IN operator along with the Order By Clause. Here, Order By Desc will sort the query result in descending order based on the yearly income column.

-- SQL Server IN Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE [Occupation] IN ('Management', 'Clerical')
  OR YearlyIncome = 50000
  ORDER BY YearlyIncome DESC

OUTPUT

SQL Where IN Operator in Subquery

The IN operator allows you to use it inside a Subqueries in Where Clause.

Before we start this example, let me show you another table that we use here

For example, the following query will return all the Employees whose Occupation is present in the Employees 2015 table.

-- SQL Server IN Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE [Occupation] IN (SELECT [Occupation] 
			FROM [Employees 2015])

OUTPUT

SQL NOT IN Example

In SQL, We can also use the NOT Keyword along with the IN operator. For example, the following query finds all the employees present in the employee table whose Occupation is NOT Management and Professional

-- SQL Server NOT IN Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE [Occupation] 
		NOT IN ('Management', 'Professional')

OUTPUT

SQL NOT IN Example 2

IYou can also use this NOT IN operator on Numerical columns as well. Below query returns the employees whose yearly income is not 50000 or 80000

-- SQL Server NOT IN Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE [YearlyIncome] NOT IN (50000, 80000)

OUTPUT

SQL NOT IN Example 3

In SQL, you can also write a Subquery inside the NOT IN operator.

-- SQL Server NOT IN Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE [Occupation] NOT IN (SELECT [Occupation] 
				FROM [Employees 2015])

OUTPUT