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

[Guest]

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')

 

[Guest]

Here is the query that is failling - in that it returns the 15 records with
B.F3(=Null) -> C.F3 (=Null) that should not be returned...

INSERT INTO C ( F1, F2, F3, F4, F5, F6, F7, F8, F9 )
SELECT A.F1, B.F2, B.F3, B.F4, "Distribution" AS F5, "QA Contact" AS F6,
IIf(nz(.[F7],"")="X",True,False) AS F7, True AS F8, Null AS F9
FROM
(A RIGHT JOIN B ON A.F1 = B.F1)
LEFT JOIN C AS C_1 ON
(B.F3 = C_1.F3) AND (B.F2 = C_1.F2) AND (B.F4 = C_1.F4)
WHERE ((B.F4 Is Not Null) AND (C_1.F1 Is Null));

....and this version of the query returns the expected ZERO records (the 2nd
time it is run):

INSERT INTO C ( F1, F2, F3, F4, F5, F6, F7, F8, F9 )
SELECT A.F1, B.F2, B.F3, B.F4, "Distribution" AS F5, "QA Contact" AS F6,
IIf(nz(.[F7],"")="X",True,False) AS F7, True AS F8, Null AS F9
FROM
(A RIGHT JOIN B ON A.F1 = B.F1)
LEFT JOIN C AS C_1 ON
(B.F2 = C_1.F2) AND (B.F4 = C_1.F4)
WHERE ((B.F4 Is Not Null) AND (C_1.F1 Is Null));

....which would be great, if I could afford to not care about F3=F3 or F3<>F3
cases (from an equi-join perspective), but I do need to care about that.

 

[Marshall Barton]

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.

 

[Guest]

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...>

 

[Michel Walsh]

Hi,


If you want null matching another null, you can coalesce, Nz(), them to a
value that is known to not present in your data, such as -1 for a field
having only positive values:

FROM .... SOME JOIN ....
ON
a.f1=b.f2 AND
a.f4=b.f4 AND
Nz(a.f3, -1) = Nz(b.f3, -1)


If you want a null matching any thing:

FROM .... SOME JOIN ....
ON
a.f1=b.f2 AND
a.f4=b.f4 AND
Nz(a.f3 = b.f3, true)


and if you want a null matching nothing, well, just leave it as it is.

FROM .... SOME JOIN ....
ON
a.f1=b.f2 AND
a.f4=b.f4 AND
a.f3 = b.f3



Null are as useful as 0 is, but you probably just re-experience what the
introduction of the 0 has been 400 years ago (in Europe, that is). And since
no one is born with that knowledge, many of us, like you, just got "that
moment". And since it would hardly kill you, I hope it won't, it will just
make you stronger!




Hoping it may help,
Vanderghast, Access MVP.

 

[Guest]

Vanderghast,

<epiphany moment>
You can use functions/expressions in the ON phrase of a JOIN expression in
JET SQL??!!
</epiphany moment>
Well DANG! Why didn't somebody tell me that a few years ago?
(and why can't the Query designer give me a clue/ability to do that more
easily!?)

YOU are one Awesome dude, V.! Thanks!

 

[Marshall Barton]

What Vanderghast said!

Functions in query expressions is one of Jet's great
features. Just don't rely on it if you want to port your db
to another db engine.

The query designer has no way for you to specify the
potentially complex ON clauses that are acceptable in an SQL
statement. Not only functions, but non-equi joins also
require SQL view to create. One of my favorites is to join
records on inexact matches using:
ON tbl1.fldx Like tbl2.patternfield
I don't know about you, but it kind of boggles my mind what
can be done with this kind of thing.

 

[Guest]

V. & M.,

Now you two have my wheels turning...

Is it too late to put a wishlist item in for Access 2007?

Wishlist Item:
On the Join Properties dialog have MS. Add a "Match Null values = Null
Values on this join" option checkbox and have the Query designer emit your
Nz(a.f3, True) = Nz(b.f3, True)
syntax in the resultant query SQL if selected.

....better yet, add a "complex Join Expression constructor" [...] button to
open a second dialog box so we can build "LIKE" or non-equiJoin expressions
through the designer for each/all joins between the two tables affected by
the selected join...

<*sigh*> ...what could've been...</*sigh*>

