In SQL, the not equal to operator (<>) compares the non-equality of two expressions. That is, it tests whether one expression is not equal to another expression.
If either or both operands are NULL, NULL is returned.
SQL also has another not equal to operator (!=), which does the same thing. Which one you use may depend on your DBMS, which one you’re the most comfortable using, and perhaps also whether your organisation has any coding conventions that dictate which one should be used.
Source Table
Imagine our database contains the following table. This is the table we will use for the examples on this page.
SELECT * FROM Pets;Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +---------+-------------+-----------+-----------+------------+ (8 rows affected)
Example
If we wanted to get all pets that do not have a certain owner, we could do this:
SELECT *
FROM Pets
WHERE OwnerId <> 3;Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 3 | 2 | 2 | Scratch | 2018-10-01 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +---------+-------------+-----------+-----------+------------+
Our query uses the not equal to operator (<>) to test whether the OwnerId column is not equal to 3. The query returns all pets that do not have owner number 3 as an owner.
Strings
When comparing with a string value, use quotes around the string. For example, if we wanted to get information about all pets that are not named Fluffy, we could do the following:
SELECT *
FROM Pets
WHERE PetName <> 'Fluffy';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +---------+-------------+-----------+-----------+------------+
Multiple Conditions
If you have multiple conditions, you can use multiple operators (whether both the same operators or different).
Like this:
SELECT * FROM Pets
WHERE OwnerId <> 1 AND OwnerId <> 3;Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 3 | 2 | 2 | Scratch | 2018-10-01 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +---------+-------------+-----------+-----------+------------+
Negating the Condition
If you use the NOT operator to negate the condition provided by the not equal to operator, you’ll end up getting the results of the equal to (=) operator:
SELECT *
FROM Pets
WHERE NOT PetName <> 'Fluffy';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | +---------+-------------+-----------+-----------+------------+
In this case, you’re better off just using the equal to (=) operator, like this:
SELECT *
FROM Pets
WHERE PetName = 'Fluffy';Of course, this itself could be negated with the NOT operator, which would then give us the same result that the equal to (<>) operator gives us:
SELECT *
FROM Pets
WHERE NOT PetName = 'Fluffy';NULL Values
You may have noticed that our original sample table contains a couple of NULL values in the DOB column.
A column containing NULL means that it has no value. This is different to 0 or false, or even an empty string.
You can’t use the not equal to operator to compare against NULL. Actually, this may depend on your DBMS and its configuration. But for now, let’s look at what happens if I try to compare the DOB column to NULL.
SELECT * FROM Pets
WHERE DOB <> NULL;Result:
(0 rows affected)
The way to test for non-NULL values is to use IS NOT NULL.
Therefore, we would need to rewrite the above statement as follows.
SELECT * FROM Pets
WHERE DOB IS NOT NULL;Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | +---------+-------------+-----------+-----------+------------+
Now we get only those rows that aren’t NULL in the DOB column.
If you’re interested, see SQL Server ANSI_NULLS Explained to see how you can change the way NULL values are treated in SQL Server.