SQL NOT IN Operator

The SQL NOT IN Operator will act quite the opposite of SQL IN Operator. NOT IN operator is used to restrict the number of records (or rows) returned by the SELECT Statement.

The NOT IN Operator in SQL Server check the given expression or Column name against the Values inside the NOT IN Operator, and If there is a match then SELECT Statement will not return that record

SQL NOT IN Operator Syntax

The basic syntax of the NOT IN operator in SQL Server is:

SELECT [Column Names]
FROM [Source]
WHERE [Column Name] NOT IN (Value1, Value2, ...., ValueN)

Values: Here, we have to provide the value or expression that we want to check against the Column Name. If the column name matches with any Value, then that records will not be displayed. For this NOT IN Operator in SQL Server demo, we are going to use the below-shown data

SQL NOT IN Operator 1

SQL NOT IN Operator Example 1

The below NOT IN query will find all the Employees present in the Employees table whose [Yearly Income] is neither 45000 nor 50000

-- Example for SQL Server NOT IN Operator
USE [SQL Tutorial]
GO
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  WHERE [YearlyIncome] NOT IN (45000, 50000)

TIP: If you want to check the expression against Multiple values, use IN Operator.

SQL NOT IN Operator 2

NOT IN Operator Example 2

Let us show you one more SQL Server example for better understanding. The following NOT IN query will find all the Employees available in the Employees table whose Education not equal to Education, High School, or Partial High School.

-- Example for SQL Server NOT IN Operator
USE [SQL Tutorial]
GO
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  WHERE [Education] NOT IN ('Education', 'High School', 'Partial High School')
SQL NOT IN Operator 3

SQL NOT IN Operator Example 3

You can also use multiple operators in a single WHERE Clause. In this example, we are going to use NOT IN Operator and AND Operator (Logical Operator) in a single Select statement.

The following NOT IN query finds the Employees whose Education not equal to Education, High School, or Partial High School, and their [Sales] should be more than 1000

-- Example for SQL Server NOT IN Operator
USE [SQL Tutorial]
GO
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  WHERE [Education] NOT IN ('Education', 'High School', 'Partial High School') AND
	[Sales] > 1000
SQL NOT IN Operator 4