Comparing NULL values in SQL Server

Comparing a field or variable to NULL. It’s important to remember that a T-SQL syntax should use the IS operator in place of the Equals (=) and Not Equal To (<>) operators. This is a behavior setting in SQL Server called ANSI_NULLS. When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are non-null values in column_name.

The following T-SQL snippet demonstrates this behavior:

--create temporary table
DECLARE @CompanyTable TABLE (
	CompanyID int NOT NULL,
	Name varchar(255) NULL
);

--insert data
INSERT INTO @CompanyTable VALUES (1, 'Microsoft');
INSERT INTO @CompanyTable VALUES (2, 'Blackberry');
INSERT INTO @CompanyTable VALUES (3, 'Apple');
INSERT INTO @CompanyTable VALUES (4, NULL);
INSERT INTO @CompanyTable VALUES (5, 'Google');

-- declaring variable
DECLARE @NameValue varchar(255) = NULL;

--select company with name = null
SELECT * FROM @CompanyTable WHERE Name = @NameValue;

--select company with name is null
SELECT * FROM @CompanyTable WHERE Name IS NULL;

You can SET ANSI_NULLS OFF then both of these queries would work, but Microsoft recommends that you avoid using this feature in new development work, and plan to modify applications that currently use this feature.

And if you write,

SELECT * FROM @CompanyTable WHERE Name IS @NameValue

you will get the error: Incorrect syntax near ‘@NameValue’. Expecting NOT, or NULL.

So the best approach here would be simply:

IF (@NameValue IS NULL)
	SELECT * FROM @CompanyTable WHERE Name IS NULL;
ELSE
	SELECT * FROM @CompanyTable WHERE Name = @NameValue;

--same way when using <>
IF (@NameValue IS NULL)
	SELECT * FROM @CompanyTable WHERE Name IS NOT NULL;
ELSE
	SELECT * FROM @CompanyTable WHERE Name <> @NameValue;

Another option is to use the ISNULL function which replaces NULL with a specified value. Example:

SELECT * FROM @CompanyTable WHERE ISNULL(Name, '') = ISNULL(@NameValue, '');

My example will also get all rows that have an empty value (”). You can attribute any value of type varchar(255) to this this parameter, because the field/variable in use is a type varchar(255).

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s