SQL Book

Learn to use SQL not equal [Complete Guide]

Comparison with Not Equal

When writing SQL queries, the Not Equal operator is used to compare 2 expressions to determine if they are equal to each other or not.

If the expressions are not NULL and are not equal to each other then the Not Equal operator will return true.

If the expressions are not NULL and they are equal to each other than the Not Equal operator will return false.

If either of the expressions is NULL then Not Equal will return NULL.

Not Equal can be written as either <> or as !=. Both of these ways of writing the operator have their advantages and disadvantages that we will discuss below.

Expression 1   Expression 2 Return Value
1 <>  2 True
1 <> 1 False
1 <> NULL NULL

Syntax

expression <> expression
-- or alternative
expression != expression

The expressions must be of a matching datatype or datatypes that can be implicitly converted to each other. Otherwise, a data type conversion error will occur.

<> vs !=

Which version of writing the Not Equal operator should you use? This may depend on the following:

  • ANSI SQL compliance
  • Experience with other coding languages
  • Following company/project coding standards

ANSI compliance for easier migrations

ANSI SQL is a standard for writing SQL that means that different Relational Database Management Systems (RDBMS) can use the same SQL code. e.g. If you write a SQL query that is ANSI SQL compliant it would work in SQL Server, Oracle, MySQL, etc.

The <> form of the Not Equal operator is SQL-92 ANSI compliant whereas the != form is not.

Writing your SQL code using the ANSI compliant version of the operator means that your database code would be easier to migrate to a different RDBMS.

If you had written queries in a Microsoft Access database that you wanted to migrate to MySQL. You would not need to go through your SQL code looking for instances of != that you would need to change to <>.

Familiarity with other coding languages

If you are already familiar with other programming languages that also use != to represent Not Equal this may encourage you to use the != form in SQL.

Examples of other languages that use != include C#, Java, Javascript, and PHP.

If you already use one of these languages then you may find that it feels more natural to use != in your SQL code.

However, with a little practice, you can soon get used to using <> instead. This will provide better compatibility with other RDBMS.

Pre-existing code or coding standards

Another reason for using the != operator is if you work on a database where there is existing SQL code that uses !=. If the pre-existing code uses != you may choose to continue to use this for reasons of consistency.

There maybe coding standards that have already been defined for a project/database that you need to follow.

Summary of factors influencing whether to use <> or !=

  <> !=
ANSI SQL Compliant?  Yes  No
RDBMS support  
  • MySQL
  • Oracle
  • Postgre
  • SQLite
  • SQL Server
  • Sybase
Also used in
  • VB
  • VB.NET
  • VB Script
  • c#
  • java
  • javascript
  • php

Show me some examples

First, let's create a small table and insert a few rows of data into it.


CREATE TABLE Person (
	FirstName varchar(10) NULL,
	DateOfBirth date NULL
);
INSERT INTO Person (FirstName, DateOfBirth) 
	VALUES ('Nick', '01/05/1970'), 
		('Jane', '03/08/2000'),
		('Simon', '01/01/1988');

Selecting all rows from the table returns 3 rows.


SELECT * FROM Person;
FirstName DateOfBirth
Nick 1970-01-05
Jane 2000-03-08
Simon 1988-01-01

Comparison of strings

Select all rows where the FirstName does not equal Nick


SELECT * 
FROM Person
WHERE FirstName <> 'Nick';
FirstName DateOfBirth
Jane 2000-03-08
Simon 1988-01-01

SQL Not Equal with a Date


SELECT * 
FROM Person
WHERE DateOfBirth <> '03/08/2000';
FirstName DateOfBirth
Nick 1970-01-05
Simon 1988-01-01

Take care when using the operator with NULL

NULL can throw a spanner in the works when using this comparison operator. This is because if one or both of the expressions being compared are NULL then Not Equal returns NULL.

NULL is not the same as true and so the comparison won't return any matching values. This can lead to results that we might not initially expect.


-- INSERT a row with NULL values
-- Add a row with NULL values
INSERT INTO Person (FirstName, DateOfBirth)
	VALUES (NULL, NULL);

-- You may expect 3 rows (including the row with NULLs) 
-- to be returned but only 2 rows are returned
SELECT * 
FROM Person
WHERE FirstName <> 'Nick';

 If we want to compare where something does not equal NULL we can instead use IS NOT NULL.

SELECT * 
FROM Person
WHERE FirstName <> 'Jane' 
   AND FirstName IS NOT NULL; 

Specifying NOT Equal with multiple values

There may be occasions when you need to specify more than one value that you want to compare to. You can achieve this in the WHERE clause by chaining multiple Not Equal comparisons together with the AND keyword. 


SELECT *
FROM Person
WHERE FirstName <> 'Nick'
	AND Year(DateOfBirth) <> 1988;
FirstName DateOfBirth
Jane 2000-03-08

 

Quick Quiz: Test your knowledge for

Question . Write the SQL to:

Conclusion

In this article, we have seen how the Not Equal operator can be used when writing our SQL queries. We have seen how using the ANSI SQL version of <> is preferable unless there is a good reason not to. 

We have demonstrated how the operator can be used in the WHERE clause. The WHERE clause can be used when reading data with SELECT statements as well as when modifying data with UPDATE statements. It is important to understand how NULL values can affect the results that are returned by Not Equal operator.

Join our newsletter

Subscribe to our free weekly newsletter and get exclusive SQL tips and articles straight to your inbox. 

 Your email address is only used to send you our newsletter.