Join on an Empty Field

[Commish]

I am trying to use Access to confirm that input files are being
properly imported and processed by a third party app - testing the
developer's work.

And I am using access to join the tables that represent that input and
output. Anyway, when I match on a given column of data that contains a
mix of values like "0318" and empty cells (nulls), the query only
joins and matches on the data that is present.

For the purpose of a join, does Null equal Null? If the value is null
in both tables, will that be a successful join?

 

[MGFoster]

I am trying to use Access to confirm that input files are being
properly imported and processed by a third party app - testing the
developer's work.

And I am using access to join the tables that represent that input and
output. Anyway, when I match on a given column of data that contains a
mix of values like "0318" and empty cells (nulls), the query only
joins and matches on the data that is present.

For the purpose of a join, does Null equal Null? If the value is null
in both tables, will that be a successful join?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

NULL does not equal NULL. NULL means "the unknown value," and,
therefore, and unknown cannot be compared to an unknown - the result
would be unknown. Have you tried LEFT JOINing the tables:

FROM MainTable AS MT LEFT JOIN TestTable AS TT ON MT.<identifier column
name> = TT.<identifier column name>

By adding

WHERE TT.nullable_column_name IS NULL

you will get only those rows (Records) that have a NULL in the "given
column."

In the FROM clause you can change the <identifier column name> to your
nullable column and you should get all the rows in the Main Table with
the NULLed and non-NULLed column rows in the Test Table.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSUcIaoechKqOuFEgEQL7zACePjWgzXm9aovj/NHi0yJva15NkhgAoKRL
wbkQTR28jT3M6uNW93IbiS8V
=PA6j
-----END PGP SIGNATURE-----

 

[Allen Browne]

Without testing, I would expect this kind of thing to work:

SELECT Table1.*
FROM Table1 INNER JOIN Table2
ON ((Table1.Field1 = Table2.Field1)
OR ((Table1.Field1 Is Null) AND (Table2.Field1 Is Null)));

Access probably won't be able to show that in design view.
Watch the bracketing when mixing ANDs and ORs.
The expression above should be more efficent than Nz().

 

[Commish]

Without testing, I would expect this kind of thing to work:

SELECT Table1.*
FROM Table1 INNER JOIN Table2
ON ((Table1.Field1 = Table2.Field1)
OR ((Table1.Field1 Is Null) AND (Table2.Field1 Is Null)));

Access probably won't be able to show that in design view.
Watch the bracketing when mixing ANDs and ORs.
The expression above should be more efficent than Nz().

Thanks, this is useful and I've made it work. There is a way to make
it work in the design view - it's amost like an easter egg - you make
the SQL work and then if successful it is displayed in Design View.