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.

[Former Member]

I haven't tested your code, but the first place I'd look it to see what exactly is in the @noVal variable.

Null is not a value, it is the absence of a value. You are probably treading in very dangerous territory here of unpredictable results.

If it your intent to look for nulls, use IS NULL.

 select * from AACoupons where AAItemID IS NULL;

Since it is not a value, the concept of equating null to another value doesn't make sense.

One of the many things I'm grateful for in SQLA is that it allows me to divide by zero to return a null, rather than crashing and burning on a div by zero error. (It's an option,and not default behavior since when, maybe release 4.something?)

[former_SQLA_member1694868]

The divide by 0 trick is a good one to know. I'm not looking for null explicitly. The variable value is filled in dynamically, but if it is null I would like it to return the corresponding rows that have a null value, but if the value is 3 then return the rows that have a value of 3.

I would think having set the variable = null would settle what was in it.

I just wondered why.