The SQL IN statement matches a specified value from a list or subquery. It reduces our efforts to write various OR statements with equal to ( = ) operator. The output will be returned the same either you use OR statements or IN statements. It can be used with SELECT, UPDATE & DELETE statements to get, update or delete multiple rows in one shot.
- Compare SQL OR and SQL IN
- SQL IN Statement with Expression Lists
- SQL IN Statement with Subqueries
- SQL NOT IN Statement
Below is the syntax of this statement as given in MSDN documents:
Test_expression [ NOT ] IN
( subquery | expression )The description of all the above arguments is below:
- Test_expression is any valid combination of symbols and operators that SQL Server uses to evaluate data values
- A subquery is a result set of one column
- Expression is a list of comma-separated expressions which will be matched
I will demonstrate how to use the SQL IN statement in your T-SQL queries with the help of the below use cases given in this article. I will also show you a comparison between the OR statement and IN statement in the below sections.
I have created a table Employee for these use cases demonstration. There are 7 columns in this table: “ID”, “name”, “city”, “Age”, “Gender”, “Designation”, and “employment nature”. I will use SQL IN statements to fetch various details which you can compare, learn, and use in your reporting or business needs.
Compare SQL OR and SQL IN Statements
We can get the same output using OR statement or IN statement in SQL Server. If you are using SQL OR statement, then you need to write a separate line or statement for each value you want to match, and it will take additional effort while writing the code whereas you can get the same thing done using SQL IN statement by using IN statement once and pass all values you want to match. Let me show you the demonstration of this statement.,
Let’s say you want to find the list of all employees who belong to 5 cities London, Paris, Mumbai, Hongkong, and New Delhi. If you use SQL OR statement, then your T-SQL code will look like this:
SELECT name, city, Gender
FROM employee
WHERE city = ‘London’,
OR city = ‘Paris’,
OR city = ‘Mumbai’,
OR city = ‘Hongkong’,
OR city = ‘New Delhi’
You can focus on the WHERE clause where I have to write a separate OR statement for each city value, but this can be done easily using IN statement. Here is an example using SQL IN statement to get the same output.
SELECT name, city, Gender
FROM employee
WHERE city IN (‘London’, ‘Paris’, ‘Mumbai’, ‘Hongkong’, ‘New Delhi’)
Let me show you the output of both T-SQL statements. I have executed both T-SQL statements under the same session and got the below output.
Both queries have returned the same output in the above image. We can see the benefits of using the IN operator in the above query. SQL IN statement also ignores any duplicate entry listed in the expression to get its result. Have a look at the below output image where I have listed “London” and “Hongkong” twice to see its output and we can see there is no change in the output.
SELECT name, city, Gender
FROM employee
WHERE city IN (‘London’, ‘London’, ‘Paris’, ‘Mumbai’, ‘Hongkong’, ‘New Delhi’, ‘Hongkong’)The output of the above query ignored duplicate entries of both cities London and Hongkong and returned its output the same as it was showing in the previous image.
Now, I will explain several use cases of IN statements with expressions and subqueries in this article.
SQL IN Statement with Expression Lists
As I have described above that expressions are comma-separated values which we list in the query to display their results with the help of the SQL IN statement. You can consider the above queries as an example of IN statement with expressions.
Let me give you another example where I will display a list of sales done by all employees who are working at designation “Specialist”, “Analyst”, and “Sr. Analyst” in July 2021. We will run the below T-SQL statement and use the IN statement to pull out details for only these 3 designations.
SELECT e.name, e.designation, s.InvoiceMonth, s.Price
FROM Employee AS e
JOIN Sales AS s
ON e.id=s.Personid
WHERE e.designation IN (‘Analyst’, ‘Sr Analyst’, ‘Specialist’)
AND s.InvoiceMonth = ‘July’The sales table stores a single row for each invoice processed. Here is the output of the above query:
We can see each employee who is working as any of the above 3 designations and has done any sales in July is listed out using the above query. Other use cases using expressions are also given in the above section so now let’s understand another use case where I will show you how to use SQL IN statements with subqueries.
Optimize your SQL queries using the AS statement, which you can learn more about in our related article.
SQL IN Statement with Subqueries
Here also, I will use two tables “Employee” and “Sales” and join them to fetch the output using IN statement with the help of using a subquery. Employee table has all details regarding each employee whereas Sales table stores details about product sales.
Consider a reporting requirement where you need to fetch a report for all employees who have sold at least one product that is more than $3000 along with their other remaining small or big transactions. We can get this done easily by using a subquery with IN statement. Here is an example of such a scenario.
SELECT e.ID, e.name, e.designation, s.Price
FROM Employee AS e
JOIN Sales AS s
ON e.ID = s.PersonID
WHERE s.PersonID IN (SELECT PersonID FROM Sales WHERE Price >3000)
Below is the output of the above query. Here, we can see all employees who have sold at least one transaction of more than $3000 along with their other transactions.
Now, let’s say there is another requirement where you need to find all employees who work in the Analyst and Sr Analyst positions and get at least one invoice of more than $3000. We can get it by adding a line of code in the above query.
SELECT e.name, e.designation, s.InvoiceMonth, s.Price
FROM Employee AS e
JOIN Sales AS s
ON e.id=s.Personid
WHERE s.Personid IN (SELECT PersonID FROM Sales WHERE Price >3000)
AND e.designation IN (‘Analyst’, ‘Sr Analyst’)
Here is the output. This is also an example of using multiple IN statements in a single query. You can also see I have used subquery and expressions both with SQL IN statement in a single query.
You can compare this output with the above one to validate its result. Now, the query has excluded Mark because he works at the Manager level.
SQL NOT IN Statement
I will explain how to use the NOT IN statement in a query in this section. The NOT IN statement works just opposite to the IN statement. The IN statement returns the output for all the expressions that are listed along with the IN statement whereas the NOT IN statement will return the output for all expressions except the one listed with the NOT IN statement. I will show you this with the help of an example.
Let’s take the above example for this demonstration as well. I have displayed the details about employees who have converted at least one sale for more than $3000 and working in the Analyst and Sr Analyst position in the above example. Now I will use the same query and use the NOT IN statement to display details of all employees who have achieved at least one invoice of more than $3000 and NOT working in the positions of Analyst and Sr Analyst. Even you can use the NOT IN statement in the above example with the last line as NOT IN as Manager.
As you can see, I have made the only one-word change in the above example i.e. I have included NOT in the last line and what we can see is its output has completely changed.
SELECT e.name, e.designation, s.InvoiceMonth, s.Price
FROM Employee AS e
JOIN Sales AS s
ON e.id=s.Personid
WHERE s.Personid IN (SELECT PersonID FROM Sales WHERE Price >3000)
AND e.designation NOT IN (‘Analyst’, ‘Sr Analyst’)
Here is the output, you can see, this time query has returned the details about only one employee who works at the Manager level.
The above example is a demonstration of using the NOT IN statement for expressions. Now, I will show you the NOT IN statement with a subquery.
Get a list of all employees who have not made any sale of $3000 in a single invoice irrespective of their designations. I have used the same statements and tables for this example as well. Added NOT IN in the above statement and removed the last line to remove the designation-wise filter.
SELECT e.name, e.designation, s.InvoiceMonth, s.Price
FROM Employee AS e
JOIN Sales AS s
ON e.id=s.Personid
WHERE s.Personid NOT IN (SELECT PersonID FROM Sales WHERE Price >3000)
Here is the output which shows the result as per our requirement:
Now an additional requirement came up to get a list of employees who had not done any sales of greater than $3000 in a single invoice and do not work in either Analyst or Sr Analyst position. We can get this by adding a line with the NOT IN statement.
SELECT e.name, e.designation, s.InvoiceMonth, s.Price
FROM Employee AS e
JOIN Sales AS s
ON e.id=s.Personid
WHERE s.Personid NOT IN (SELECT PersonID FROM Sales WHERE Price >3000)
AND e.designation NOT IN (‘Analyst’, ‘Sr Analyst’)
The above query is a demonstration of using the NOT IN statement with subquery and expressions in a single query.
You can also use this statement to modify and delete data in UPDATE and DELETE statements in a similar way using the WHERE clause. Write your feedback in the comment section if you want us to write another article that covers the use of IN statements in UPDATE and DELETE statements.
Conclusion
I have demonstrated several examples and use cases of SQL IN statements with expressions and subqueries to get the output as per our requirement. You can go ahead and test this SQL statement as per your business need. It is very useful to reduce the developer’s efforts where you need to fetch details of multiple expressions. I have also demonstrated the same examples of SQL NOT IN statement in this article. SQL IN statements can also be used in UPDATE and DELETE statements to modify or delete multiple records quickly. You can also use subquery and expressions both in a single query as I have shown in this article.
Last modified: May 15, 2023
