If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
'NOT IN' vs ' ALL'
greetings to all
An SQL fragment I use is... WHERE tblValues NOT IN (SELECT ... The query runs properly. I have noticed that I can get the very same output by using the fragment... WHERE tblValues ALL (SELECT ... Is 'NOT IN' the same as ' ALL'? Is there a preference? thanks in advance Sarah |
#2
|
|||
|
|||
'NOT IN' vs ' ALL'
Sarah wrote:
An SQL fragment I use is... WHERE tblValues NOT IN (SELECT ... The query runs properly. I have noticed that I can get the very same output by using the fragment... WHERE tblValues ALL (SELECT ... Is 'NOT IN' the same as ' ALL'? Is there a preference? Sure seems that way, but I have not explored the case when the rblValues field contains Null. I think a better way would be to use an outer join that also appears to be equivalent to those. If the tblValues and valuesfield fields are indexed, this approach should be dramtically faster for even slightly large tables: FROM table LEFT JOIN othertable ON table.tblValues = othertable.valuesfield WHERE othertable.valuesfield Is Null -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
'NOT IN' vs ' ALL'
Sarah:
As Marshall says, a join will generally perform best, but if using a subquery the NOT IN predicate (but not the IN predicate) will only work if none of the rows returned by the subquery has a NULL at the column position in question. This is because a NOT IN(x,y, NULL, z) is the equivalent of: a x AND a y AND a NULL AND a z so it evaluates to NULL, neither TRUE nor FALSE The same is true of ALL Using the NOT EXISTS predicate will get round this: SELECT SomeTable.* WHERE NOT EXISTS (SELECT * FROM SomeOtherTable WHERE SomeOtherTable.SomeColumn = SomeTable.SomeColumn); Performance is also generally better with the EXISTS or NOT EXISTS predicate than with IN or NOT IN. Ken Sheridan Stafford, England Sarah wrote: greetings to all An SQL fragment I use is... WHERE tblValues NOT IN (SELECT ... The query runs properly. I have noticed that I can get the very same output by using the fragment... WHERE tblValues ALL (SELECT ... Is 'NOT IN' the same as ' ALL'? Is there a preference? thanks in advance Sarah -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 |
#4
|
|||
|
|||
'NOT IN' vs ' ALL'
Thank to Ken and Marshall for great advice.
Sarah "KenSheridan via AccessMonster.com" wrote: Sarah: As Marshall says, a join will generally perform best, but if using a subquery the NOT IN predicate (but not the IN predicate) will only work if none of the rows returned by the subquery has a NULL at the column position in question. This is because a NOT IN(x,y, NULL, z) is the equivalent of: a x AND a y AND a NULL AND a z so it evaluates to NULL, neither TRUE nor FALSE The same is true of ALL Using the NOT EXISTS predicate will get round this: SELECT SomeTable.* WHERE NOT EXISTS (SELECT * FROM SomeOtherTable WHERE SomeOtherTable.SomeColumn = SomeTable.SomeColumn); Performance is also generally better with the EXISTS or NOT EXISTS predicate than with IN or NOT IN. Ken Sheridan Stafford, England Sarah wrote: greetings to all An SQL fragment I use is... WHERE tblValues NOT IN (SELECT ... The query runs properly. I have noticed that I can get the very same output by using the fragment... WHERE tblValues ALL (SELECT ... Is 'NOT IN' the same as ' ALL'? Is there a preference? thanks in advance Sarah -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 . |
#5
|
|||
|
|||
'NOT IN' vs ' ALL'
Marshall - this works great but it took me a while before the light bulb came
on. I guess the idea is that after the LEFT JOIN, the non-NULL field values in the right table are in exactly the records we want to exclude. Clever! I haven't used OUTER JOINS that way before. Thanks - Sarah "Marshall Barton" wrote: Sarah wrote: An SQL fragment I use is... WHERE tblValues NOT IN (SELECT ... The query runs properly. I have noticed that I can get the very same output by using the fragment... WHERE tblValues ALL (SELECT ... Is 'NOT IN' the same as ' ALL'? Is there a preference? Sure seems that way, but I have not explored the case when the rblValues field contains Null. I think a better way would be to use an outer join that also appears to be equivalent to those. If the tblValues and valuesfield fields are indexed, this approach should be dramtically faster for even slightly large tables: FROM table LEFT JOIN othertable ON table.tblValues = othertable.valuesfield WHERE othertable.valuesfield Is Null -- Marsh MVP [MS Access] . |
Thread Tools | |
Display Modes | |
|
|