Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Difference Between Exists And IN
- Thread starter Flo79
- Start date
IN" tests that a specified field value is "IN" (or NOT IN) a list of values. For example
[blue][tt]
WHERE fld IN (2, 5, 7, 9)
[/tt][/blue]
"EXISTS" returns a TRUE or FALSE result if a specified subquery returns any records. For example
[blue][tt]
WHERE EXISTS (Select 1 From tbl Where fld < 99)
[/tt][/blue]
Note that nothing from the subquery is returned to the main query other than the fact that it does (TRUE) or does not (FALSE) contain at least one record.
[blue][tt]
WHERE fld IN (2, 5, 7, 9)
[/tt][/blue]
"EXISTS" returns a TRUE or FALSE result if a specified subquery returns any records. For example
[blue][tt]
WHERE EXISTS (Select 1 From tbl Where fld < 99)
[/tt][/blue]
Note that nothing from the subquery is returned to the main query other than the fact that it does (TRUE) or does not (FALSE) contain at least one record.
Leslie
I'm becoming that predictable? Gotta do something about that.
While there are some instances where you could use one or the other, the fundamental difference is that "IN" tests a field in the current record for the existence of certain specified values. "EXISTS" tests a completely separate SQL statement for record existence. There is no requirement that an EXISTS subquery needs to be on the same table or tables as the main query. For example
[blue][tt]
Select fld1, fld2, ...
From tblA A
Where EXISTS
(Select * From tblB B
WHERE B.fld7 < A.fld9 )
[/tt][/blue]
Is perfectly legitimate use of "EXISTS" but could not be implemented as an "IN" clause.
In those instances where either "IN" or "EXISTS" could be used, you probably want to use "IN" because it doesn't involve running a separate subquery and (depending on the DBMS) the query optimizer can often build a temporary index on the elements of an IN-list to improve execution performance.
I'm becoming that predictable? Gotta do something about that.
While there are some instances where you could use one or the other, the fundamental difference is that "IN" tests a field in the current record for the existence of certain specified values. "EXISTS" tests a completely separate SQL statement for record existence. There is no requirement that an EXISTS subquery needs to be on the same table or tables as the main query. For example
[blue][tt]
Select fld1, fld2, ...
From tblA A
Where EXISTS
(Select * From tblB B
WHERE B.fld7 < A.fld9 )
[/tt][/blue]
Is perfectly legitimate use of "EXISTS" but could not be implemented as an "IN" clause.
In those instances where either "IN" or "EXISTS" could be used, you probably want to use "IN" because it doesn't involve running a separate subquery and (depending on the DBMS) the query optimizer can often build a temporary index on the elements of an IN-list to improve execution performance.
I have not tested the differences in Access, although I use Access frequently with small applications and a few Access Projects connected to SQL Server. Right now, I am trying to Optimize some queries going from Crystal Reports to an Oracle datawarehouse. Here are some differences I found with the version of Oracle I am working with.
Whether the field in the Exists or In is indexed can affect performance.
Whether it is a correlated subquery or not.
Whether there is an index on the field in the subquery.
Joins can many times replace the subquery.
Oracle will convert IN subqueries to joins many times.
An Exists executes the subquery for each row in the outer query while an IN executes the subquery once.
The Exists applies the outer query to the subquery while the IN applies the inner query to the outer query.
Also, adding NOT changes the rules. It is usually acceptable to use NOT with Exists but the NOT IN is usually very inefficient.
As you can see from this partial illustration there are many factors that can affect the decision of which is more efficient. I don't know if any of this applies to Access, but you should do some reading and testing.
Whether the field in the Exists or In is indexed can affect performance.
Whether it is a correlated subquery or not.
Whether there is an index on the field in the subquery.
Joins can many times replace the subquery.
Oracle will convert IN subqueries to joins many times.
An Exists executes the subquery for each row in the outer query while an IN executes the subquery once.
The Exists applies the outer query to the subquery while the IN applies the inner query to the outer query.
Also, adding NOT changes the rules. It is usually acceptable to use NOT with Exists but the NOT IN is usually very inefficient.
As you can see from this partial illustration there are many factors that can affect the decision of which is more efficient. I don't know if any of this applies to Access, but you should do some reading and testing.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
