query results in Access

[maiaeutic]

I am querying the production database against a spreadsheet of updates and
looking for mismatches on one column. The format of the query is as follows:

SELECT recat.IOEXSO, recat.IOINSO, recat.IOEXOF, CRMOFFCP1.IOEXSO,
CRMOFFCP1.IOINSO, recat.IOSUBC, CRMOFFCP1.IOSUBC
FROM recat INNER JOIN CRMOFFCP1 ON (recat.IOEXSO = CRMOFFCP1.IOEXSO) AND
(recat.IOINSO = CRMOFFCP1.IOINSO) AND (recat.IOEXOF = CRMOFFCP1.IOEXOF) AND
(recat.IOEXCO = CRMOFFCP1.IOEXCO)
WHERE (((recat.IOSUBC)<>[CRMOFFCP1].[IOSUBC]));

Now when I run this query I get 8 results which correspond to entries in
which the recat.IOSUBC field is NULL and the CRMOFFCP1.IOSUBC file has a
value.

I have found two things ususual about this, however:
1. First of all, it is my understanding that a NULL value in a field will
not produce a meaningful comparison to a non-NULL field; i.e. I don’t
understand why these results are being returned in the first place since for
every row returned the value of IOSUBC in recat is NULL.

2. Secondly, I can’t seem to reproduce this result by generating my own
table in which I populate the recat.IOSUBC field with NULL values, and then
duplicate all the other values in both tables. When I run this query using a
manually generated “dummy†table I don’t get any results, which again
indicates to me that NULL fields do not compare to non-NULL fields.

3. When I run the same query and switch to option “2†for the join fields
between the two tables, I get no results. This is also counterintuitive, as
I don’t understand how you could ever get fewer records on an outer left join
than the same query run as an inner join.

 

[vanderghast]

1. You are right. Any null compared (= or <> ) to any value return unknown
(null) which is NOT the value true, so WHEREshould not return the record
where that conditition evaluates to null. Your field is probably an empty
string, a string with no character in it, which is also considered a NULL in
some database, but not in Jet, neither in MS SQL Server.

2. Probably because they are not NULL but empty (zero length ) strings.


3.The WHERE clause is evaluated AFTER the join, and while the join will
generate the extra rows, the WHERE clause, not dealing with the potential
REAL null values, will remove them.


Vanderghast, Access MVP

 

[maiaeutic]

2. But if this is a data type issue then why would the inner join query
produce eight results, and the left outer join run on the same data, and with
the same set of parameters, fail to produce those eight results?

Just to check on the data type, I ran the
=IIf(IsNull([IOSUBC]),"Unknown",Format([IOSUBC],"@;\ZLS"))

query on the production data. All of the blank fields in IOSUBC came back
NULL as opposed to zero-type fields.

So I am still stumped as to why when i toggle between an inner join and a
left join, I get eight results in the first case (corresponding to a NULL
value in recat table and a distinct value in production table), yet when I
change the join type to left outer, there are no results.

1. You are right. Any null compared (= or <> ) to any value return unknown
(null) which is NOT the value true, so WHEREshould not return the record
where that conditition evaluates to null. Your field is probably an empty
string, a string with no character in it, which is also considered a NULL in
some database, but not in Jet, neither in MS SQL Server.

2. Probably because they are not NULL but empty (zero length ) strings.


3.The WHERE clause is evaluated AFTER the join, and while the join will
generate the extra rows, the WHERE clause, not dealing with the potential
REAL null values, will remove them.


Vanderghast, Access MVP

I am querying the production database against a spreadsheet of updates and
looking for mismatches on one column. The format of the query is as
follows:

SELECT recat.IOEXSO, recat.IOINSO, recat.IOEXOF, CRMOFFCP1.IOEXSO,
CRMOFFCP1.IOINSO, recat.IOSUBC, CRMOFFCP1.IOSUBC
FROM recat INNER JOIN CRMOFFCP1 ON (recat.IOEXSO = CRMOFFCP1.IOEXSO) AND
(recat.IOINSO = CRMOFFCP1.IOINSO) AND (recat.IOEXOF = CRMOFFCP1.IOEXOF)
AND
(recat.IOEXCO = CRMOFFCP1.IOEXCO)
WHERE (((recat.IOSUBC)<>[CRMOFFCP1].[IOSUBC]));

Now when I run this query I get 8 results which correspond to entries in
which the recat.IOSUBC field is NULL and the CRMOFFCP1.IOSUBC file has a
value.

