Explanation???

[Jon Berraondo]

I've found something in access I can't explain.....

I have two tables:

CLIENTS_COMPANY_A:
CodA Int.
NameA Text(40)
NIFA TEXT(15)

CLIENTS_COMPANY_B:
CodB Int.
NameB Text(40)
NIFB TEXT(15)

A want to know which clients of company A aren't on company B, with the next
SQL: (using the field NIF)

Of course there is clients on A which NIF's aren't found on B

select * from clients_company_A
where NIFA not in (select NIFB from clients_company_B)

It gives back 0 rows !!!!!!

There are NULL values on NIFA and in NIFB, so I retried:

select * from clients_company_A
where NIFA <> ""
and NIFA not in (select NIFB from clients_company_B where NIFB<>"")

This one gives back all the rows I where looking for, but I don't understand
why the first SQL didn't work.

I've trid the same on Mysql and the first SQL works ok.

Is there any explanation for this? Is access working properly?
Thanks in advance, Jon

[Keith W]

"Jon Berraondo" <jo***********@elektra-sa.es> wrote in message
news:dn**********@domitilla.aioe.org...

I've found something in access I can't explain.....

I have two tables:

CLIENTS_COMPANY_A:
CodA Int.
NameA Text(40)
NIFA TEXT(15)

CLIENTS_COMPANY_B:
CodB Int.
NameB Text(40)
NIFB TEXT(15)

A want to know which clients of company A aren't on company B, with the
next
SQL: (using the field NIF)

Of course there is clients on A which NIF's aren't found on B

select * from clients_company_A
where NIFA not in (select NIFB from clients_company_B)

It gives back 0 rows !!!!!!

There are NULL values on NIFA and in NIFB, so I retried:

select * from clients_company_A
where NIFA <> ""
and NIFA not in (select NIFB from clients_company_B where NIFB<>"")

This one gives back all the rows I where looking for, but I don't
understand
why the first SQL didn't work.

I've trid the same on Mysql and the first SQL works ok.

Is there any explanation for this? Is access working properly?

Do you have your text fields set to allow zero-length strings?

Keith.
www.keithwilby.com

[Terry Kreft]

Hmm, all your second statement does is exclude NIFAs which are not empty
strings and shouldn't affect Null values (which would be excluded anyway as
they wouldn't equate).

Personally as "not in" is not optimised in Access I would have gone with

SELECT
*
FROM
clients_company_A
LEFT OUTER JOIN
clients_company_B
ON
NIFA = NIFB
WHERE
NIFB Is Not Null
--
Terry Kreft

"Jon Berraondo" <jo***********@elektra-sa.es> wrote in message
news:dn**********@domitilla.aioe.org...

I've found something in access I can't explain.....

I have two tables:

CLIENTS_COMPANY_A:
CodA Int.
NameA Text(40)
NIFA TEXT(15)

CLIENTS_COMPANY_B:
CodB Int.
NameB Text(40)
NIFB TEXT(15)

A want to know which clients of company A aren't on company B, with the
next
SQL: (using the field NIF)

Of course there is clients on A which NIF's aren't found on B

select * from clients_company_A
where NIFA not in (select NIFB from clients_company_B)

It gives back 0 rows !!!!!!

There are NULL values on NIFA and in NIFB, so I retried:

select * from clients_company_A
where NIFA <> ""
and NIFA not in (select NIFB from clients_company_B where NIFB<>"")

This one gives back all the rows I where looking for, but I don't
understand
why the first SQL didn't work.

I've trid the same on Mysql and the first SQL works ok.

Is there any explanation for this? Is access working properly?
Thanks in advance, Jon

[David W. Fenton]

"Terry Kreft" <te*********@mps.co.uk> wrote in
news:Xj********************@karoo.co.uk:

Personally as "not in" is not optimised in Access I would have
gone with

Who says it's not optimized? No, SHOWPLAN does not show any
optimization for it, but that's because SHOWPLAN never implemented
handling of any form of subqueries. That doesn't mean the query
optimizer is not optimizing subqueries, only that you can't find out
how using SHOWPLAN.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Lyle Fairfield]

SELECT DISTINCT cca.* FROM CLIENTS_COMPANY_A cca
LEFT OUTER JOIN CLIENTS_COMPANY_B ccb
ON cca.NIFA=ccb.NIFB
WHERE
Nz(cca.NIFA,"") <> ""
AND
Nz(ccb.NIFB,"") = ""

