Not In (Select...) and Outer Join yields diff. result

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to update my table of items and I want to update it with new items.
The table STAGEItems holds all items - new and old. To find the new ones I
have used this query which doesn't work any more:


SELECT STAGEItems.ItemNo, STAGEItems.CostPrice
FROM STAGEItems
WHERE STAGEItems.ItemNo Not In(Select ItemNo from tblItems);

Result: 0 rows !
So, I had to rewrite the query to use an outer join between the tables and
the criteria Is Null to find the new recs.

When has the Not In (Select...) become unreliable?
I'm running Windows XP Pro SP2 Build 2600 and Access 10.2627.2625
 
Hi Ruben

If the tblItems has some ItemNo value to null, the "not in" doesn't return
records.
That is a correct result in accord with Boolean Algebra.

To retrieve the records, You can insert a filter for null values
 
Thanhx Giorgio
That was exactly the problem. I need to put a constraint (Required) on that
field - a user had edited the table directly, I think.
Btw: Do you think this performs even better:
.... Where Not Exists (Select * from tblItems Where tblItems.ItemNo =
StageItems.ItemNo) ?
/regards
--
Ruben Lysemose, Systems Consultant


giorgio rancati said:
Hi Ruben

If the tblItems has some ItemNo value to null, the "not in" doesn't return
records.
That is a correct result in accord with Boolean Algebra.

To retrieve the records, You can insert a filter for null values
----
SELECT STAGEItems.ItemNo, STAGEItems.CostPrice
FROM STAGEItems
WHERE STAGEItems.ItemNo
Not In(Select ItemNo from tblItems Where tblItems.ItemNo Is Not Null);
----

However the outer left join performance is better than not in

Bye
--
Giorgio Rancati
[Office Access MVP]

Ruben L said:
I want to update my table of items and I want to update it with new items.
The table STAGEItems holds all items - new and old. To find the new ones I
have used this query which doesn't work any more:


SELECT STAGEItems.ItemNo, STAGEItems.CostPrice
FROM STAGEItems
WHERE STAGEItems.ItemNo Not In(Select ItemNo from tblItems);

Result: 0 rows !
So, I had to rewrite the query to use an outer join between the tables and
the criteria Is Null to find the new recs.

When has the Not In (Select...) become unreliable?
I'm running Windows XP Pro SP2 Build 2600 and Access 10.2627.2625
Click to expand...
Click to expand...
 
Pardon me, but I think that would be the slowest option.

The db engine will have to run that subquery ONCE for every record in
StageItems.

The most efficient is probably using the Left Outer Join as mentioned by
Giorgio Rancati in his reply. That query would look like:

SELECT STAGEItems.ItemNo, STAGEItems.CostPrice
FROM STAGEItems LEFT JOIN TblItems
ON STAGEItems.ItemNo = tblItems.ItemNo
WHERE tblItems.ItemNo is Null;


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ruben L said:
Thanhx Giorgio
That was exactly the problem. I need to put a constraint (Required) on
that
field - a user had edited the table directly, I think.
Btw: Do you think this performs even better:
... Where Not Exists (Select * from tblItems Where tblItems.ItemNo =
StageItems.ItemNo) ?
/regards
--
Ruben Lysemose, Systems Consultant


giorgio rancati said:
Hi Ruben

If the tblItems has some ItemNo value to null, the "not in" doesn't
return
records.
That is a correct result in accord with Boolean Algebra.

To retrieve the records, You can insert a filter for null values
----
SELECT STAGEItems.ItemNo, STAGEItems.CostPrice
FROM STAGEItems
WHERE STAGEItems.ItemNo
Not In(Select ItemNo from tblItems Where tblItems.ItemNo Is Not Null);
----

However the outer left join performance is better than not in

Bye
--
Giorgio Rancati
[Office Access MVP]

Ruben L said:
I want to update my table of items and I want to update it with new
items.
The table STAGEItems holds all items - new and old. To find the new
ones I
have used this query which doesn't work any more:


SELECT STAGEItems.ItemNo, STAGEItems.CostPrice
FROM STAGEItems
WHERE STAGEItems.ItemNo Not In(Select ItemNo from tblItems);

Result: 0 rows !
So, I had to rewrite the query to use an outer join between the tables
and
the criteria Is Null to find the new recs.

When has the Not In (Select...) become unreliable?
I'm running Windows XP Pro SP2 Build 2600 and Access 10.2627.2625
Click to expand...
Click to expand...
Click to expand...
 
Hi Ruben

If tblItems has an index on ItemNo field, the "not exists" perform is good.
The "left outer join" perform is however good, with or without index on
tblItems.ItemNo field.

bye
--
Giorgio Rancati
[Office Access MVP]

Ruben L said:
Thanhx Giorgio
That was exactly the problem. I need to put a constraint (Required) on that
field - a user had edited the table directly, I think.
Btw: Do you think this performs even better:
... Where Not Exists (Select * from tblItems Where tblItems.ItemNo =
StageItems.ItemNo) ?
/regards
--
Ruben Lysemose, Systems Consultant


giorgio rancati said:
Hi Ruben

If the tblItems has some ItemNo value to null, the "not in" doesn't return
records.
That is a correct result in accord with Boolean Algebra.

To retrieve the records, You can insert a filter for null values
----
SELECT STAGEItems.ItemNo, STAGEItems.CostPrice
FROM STAGEItems
WHERE STAGEItems.ItemNo
Not In(Select ItemNo from tblItems Where tblItems.ItemNo Is Not Null);
----

However the outer left join performance is better than not in

