SQL IN Operator

The SQL Server IN Operator helps restrict the number of rows (or records) the SELECT Statement returns. The SQL IN Operator allows multiple values and checks the given expression or Column name against these values. The SELECT Statement will return the records if there is a match between the row and values. It avoids all the non-matching records from the result set.

We can call this IN operator the filtering tool that helps show the needed records instead of all. This article will explore the importance of SQL Server IN operator and syntax with examples of numbers, dates, strings, and subquery.

One practical scenario of SQL IN Operator is working with Multivalue parameters in SSRS reporting. To allow the users to select multiple values to filter the chart, you must use the IN Operator as the filtering condition. An alternative way is using multiple OR conditions with the combination of the = operator.

Please refer to Multi-Value Parameters and SSRS articles for further reference.

SQL IN Operator Syntax

The syntax of the SQL Server IN operator to accept multiple values as the filtering condition is as follows.

SELECT Column(s)
FROM Table(s)
WHERE [Column_Name] IN (Value1, Value2, ...., ValueN)

We can also use the OR operator to write the above statement.

SELECT Column(s)
FROM Table(s)
WHERE [Column_Name] = Value1 OR
   [Column_Name] = Value2 OR
   [Column_Name] = ValueN

If there are multiple values to check against the expression or column_value, always prefer SQL IN Operator over the OR operator. Let me explain the individual components of the above syntax to understand them better.

  • Columns: Please mention all the column names in a table you want to show the server in the result set. Whatever you mentioned here will only display in the final result set. It may be one or more.
  • Table: Specify the source table name. If you select columns from more than one table in the Database, use JOINS to join multiple tables.
  • Column_Name: It represents the table column you want to compare against the Values.
  • Value1,….ValueN: Here, we must provide the values or expressions inside the SQL IN operator that we want to check against the table Column_Name. If the column_name matches any Value, then that record will display. The process will continue for all the remaining rows in a table.

For this SQL IN operator example, we will use the below-shown data.

Employee Table

SQL Server IN Operator on Numbers Example

The SQL Server allows you to use the IN operator on Numeric columns or numbers to filter them. For example, if you want Customers whose has done some landmarking orders, such as 10, 25, 50, 75, 100, 125, and 150. In such a case, you can place all those numbers within the IN operator parentheses to check against the orders column in a customer table.

The following SQL IN operator query will find all the employees in the Employee table whose YearlyIncome is either 70000 or 80000.

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

The last line of the code can also write using the OR operator.

WHERE YearlyIncome = 70000 OR YearlyIncome = 80000
SQL IN Operator On Numbers

Let us show you another example to better understand the SQL IN operator on the Numbers column. The following query returns the employees whose Yearly Income is either 50000, 70000, 80000, or 90000.

If you write the same query using the OR operator, it becomes lengthy and tedious.
WHERE YearlyIncome = 50000 OR YearlyIncome = 70000 OR YearlyIncome = 80000 OR YearlyIncome = 90000.

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

SQL IN Operator on String Columns

You can use any data type column inside this Server IN keyword. This SQL IN operator query finds all employees in the Employee table whose Occupation is either Management or Clerical.

  • The SELECT statement selects the EmpID, FirstName, LastName, Education, Occupation, YearlyIncome, and Sales columns from the Employee table.
  • The WHERE clause uses the Occupation column to filter the Employee table result set from the above SELECT statement. 
  • Within the parenthesis, there are Management or Clerical words. So, for each record or row, the SQL IN operator checks these two words against the Occupation column. If the Occupation matches either Management or Clerical, that column will add to the result set.
  • The process continues for all 14 rows.
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE [Occupation] IN ('Management', 'Clerical')
On string column

TIP: Use the NOT IN operator to exclude the values that match the expression in the final result set.

The below SQL WHERE IN string operator 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 IN Operator On String Columns

SQL Server IN Operator with Multiple Where Conditions

We can also use multiple SQL IN operators in a single WHERE Clause. In this example, we will use IN Operator and OR (Logical) within a single statement to simultaneously apply a filter against string and number columns.

The following query returns employees whose Occupation is either Management or Clerical. Or their Yearly Income should equal either 70000 or 90000. If one of the above two conditions matches, that row will add to the final result set.