or

WHERE
cca.NIFA Is Not Null
AND
ccb.NIFB Is Null

The latter can be used if zero length strings are not allowed for
cca.NIFA and ccb.NIFB. If these fields are indexed then Rushmore may
optimize the query and make the JOIN many times faster. As you allow
Nulls, this may be offset or may not work at all.

In my experience "Not In" = "The query will finish executing but !!!!
not in !!!! your lifetime".

[Lyle Fairfield]

Everyone has solutions; this is mine:

SELECT DISTINCT cca.* FROM CLIENTS_COMPANY_A cca
LEFT JOIN CLIENTS_COMPANY_B ccb
ON cca.NIFA=ccb.NIFB
WHERE
Nz(cca.NIFA,"") <> ""
AND
Nz(ccb.NIFB) = ""

If you're not allowing zero length strings then you can avoid Nz with
WHERE
cca.NIFA Is Not Null
AND
ccb.NIFB Is Null

In the latter case maybe Rushmore will run the query a gazillion times
faster assuming it creates or has available to it appropriate indexes.

BTW "Not In" is an abbreviation for "This query will finish running but
!not in! your lifetime".

YMMV.

[Terry Kreft]

Well, I say it's not optimised and I've seen the same statemenmt over the
years from a number of people.

I don't quite understand the relevance of the rest of your posting, seems a
bit of a religious argument to me.
--
Terry Kreft

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@216.196. 97.142...

"Terry Kreft" <te*********@mps.co.uk> wrote in
news:Xj********************@karoo.co.uk:

Personally as "not in" is not optimised in Access I would have
gone with

Who says it's not optimized? No, SHOWPLAN does not show any
optimization for it, but that's because SHOWPLAN never implemented
handling of any form of subqueries. That doesn't mean the query
optimizer is not optimizing subqueries, only that you can't find out
how using SHOWPLAN.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Terry Kreft" <te*********@mps.co.uk> wrote in
news:Kc********************@karoo.co.uk:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@216.196. 97.142...

"Terry Kreft" <te*********@mps.co.uk> wrote in
news:Xj********************@karoo.co.uk:

Personally as "not in" is not optimised in Access I would have
gone with
Who says it's not optimized? No, SHOWPLAN does not show any
optimization for it, but that's because SHOWPLAN never
implemented handling of any form of subqueries. That doesn't mean the query optimizer is not optimizing subqueries, only that you
can't find out how using SHOWPLAN.

Well, I say it's not optimised . . .

Yes, it clearly doesn't use the indexes in some cases, but it seems
to me that in others, it *does* use the indexes. I've never been
able to figure out exactly why NOT IN (and IN) seem well-optimized
in some cases and not in others.
. . . and I've seen the same statemenmt
over the years from a number of people.
What statement? That it's not optimized? That SHOWPLAN doesn't tell
you whether it is or not?
I don't quite understand the relevance of the rest of your
posting, seems a bit of a religious argument to me.

Well, so far as I can tell, you have no actual factual basis for
claiming that NOT IN is not optimized by Jet. The only possible
justification one might have for that is SHOWPLAN, and I was only
pointing out that SHOWPLAN is actually completely silent on the
issue and can't be used as evidence to suggest that NOT IN is not
optimized.

Do you have any factual basis for the absolute claim that NOT IN is
not optimized, other than the known fact that it sometimes doesn't
use available indexes?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[jimfortune]

David W. Fenton wrote:

Well, so far as I can tell, you have no actual factual basis for
claiming that NOT IN is not optimized by Jet. The only possible
justification one might have for that is SHOWPLAN, and I was only
pointing out that SHOWPLAN is actually completely silent on the
issue and can't be used as evidence to suggest that NOT IN is not
optimized.

Do you have any factual basis for the absolute claim that NOT IN is
not optimized, other than the known fact that it sometimes doesn't
use available indexes?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Terry's explanation matches what I've seen. I've tried both NOT IN and
LEFT JOIN on indexed fields and found that NOT IN can be easily a
factor of 10 or 20 times slower than the LEFT JOIN. I suspect that the
difference has little to do with indices although the lack of them
should really bring the query to a crawl when NOT IN is used. It seems
like the NOT IN syntax checks the NOT list for each record and is
especially slow on larger recordsets. I don't have any hard, cold
justification for this similar to using SHOWPLAN. My comments are
based entirely on casual empirical observation. Perhaps I am using NOT
IN incorrectly. If anyone can provide a reference to the facts I would
appreciate it. Thanks for pointing out that SHOWPLAN can exclude
subquery optimization.

