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

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:

SQL
Copy
1SELECT * 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:

SQL
Copy
1SELECT * 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.
Categories

Design Rules, Performance Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE PROCEDURE [dbo].[CustOrdersOrders]
 2 @CustomerID nchar(5)
 3AS 
 4SELECT     OrderID,
 5           OrderDate,
 6           RequiredDate,
 7           ShippedDate
 8FROM       Orders
 9WHERE      CustomerID=@CustomerID
10       AND ShippedDate!=NULL
11       AND RequiredDate IS NULL
12
13SELECT     OrderID,
14           OrderDate,
15           RequiredDate,
16           ShippedDate
17FROM       Orders  -- IGNORE:SA0001(STATEMENT)
18
19SELECT     OrderID,
20           OrderDate,
21           RequiredDate,
22           ShippedDate
23FROM       Orders
24WHERE      CustomerID=@CustomerID
25       AND ShippedDate!=NULL AND RequiredDate IS NULL -- IGNORE:SA0001(LINE)
26
27SELECT     OrderID,
28           OrderDate,
29           RequiredDate,
30           ShippedDate
31FROM       Orders
32WHERE      CustomerID=@CustomerID
33       AND ShippedDate!=NULL -- IGNORE:SA0001
34       AND RequiredDate IS NULL
Example Test SQL with Automatic Fix
SQL
Copy
 1CREATE PROCEDURE [dbo].[CustOrdersOrders]
 2 @CustomerID nchar(5)
 3AS 
 4SELECT     OrderID,
 5           OrderDate,
 6           RequiredDate,
 7           ShippedDate
 8FROM       Orders
 9WHERE      CustomerID=@CustomerID
10       AND ShippedDate IS NOT NULL
11       AND RequiredDate IS NULL
12
13SELECT     OrderID,
14           OrderDate,
15           RequiredDate,
16           ShippedDate
17FROM       Orders  -- IGNORE:SA0001(STATEMENT)
18
19SELECT     OrderID,
20           OrderDate,
21           RequiredDate,
22           ShippedDate
23FROM       Orders
24WHERE      CustomerID=@CustomerID
25       AND ShippedDate!=NULL AND RequiredDate IS NULL -- IGNORE:SA0001(LINE)
26
27SELECT     OrderID,
28           OrderDate,
29           RequiredDate,
30           ShippedDate
31FROM       Orders
32WHERE      CustomerID=@CustomerID
33       AND ShippedDate!=NULL -- IGNORE:SA0001
34       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
See Also

Other Resources

Analysis Rules