This blog provides an overview of the SQL NOT IN operator and uses it in SELECT statements.
What Is the NOT IN Operator?
The SQL NOT IN operator is used to check if the values in one column are not included in values in another column.
For example, we could find all the vendors that are not located in the United States, like this:
SELECT VendorID FROM Vendors WHERE Country NOT IN ('USA');The SQL Server NOT IN operator replaces arguments <> (or !=) operator combined with AND operator. It makes queries easy to read and understand for SELECT, UPDATE or DELETE SQL commands.
The following query uses the <> operator to get records from [Sales].[SalesOrderDetail] table excluding the Product IDs 776,777 and 771.
The query converts the 3 <> arguments using a single NOT IN operator.
SELECT *
FROM [Sales].[SalesOrderDetail]
WHERE ProductID <> 776
AND ProductID <> 777
AND ProductID <> 771;Instead of the <> operator, we can use SQL NOT IN operator as below.
SELECT *
FROM [Sales].[SalesOrderDetail]
WHERE ProductID Not IN(776,777,771)Important points about SQL NOT IN Operator
The NOT IN operator cannot replace =, <, >, <=, >=, BETWEEN, or LIKE.
It excludes the duplicated values in the list. For example, the following arguments are the same.
ProductID Not IN(776,777,771)
ProductID Not IN(776,777,776,777,771)You can use the NOT keyword either at the start of the argument or with the in operator. However, usually, people use NOT IN as a standard for writing queries. The following where clauses are similar.
WHERE NOT ProductID IN(776,777,771)
WHERE ProductID NOT IN(776,777,771)You can specify multiple arguments values for filtering data; however, as a general thumb rule, you can use the extensive list (more than 8-10) from the performance point of view and explore alternative options.
You can use the NOT IN operator to compare various data types.
String column (char, nchar, varchar, nvarchar)
Numeric data type (int, bigint, smallint, tinyint, numeric, decimal, float, real, money)
Date or DateTime value
You do not need to hardcode values for the SQL NOT IN operator. You can also use subqueries to get required values as well.
The following query gets the SalesOrderID from the subquery listed below.
SQL Server does not use index seek operations for the arguments created with the NOT IN operator. The index scan can be expensive. Therefore, It is suitable for small result sets; however, we should use a look way to reduce the number of rows for processing for a larger table.
Conclusion
Hopefully, after reading this article, you can better understand the SQL NOT IN operator and how to use it. To make sure you have a solid grasp of this concept, try to pause a moment and answer this question: What is the SQL NOT IN operator? Take a minute to review the "SQL NOT IN operator" section of this article if you need to.
