The SQL Server NOT IN Operator will act quite the opposite of the IN operator in filtering records. The NOT IN operator restricts the number of records (or rows) the SELECT Statement returns. This article will explain the SQL NOT IN operator syntax with practical examples involving numbers, strings, dates, and subquery.
The SQL NOT IN Operator checks the given expression or Column name against the Values inside the () parenthesis. If there is a match, SELECT Statement will not return that record. You can call this operator as the alternative to not equal to <> operator, AND & OR operators.
Some might need clarification between the SQL NOT IN operator and NOT EXISTS operators. The NOT EXISTS operator checks on the complete result set returned by the subquery. The NOT IN operator filters rows based on a given value(s).
SQL NOT IN Operator Syntax
The basic syntax of the SQL Server NOT IN operator to exclude rows from a result set is as follows.
SELECT [Column Names] FROM [Source] WHERE [Column Name] NOT IN (Value1, Value2, ...., ValueN)
In the above syntax:
- Column Names: Write the table columns you want to display as a result set. The SQL NOT IN operator will filter this data only.
- Source: Please specify the table(s) name. If there are multiple tables, use joins.
- Values (1 to N): We must provide the value or expression we want to check against the Column Name. If the record in the column name matches any Value (Value1, Value2, …., ValueN), that record will not display in the result set.
For this SQL Server NOT IN Operator demo, we will use the below-shown data.
SQL Server NOT IN Operator on Numbers
In the following sections, we use the Employee table above to filter the data by SQL NOT IN Operator. The below query will find all the Employees present in the Employee table whose Yearly Income is neither 45000 nor 50000.
- The SELECT statement selects all the columns from the Employee table.
- The WHERE clause helps filter the result set returned by the SELECT statement. However, it needs a condition to check against the Yearly Income and NOT IN operator act as the condition.
- NOT IN (45000, 50000) means excluding employees whose income is 45000 and 50000.
SELECT [EmpID]
,[FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
,[HireDate]
FROM [Employee]
WHERE [YearlyIncome] NOT IN (45000, 50000)
You can also change the last line, as shown below, and it gives the same result set.
WHERE NOT [YearlyIncome] IN (45000, 50000)
TIP: If you want to include the values that match the expression in the result set, use IN query.
SQL NOT IN Operator on String Columns Example
Let us show you one more Server example for better understanding, and we don’t go into details about the steps because we already did in the first example.
The following SQL NOT IN Operator query will find all the employees available in the Employee table whose Education is not equal to Education, High School, or Partial High School.
SELECT [EmpID]
,[FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
,[HireDate]
FROM [Employee]
WHERE [Education] NOT IN ('Education', 'High School', 'Partial High School')
Use NOT IN on both String and Number Columns
You can also use multiple operators in a single WHERE Clause. In this example, we will use multiple SQL NOT IN operators combining AND (Logical) in a single Select statement.
The following query finds the employees whose Education is different from Education, High School, or Partial High School, and their YearlyIncome does not match either 45000 or 50000.
SELECT [EmpID],[FirstName],[LastName],[Education]
,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE [Education] NOT IN ('Education', 'High School', 'Partial High School')
AND [YearlyIncome] NOT IN (45000, 50000)
SQL NOT IN Operator Working on Date Columns
In some situations, you don’t want to display the sales belonging to a few days. In this scenario, you can use the SQL NOT IN operator to exclude those Dates from the result set.
The below query display all the employees whose Hire Date is not 2006-01-28 13:10:02.047, 2009-07-06 12:09:14.237, 2006-10-02 05:03:10.223, and 2013-01-14 05:03:10.367.
SELECT [EmpID],[FirstName],[LastName],[Education]
,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE [HireDate]
NOT IN ('2006-01-28 13:10:02.047',
'2009-07-06 12:09:14.237',
'2006-10-02 05:03:10.223',
'2013-01-14 05:03:10.367')
What if you wish to exclude the sales of particular years? In this case, you can use the YEAR function and SQL NOT IN operator to check against years instead of dates. For example, the below query returns all employees whose Hire Date is not in 2006, 2008, and 2013.
If you observe the below screenshot, all the Hire Dates that fall in the above years exclude from the final grid result.
SELECT [EmpID],[FirstName],[LastName],[Education]
,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE YEAR([HireDate]) NOT IN ('2006', '2008', '2013')
SQL NOT IN Operator with Subqueries
In all the above examples, we worked on a single table. However, the SQL NOT IN Operator allows you to use the subquery to exclude the results. For instance, retrieve the customers who have yet to place an order in the current fiscal year, month, etc.
To demonstrate this example, we used the Adventure Works DW 2019 database. The below query returns the products that don’t have any sales in their lifetime.
- The SELECT statement in the main query selects all the required columns from the DimProduct table.
- The subquery selects all the Product keys from the Fact Reseller Sales table.
- The WHERE clause and SQL NOT IN operator filter the main query products whose Product key is not in the subquery. The final result set is the products that don’t have any reseller sales.
SELECT [ProductKey], [EnglishProductName],[SafetyStockLevel] ,[Color],[Size],[DealerPrice],[EnglishDescription] FROM [DimProduct] WHERE [ProductKey] NOT IN (SELECT [ProductKey] FROM [FactResellerSales])
