Re: outer Join and NULL values...?!?!!!

Mark Burns wrote:

OK first the question: why are the NULLs screwing up my query results?

The problem:
I am constructing a series of append queries to dissect non-normal data from
some older tables to fit it all into a new normalized structure. I have 3
tables:
A, B, and C. I am importing data from table B into table C (referencing
table A in the process).
To avoid importing duplicate records I am joining tables B and C in my query
as an outer join B.F2->C.F2 + B.F3->C.F3 + B.F4->C.F4 (the query also matches
B.F1->A.F3, but that's not the problem here... also, importantly, I check
for B.F2 is not null AND C.F1 IS Null).
When I run this query the FIRST time, I get 80 records inserted into table C
as expected.
I expect that when I run this query again immediately, I will get ZERO
records qualified (because all the outer joins will now match table C records
which maked C.F1 NOT Null.
...BUT I still get 15 records qualifying to be inserted into Table C from
Table B!
The 15 records all contain NULL values in B.F3 (and, in C.F3).
I know this is the problem because if I remove the Outer Join B.F3->C.F3 I
get exactly the ZERO qualifying records I expect?!! HUH??!!
So...Jet 4.0 does not think Null=Null here, or what??
(and...they are all nulls, the fields are set 'allow 0 length strings =
false')


Well, it is true that Null = Null is never True. It is also
the case that Null <> Null is never True.

Think of it this way. Null sort of means Unknown. So, is
an unknown value equal (or not equal) to another unknown
value? The answer is unknown so it can not be either True
or False.

--
Marsh
MVP [MS Access]
.

Relevant Pages

  • Re: outer Join and NULL values...?!?!!!
    ... (and why can't the Query designer give me a clue/ability to do that more ... ...in an outer join = "No Match"? ... ...BUT I still get 15 records qualifying to be inserted into Table C ... Null sort of means Unknown. ...
    (microsoft.public.access.queries)
  • Re: outer Join and NULL values...?!?!!!
    ... ....in an outer join = "No Match"? ... To avoid importing duplicate records I am joining tables B and C in my query ... ...BUT I still get 15 records qualifying to be inserted into Table C from ... Null sort of means Unknown. ...
    (microsoft.public.access.queries)
  • Re: optimising the following query
    ... For this query to perform well, the optimal would be a clusterd index ... on (transctiondate, servicename) or. ... CASE ua WHEN 'unknown' THEN NULL ELSE ua END AS ua ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Math question in a query
    ... Reno, When adding numbers, a field that is null is telling access that the ... Null is the same as Unknown. ... design a new query using the tbl_payment. ... >>> payment or many smaller payments. ...
    (microsoft.public.access.forms)
  • Re: outer Join and NULL values...?!?!!!
    ... Functions in query expressions is one of Jet's great ... ...in an outer join = "No Match"? ... Null sort of means Unknown. ...
    (microsoft.public.access.queries)