• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 440
  • Last Modified:

SQLSERVER 2008R2 = NULL not the same as IS NULL

Is there a difference between = NULL and IS NULL in sqlserver 2008R2

Example: If  I change all the “ = NULL ” in this statement:

SELECT SUM ( ACT_MATERIAL_COST ), SUM ( ACT_LABOR_COST ), SUM ( ACT_BURDEN_COST ), SUM ( ACT_SERVICE_COST ), SUM ( QTY ) FROM INVENTORY_TRANS WHERE TYPE = 'O' AND WORKORDER_BASE_ID = NULL AND WORKORDER_LOT_ID = NULL AND WORKORDER_SPLIT_ID = NULL AND WORKORDER_SUB_ID = NULL AND CUST_ORDER_ID = 'CC130713' AND CUST_ORDER_LINE_NO = 7.00000000 AND TRANSACTION_ID <= 415924
Result:  NULL NULL NULL NULL NULL

To “ IS NULL ” I get the correct results:

SELECT SUM ( ACT_MATERIAL_COST ), SUM ( ACT_LABOR_COST ), SUM ( ACT_BURDEN_COST ), SUM ( ACT_SERVICE_COST ), SUM ( QTY ) FROM INVENTORY_TRANS WHERE TYPE = 'O' AND WORKORDER_BASE_ID IS NULL AND WORKORDER_LOT_ID IS NULL AND WORKORDER_SPLIT_ID IS NULL AND WORKORDER_SUB_ID IS NULL AND CUST_ORDER_ID = 'CC130713' AND CUST_ORDER_LINE_NO = 7.00000000 AND TRANSACTION_ID <= 415924
Result: 0.99 0.00 0.00 0.00 1.0000
0
BFanguy
Asked:
BFanguy
  • 3
  • 2
1 Solution
 
Daniel WilsonCommented:
Yes, there is a difference.  Now ... there is a SQL Server setting that can be changed for that, iirc, but in standard SQL (and T-SQL which is the MS variant), you use IS with NULL, not =.

= really compares values.  And NULL isn't a value ... it's undefined.  So strictly speaking, nothing can ever = NULL.  Not even NULL = NULL!
0
 
BFanguyAuthor Commented:
Thanks, do you happen to have the setting in SQL server that can be changed?
0
 
BFanguyAuthor Commented:
This statement is running from a purchased software product.
0
 
Daniel WilsonCommented:
SET ANSI_NULLS OFF

http://technet.microsoft.com/en-us/library/ms188048.aspx

The setting is deprecated and will go away eventually ... but is supported through SQL 2012.
0
 
BFanguyAuthor Commented:
Thank you very much.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now