‘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