The SQL Server IN Operator helps restrict the number of rows (or records) the SELECT Statement returns. Instead of writing multiple OR conditions, the IN Operator allows multiple values and checks whether the given expression or column name is present in any of those values. The SELECT Statement will return the records if there is a match between the row and the set of values. It avoids all the non-matching records from the result set.
We can call this SQL Server IN operator the filtering tool that helps show the needed records instead of all. This is very useful when you want to select rows that match more than two conditions. It is more readable, clearer, and performs faster. This article will explore the importance of the IN operator and syntax with examples of numbers, dates, strings, and subqueries.
One practical scenario of the SQL IN Operator is working with Multivalue parameters in SSRS reporting. To allow users to select multiple values to filter the chart, you must use the IN Operator as the filtering condition. An alternative way is to use multiple OR conditions with the combination of the =. For instance, the query below returns all the products belonging to jeans and Shirts.
SELECT * FROM products WHERE subcategory IN('Jean', 'Shirts')
Please refer to the Multi-Value Parameters and SQL Server Reporting Services articles for further reference.
SQL Server IN Operator Syntax
Generally, we use the WHERE clause to check a single condition, whereas to check multiple values against the condition use the IN operator. The syntax of the 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)
If there are multiple values to check against the expression or column_value, always prefer SQL IN 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 that you want to show on the server in the result set. Whatever you mentioned here will only be displayed 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. It may be a column or a valid expression.
- Value1,….ValueN: Here, we must provide the values or expressions inside the IN operator that we want to check against the table Column_Name. If the column_name matches any value, then the IN operator will return Boolean TRUE. So the matching rows or records will display. The process will continue for all the remaining rows in a table.
There are some situations where we use the IN operator to check values returned by the SUBQUERY. It means replacing (Value1,…, ValueN) with a Subquery. In the next SQL IN operator section, we will show you multiple examples that include strings, numbers, subqueries, joins, etc, to understand the concept.
For this SQL IN operator example, we will use the Employee and Department tables that we mentioned in the DELETE statement article. Although we have used the SELECT statement for most of the examples, you can use the same technique to implement the IN operator with the DELETE and UPDATE commands.
SQL Server IN Operator on Numbers Example
We can use the IN operator on Numeric columns or numbers to filter the selected data. For example, if you want customers who have made some landmark orders, such as 10, 25, 50, 75, 100, 125, and 150. In such a case, you can place all those numbers within the parentheses to check against the orders column in a customer table. The list of common scenarios of using the IN operator on integer columns.
- Finding employees in a particular salary bracket.
- Finding products of different prices, such as 10, 50, 100, 200, and 500.
- Stores with Sales value of 10,000, 50,000, and 100,000 per month.
The following SQL Server IN operator query uses the YearlyIncome numeric or number column to filter the selected data. It will find all the employees in the Employee table whose YearlyIncome is either 70000 or 80000.
SELECT * FROM [Employee]
WHERE YearlyIncome IN (70000, 80000)
To better understand the SQL IN operator on the Numbers column, let me show you another example. The following query returns the employees whose Yearly Income is either 50000, 70000, 80000, or 90000.
SELECT * FROM [Employee] WHERE YearlyIncome IN (50000, 70000, 80000, 90000)
Similarly, you can try the following query to find the employees whose sales are either 24.99, 59.53, 2320.49, or 3078.27.
SELECT * FROM [Employee] WHERE Sales IN (24.99, 59.53, 2320.49, 3078.27)
NOTE: The IN operator will ignore any duplicate values submitted as the argument values. It is always advisable to check for them and remove them for query performance, but don’t worry, the query won’t throw an error.
SQL IN Operator on String Columns
Microsoft SQL Server allows you to use any data type column inside the IN operator, and this section covers the string columns. The following queries use the VARCHAR and NVARCHAR columns as the arguments. However, you can use any string column that has data types such as VARCHAR, CHAR, NVARCHAR, and NCHAR.
The following are some of the real-time scenarios that we IN operator to get the result set.
- Finding products in the Mobiles, Laptops, and Desktops categories.
- Display Employees in Sales, HR, and Engineering Departments.
- Finding Customers living in the USA, Canada, and Mexico.
This 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 parentheses, there are Management or Skilled Manual 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', 'Skilled Manual')
SELECT * FROM [Employee] WHERE [Occupation] IN ('Management', 'Skilled Manual')
TIP: Use the NOT IN to exclude the values that match the expression in the final result set.
The query below uses the IN operator inside the WHERE clause on a string column. It returns employees whose Education is either Masters Degree, a Partial High School, or a High School.
SELECT * FROM [Employee] WHERE [Education]
IN ('Masters Degree', 'Partial High School', 'High School')
SQL IN Operator Working on Dates
One of the most incredible features of the 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 the MONTH function to display the sales of those Dates in a result set. Some of the common scenarios of using DATES inside the IN operator are:
- Finding the total number of customers who walk into the store on Black Friday sales every year.
- Finding the sales on Christmas Eve, Black Friday, and Easter.
- Sales of particular years.
The following SQL Server IN operator query returns all the employees whose Hire Date is 2010-12-29, 2008-09-22, 2009-08-12, 2013-09-15, and 2014-05-15.
SELECT [EmpID],[FirstName],[LastName],[Education]
,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE [HireDate] IN ('2010-12-29','2008-09-22',
'2009-08-12','2013-09-15', '2014-05-15')
TIP: Date values must be enclosed in a single quote. Otherwise, it will raise an error.
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 IN operator and the 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 code below 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 * FROM [Employee]
WHERE YEAR([HireDate]) IN ('2013', '2014', '2009', '2006')
SQL IN Operator Vs OR Operator
The working functionality of both the IN and logical OR operators is the same, and we can say that the IN operator is the shorter and more readable version of OR. Apart from that, there are some key advantages of using the IN operator over the OR, and they are:
- While working with multiple conditions, the performance of the IN operator is faster than the OR operator.
- You can use the IN operator with the combination of logical operators (AND, OR, and NOT) to increase the list items.
- It is easy to read and understand the IN operator code compared to OR.
We have already mentioned the syntax of the IN operator, and the following code shows the OR operator version of writing the IN statement.
SELECT Column(s) FROM Table(s)
WHERE [Column_Name] = Value1 OR
[Column_Name] = Value2 OR
[Column_Name] = ValueN
For instance, if you want to find the products belonging to Furniture, Clothing, and Footwear. We can use either the OR operator or the SQL IN operator.
SELECT * FROM products
WHERE category IN ('Furniture', 'Clothing', 'Footwear')
Using the OR operator.
SELECT * FROM products
WHERE category = 'Furniture' OR category = 'Clothing' OR category = 'Footwear'
When you use two or three conditions, both look OK and are more readable. However, if you add one or more conditions (such as Kitchen, Electronics), the OR operator looks lengthy and tedious.
To demonstrate the same, we used the above query that we mentioned in the Numeric column section. Here, we write the same IN query using the OR operator to show the difference.
SELECT * FROM Employee
WHERE YearlyIncome = 50000 OR YearlyIncome = 70000 OR
YearlyIncome = 80000 OR YearlyIncome = 90000.
Multiple IN Operators in SQL
We can also use multiple IN operators in a single WHERE Clause. As we mentioned earlier, we can use the IN operator along with any of the logical operators, such as AND, OR, and NOT, to further extend the searches.
In this example, we will use the SQL Server IN Operator and the Logical AND operator within a single statement to simultaneously apply a filter against string and number columns.
There are some situations where a brand may have have wide range of categories and in those situations, we can use multiple IN operators to delve deeper into the product line. For instance, assume Pepe Jeans, Wangler, and Lee brands produce shirts, shoes, t-shirts, jeans, track pants, etc. If we need only the products or the sales of Jeans and T-shirts, you can write a query below.
SELECT * FROM products
WHERE Brand IN('Pepe Jeans', 'Wangler', 'Lee') AND category IN('Jean', ‘T-Shirt')
The following query returns employees whose Occupation is either Management or Clerical, and their Yearly Income should equal either 50000 or 80000. If both conditions match, that row will be added to the final result set.
SELECT * FROM [Employee]
WHERE [Occupation] IN ('Management', 'Clerical') AND
[YearlyIncome] IN (50000, 80000)
SQL Server 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 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 parentheses. 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 parentheses after the WHERE Clause. In that case, the IN operator compares the column name against the list of values in a result set.
TIP: The subquery has to return a single column as the result set.
For instance, retrieve the customers with sales in the current financial year. Before starting this example, let me show you an example. You have two tables with a primary and foreign key relation between them. You can use the subqueries to query or select records from a table using the secondary table. If you want to find the sales of the products from stores in certain cities.
SELECT * FROM ProductSales
WHERE store_id IN (SELECT ID FROM stores WHERE City IN ('Delhi', ‘Mumbai')
The above query uses multiple or nested SQL IN operators. To make it simple, let me use the query below. It only displays the Employees whose DeptID matches the id column returned by the subquery Department table.
SELECT * FROM [Employee]
WHERE [DeptID] IN (SELECT id FROM Department)
To make more complex, let me use another IN operator nested in the subquery to filter the Department table.
SELECT * FROM [Employee]
WHERE [DeptID] IN (SELECT id FROM Department WHERE id IN (6, 7, 8))
We have taken one more example of the subquery, and for this demonstration, we use the AdventureWorksDW2022 database. The SQL IN Operator query below returns all the products from the FactInternetSales table with at least a single sale, and the product color is Red, NA, or Black.
SELECT DISTINCT [ProductKey],[UnitPrice],[TotalProductCost]
,[SalesAmount],[TaxAmt] FROM [FactInternetSales]
WHERE [ProductKey] IN (
SELECT [ProductKey] FROM [DimProduct]
WHERE Color IN ('Multi', 'Blue','White')
)
Using JOIN
You can also use the JOIN condition to join the Fact and the product table to show the extra information about each product.
SELECT DISTINCT fact.[ProductKey],[EnglishProductName], Color,
[TotalProductCost],[SalesAmount]
FROM [FactInternetSales] AS fact JOIN DimProduct ON
fact.ProductKey = DimProduct.ProductKey
WHERE fact.[ProductKey] IN (
SELECT [ProductKey] FROM [DimProduct]
WHERE Color IN ('Multi', 'White')
The result set will be
| ProductKey | EnglishProductName | Color | TotalProductCost | SalesAmount | |
|---|---|---|---|---|---|
| 225 | AWC Logo Cap | Multi | 6.9223 | 8.99 | |
| 228 | Long-Sleeve Logo Jersey, S | Multi | 38.4923 | 49.99 | |
| 231 | Long-Sleeve Logo Jersey, M | Multi | 38.4923 | 49.99 | |
| 234 | Long-Sleeve Logo Jersey, L | Multi | 38.4923 | 49.99 | |
| 237 | Long-Sleeve Logo Jersey, XL | Multi | 38.4923 | 49.99 | |
| 481 | Racing Socks, M | White | 3.3623 | 8.99 | |
| 482 | Racing Socks, L | White | 3.3623 | 8.99 |
Combination of IN and ORDER BY clause
Use this SQL IN operator with the combination of the 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 60000, 90000. 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 * FROM [Employee]
WHERE [Occupation] IN ('Professional', 'Skilled Manual') OR
[YearlyIncome] IN (60000, 90000)
ORDER BY Sales
Using IN and BETWEEN operators
In the SQL Server WHERE clause, you can use the IN operator along with the BETWEEN operator to further filter the data. For instance, if you want the product sales within a time period and certain locations, you can use the combination of IN and BETWEEN operators.
SELECT * FROM products
WHERE OrderDate BETWEEN '2024-12-01' AND '2025-05-31'
AND city IN('New York', 'Los Angeles')
The query below uses the Employee table to demonstrate the combination of the BETWEEN and IN operators. Here, it displays all employees who joined between January 2005 and January 2012, and their education is either a Partial College, High School, or Masters Degree.
SELECT * FROM Employee
WHERE HireDate BETWEEN '2005-01-01' AND '2012-01-01'
AND Education IN('Partial College', 'High School', 'Masters Degree')
LIKE
Similar to the above, you can also use the combination of the SQL IN operator and the LIKE Wildcard search to perform complex filters. For instance, the below LIKE Wildcard query will return all products sold in New York and Los Angeles cities, and the product subcategory name starts with M.
SELECT * FROM products
WHERE subcategory LIKE 'M%'
AND city IN('New York', 'Los Angeles')
DML Operations using the SQL IN Operator
Apart from the above-mentioned SELECT statement examples, you can use the IN operator to UPDATE and DELETE the existing records from a table.
UPDATE
As we all know, the UPDATE statement uses the WHERE clause to modify certain records based on the condition result. So, we can use the IN operator to filter the rows modified by the UPDATE command.
For instance, you want to update the salary of the employees working in Marketing, Sales, and Production. The query below uses the IN operator to check whether their department is in those categories and update their salaries.
UPDATE Employee
SET Salary = Salary + Salary * 0.20
WHERE Department IN('Marketing', 'Sales', 'Production')
DELETE
Similar to the above, you can use the IN operator along with the DELETE statement to remove existing rows from a table. The query below will remove records from an employee table whose Education qualification is High School and Partial High School.
DELETE FROM Employee
WHERE Education IN('High School', 'Partial High School')
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 * FROM [Employee]
WHERE [Occupation] NOT IN ('Management', 'Professional')
SQL IN Operator Handle a Large List of Values
While working with the IN operator, you should be careful with the total number of values passed as arguments. If you explicitly pass a large set of values separated by a comma, the query execution process will slow down and cause performance issues. Sometimes, it may return errors 8623 or 8632, depending on the set of values.
To resolve these issues, there are multiple options, and all these options use the subquery. This section shows how to handle a large set of multiple values in the SQL Server IN operator.
Using CTE
First, we create a simple CTE (Common Table Expression) to store the list of product IDs whose category belongs to Clothing.
WITH sample AS (
SELECT product_id FROM products
WHERE category = 'Clothing'
Next, write the SELECT statement to return the required records from the product table with a WHERE clause for filtering. Here, the IN operator uses the above-created CTE to check the category column.
SELECT * FROM products p
WHERE p.product_id IN (SELECT product_id FROM sample)
If you know the values and you want to hardcode those values instead of using the SELECT statement to retrieve, use the query below.
WITH sample AS (
SELECT 'Clothing' AS cat
UNION ALL
SELECT 'Furniture'
UNION ALL
SELECT 'Footwear'
UNION ALL
SELECT 'Electronics'
Query to get the products belong to above mentioned categories.
SELECT * FROM products p
WHERE p.category IN (SELECT cat FROM sample)
Using a Temporary Table
Similar to the above, you can use the Temporary tables to store a large list of multiple values and use temp as the SQL IN operator source. First, we create a temporary table to store all product price lists.
CREATE TEMPORARY TABLE sample(range INT)
INSERT INTO sample(range) VALUES(50, 100, 200, 500, 1000, 2000, 5000, 10000)
Next, we use the IN operator to check the price against this temp table instead of hitting the original table for each price check. It improves the query performance and avoids the above-mentioned errors.
SELECT * FROM products
WHERE price IN(SELECT range FROM sample)
Using SQL IN Operator with NULL Values
For instance, you have an employee table with sales information where a few employees who joined recently or who belong to the desk (R&D) work have NULL sales. In such a case, if you write the below query, it will show all employees whose sales are 1000, 2000, and 5000. However, it won’t display the NULL (no sales) in this case.
SELECT * FROM Employee
WHERE sales IN(1000, 2000, 5000, NULL)
IS NULL
To deal with this situation, you must use the IS NULL function in conjunction with the IN operator.
SELECT * FROM Employee
WHERE sales IN(1000, 2000, 5000) OR sales IS NULL;
COALESCE
Alternatively, you can try the COALESCE() function to replace integer NULLs with zeros and string NULLs with Unknown or any text.
SELECT * FROM Employee
WHERE COALESCE(sales, 0) IN(0, 1000, 2000, 5000)
SQL IN Operator Best Practices
- Avoid a large list of values and prefer either CTE or temp tables to hold multiple values before using the IN operator.
- Always use the index columns within the IN operator to improve performance.
- The data type of the left side column or expression should match the given values inside the IN operator. For instance, if the column value is INT, the list of multiple IN operator values should be INT. Otherwise, it will throw an error.
- Use Logical AND, OR, and NOT operators to combine multiple IN operators or conditions. By this, you can write complex filters.
- Always use parentheses while combining multiple conditions.
- Using subqueries inside the SQL IN operator helps you to dynamically filter the result set based on the output written by the other query. Apart from that, if the list is large, use subqueries.
- While performing string comparison, case-sensitivity plays a vital role. If your database is case-sensitive and if it considers Apple and apple are different, use the LOWER() or UPPER() function.
- Use JOINS to check multiple values against multiple tables.
- While working with large datasets or complex relationships, instead of the IN operator, use the EXISTS operator.
- Instead of using NULL values inside the IN operator, handle NULL values separately.