James A. Fortune

[Terry Kreft]

I'll expand the first paragraph as it seems that it is not clear to you.

Well, I say it's not optimised and over the years I have seen a number of
people state that it is not optimised.

Hmmm, the SHOWPLAN part of your post I found slightly confusing as you
seemed to be saying that the lack of proof by SHOWPLAN was proof that my
statement was false, hence my comment that it seemed a religious statement.

I do remember reading an article about this quite a number of years ago but
as my memory is lousy so I cannot for the life of me point you to it. On
that basis I would have to say no I cannot present a written statement as
fact to your satisfaction, however I have had a number of experiences where
left outer join has shown a dramatic performance improvement over NOT IN.

For example, a support call (last week) from one of our implementors
involved a query he had created which used NOT IN to exclude some records.
He reported that performance was abysmal, with the query taking minutes to
run. I talked him through restructuring the query using a left outer join
and excluding null values on the RHS of the join. We confirmed the SQL and
I asked him to run the query at his leisure and ring back if there were any
problems, I had barely finished the sentence when he said he had already run
it, it had finished and he could see the results. So from minutes to
approximately a second.

--
Terry Kreft

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@216.196. 97.142...

"Terry Kreft" <te*********@mps.co.uk> wrote in
news:Kc********************@karoo.co.uk:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@216.196. 97.142...

"Terry Kreft" <te*********@mps.co.uk> wrote in
news:Xj********************@karoo.co.uk:

Personally as "not in" is not optimised in Access I would have
gone with

Who says it's not optimized? No, SHOWPLAN does not show any
optimization for it, but that's because SHOWPLAN never
implemented handling of any form of subqueries. That doesn't mean the query optimizer is not optimizing subqueries, only that you
can't find out how using SHOWPLAN.

Well, I say it's not optimised . . .

Yes, it clearly doesn't use the indexes in some cases, but it seems
to me that in others, it *does* use the indexes. I've never been
able to figure out exactly why NOT IN (and IN) seem well-optimized
in some cases and not in others.

. . . and I've seen the same statemenmt
over the years from a number of people.

What statement? That it's not optimized? That SHOWPLAN doesn't tell
you whether it is or not?

I don't quite understand the relevance of the rest of your
posting, seems a bit of a religious argument to me.

Well, so far as I can tell, you have no actual factual basis for
claiming that NOT IN is not optimized by Jet. The only possible
justification one might have for that is SHOWPLAN, and I was only
pointing out that SHOWPLAN is actually completely silent on the
issue and can't be used as evidence to suggest that NOT IN is not
optimized.

Do you have any factual basis for the absolute claim that NOT IN is
not optimized, other than the known fact that it sometimes doesn't
use available indexes?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Terry Kreft" <te*********@mps.co.uk> wrote in
news:Jm********************@karoo.co.uk:

For example, a support call (last week) from one of our
implementors involved a query he had created which used NOT IN to
exclude some records. He reported that performance was abysmal,
with the query taking minutes to run. I talked him through
restructuring the query using a left outer join and excluding null
values on the RHS of the join. We confirmed the SQL and I asked
him to run the query at his leisure and ring back if there were
any problems, I had barely finished the sentence when he said he
had already run it, it had finished and he could see the results.
So from minutes to approximately a second.

I am not disagreeing that NOT IN is not reliably optimized. I am
only disagreeing that it is *never* optimized, as I've used it in
circumstances where it caused no performance problems at all (with
reasonably large recordsets).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Jon Berraondo]

I'm not talking about optimization but about the posible bug on the jet
engine... This has brought a lot of problems to me.

Thanks again, Jon

ji********@compumarc.com wrote:

David W. Fenton wrote:

Well, so far as I can tell, you have no actual factual basis for
claiming that NOT IN is not optimized by Jet. The only possible
justification one might have for that is SHOWPLAN, and I was only
pointing out that SHOWPLAN is actually completely silent on the
issue and can't be used as evidence to suggest that NOT IN is not
optimized.

