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 Owners;Result:
+-----------+-------------+------------+----------------+-------------------+ | OwnerId | FirstName | LastName | Phone | Email | |-----------+-------------+------------+----------------+-------------------| | 1 | Homer | Connery | (308) 555-0100 | [email protected] | | 2 | Bart | Pitt | (231) 465-3497 | [email protected] | | 3 | Nancy | Simpson | (489) 591-0408 | NULL | | 4 | Boris | Trump | (349) 611-8908 | NULL | | 5 | Woody | Eastwood | (308) 555-0112 | [email protected] | | 6 | Burt | Tyson | (309) 565-0112 | [email protected] | +-----------+-------------+------------+----------------+-------------------+
Example
If we wanted to return a list of all owners that do not have an OwnerId of 3, we could do this:
SELECT *
FROM Owners
WHERE OwnerId != 3;Result:
+-----------+-------------+------------+----------------+-------------------+ | OwnerId | FirstName | LastName | Phone | Email | |-----------+-------------+------------+----------------+-------------------| | 1 | Homer | Connery | (308) 555-0100 | [email protected] | | 2 | Bart | Pitt | (231) 465-3497 | [email protected] | | 4 | Boris | Trump | (349) 611-8908 | NULL | | 5 | Woody | Eastwood | (308) 555-0112 | [email protected] | | 6 | Burt | Tyson | (309) 565-0112 | [email protected] | +-----------+-------------+------------+----------------+-------------------+
Our query uses the not equal to operator (!=) to test whether the OwnerId column is not equal to 3. The query returns all owners except owner number 3.
Strings
When comparing with a string value, use quotes around the string. For example, if we wanted to get information about all owners whose first name is not Homer, we could do the following:
SELECT *
FROM Owners
WHERE FirstName != 'Homer';Result:
+-----------+-------------+------------+----------------+-------------------+ | OwnerId | FirstName | LastName | Phone | Email | |-----------+-------------+------------+----------------+-------------------| | 2 | Bart | Pitt | (231) 465-3497 | [email protected] | | 3 | Nancy | Simpson | (489) 591-0408 | NULL | | 4 | Boris | Trump | (349) 611-8908 | NULL | | 5 | Woody | Eastwood | (308) 555-0112 | [email protected] | | 6 | Burt | Tyson | (309) 565-0112 | [email protected] | +-----------+-------------+------------+----------------+-------------------+
Multiple Conditions
If you have multiple conditions, you can use multiple operators (whether both the same operators or different).
Like this:
SELECT * FROM Owners
WHERE OwnerId != 1 AND OwnerId != 3;Result:
+-----------+-------------+------------+----------------+-------------------+ | OwnerId | FirstName | LastName | Phone | Email | |-----------+-------------+------------+----------------+-------------------| | 2 | Bart | Pitt | (231) 465-3497 | [email protected] | | 4 | Boris | Trump | (349) 611-8908 | NULL | | 5 | Woody | Eastwood | (308) 555-0112 | [email protected] | | 6 | Burt | Tyson | (309) 565-0112 | [email protected] | +-----------+-------------+------------+----------------+-------------------+
Once you start using more conditions, you should use parentheses to surround the conditions that you want to be evaluated first. If you don’t do this, you may find that you get unexpected results, due to the conditions being evaluated in an order that you didn’t intend.
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 equals (=) operator:
SELECT *
FROM Owners
WHERE NOT FirstName != 'Homer';Result:
+-----------+-------------+------------+----------------+-------------------+ | OwnerId | FirstName | LastName | Phone | Email | |-----------+-------------+------------+----------------+-------------------| | 1 | Homer | Connery | (308) 555-0100 | [email protected] | +-----------+-------------+------------+----------------+-------------------+
In this case, you’re better off just using the equals (=) operator, like this:
SELECT *
FROM Owners
WHERE FirstName = 'Homer';Of course, this itself could be negated with the NOT operator, which would then give us the same result that the not equal to (!=) operator gives us:
SELECT *
FROM Owners
WHERE NOT FirstName = 'Homer';NULL Values
You may have noticed that our original sample table contains a couple of NULL values in the Email 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 Email column to NULL.
SELECT * FROM Owners
WHERE Email != 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 Owners
WHERE Email IS NOT NULL;Result:
+-----------+-------------+------------+----------------+-------------------+ | OwnerId | FirstName | LastName | Phone | Email | |-----------+-------------+------------+----------------+-------------------| | 1 | Homer | Connery | (308) 555-0100 | [email protected] | | 2 | Bart | Pitt | (231) 465-3497 | [email protected] | | 5 | Woody | Eastwood | (308) 555-0112 | [email protected] | | 6 | Burt | Tyson | (309) 565-0112 | [email protected] | +-----------+-------------+------------+----------------+-------------------+
Now we get only those rows that aren’t NULL in the Email 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.