Does anybody know if we lose this mahhvelous ability in the new Access '07
ACE engine (I'm DL-ing the just released Beta 2 for a look...)?

 

[Guest]

Marsh,

You know, I once WROTE CODE (and not just a little bit of it) to implement
just exactly what you said below (a textfield partial-match LIKE-Join), and
now you tell me that JET SQL could have done it for me all along!
<Oh, the man-hours I wasted on that!>
....Right Outer Join T1 TO T2 ON T1.Fld1 LIKE "%" & mid(T2.Fld4, 2, 4) &
"%-%"...

achh. it's almost enough to make one wanna cry...or get spitting mad...

 

[Michel Walsh]

Hi,


I think it is preferable to let Microsoft make the announcements first ,
but it would be hard to imagine loosing functionalities... people would just
not upgrade if it was so!


Vanderghast, Access MVP

V. & M.,

Now you two have my wheels turning...

Is it too late to put a wishlist item in for Access 2007?

Wishlist Item:
On the Join Properties dialog have MS. Add a "Match Null values = Null
Values on this join" option checkbox and have the Query designer emit your
Nz(a.f3, True) = Nz(b.f3, True)
syntax in the resultant query SQL if selected.

...better yet, add a "complex Join Expression constructor" [...] button to
open a second dialog box so we can build "LIKE" or non-equiJoin
expressions
through the designer for each/all joins between the two tables affected by
the selected join...

<*sigh*> ...what could've been...</*sigh*>

Does anybody know if we lose this mahhvelous ability in the new Access '07
ACE engine (I'm DL-ing the just released Beta 2 for a look...)?

Hi,


If you want null matching another null, you can coalesce, Nz(), them to a
value that is known to not present in your data, such as -1 for a field
having only positive values:

FROM .... SOME JOIN ....
ON
a.f1=b.f2 AND
a.f4=b.f4 AND
Nz(a.f3, -1) = Nz(b.f3, -1)


If you want a null matching any thing:

FROM .... SOME JOIN ....
ON
a.f1=b.f2 AND
a.f4=b.f4 AND
Nz(a.f3 = b.f3, true)


and if you want a null matching nothing, well, just leave it as it is.

FROM .... SOME JOIN ....
ON
a.f1=b.f2 AND
a.f4=b.f4 AND
a.f3 = b.f3



Null are as useful as 0 is, but you probably just re-experience what the
introduction of the 0 has been 400 years ago (in Europe, that is). And
since
no one is born with that knowledge, many of us, like you, just got "that
moment". And since it would hardly kill you, I hope it won't, it will
just
make you stronger!




Hoping it may help,
Vanderghast, Access MVP.

 

[Guest]

V.,

awww...come on... is it an announcement if you just give us a hint? ;-)

As for your second point, I think history is on my side, so I stand by the
appropriateness of asking the question.
Let me remind you that you are saying this about the folks who turned Visual
Basic 6 into VB.Net 1.0! (specifically, see features like: "edit & continue"
which didn't return until _2 versions later_!)...'nuff said?

Hi,


I think it is preferable to let Microsoft make the announcements first ,
but it would be hard to imagine loosing functionalities... people would just
not upgrade if it was so!


Vanderghast, Access MVP

V. & M.,

Now you two have my wheels turning...

Is it too late to put a wishlist item in for Access 2007?

Wishlist Item:
On the Join Properties dialog have MS. Add a "Match Null values = Null
Values on this join" option checkbox and have the Query designer emit your
Nz(a.f3, True) = Nz(b.f3, True)
syntax in the resultant query SQL if selected.

...better yet, add a "complex Join Expression constructor" [...] button to
open a second dialog box so we can build "LIKE" or non-equiJoin
expressions
through the designer for each/all joins between the two tables affected by
the selected join...

<*sigh*> ...what could've been...</*sigh*>

Does anybody know if we lose this mahhvelous ability in the new Access '07
ACE engine (I'm DL-ing the just released Beta 2 for a look...)?

Hi,


If you want null matching another null, you can coalesce, Nz(), them to a
value that is known to not present in your data, such as -1 for a field
having only positive values:

FROM .... SOME JOIN ....
ON
a.f1=b.f2 AND
a.f4=b.f4 AND
Nz(a.f3, -1) = Nz(b.f3, -1)


If you want a null matching any thing:

FROM .... SOME JOIN ....
ON
a.f1=b.f2 AND
a.f4=b.f4 AND
Nz(a.f3 = b.f3, true)


and if you want a null matching nothing, well, just leave it as it is.

FROM .... SOME JOIN ....
ON
a.f1=b.f2 AND
a.f4=b.f4 AND
a.f3 = b.f3



Null are as useful as 0 is, but you probably just re-experience what the
introduction of the 0 has been 400 years ago (in Europe, that is). And
since
no one is born with that knowledge, many of us, like you, just got "that
moment". And since it would hardly kill you, I hope it won't, it will
just
make you stronger!




Hoping it may help,
Vanderghast, Access MVP.

 

[Michel Walsh]

.... but VB.Net is NOT an upgraded version of VB6, it is a new product with a
"marketing-touch" chosen name.


Vanderghast, Access MVP

V.,

awww...come on... is it an announcement if you just give us a hint? ;-)

As for your second point, I think history is on my side, so I stand by the
appropriateness of asking the question.
Let me remind you that you are saying this about the folks who turned
Visual
Basic 6 into VB.Net 1.0! (specifically, see features like: "edit &
continue"
which didn't return until _2 versions later_!)...'nuff said?

Hi,


I think it is preferable to let Microsoft make the announcements first
,
but it would be hard to imagine loosing functionalities... people would
just
not upgrade if it was so!


Vanderghast, Access MVP

V. & M.,

Now you two have my wheels turning...

Is it too late to put a wishlist item in for Access 2007?

Wishlist Item:
On the Join Properties dialog have MS. Add a "Match Null values = Null
Values on this join" option checkbox and have the Query designer emit
your
Nz(a.f3, True) = Nz(b.f3, True)
syntax in the resultant query SQL if selected.

...better yet, add a "complex Join Expression constructor" [...] button
to
open a second dialog box so we can build "LIKE" or non-equiJoin
expressions
through the designer for each/all joins between the two tables affected
by
the selected join...

<*sigh*> ...what could've been...</*sigh*>

Does anybody know if we lose this mahhvelous ability in the new Access
'07
ACE engine (I'm DL-ing the just released Beta 2 for a look...)?


:

Hi,


If you want null matching another null, you can coalesce, Nz(), them
to a
value that is known to not present in your data, such as -1 for a
field
having only positive values:

FROM .... SOME JOIN ....
ON
a.f1=b.f2 AND
a.f4=b.f4 AND
Nz(a.f3, -1) = Nz(b.f3, -1)


If you want a null matching any thing:

FROM .... SOME JOIN ....
ON
a.f1=b.f2 AND
a.f4=b.f4 AND
Nz(a.f3 = b.f3, true)


and if you want a null matching nothing, well, just leave it as it is.

FROM .... SOME JOIN ....
ON
a.f1=b.f2 AND
a.f4=b.f4 AND
a.f3 = b.f3



Null are as useful as 0 is, but you probably just re-experience what
the
introduction of the 0 has been 400 years ago (in Europe, that is). And
since
no one is born with that knowledge, many of us, like you, just got
"that
moment". And since it would hardly kill you, I hope it won't, it will
just
make you stronger!




Hoping it may help,
Vanderghast, Access MVP.

 

[Guest]

.... but VB.Net is NOT an upgraded version of VB6, it is a new product with a
"marketing-touch" chosen name.

That's not what we were all told "back then"...it was to be our "upgrade
path" to the future! ...but now were just lobbing semantics at each other.

I guess I'll just have to find out for myself whether ACE supports this
stuff or not.

However, this may all be moot. If User-Level Security is gone from
ACE/Access 2007...what's the point? This alone may be enough of an anchor to
dissuade a REALLY LARGE food company from moving up to Office 2007
altogether...

 

[Gary Walter]

PMFBI

Here be a "cheat sheet" that may be worth rechecking sometimes if you are like me:

Conditional Expressions in ON or WHERE clauses:

A record will be returned only if conditional expression evaluates to -1 (TRUE)

A record will not be returned if conditional expression evaluates to 0 (FALSE) or if conditional expression evaluates to Null


======================================
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 Nz(t1.f1<>t2.f2, -1) NOT (t1.f1 IS NULL AND t2.f2 IS NULL) Nz(t1.f1<>t2.f2, -1) AND NOT (t1.f1 IS NULL AND t2.f2 IS NULL)
----- ------ ------------------------------------------- --------------------------------- -------------------------------------------------------- ----------------------------------------------------------------------------------------------
a b -1 return {a, b} -1 return {a, b} -1 -1 AND -1 = -1 return {a, b}
a a 0 do not return {a, a} 0 do not return {a, a} -1 0 AND -1 = 0 do not return {a, a}
a Null Null do not return {a, Null} -1 return {a, Null} -1 -1 AND -1 = -1 return {a, Null}
Null b Null do not return {Null, b} -1 return {Null, b} -1 -1 AND -1 = -1 return {Null, b}
Null Null Null do not return {Null, Null} -1 return {Null, Null} 0 -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) (t1.f1 IS NULL AND t2.f2 IS NULL) (t1.f1=t2.f2) OR (t1.f1 IS NULL AND t2.f2 IS NULL) (Nz(t1.f1,'zzzz') = Nz(t2.f2,'zzzz'))
----- ------ ---------------------------------------- ------------------------- -------------------------------------------------- --------------------------------------------------------------------------- ---------------------------------------------------
a b 0 do not return {a, b} 0 0 0 OR 0 = 0 do not return {a, b} ('a' = 'b') = 0
a a -1 return {a, a} -1 0 -1 OR 0 = -1 return {a, a} ('a' = 'a') = -1
a Null Null do not return {a, Null} 0 0 Null OR 0 = Null do not return {a, Null} ('a' = 'zzzz') = 0
Null b Null do not return {Null, b} 0 0 Null OR 0 = Null do not return {Null, b} ('zzzz' = 'b') = 0
Null Null Null do not return {Null, Null} 0 -1 Null OR -1 = -1 return {Null, Null} ('zzzz' = 'zzzz') = -1

 

[Guest]

Gary,

Thanks.

That chart looks interesting, but I'm afraid its formatting didn't translate
well into the newsgroup.
Is there any way you could try that again - perhaps with an HTML-ized
verison of the text I can copy and paste so the fcolumn formatting survives
the translation...?

PS. I think your info should probably be in the Access documentation
somewhere...

- Mark

 

[Gary Walter]

Conditional Expressions in ON or WHERE clauses:

A record will be returned
- only if conditional expression evaluates to -1 (TRUE)

A record will not be returned
- if conditional expression evaluates to 0 (FALSE)
- or if conditional expression evaluates to Null


======================================
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}