SQL NOT IN Operator

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

The NOT IN Operator check the given expression or Column name against the Values. If there is a match, SELECT Statement will not return that record.

Syntax

The basic syntax of the SQL Server NOT IN operator 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 any Value, then that record will not display. For this NOT IN demo, we are going to use the below-shown data

Employee Table Data 1

SQL Server NOT IN Operator Example

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

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 query.

Example 2

Example 2

Let us show you one more Server example for better understanding. The following NOT IN query will find all the Employees available in the Employees 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')
SQL NOT IN Operator 3

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 and AND (Logical) in a single Select statement.

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

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