SQL IN Operator

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

SQL IN Operator Syntax

The syntax of the IN operator is

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

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

If you want to check the expression against Multiple values, use the SQL IN Operator syntax.

• Columns: It allows us to choose the number of columns from the tables. It may be one or more.
• Source: If one or more tables are present within the Database, use JOINs to join multiple tables.
• Values: Here, we have to provide the values or expressions we want to check against the Column Name inside an in operator. If the column name matches any Value, then only records will be displayed.

For this example, we are going to use the below-shown data

SQL Server IN Operator Example

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

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE YearlyIncome IN (70000, 80000)

Let us show you one more example for a better understanding.

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

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

SQL Where IN String Example

You can use any data type column inside this Server keyword. This In operator query finds all the Employees present in the Employees table whose Occupation is either Management or Clerical.

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

The below Where in string query returns the employees whose Occupation is either Management, Professional or Clerical

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

SQL Server IN Operator with Multiple Where Conditions

We can also use multiple operators in a single WHERE Clause. In this example, we are going to use IN Operator (Arithmetic) and OR (Logical) within a single statement. The following query returns Employees whose Occupation is either Management or Professional Or their Yearly Income should equal to 50000

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

SQL IN and Order By Clause

Use this along with the Order By Clause. Here, Order By Desc will sort the query result in descending order based on the yearly income column.

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

SQL Subquery IN Operator

It allows you to use inside a Subqueries of Where Clause. Before starting this example, let me show you another table we use here.

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

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

NOT IN Example

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

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

You can also use this one on Numerical columns as well. The below query shows the employees whose yearly income is not 50000 or 80000

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

We can also write a Subquery inside the NOT IN.

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