Bye
--
Giorgio Rancati
[Office Access MVP]

Ruben L said:
I want to update my table of items and I want to update it with new items.
The table STAGEItems holds all items - new and old. To find the new ones I
have used this query which doesn't work any more:


SELECT STAGEItems.ItemNo, STAGEItems.CostPrice
FROM STAGEItems
WHERE STAGEItems.ItemNo Not In(Select ItemNo from tblItems);

Result: 0 rows !
So, I had to rewrite the query to use an outer join between the tables and
the criteria Is Null to find the new recs.

When has the Not In (Select...) become unreliable?
I'm running Windows XP Pro SP2 Build 2600 and Access 10.2627.2625
Click to expand...
Click to expand...
Click to expand...
 
Thanks John
:-)
--
Giorgio Rancati
[Office Access MVP]

John Spencer said:
Pardon me, but I think that would be the slowest option.

The db engine will have to run that subquery ONCE for every record in
StageItems.

The most efficient is probably using the Left Outer Join as mentioned by
Giorgio Rancati in his reply. That query would look like:

SELECT STAGEItems.ItemNo, STAGEItems.CostPrice
FROM STAGEItems LEFT JOIN TblItems
ON STAGEItems.ItemNo = tblItems.ItemNo
WHERE tblItems.ItemNo is Null;


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Ruben L said:
Thanhx Giorgio
That was exactly the problem. I need to put a constraint (Required) on
that
field - a user had edited the table directly, I think.
Btw: Do you think this performs even better:
... Where Not Exists (Select * from tblItems Where tblItems.ItemNo =
StageItems.ItemNo) ?
/regards
--
Ruben Lysemose, Systems Consultant


giorgio rancati said:
Hi Ruben

If the tblItems has some ItemNo value to null, the "not in" doesn't
return
records.
That is a correct result in accord with Boolean Algebra.

To retrieve the records, You can insert a filter for null values
----
SELECT STAGEItems.ItemNo, STAGEItems.CostPrice
FROM STAGEItems
WHERE STAGEItems.ItemNo
Not In(Select ItemNo from tblItems Where tblItems.ItemNo Is Not Null);
----

However the outer left join performance is better than not in

Bye
--
Giorgio Rancati
[Office Access MVP]

"Ruben L" <[email protected]> ha scritto nel messaggio
I want to update my table of items and I want to update it with new
items.
The table STAGEItems holds all items - new and old. To find the new
ones I
have used this query which doesn't work any more:


SELECT STAGEItems.ItemNo, STAGEItems.CostPrice
FROM STAGEItems
WHERE STAGEItems.ItemNo Not In(Select ItemNo from tblItems);

Result: 0 rows !
So, I had to rewrite the query to use an outer join between the tables
and
the criteria Is Null to find the new recs.

When has the Not In (Select...) become unreliable?
I'm running Windows XP Pro SP2 Build 2600 and Access 10.2627.2625
Click to expand...
Click to expand...
Click to expand...
 
- but the Not In is even slower, right?
--
Ruben Lysemose, Systems Consultant


John Spencer said:
Pardon me, but I think that would be the slowest option.

The db engine will have to run that subquery ONCE for every record in
StageItems.

The most efficient is probably using the Left Outer Join as mentioned by
Giorgio Rancati in his reply. That query would look like:

SELECT STAGEItems.ItemNo, STAGEItems.CostPrice
FROM STAGEItems LEFT JOIN TblItems
ON STAGEItems.ItemNo = tblItems.ItemNo
WHERE tblItems.ItemNo is Null;


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ruben L said:
Thanhx Giorgio
That was exactly the problem. I need to put a constraint (Required) on
that
field - a user had edited the table directly, I think.
Btw: Do you think this performs even better:
... Where Not Exists (Select * from tblItems Where tblItems.ItemNo =
StageItems.ItemNo) ?
/regards
--
Ruben Lysemose, Systems Consultant


giorgio rancati said:
Hi Ruben

If the tblItems has some ItemNo value to null, the "not in" doesn't
return
records.
That is a correct result in accord with Boolean Algebra.

To retrieve the records, You can insert a filter for null values
----
SELECT STAGEItems.ItemNo, STAGEItems.CostPrice
FROM STAGEItems
WHERE STAGEItems.ItemNo
Not In(Select ItemNo from tblItems Where tblItems.ItemNo Is Not Null);
----

However the outer left join performance is better than not in

Bye
--
Giorgio Rancati
[Office Access MVP]

"Ruben L" <[email protected]> ha scritto nel messaggio
I want to update my table of items and I want to update it with new
items.
The table STAGEItems holds all items - new and old. To find the new
ones I
have used this query which doesn't work any more:


SELECT STAGEItems.ItemNo, STAGEItems.CostPrice
FROM STAGEItems
WHERE STAGEItems.ItemNo Not In(Select ItemNo from tblItems);

Result: 0 rows !
So, I had to rewrite the query to use an outer join between the tables
and
the criteria Is Null to find the new recs.

When has the Not In (Select...) become unreliable?
I'm running Windows XP Pro SP2 Build 2600 and Access 10.2627.2625
Click to expand...
Click to expand...
Click to expand...
 
yes, it is

you can test the perform difference in large tables with 5.000 ~100.000
records.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Ambiguous outer join 6
Relationship - Outer Join - Microsoft Access 2002 1
Union query or Outer Joins do not create desired results 3
Outer Join? 3
Left Outer Join Producing Incorrect Results 3
A bug in GUI of creating query with more outer joins between two tables 5
OUTER JOIN Trouble 3
outer join issues 1

Back
Top