Arindam Mondal
SQL IN with Subquery

Understanding the SQL IN operator with examples

March 19, 2024 by

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.


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.


Let us discuss the SQL IN operator syntax below:

SELECT column1, column2, FROM table where expressions IN (value1, value2, value3… so on)


SELECT column1, column2, FROM table where expressions IN (SELECT statements)


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 :

The output is below, the City column filtered with value ‘Bothell’:

SQL IN Operator with Strings

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:

The output shows only results for those two SalesOrderID.:

SQL IN Operator with Numbers

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:

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:

SQL IN Operator with Dates

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’:

The above SQL statements can also be shortened using IN operator as below:

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:

Replace multiple OR condition with SQL IN Operator

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:

Multiple IN Operator

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:

SQL IN with Subquery

Nested IN Operator

We can also use the SQL IN operator inside another IN operator. Let us see an example below:

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.

The Output is below:

Nested SQL IN Operator

IN Operator duplicate values

Duplicate values in the SQL IN operator are ignored. Let us understand this by the below examples:

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:

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:

SQL IN Operator duplicate values


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:

The Output is below:



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 Mondal

About Arindam Mondal

Arindam is an experienced and highly motivated IT enthusiast who likes to leverage his technical expertise to address critical business needs. A self-guided learner who loves to learn every single day. He loves to indulge in cultural diversity and travel to newer destinations. 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)