Not Equal in SQL WHERE Clause

By: Joe Gavin | Updated: 2023-07-31 | Comments (2) | Related: > TSQL

Problem

I need a refresher on how to use Not Equal in SQL queries. Can you show the syntax, some SQL statement examples and result sets in your explanation?

Solution

The purpose of comparison operators is to compare two expressions in a Microsoft SQL database. As its name implies, the Not Equal comparison operator is used to check if two values are not equal. As with other comparison operators, Not Equal can be used on any expression except text, ntext, or image data types.

The examples for this tutorial were run in the AdventureWorks2022 database on a SQL Server 2022 server. If you want to copy and paste the SQL in any or all examples, this tip, AdventureWorks Database Installation Steps,, will show you two ways to install the database.

Below are examples of sample queries and their result sets to illustrate using Not Equal. This table summarizes the three operator symbols used for this comparison operator in a SQL relational database:

Operator	Meaning	If Values Not Equal	Comment
!=	Not equal to	1	Not ISO standard Commonly used
NOT	Not equal to	1	Not ISO standard Not commonly used Looks odd
<>	Not equal to	1	IIs ISO standard Commonly used

Let's start by looking at all the records in the HumanResources.Department table with this SELECT statement which can be run in SSMS:

/* mssqltips.com */SELECT [DepartmentID]
      ,[Name]
      ,[GroupName]
      ,[ModifiedDate]
FROM [AdventureWorks2022].[HumanResources].[Department]
ORDER BY [DepartmentID];
GO

We have 16 different departments, each in one of six groups.

All records in HumanResources.Department

Next, we'll look at all the fields in the HumanResources.Department table that are in the Research and Development group by specifying it in the WHERE clause:

/* mssqltips.com */
SELECT [DepartmentID]
      ,[Name]
      ,[GroupName]
      ,[ModifiedDate]
FROM [AdventureWorks2022].[HumanResources].[Department]
WHERE [GroupName] = 'Research and Development'
ORDER BY DepartmentID;
GO

Groups equal to Research and Development

We've seen records that are equal to a value. But we're here to find out how to get records that are not equal to a value. Let's find the records of each department that are not in the Research and Development group. Based on the data, one way to do this is to select all the records equal to the other groups.

/* mssqltips.com */
SELECT [DepartmentID]
     , [Name]
     , [GroupName]
     , [ModifiedDate]
FROM [AdventureWorks2022].[HumanResources].[Department]
WHERE [GroupName] IN ( 'Executive General and Administration', 'Inventory Management', 'Manufacturing'
                     , 'Quality Assurance', 'Sales and Marketing'
                     )
ORDER BY DepartmentID;
GO

Records IN groups other than Research and Development

This is not a very good idea because, as mentioned, it is dependent on the data and would no longer work if a new group is added. This is where 'not equal to' comes in.

The following are three examples of the Not Equal To operator with three different symbols, and their result sets to find records of departments not in the Research and Development group.

Exclamation Mark and Equal Sign (!=)

Based on my experience, the exclamation mark and equal sign (!=) is probably the most commonly used symbol for 'not equal to' and will look familiar to anyone familiar with other programming languages like C / C++, etc. To see just the records not in the Research and Development group, we can simply change the WHERE clause in the first example from equals to (=) to not equals to (!=).

/* mssqltips.com */
SELECT [DepartmentID]
      ,[Name]
      ,[GroupName]
      ,[ModifiedDate]
FROM [AdventureWorks2022].[HumanResources].[Department]
WHERE [GroupName] != 'Research and Development'
ORDER BY DepartmentID;
GO

Here are the 13 records in the table not in the Research and Development group:

SQL NOT Equal Operator

Unlike the exclamation mark and equal sign (!=), the NOT operator is not very common, but we'll show it to see how it's used. We use the first query again and precede the field in the WHERE clause with NOT; the = is negated to see the records where the group name is not Research and Development.

/* mssqltips.com */
SELECT [DepartmentID]
      ,[Name]
      ,[GroupName]
      ,[ModifiedDate]
FROM [AdventureWorks2022].[HumanResources].[Department]
WHERE NOT [GroupName] = 'Research and Development'
ORDER BY DepartmentID;
GO

This shows that the NOT operator is functionally equivalent to the Exclamation Mark and Equal Sign (!=). However, this is another case of 'just because you can doesn't mean you should.' And I find it confusing reading the filter as 'where not something equals something' is unintuitive, confusing, and it just doesn't look right to me.

Departments NOT in the Research and Development group

Left Arrow and Right Arrow (<>)

The traditional Left Arrow and Right Arrow (<>) can also be used and is ISO compliant. It's used in place of the Exclamation Mark and Equal Sign (!=) or NOT in the WHERE clause.

/* mssqltips.com */
SELECT [DepartmentID]
      ,[Name]
      ,[GroupName]
      ,[ModifiedDate]
FROM [AdventureWorks2022].[HumanResources].[Department]
WHERE [GroupName] <> 'Research and Development'
ORDER BY DepartmentID;
GOGO

There is a clear pattern here. We see that the results are identical in all three examples in the above queries.

Groups <> Research and Development group

Should You Use !=, NOT, or <>

SoSo, should you use !=, NOT, or <> in SQL statements and stored procedures? As with many things, DBAs love to answer it with 'it depends,' that answer also applies here. We've seen that SQL Server supports all three, which are all functionally the same. Let's review:

!=
- != is probably the most common operator used
- Reading it left to right, it's apparent that it means 'not equal' and is, therefore, more readable
- Consistent with many other programming languages
NOT
- Unintuitive
- Not commonly used
<>
- Supported in SQL Server
- Required in database systems that require ISO Standard SQL (DB2, access,…)
- Less readable as it doesn't make as much sense as !=

Next Steps

Here are additional Not Equal SQL-related tips to learn more:

About the author

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-07-31

Not Equal in SQL WHERE Clause Examples