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 SQL NOT IN Operator checks the given expression or Column name against the Values. If there is a match, SELECT Statement will not return that record.

SQL NOT IN Operator 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

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.

Use NOT IN in String Columns Example

Let us show you one more Server example for better understanding. The following NOT IN Operator 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')

Use SQL NOT IN Operator on both String and Numeric Columns

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