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

Marsh,

Thanks for the speedy reply....I think... :-\

So, just so I have this straight, according to Jet's internal Join
arithmatic,
Table B Table C
F2 = "Smith" = -> F2 = "Smith"
F3 = Null = -> F3 = Null
F4 = "QA" = -> F4 = "QA"
....in an outer join = "No Match"?
....but
Table B Table C
F2 = "Smith" = -> F2 = "Smith"
F3 = Null F3 = Null
F4 = "QA" = -> F4 = "QA"
....in an outer join = "Match"?

....I just KNEW these damn Nulls would drive me INSANE one day...

So, how do you suggest I handle that small %age of data records where F3 =
Null?
....build another, separate query to handle just those records? <Grrrr...>


"Marshall Barton" wrote:

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)
  • Left Outer Join: Index Seek not providing all index columns
    ... We have a left outer join query which is intended to prove ... The wrinkle is that the SQL Server 2000 query optimizer is generally ... left outer join entextractitems eei ...
    (microsoft.public.sqlserver.server)
  • Re: Cond. Formatting w/ DLookup
    ... I'd like to be able to indicate on the first form, ... form's record source to be a query with an outer join of the ... This form is continuous (actually a subform), ... Using your excellent idea of the outer join, ...
    (microsoft.public.access.formscoding)
  • Re: Optimising the Query
    ... SUM ... FROM TABLE3 ... The query is taking records from TABLE2 and TABLE3 and LEFT OUTER JOIN ...
    (comp.databases.oracle.misc)
  • Re: Cond. Formatting w/ DLookup
    ... I'd like to be able to indicate on the first form, ... IvId field, then a better way would be to change the first ... form's record source to be a query with an outer join of the ... Using your excellent idea of the outer join, ...
    (microsoft.public.access.formscoding)