I have found two things ususual about this, however:
1. First of all, it is my understanding that a NULL value in a field will
not produce a meaningful comparison to a non-NULL field; i.e. I don’t
understand why these results are being returned in the first place since
for
every row returned the value of IOSUBC in recat is NULL.

2. Secondly, I can’t seem to reproduce this result by generating my own
table in which I populate the recat.IOSUBC field with NULL values, and
then
duplicate all the other values in both tables. When I run this query
using a
manually generated “dummy†table I don’t get any results, which again
indicates to me that NULL fields do not compare to non-NULL fields.

3. When I run the same query and switch to option “2†for the join fields
between the two tables, I get no results. This is also counterintuitive,
as
I don’t understand how you could ever get fewer records on an outer left
join
than the same query run as an inner join.

 

[vanderghast]

Indeed:

SELECT ...
FROM recat LEFT JOIN unpreservedSide ON ...
WHERE recat.IOSUBC <> unpreservedSide.[IOSUBC];



should return the same thing as an INNER JOIN, since while the LEFT JOIN
could return more record, those are with

unpreservedSide.[IOSUBC] IS
NULL
and then
WHERE recat.IOSUBC <> unpreservedSide.[IOSUBC]

would then reject them. So, the inner and the left join should return the
same thing... ***if*** the is the ***only*** difference is LEFT being
changed to INNER, or vice-versa.




Vanderghast, Access MVP

2. But if this is a data type issue then why would the inner join query
produce eight results, and the left outer join run on the same data, and
with
the same set of parameters, fail to produce those eight results?

Just to check on the data type, I ran the
=IIf(IsNull([IOSUBC]),"Unknown",Format([IOSUBC],"@;\ZLS"))

query on the production data. All of the blank fields in IOSUBC came back
NULL as opposed to zero-type fields.

So I am still stumped as to why when i toggle between an inner join and a
left join, I get eight results in the first case (corresponding to a NULL
value in recat table and a distinct value in production table), yet when I
change the join type to left outer, there are no results.

1. You are right. Any null compared (= or <> ) to any value return
unknown
(null) which is NOT the value true, so WHEREshould not return the record
where that conditition evaluates to null. Your field is probably an empty
string, a string with no character in it, which is also considered a NULL
in
some database, but not in Jet, neither in MS SQL Server.

2. Probably because they are not NULL but empty (zero length ) strings.


3.The WHERE clause is evaluated AFTER the join, and while the join will
generate the extra rows, the WHERE clause, not dealing with the potential
REAL null values, will remove them.


Vanderghast, Access MVP

I am querying the production database against a spreadsheet of updates
and
looking for mismatches on one column. The format of the query is as
follows:

SELECT recat.IOEXSO, recat.IOINSO, recat.IOEXOF, CRMOFFCP1.IOEXSO,
CRMOFFCP1.IOINSO, recat.IOSUBC, CRMOFFCP1.IOSUBC
FROM recat INNER JOIN CRMOFFCP1 ON (recat.IOEXSO = CRMOFFCP1.IOEXSO)
AND
(recat.IOINSO = CRMOFFCP1.IOINSO) AND (recat.IOEXOF = CRMOFFCP1.IOEXOF)
AND
(recat.IOEXCO = CRMOFFCP1.IOEXCO)
WHERE (((recat.IOSUBC)<>[CRMOFFCP1].[IOSUBC]));

Now when I run this query I get 8 results which correspond to entries
in
which the recat.IOSUBC field is NULL and the CRMOFFCP1.IOSUBC file has
a
value.

I have found two things ususual about this, however:
1. First of all, it is my understanding that a NULL value in a field
will
not produce a meaningful comparison to a non-NULL field; i.e. I don’t
understand why these results are being returned in the first place
since
for
every row returned the value of IOSUBC in recat is NULL.

2. Secondly, I can’t seem to reproduce this result by generating my own
table in which I populate the recat.IOSUBC field with NULL values, and
then
duplicate all the other values in both tables. When I run this query
using a
manually generated “dummy†table I don’t get any results, which again
indicates to me that NULL fields do not compare to non-NULL fields.

3. When I run the same query and switch to option “2†for the join
fields
between the two tables, I get no results. This is also
counterintuitive,
as
I don’t understand how you could ever get fewer records on an outer
left
join
than the same query run as an inner join.