Thursday, September 4, 2008

IN vs Exists

Both are used as a filter for searching. IN clause wants the inner query to drive the outer query. EXISTS clause wants the outer query to be run first, using each value to fetch a value from the inner query. in clause is better if the results of the sub query are very small.exists forces to specify join condition, which invokes INDEX SCAN. so its faster than in clause.
- IN = inside to outside
- EXISTS = outside to inside

EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.

No comments: