Are you new to T-SQL and wondering about the NOT IN operator in Microsoft SQL Server? In this article, we will explore the functionality and use cases of the NOT IN operator in detail.

What is the NOT IN Operator?

The SQL Server NOT IN operator is a logical operator used to replace a group of arguments combined with the <> (or !=) operator. It is commonly used in SELECT, UPDATE, or DELETE SQL commands to make code more readable and understandable.

Let’s consider an example:

SELECT * 
FROM Sales.Invoices 
WHERE LastEditedBy <> 11
  AND LastEditedBy <> 17
  AND LastEditedBy <> 13;

While this query is understandable, it can become lengthy and cumbersome when dealing with multiple arguments. This is where the NOT IN operator comes in handy.

Using the NOT IN Operator

The NOT IN operator works by comparing a value, usually a column, to a comma-separated list of potential match values held in a set of parentheses. The value to the left of the NOT IN operator is then compared, one at a time, to the entire list. If there is an exact match to any one member of the list, the argument evaluates as false.

Let’s rewrite the previous query using the NOT IN operator:

SELECT * 
FROM Sales.Invoices 
WHERE LastEditedBy NOT IN (11, 17, 13);

This query is functionally equivalent to the previous one, but it is shorter and easier to read.

Rules and Best Practices for NOT IN

Here are some important rules and best practices to keep in mind when using the NOT IN operator:

• The NOT IN operator can only replace the <> or != operators. It cannot replace =, <, >, <=, >=, BETWEEN, or LIKE.
• The NOT IN operator only finds and excludes exact matches. Duplicate values in the list are ignored.
• The NOT keyword can be placed either at the start of the argument or in the operator. Both options are functionally equivalent.
• The NOT IN operator can be used anywhere any other operator is used, including WHERE clauses, HAVING clauses, IF statements, or join predicates.
• The list of values can be defined by a query, opening up the possibility of having zero values in the list.
• Consider using an OUTER JOIN instead of a NOT IN statement with a large number of values for better performance.

Using NOT IN with Strings, Numbers, and Dates

The NOT IN operator can be used with various data types, including strings, numbers, and dates. Here are some examples:

Using NOT IN with Strings:

-- Ignore test users (AND Version)
IF @UserName <> 'TrainingUser' AND @UserName <> 'TestUser'
BEGIN
  -- Your code here
END

-- Ignore test users (IN Version)
IF @UserName NOT IN ('TrainingUser', 'TestUser')
BEGIN
  -- Your code here
END

Using NOT IN with Numbers:

-- Find account persons with 6, 8, or 9 sales (AND Version)
SELECT AccountsPersonID, COUNT(*) TotalInvoices
FROM Sales.Invoices 
GROUP BY AccountsPersonID 
HAVING COUNT(*) != 6
  AND COUNT(*) != 8
  AND COUNT(*) != 9;

-- Find account persons with 6, 8, or 9 sales (IN Version)
SELECT AccountsPersonID, COUNT(*) TotalInvoices
FROM Sales.Invoices 
GROUP BY AccountsPersonID 
HAVING COUNT(*) NOT IN (6, 8, 9);

Using NOT IN with Dates:

-- Get average number of items ordered daily per customer for the year 2013, excluding holidays (AND Version)
SELECT CustomerID, AVG(ItemsPurchased) AverageDay
FROM (
   SELECT CustomerID, InvoiceDate, COUNT(*) ItemsPurchased
   FROM Sales.Invoices
     INNER JOIN Sales.InvoiceLines ON Invoices.InvoiceID = InvoiceLines.InvoiceID
   WHERE InvoiceDate != '25-Dec-2013' 
     AND InvoiceDate != '4-Jul-2013' 
     AND InvoiceDate != '28-Nov-2013'
   GROUP BY CustomerID, InvoiceDate) SubQuery
GROUP BY CustomerID;

-- Get average number of items ordered daily per customer for the year 2013, excluding holidays (IN Version)
SELECT CustomerID, AVG(ItemsPurchased) AverageDay
FROM (
   SELECT CustomerID, InvoiceDate, COUNT(*) ItemsPurchased
   FROM Sales.Invoices
     INNER JOIN Sales.InvoiceLines ON Invoices.InvoiceID = InvoiceLines.InvoiceID
   WHERE InvoiceDate NOT IN ('25-Dec-2013', '4-Jul-2013', '28-Nov-2013')
   GROUP BY CustomerID, InvoiceDate) SubQuery
GROUP BY CustomerID;

Performance Considerations

It’s important to note that arguments created with the NOT IN operator are not conducive to index seek operations and can result in slower scan operations. To optimize performance, it is recommended to use NOT IN arguments in conjunction with additional arguments that can use indexes to reduce the number of rows to be processed.

Always check the execution plan of your SQL queries to ensure optimal performance.

Conclusion

The SQL Server NOT IN operator is a powerful tool for replacing multiple arguments combined with the <> or != operator. By using the NOT IN operator, you can make your code more readable and concise. Remember the rules and best practices mentioned in this article to effectively use the NOT IN operator in your T-SQL queries.

Article Last Updated: 2023-10-25