SELECT [EmpID],[FirstName],[LastName],[Education]
      ,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE [Occupation] IN ('Management', 'Clerical') OR
[YearlyIncome] IN (70000, 90000)
SQL Server IN Operator with Multiple Where Conditions on string and numbers

IN and Order By Clause

Use this SQL IN operator with the combination of Order By Clause to sort the final results in a particular order. 

This example returns all employees whose Occupation is Professional, Skilled Manual, or Income is 45000, 70000. Next, the ORDER BY clause sorts the result set based on the Sales column in ascending order. Add the DESC keyword after the Sales to sort them in descending order.

SELECT [EmpID],[FirstName],[LastName],[Education]
      ,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE [Occupation] IN ('Professional', 'Skilled Manual') OR
[YearlyIncome] IN (45000, 70000)
ORDER BY Sales
SQL IN Operator and Order By Clause

SQL IN Operator with a Subquery

The primary goal of the IN operator is to compare a column value against multiple values. However, it is not limited to comparing column names against a fixed set of values. You can use the SQL Server IN operator with a subquery to retrieve values dynamically.

The working functionality of the IN operator is to check the specified column against values within the parenthesis. It doesn’t matter whether it is a static value or a single-column result set returned by the subquery. Suppose it is a subquery within the parenthesis after the WHERE Clause. In that case, the IN operator compares the column name against the list of values in a result set.

For instance, retrieve the customers with sales in the current financial year. Before starting this example, let me show you another table we use here.

SQL IN Operator 8

The following query will return all the Employees whose Occupation is present in the Employees 2015 table.

  1. The Main query SELECT statement selects all the Employee columns.
  2. The Subquery selects all Occupations available in the Employees 2015 table.
  3. The SQL IN operator filters the main query employees whose Occupation is also in the subquery.
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE [Occupation] IN (SELECT [Occupation] 
 FROM [Employees 2015])
SQL IN Operator With Subquery

We have taken one more example of the subquery, and for this demonstration, we use the AdventureWorksDW2019 database. The SQL IN Operator query below returns all the products from the DimProduct table with at least a single sale, and the product color is Red, NA, or Black.

SELECT [ProductKey], [EnglishProductName]
	,[Color],[DealerPrice],[EnglishDescription]
FROM [DimProduct]
WHERE [ProductKey] IN (
	SELECT [ProductKey] FROM [FactResellerSales]
	WHERE Color IN ('Red', 'NA','Black')
SQL IN Operator With Subquery Example 2

SQL IN Operator Working on Dates

One of the most incredible features of the SQL IN Operator is that it allows you to work with dates and filter the table using multiple date values. For instance, you want to display the beginning month of each quarter, such as January, April, July, and October. In that case, you can use the IN operator and MONTH function to display the sales of those Dates in a result set.

The following IN operator query returns all the employees whose Hire Date is 2010-12-29 15:10:02.407, 2008-09-22 19:04:22.123, 2009-08-12 15:13:14.113, 2013-09-15 15:13:14.137, and 2014-05-15 05:03:10.157.

SELECT [EmpID],[FirstName],[LastName],[Education]
      ,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE [HireDate] IN ('2010-12-29 15:10:02.407',
	'2008-09-22 19:04:22.123',
	'2009-08-12 15:13:14.113',
	'2013-09-15 15:13:14.137', '2014-05-15 05:03:10.157')
SQL IN Operator Working on Dates Column

The above example filters the records belonging to a few important dates. What if you need the employees hired in particular years? In this scenario, you can use the SQL Server IN operator and YEAR function. The YEAR function extracts the year from the hire date column, and the IN operator checks each HireDate column row against years instead of dates.

For example, the below code returns all employees whose HireDate year is 2013, 2014, 2009, and 2006. If you observe the following image, all the employees whose HireDates fall in the above years are only displayed in the final result.

SELECT [EmpID],[FirstName],[LastName],[Education]
      ,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE YEAR([HireDate]) IN ('2013', '2014', '2009', '2006')
SQL IN Operator Working on Calendar Year

NOT IN Example

We can also use the NOT Keyword along with the SQL 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')
SQL NOT IN Operator Example

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)
NOT IN Example

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])
NOT IN Subquery
Categories SQL