"=null" and select statement!

Question

I have asked this question before in this forum and they told me that it will return an empty result set. I want to know that if I set the column with null values, will it also return an empty result set? (Note: ANSI_NULLS is OFF)

SELECT 'A' FROM T WHERE A = NULL;

Also this is an example that I find confusing:

DECLARE @val CHAR(4)
SET @val = NULL
SET ANSI_NULLS ON

If @val =NULL
    PRINT ‘TRUE’
ELSE
    PRINT ‘FALSE’

SET ANSI_NULLS OFF

If @val =NULL
    PRINT ‘TRUE’
ELSE
    PRINT ‘FALSE’

The site that I found this example on is: http://www.sqlservercentral.com/articles/T-SQL/understandingthedifferencebetweenisnull/871/

Answer 1

It will always return an empty result set, because nothing can ever equal NULL. NULL is considered "unknown", so even if you did SELECT 'A' FROM T WHERE NULL = NULL; it would return an empty set. If you want to check if a column is null, do:

SELECT 'A' FROM T WHERE A IS NULL;

Answer 2

You need IS NULL. NULL = NULL evaluates to false in SQL