Do you have any factual basis for the absolute claim that NOT IN is
not optimized, other than the known fact that it sometimes doesn't
use available indexes?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Terry's explanation matches what I've seen. I've tried both NOT IN and
LEFT JOIN on indexed fields and found that NOT IN can be easily a
factor of 10 or 20 times slower than the LEFT JOIN. I suspect that the
difference has little to do with indices although the lack of them
should really bring the query to a crawl when NOT IN is used. It seems
like the NOT IN syntax checks the NOT list for each record and is
especially slow on larger recordsets. I don't have any hard, cold
justification for this similar to using SHOWPLAN. My comments are
based entirely on casual empirical observation. Perhaps I am using NOT
IN incorrectly. If anyone can provide a reference to the facts I would
appreciate it. Thanks for pointing out that SHOWPLAN can exclude
subquery optimization.

James A. Fortune

[Lyle Fairfield]

Tell us about "the posible bug on the jet engine", please.

[Jon Berraondo]

Lyle Fairfield wrote:

Tell us about "the posible bug on the jet engine", please.

If I'm not wrong, the SQL sentence written at the begining of this post is
correct, and it should give back the data I was looking for instead of
giving 0 rows back.

Why the second sentence (especifying <> "") works fine?

Have I correctly understood there is a problem of timeOut for the second
sentence?
If yes, I think this a bug, isn't it?

Thanks, Jon

[Lyle Fairfield]

I would not classify something as a bug unless it and everything
surrounding it had been very thorougly examined by several capable
devlopers/programmers etc.
I think JET makes few promises about how it may deal with nulls and
zero length strings and probably rightly so. And "not" can be a logical
nightmare when it is NOT handled carefully. If NIFA one is a zero
length string and NIFB two is a zero length string what does that
signify? Is NIFA one = NIFB one? In VBA the answer is Yes. But when we
are looking at the IN operator in SQL is this (the two zero length
strings) likely to indicate some relationship between the two records
which hold them. Maybe a trivial relationship, ie, we don't know what
they are, but not anything more.
I don't have any aunts. You don't have any aunts. So we're cousins,
right?

[Jon Berraondo]

Lyle Fairfield wrote:

Hi Lyle, thanks for thew answer.

I would not classify something as a bug unless it and everything
surrounding it had been very thorougly examined by several capable
devlopers/programmers etc.
Ok, but you got to start from any point...
Thats what I'm looking for: somebody able of exlpaining to me the reason for
this behaviour.... I don't like answers like "you have to manage "NOT IN"
carefully: I would prefer to know WHY my sentence didn't retrieve any row
(and it was the simplest of sentences)

I've tried it on Access 97, 2000, and 2003 with the same result.... No
matches.

SQL Server , MySql and Postgress do it correctly: they found matches: X==X,
even if there are zero lengths. You can have thousands of records with zero
lengths, but in my opinion it should give back coincidences like NIFA="Jon"
and NIFB="Jon". Shouldn't access compare every record ??
I think JET makes few promises about how it may deal with nulls and
zero length strings and probably rightly so.
I know what is Jet make to. I know it is not a professional RRBBDD system,
but I thought I could trust it during a VERY simple SQL sentence. Now I
know I can't.
And "not" can be a logical
nightmare when it is NOT handled carefully. I do NOT thing that manage " NOT IN" can be a nigthmare to ANY database
system....
If NIFA one is a zero
length string and NIFB two is a zero length string what does that
signify? Is NIFA one = NIFB one? In VBA the answer is Yes. But when we
are looking at the IN operator in SQL is this (the two zero length
strings) likely to indicate some relationship between the two records
which hold them. Maybe a trivial relationship, ie, we don't know what
they are, but not anything more. I was lloking for coincidences: what you say explains is why zero lenght
strings aren't found as coincidences, but does not explain why it doesn'
find (NiFA="jon"==NIFB="jon").
You just need two records in each table: if each one has zero lenth string
the sentence won't work.
I don't have any aunts. You don't have any aunts. So we're cousins,
right?

I'm sorry, but I don't understand this... My english is quit poor.
What that means, please?

Thanks again, Lyle.
Best regards, Jon

[Lyle Fairfield]

OK
I can understand your wanting to KNOW WHY.
When, if I get some time I'll examine this more closely and try to
identify why MS-SQL works and JET does not. And when I do I'll report
if I find anything. But don't hold your breath, please.
Forget about the Cousins thing; it was a misplaced auntalogy. No, just
forget about it.