Null Value Exploration

[former_SQLA_member1694868]

I have two statements that seem the same that look for the same rows with the same null values. One returns the correct results and one returns 0 results.

With this table having some null values:

CREATE TABLE "AACoupons" (
"AACouponID" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
"CouponAmount" INTEGER NULL,
"AAItemID" INTEGER NULL,
PRIMARY KEY ( "AACouponID" ASC )
) ;

INSERT INTO "AACoupons" ("AACouponID","CouponAmount","AAItemID") VALUES(300,2,3);
INSERT INTO "AACoupons" ("AACouponID","CouponAmount","AAItemID") VALUES(301,4,3);
INSERT INTO "AACoupons" ("AACouponID","CouponAmount","AAItemID") VALUES(302,6,3); 
INSERT INTO "AACoupons" ("AACouponID","CouponAmount","AAItemID") VALUES(500,162,NULL);
INSERT INTO "AACoupons" ("AACouponID","CouponAmount","AAItemID") VALUES(501,NULL,NULL);
INSERT INTO "AACoupons" ("AACouponID","CouponAmount","AAItemID") VALUES(506,NULL,NULL);

Why is it that this statement returns 3 results:

select * from AACoupons where AAItemID = null;

But this block returns 0 results:

begin
declare @Noval int;
set @Noval = null;
select * from AACoupons where AAItemID = @Noval;
end;

I realize the variable is an intermediate step, but the variable is null on initializing and then it is explicitly set to null so I do not understand the difference.

[VolkerBarth]

If you want to have a comparison that returns TRUE in both cases:

• not-null-value = the-same-not-null-value

and

• null = null

and you're using SA12, you can use the new (and ANSI-compliant) NOT DISTINCT FROM search condition.

I.e.

col1 not distinct from col2

is sematically the same as

col1 = col2 or (col1 is null and col2 is null)

However, the performance for NOT DISTINCT FROM should be better, as it is sargable.

[former_SQLA_member1694868]

Another new one that is good to know.