SA0001 : Equality and inequality comparisons involving a NULL constant found. Use IS NULL or IS NOT NULL

Introduction

Equality and inequality comparisons with a NULL constant can lead to unexpected results, as NULL represents an unknown value and such comparisons always evaluate to UNKNOWN .

Description

In SQL Server, handling NULL correctly is critical for database accuracy and integrity. A frequent issue arises when equality or inequality comparisons involve a NULL constant. This occurs often in stored procedures, views, functions, and triggers. When SET ANSI_NULLS is ON, such comparisons do not return a true or false value; instead, they yield UNKNOWN , causing logical errors in queries.

Example of problematic query:

SELECT * FROM Sales WHERE ytd_sales > NULL;

This query yields UNKNOWN instead of returning rows. Comparisons like ytd_sales > @MyVariable also yield UNKNOWN when @MyVariable is NULL , potentially leading to incorrect query results.

Unexpected logical outcomes when NULL is part of a comparison.
Potential data integrity issues if query results are misinterpreted or go unnoticed due to UNKNOWN results.

How to fix

To ensure accurate query results and maintain data integrity, correct any comparisons involving NULL constants by using IS and IS NOT predicates instead of equality and inequality operators.

Follow these steps to address the issue:

1.Identify any instances in your SQL Server queries, stored procedures, views, functions, and triggers where you use equality ( = ) or inequality ( != ) operators with NULL constants.

2.Replace these comparisons with appropriate IS NULL or IS NOT NULL predicates to ensure the query logic remains sound and returns expected results.

3.Review your database code to ensure that setting SET ANSI_NULLS is ON, so these changes take effect correctly in your SQL Server environment.

Example of corrected query:

SELECT * FROM Sales WHERE ytd_sales IS NOT NULL;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

2 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE PROCEDURE [dbo].[CustOrdersOrders]
 @CustomerID nchar(5)
AS
SELECT     OrderID,
           OrderDate,
           RequiredDate,
           ShippedDate
FROM       Orders
WHERE      CustomerID=@CustomerID
       AND ShippedDate!=NULL
       AND RequiredDate IS NULL

SELECT     OrderID,
           OrderDate,
           RequiredDate,
           ShippedDate
FROM       Orders  -- IGNORE:SA0001(STATEMENT)

SELECT     OrderID,
           OrderDate,
           RequiredDate,
           ShippedDate
FROM       Orders
WHERE      CustomerID=@CustomerID
       AND ShippedDate!=NULL AND RequiredDate IS NULL -- IGNORE:SA0001(LINE)

SELECT     OrderID,
           OrderDate,
           RequiredDate,
           ShippedDate
FROM       Orders
WHERE      CustomerID=@CustomerID
       AND ShippedDate!=NULL -- IGNORE:SA0001
       AND RequiredDate IS NULL

Example Test SQL with Automatic Fix

CREATE PROCEDURE [dbo].[CustOrdersOrders]
 @CustomerID nchar(5)
AS
SELECT     OrderID,
           OrderDate,
           RequiredDate,
           ShippedDate
FROM       Orders
WHERE      CustomerID=@CustomerID
       AND ShippedDate IS NOT NULL
       AND RequiredDate IS NULL

SELECT     OrderID,
           OrderDate,
           RequiredDate,
           ShippedDate
FROM       Orders  -- IGNORE:SA0001(STATEMENT)

SELECT     OrderID,
           OrderDate,
           RequiredDate,
           ShippedDate
FROM       Orders
WHERE      CustomerID=@CustomerID
       AND ShippedDate!=NULL AND RequiredDate IS NULL -- IGNORE:SA0001(LINE)

SELECT     OrderID,
           OrderDate,
           RequiredDate,
           ShippedDate
FROM       Orders
WHERE      CustomerID=@CustomerID
       AND ShippedDate!=NULL -- IGNORE:SA0001
       AND RequiredDate IS NULL

Analysis Results

	Message	Line	Column
1	SA0001 : Equality and inequality comparisons involving a NULL constant found. Use IS NULL or IS NOT NULL.	10	22

Analysis Rules