‘Not Equal To’ comparison operators in SQL Server

There are two 'Not Equal To' comparison operators that you can use in SQL Server, != (not ISO compliant) and <> (ISO compliant). Both operators function the same way in SQL Server, they give the result TRUE when you compare non equal nonnull values:

First example is using != comparison operator:

USE AdventureWorks
SELECT Title, FirstName, MiddleName, LastName
FROM Person.Person
WHERE Title != 'Mr.'

not_equal_example_1

You get the same result using <> comparison operator:

USE AdventureWorks
SELECT Title, FirstName, MiddleName, LastName
FROM Person.Person
WHERE Title <> 'Mr.'

not_equal_example_2

Both operators give result NULL if either or both values you compare are NULL (when SET ANSI_NULLS is ON):

USE AdventureWorks
SELECT Title, FirstName, MiddleName, LastName
FROM Person.Person
WHERE Title <> NULL

not_equal_example_NULL

If you want to list all rows that have NULL value in a column, use IS NULL construct:

USE AdventureWorks
SELECT Title, FirstName, MiddleName, LastName
FROM Person.Person
WHERE Title IS NULL

not_equal_example_IS_NULL

If you want to list all rows that don't have NULL value in a column, use IS NOT NULL construct:

USE AdventureWorks
SELECT Title, FirstName, MiddleName, LastName
FROM Person.Person
WHERE Title IS NOT NULL

not_equal_example_IS_NOT_NULL

Related articles:
List Rows with NULL values in a Column in SQL Server
List all Nullable or Non-Nullable Columns in a SQL Server Table
How to replace NULL with a different value in a query result in SQL Server

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">