Null Values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MJatAflac - 20 Feb 2008 16:42 GMT

Someone sent me this sql and asked me to try to find an answer for him. He
runs the query twice once saying where field a equals field b and once where
field a is not equal to field b. I would expect that the total records from
each run would add up to the same number as taking the criteria out all
together. This is not true and by a process of elimination we determined that
the records falling out where field b is null.

Can anyone explain that? Thanks All!

SELECT DISTINCT dbo_axiom_flex_gm.duns,
dbo_axiom_flex_gm.gm_srce_group_name, dbo_axiom_flex_gm.gm_group_nbr,
dbo_axiom_flex_gm.fl_group_nbr, dbo_axiom_flex_gm.fl_flex_id,
dbo_axiom_flex_gm.fl_srce_group_name, dbo_axiom_flex_gm.gm_acc1_nm,
dbo_axiom_flex_gm.gm_acc2_nm, dbo_axiom_flex_gm.gm_srce_tin,
dbo_axiom_flex_gm.fl_srce_tin, dbo_axiom_flex_gm.gm_tin

FROM [Distinct Group Numbers - Col] INNER JOIN dbo_axiom_flex_gm ON
[Distinct Group Numbers - Col].gm_group_nbr = dbo_axiom_flex_gm.gm_group_nbr

WHERE (((dbo_axiom_flex_gm.gm_group_nbr)=[dbo_axiom_flex_gm]![fl_group_nbr])
AND ((dbo_axiom_flex_gm.gm_srce_tin)<>[dbo_axiom_flex_gm]![fl_srce_tin] And
(dbo_axiom_flex_gm.gm_srce_tin)<>" ") AND
((dbo_axiom_flex_gm.fl_srce_tin)=[gm_tin])) OR
(((dbo_axiom_flex_gm.gm_group_nbr)=[dbo_axiom_flex_gm]![fl_group_nbr]) AND
((dbo_axiom_flex_gm.gm_srce_tin)<>[dbo_axiom_flex_gm]![fl_srce_tin] And
(dbo_axiom_flex_gm.gm_srce_tin)<>" ") AND ((dbo_axiom_flex_gm.gm_tin)
Is Null))

ORDER BY dbo_axiom_flex_gm.gm_srce_tin;

Signature

Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

Reply to this Message

Gary Walter - 20 Feb 2008 16:56 GMT

> Someone sent me this sql and asked me to try to find an answer for him. He
> runs the query twice once saying where field a equals field b and once
[quoted text clipped - 32 lines]
>
> ORDER BY dbo_axiom_flex_gm.gm_srce_tin;

Hi Michal,

======================================
Return Records WHERE 2 Fields Do Not Match
======================================

Null AND True = Null
Null AND False = False
Null AND Null = Null

t1.f1 t2.f2 t1.f1<>t2.f2
----- ------ ----------------------------------
a b -1 return {a, b}
a a 0 do not return {a, a}
a Null Null do not return {a, Null}
Null b Null do not return {Null, b}
Null Null Null do not return {Null, Null}

t1.f1 t2.f2 Nz(t1.f1<>t2.f2, -1)
----- ------ -----------------------
a b -1 return {a, b}
a a 0 do not return {a, a}
a Null -1 return {a, Null}
Null b -1 return {Null, b}
Null Null -1 return {Null, Null}

Nz(t1.f1<>t2.f2, -1)
AND
NOT (t1.f1 IS NULL
t1.f1 t2.f2 AND t2.f2 IS NULL)
----- ------ ---------------------------------------
a b -1 AND -1 = -1 return {a, b}
a a 0 AND -1 = 0 do not return {a, a}
a Null -1 AND -1 = -1 return {a, Null}
Null b -1 AND -1 = -1 return {Null, b}
Null Null -1 AND 0 = 0 do not return {Null, Null}

======================================
Return Records WHERE 2 Fields Do Match
======================================

Null OR True = True
Null OR False = Null
Null OR Null = Null

t1.f1 t2.f2 t1.f1=t2.f2 Nz(t1.f1=t2.f2, 0)
----- ------ ------------------------------ ----------------
a b 0 do not return {a, b} 0
a a -1 return {a, a} -1
a Null Null do not return {a, Null} 0
Null b Null do not return {Null, b} 0
Null Null Null do not return {Null, Null} 0

t1.f1 t2.f2 (t1.f1=t2.f2) OR (t1.f1 IS NULL AND t2.f2 IS NULL)
----- ------ -----------------------------------------------------
a b 0 OR 0 = 0 do not return {a, b}
a a -1 OR 0 = -1 return {a, a}
a Null Null OR 0 = Null do not return {a, Null}
Null b Null OR 0 = Null do not return {Null, b}
Null Null Null OR -1 = -1 return {Null, Null}

t1.f1 t2.f2 (Nz(t1.f1,'zzzz') = Nz(t2.f2,'zzzz'))
----- ------ ------------------------------------------
a b ('a' = 'b') = 0 do not return {a, b}
a a ('a' = 'a') = -1 return {a, a}
a Null ('a' = 'zzzz') = 0 do not return {a, Null}
Null b ('zzzz' = b) = 0 do not return {Null, b}
Null Null ('zzzz' = 'zzzz') = -1 return {Null, Null}

Reply to this Message

Dale Fye - 20 Feb 2008 18:03 GMT

Put another way, a NULL value is an unknown value, so any query that two
fields (whether equal or not equal) will not include values where either or
both of the fields is NULL.

For the equality query, I would continue to test for [Fielda] = [Fieldb]

For the inequality query, you might want to consider something like:

WHERE [Fielda] IS NULL
OR [Fieldb] IS NULL
OR [Fielda] <> [Fieldb]

HTH
Dale

Signature

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

> Someone sent me this sql and asked me to try to find an answer for him. He
> runs the query twice once saying where field a equals field b and once where
[quoted text clipped - 25 lines]
>
> ORDER BY dbo_axiom_flex_gm.gm_srce_tin;

Reply to this Message