Learn to use SQL not equal [Complete Guide]
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|
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.
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|
|Also used in||
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;
Comparison of strings
Select all rows where the FirstName does not equal Nick
SELECT * FROM Person WHERE FirstName <> 'Nick';
SQL Not Equal with a Date
SELECT * FROM Person WHERE DateOfBirth <> '03/08/2000';
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;
Quick Quiz: Test your knowledge for
Question . Write the SQL to:
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.