Query on two tables with matching null fields

[tryit]

Suppose I have two tables:

Table 1:

ID a b c
1 1 2
2 3 4
3 5 6
4 7 8 9

Table2

ID a b c
1 1 2
2 3 4
3 5 6
4 7 8 9

The following SQL:

SELECT Table1.ID, Table2.ID
FROM Table1 INNER JOIN Table2
ON (Table1.c = Table2.c)
AND (Table1.b = Table2.b)
AND (Table1.a = Table2.a);

Returns:

Table1.ID Table2.ID
4 4

I do want these tables connected by inner joins not left or right (in
other words I want only those records where all the joined fields are
equal to be returned). It seems that when the records contain
matching blank (null) fields that that is not considered a match.

How do I get the records with matching null fields to be returned with
the query?

I seem to have missed a fundamental concept about SQL. I would have
assumed that if two records contain nulls in joined fields that, given
all other fields are equal, they would match. Apparently not, so my
question is how do I force this behavior?

Thanks,
Tom

 

[Jeff Boyce]

"Null" means nothing there to compare. You can't join on nulls because
there's nothing there.

Besides, how would you tell the difference between two or more Nulls (hint,
nothing there, no way to compare!)?

Do you need to use an actual value instead of a Null? (remember, 0 can mean
"none of what you are measuring", but Null means nothing there)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John W. Vinson]

I do want these tables connected by inner joins not left or right (in
other words I want only those records where all the joined fields are
equal to be returned). It seems that when the records contain
matching blank (null) fields that that is not considered a match.

Correct. It isn't a match.

Null is a funny beast. I think of a NULL value as meaning "this value is
unknown, unspecified, undefined". It's sort of like saying that you don't know
Bill's address, and you also don't know Jane's address - can you conclude that
Bill and Jane are POSSLQs? Certainly not!

If you want to make the (logically invalid) assumption that a NULL value in
table1 should match a NULL value in TableB (but if so which of the twenty
records in TableB...?) you can use the NZ() function in the join; assuming
that only c might be NULL, and that 0 is never a valid value, then

SELECT Table1.ID, Table2.ID
FROM Table1 INNER JOIN Table2
ON (NZ(Table1.c,0) = NZ(Table2.c,0))
AND (Table1.b = Table2.b)
AND (Table1.a = Table2.a);

should work.

 

[Marshall Barton]

Suppose I have two tables:

Table 1:

ID a b c
1 1 2
2 3 4
3 5 6
4 7 8 9

Table2

ID a b c
1 1 2
2 3 4
3 5 6
4 7 8 9

The following SQL:

SELECT Table1.ID, Table2.ID
FROM Table1 INNER JOIN Table2
ON (Table1.c = Table2.c)
AND (Table1.b = Table2.b)
AND (Table1.a = Table2.a);

Returns:

Table1.ID Table2.ID
4 4

I do want these tables connected by inner joins not left or right (in
other words I want only those records where all the joined fields are
equal to be returned). It seems that when the records contain
matching blank (null) fields that that is not considered a match.

How do I get the records with matching null fields to be returned with
the query?

I seem to have missed a fundamental concept about SQL. I would have
assumed that if two records contain nulls in joined fields that, given
all other fields are equal, they would match. Apparently not, so my
question is how do I force this behavior?

Null is, in general, never equal to anything, not even Null.
One way to think about Null is to call it unknown. An
unknown value is not necessarily equal to another unknown
value. So the result of the comparison unknown = unknown is
unknown.

A way around it is to use the Nz function to convert Null
values to some value that can never occur in the table
field. In your simple example 0 never appears in the three
join fields so this would be adequate.
ON (Nz(Table1.c,0) = Nz(Table2.c),0)
AND (Nz(Table1.b,0) = Table2.b),0)
AND (Nz(Table1.a,0) = Table2.a),0)

But that can not be represented in the query designer and
coming up with a never used value is not always possible.
Using an old fashioned join via the Where clause is more
general:

SELECT Table1.ID, Table2.ID
FROM Table1, Table2
WHERE (Table1.c = Table2.c
OR (Table1.c Is Null And Table2.c Is Null))
AND (Table1.b = Table2.b
OR (Table1.b Is Null And Table2.b Is Null))
AND (Table1.a = Table2.a
OR (Table1.a Is Null And Table2.a Is Null))

 

[KARL DEWEY]

If it is a text field you can replace all nulls with a zero lenght string
"" and that will match.

If it is a number field then replace with a zero.

Or if those will not work as you need the null for some reason then use the
Nz function in a query and join the query instead of the table.