Null Values

[MJatAflac]

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;

 

[Gary Walter]

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;

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}

 

[Dale Fye]

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