Using IS NULL or equal operator, IS NOT NULL or not equal operator?

Begin with get data from AdventureWorks2012 database and Person table.

SELECT BusinessEntityID
      ,Title
      ,FirstName
      ,LastName
      ,ModifiedDate
  FROM AdventureWorks2012.Person.Person

Above You see all data from several columns.

What is better solution compare NULL value with content of column? If You would like get records from table with column stores NULL value, in SQL Server You must use IS NULL operator. The first example compare NULL value with contenet of field by = operator.

SELECT BusinessEntityID
      ,Title
      ,FirstName
      ,LastName
      ,ModifiedDate
  FROM AdventureWorks2012.Person.Person
  WHERE Title = NULL

In this way You don’t see any records, even though some records have column stores NULL value. In this situation only IS NULL return these rows:

SELECT BusinessEntityID
      ,Title
      ,FirstName
      ,LastName
      ,ModifiedDate
  FROM AdventureWorks2012.Person.Person
  WHERE Title IS NULL

Here You see record with NULL value in Title column.

It is similarly with != or operator, if You would like to get data not equals NULL.

SELECT BusinessEntityID
      ,Title
      ,FirstName
      ,LastName
      ,ModifiedDate
  FROM AdventureWorks2012.Person.Person
  WHERE Title != NULL

Or

SELECT BusinessEntityID
      ,Title
      ,FirstName
      ,LastName
      ,ModifiedDate
  FROM AdventureWorks2012.Person.Person
  WHERE Title  NULL

Use != or operator the rows will not be return. But if You use IS NOT NULL operator, You see records without NULL value in Title column.

SELECT BusinessEntityID
      ,Title
      ,FirstName
      ,LastName
      ,ModifiedDate
  FROM AdventureWorks2012.Person.Person
  WHERE Title IS NOT NULL

The same effect will be if You compare by != or operator to empty string:

SELECT BusinessEntityID
      ,Title
      ,FirstName
      ,LastName
      ,ModifiedDate
  FROM AdventureWorks2012.Person.Person
  WHERE Title != ''

Or

SELECT BusinessEntityID
      ,Title
      ,FirstName
      ,LastName
      ,ModifiedDate
  FROM AdventureWorks2012.Person.Person
  WHERE Title  ''

This last compare by != or with empty string work well either for number, not only strings datatypes in column storing NULL values.