Problem
I am new to T-SQL programming and want to learn about the not equal operators in my Microsoft SQL Server relational database. How do they work for beginners? Is there a difference between them based on the SQL data? Which one should I use in my SQL database for stored procedures, subqueries, scripts, etc.?
Solution
This tutorial is all about the various not equal operators.
For reference, they are:
- A <> B
- A != B
- NOT A = B
I will do a deep dive covering how the operators work and explaining the use case. There will be many different demos to show examples of how to use these operators in different situations.
This tutorial utilizes the WideWorldImporters sample database for all demos. The database can be downloaded for free from Github. All demos are being executed using SSMS and SQL Server 2019, but the information contained in this tip is valid going back many, many versions of SQL Server.
Overview – SQL NOT Equal Operator
The SQL Server not equal operators are used to test that one value, often a column, does not match the value of another. These operators can also be used in T-SQL code as a part of WHILE loops, IF statements, HAVING clauses, join predicates, SQL GROUP BY or CASE statements.
Consider this SQL query.
SELECT OrderID
,SalespersonPersonID
,PickedByPersonID
FROM WideWorldImporters.Sales.Orders
WHERE SalespersonPersonID <> 2;This SQL query will return every row from the Sales.Orders table where the SalespersonPersonID is any value OTHER than 2.
Rules and Best Practices – Comparison Operators
Choosing An Operator
The query shown above would work exactly the same if the WHERE clause used one of the alternative operators as shown below. In fact, this is true in any situation. These operators can be used interchangeably in SQL Server.
The ANSI standard operand is <> (greater than – less than) and may be preferred by some organizations as it is more likely to work on other platforms and thus increases the likelihood of cross-platform code reusability. The “NOT Value =” is quite unpopular and none of the code examples in this tip will use it.
WHERE SalespersonPersonID != 2
WHERE NOT SalespersonPersonID = 2Not equal and NULL values
Working with NULL values can yield unexpected results. Consider, again, the query above. Note that in the output the first row is order number 2. That is because order number 1 was associated to Salesperson number 2 and was excluded. There are several rows that were packed by person number 3 along with a series of NULL values for the column. To remove the rows with the value of 3 it would make sense to add another argument that compares the PickedByPersonID column to the value of 3 using the SQL NOT equal operator.
Here is that SQL statement:
SELECT OrderID
,SalespersonPersonID
,PickedByPersonID
FROM WideWorldImporters.Sales.Orders
WHERE SalespersonPersonID <> 2
AND PickedByPersonID != 3;The result set, however, is highly unexpected. It jumped all the way down to order 21 and did not show any of the rows that had a NULL value for PickedByPersonID. Since NULL doesn’t equal 3 and that was the only new criterion this can be confusing to new SQL writers.
The issue here is that the not equal operators cannot be used to compare to NULL values and will always evaluate as false when a NULL value is involved. To avoid confusion when writing SQL code, this author likes to logically think of the operator this way. “PickedByPersonID != 3” means “PickedByPersonID has a value and that value doesn’t equal 3.”
This NULL value issue goes both ways. Consider this version of the SELECT statement where an integer variable is declared, but no value is ever assigned to it. The variable is, therefore, a NULL value.
DECLARE @NO_VALUE INT;
SELECT OrderID
,SalespersonPersonID
,PickedByPersonID
FROM WideWorldImporters.Sales.Orders
WHERE SalespersonPersonID <> 2
AND PickedByPersonID != @NO_VALUE;This query is essentially saying “Only show me rows when @NO_VALUE has a value and that value doesn’t match PickedByPersonID”. Since @NO_VALUE does not have a value, it will return exactly zero rows.
Successfully comparing to NULL values
If code needs to check for any value OTHER than NULL then the not equal operators will not work. As seen in the example above, if either side of the not equal operator is NULL then the argument will always evaluate as false. Instead, replace a not equal operator with the text “IS NOT”. The query below will show any rows where the SalespersonPersonID is any non-null value other than 2 and the PickedByPersonID is any non-NULL value. Here is the syntax:
SELECT OrderID
,SalespersonPersonID
,PickedByPersonID
FROM WideWorldImporters.Sales.Orders
WHERE SalespersonPersonID <> 2
AND PickedByPersonID IS NOT NULL;Sometimes a query needs to include NULL values as part of a not equal argument. Consider a scenario where a report writer is asked to show all orders that were not packed by person 3. In this situation an unpacked order (meaning a NULL value for PackedByPersonID) would still need to be shown.
There are 2 common methods for dealing with this issue. The first is to use the ISNULL function on the side of the argument where the NULL value is expected and for which it needs to be accounted. The second is to use an OR to explicitly check for the NULL value using the IS NOT NULL text.
Here are both options.
SELECT OrderID
,SalespersonPersonID
,PickedByPersonID
FROM WideWorldImporters.Sales.Orders
WHERE SalespersonPersonID <> 2
AND ISNULL(PickedByPersonID, -1) != 3;
SELECT OrderID
,SalespersonPersonID
,PickedByPersonID
FROM WideWorldImporters.Sales.Orders
WHERE SalespersonPersonID <> 2
AND (PickedByPersonID != 3 OR PickedByPersonID IS NULL);Not equal with strings
The not equal operators can be used to compare a string data type value (char, nchar, varchar, nvarchar) to another. The following example shows an IF that compares a string data type variable to a hard coded string value.
--Ignore test user
IF @UserLogin <> 'TestUser'
BEGIN
END Not equal with numbers
The not equal operators can be used with a numeric data type including int, bigint, smallint, tinyint, numeric, decimal, float, real, and money). The example below shows a WHILE loop being based on the @@FETCH_STATUS variable. This is a common way to write cursor loops.
WHILE @@FETCH_STATUS != 0
BEGIN
END Not equal with dates
A not equal operator can be used to compare any date or datetime value. The query below shows invoices for the month of December 2013 grouped by salesperson, but explicitly excludes Christmas Day using a not equal operator.
SELECT SalespersonPersonID, SUM(ExtendedPrice) TotalPrice
FROM Sales.Invoices
INNER JOIN Sales.InvoiceLines ON Invoices.InvoiceID = InvoiceLines.InvoiceID
WHERE InvoiceDate BETWEEN '01-Dec-2013' AND '31-Dec-2013'
AND InvoiceDate != '25-Dec-2013' --Christmas
GROUP BY SalespersonPersonID
ORDER By SalespersonPersonID;There are several ways to handle entering date and datetime values, but the most common method is to use quotes and type in a string that can be easily converted to a date or datetime.
Final Thoughts
The not equal operator is a fundamental tool in every SQL writer’s toolbox. Understanding how to use it is an absolute must for anyone that wants to write effective code.
Next Steps
