Today we will learn about the SQL IN operator. The RDBMS systems are very popular today in terms of data storage, data security, and data analysis. SQL stands for Structured Query Language which is used to create, update, or retrieve data in RDBMS or Relational Database Management Systems like SQL Server, Oracle, Microsoft Access, MySQL, and PostgreSQL.
When we query or retrieve data from a SQL database, we filter records in various ways so that they can meet the application’s requirements. The SQL IN operator can be used with SQL databases like SQL Server, MySQL, PostgreSQL, and other RDBMS systems.
While writing SQL queries you gather all the business requirements to write complex SQL statements. SQL Provides various operators to query the data from the database. An operator is a reserved keyword that is used in the query so that it can generate specific output for the application. To use the operators within the query, we use the Where clause, then operators (e.g., SQL IN Operator) can be used to define one or more conditions.
Introduction
SQL where clause is used to filter the data from a query, various operators can be used in the where clause. By using an operator, you can define specific conditions to retrieve results from the database and multiple conditions can also be defined using operators. There are many useful operators available in SQL like Arithmetic Operators, Comparison Operators, and Logical Operators, we will focus on SQL IN operator in today’s article. SQL IN operator is one of the most common operators used in the where clause to specify one or more values or in the subquery so that the output can meet the requirement.
Syntax
Let us discuss the SQL IN operator syntax below:
SELECT column1, column2, FROM table where expressions IN (value1, value2, value3… so on)
Or
SELECT column1, column2, FROM table where expressions IN (SELECT statements)
Parameters
expressions or statements
This is the value or column name to evaluate
value1, value2, value3… so on
These values are checked against the expression. If one or more values match the expression then the IN operator evaluates to true.
expressions IN (SELECT statements)
The result set of these select statements will be tested against expressions. If any of the values in the result set match expression, then the IN operator will evaluate to True.
Now we will discuss a few examples of the SQL IN operator so that you can understand it better. The IN operator works with various data types including Strings, Numbers, and Dates. I will provide examples of different use cases of IN operator.
IN Operator with Strings
The SQL IN operator works with String columns which have data types char, nchar, varchar, or nvarchar. We are using the Adventure Works sample database in this article. If you have not already installed the Adventure Works database, you can download and install it before using the below query sample. In this query, I used the Person.Address table and used the City column in the Where clause to filter with value ‘Bothell’, you need to use a single quote for string value :
|
1 2 3 |
SELECT TOP 10 AddressLine1, City, PostalCode FROM Person.Address WHERE City IN ('Bothell') |
The output is below, the City column filtered with value ‘Bothell’:
IN Operator with Numbers
The SQL IN operator also works with columns that have a data type. In the below query I used the SalesOrderHeader table and the SalesOrderID column has a numeric value. The query is filtered for SalesOrderID 43660 and 43661:
|
1 2 3 4 5 |
SELECT SalesOrderID,CustomerID,TotalDue FROM Sales.SalesOrderHeader WHERE SalesOrderID IN (43660,43661) |
The output shows only results for those two SalesOrderID.:
IN Operator with Dates
Sometimes we need to filter data based on date values in specific columns and IN operator works perfectly with any date column or values. Let us understand below example:
|
1 2 3 4 5 |
SELECT [SpecialOfferID],[Description],[DiscountPct],[StartDate] FROM [Sales].[SpecialOffer] Where StartDate IN ('2011-05-01 00:00:00.000','2012-04-13 00:00:00.000') |
In the above example query we have used the SpecialOffer table which has the date column StartDate which is used in the SQL IN operator with two values ‘2011-05-01 00:00:00.000’ and ‘2012-04-13 00:00:00.000’. You need to use a single quote for the date value.
The output shows the filtered result as expected:
Replace multiple OR conditions with SQL IN Operator
The IN Operator is used to replace multiple OR conditions. To understand this let us take an example below. In the first query below, I used the Person table, and in the Where clause filtered for the FirstName column which was used two times combined with the OR condition, at the first query below the FirstName has the value ‘Kim’ and the second Firstname has value ‘Sam’:
|
1 2 3 |
SELECT FirstName,LastName FROM Person.Person WHERE FirstName ='Kim' or FirstName ='Sam' |
The above SQL statements can also be shortened using IN operator as below:
|
1 2 3 |
SELECT FirstName, LastName FROM Person.Person WHERE FirstName IN ('Kim','Sam') |
The above query used the Person table and the FirstName column is used in the IN operator, there are two values in the SQL IN operator ‘Kim’ and ‘Sam’. The output is showing all results which have FirstName either ‘Kim’ or ‘Sam’.
The output for both above queries is as below:
Multiple IN Operator
We can use the SQL IN operator with the AND operator to check conditions with more than one column. In the below example, I used the Person table and added FirstName and LastName in the where clause, both the columns FirstName and LastName contains SQL IN operator, so we can use multiple IN operator to build complex SQL logic:
|
1 2 3 |
SELECT FirstName, LastName FROM Person.Person WHERE FirstName IN (‘Kim’,’Sam’) AND LastName IN (‘Foster’,’Akers’) |
IN with Subquery
In the above examples we used the static values in the IN operator, sometimes the values to filter in the SQL IN operator are not predefined for example we may need to retrieve the values from another table using a select query. In this case, we can use the subquery in the SQL IN operator to filter the dataset dynamically. Subquery gives you more flexibility in the filter criteria. The subquery in the IN operator also helps to combine two queries into a single query. The subquery should contain a single column in the select statements and multiple columns are not allowed.
In the below SQL query, the outer query retrieves data from the SalesOrderDetail table and the subquery is getting data from the SalesOrderHeader table:
|
1 2 3 4 5 |
SELECT [SalesOrderID],[OrderQty],[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount],[LineTotal] FROM [Sales].[SalesOrderDetail] WHERE SalesOrderID IN (SELECT SalesOrderID FROM [Sales].[SalesOrderHeader]) |
Nested IN Operator
We can also use the SQL IN operator inside another IN operator. Let us see an example below:
|
1 2 3 |
SELECT TOP 10 NAME, ProductNumber FROM [Production].[Product] WHERE ProductSubcategoryID IN (SELECT [ProductCategoryID] FROM [AdventureWorks2019].[Production].[ProductCategory] WHERE [ProductCategoryID] IN (1,2)) |
In the above query, the outer query selects Name and ProductNaumber from the Product table which is filtered with ProductSubCategoryID from the subquery and the subquery also contains an IN operator which is used to filter ProductCategoryID.
IN Operator duplicate values
Duplicate values in the SQL IN operator are ignored. Let us understand this by the below examples:
|
1 2 3 4 |
SELECT [BusinessEntityID],[Name] FROM [Sales].[Store] WHERE BusinessEntityID IN (300,302,300) |
In the above SQL statements, the IN operator contains a duplicate value of 300 and it is ignored by the IN operator. The above SQL statements are the same as those below:
|
1 2 3 4 |
SELECT [BusinessEntityID],[Name] FROM [Sales].[Store] WHERE BusinessEntityID IN (300,302,300) |
The above query uses the Store table and it is showing all the rows which have Business Entity ID 300 or 302. Both statements return the same result as below:
NOT IN
In the above examples, we wrote queries to include records based on the values in the SQL IN operator. Your query might give an output of 1000 records but you want to opposite this time which means you want to exclude some records. We can use the NOT operator along with IN operator to show the results that do not match the specified value. We have used IN operator to filter records in the above examples. Now we can use the NOT operator along with the SQL IN operator to filter records which are not matching with the provided values. Let us see an example below.
In the below query we are using the Customer table and filtering data based on the StoreID column and the IN operator combined with the NOT operator shows the result which is not matching with values 932 and 934:
|
1 2 3 |
SELECT TOP 10 CustomerID, StoreID from Sales.Customer WHERE StoreID NOT IN (932,934) |
The Output is below:
Conclusion
This article explained the SQL IN operator with various examples and use cases. The IN operator is used to filter data for a specified set of values. It can also be used to replace multiple OR conditions. We can simplify complex SQL to more readable statements by using IN operator. We have seen different data types like Strings, Numbers, and Dates supported by IN operator.
Thank you for reading this article, please share your valuable feedback in the comment section.
Arindam has rich experience in Azure solution implementation and support activities for large enterprise clients, having worked on multiple developments and enhancement projects. He is
currently based in India and is working as a Technical Lead for a leading MNC Company.
A few significant facts about his career:
* Has over 10.7 years of IT experience in Microsoft platforms.
* Has around 8 years of experience in SQL database development (T-SQL/Performance tuning/CDC) and ETL-SSIS.
* Has 5 years' experience in Azure Environment (Azure Data Factory, Azure Data Lake Storage, Azure SQL Database, Azure Cosmos DB, Azure Synapse Analytics)
- Understanding the SQL IN operator with examples - March 19, 2024
- An introduction to PostgreSQL Data Types with examples - September 15, 2023
- Understanding Substring in Oracle SQL - March 22, 2023