The Exists keyword evaluates true or false, but the IN keyword will compare all values in the corresponding subquery column. If you are using the IN operator, the SQL engine will scan all records fetched from the inner query. On the other hand, if we are using EXISTS, the SQL engine will stop the scanning process as soon as it found a match.
The EXISTS subquery is used when we want to display all rows where we have a matching column in both tables. In most cases, this type of subquery can be re-written with a standard join to improve performance.
select
book_key
from
book
where
exists (select book_key from sales);
The EXISTS clause is much faster than IN when the subquery results is very large. Conversely, the IN clause is faster than EXISTS when the subquery results is very small.
Also, the IN clause can't compare anything with NULL values, but the EXISTS clause can compare everything with NULLs.
Source : http://www.dba-oracle.com/t_exists_clause_vs_in_clause.htm
The EXISTS subquery is used when we want to display all rows where we have a matching column in both tables. In most cases, this type of subquery can be re-written with a standard join to improve performance.
select
book_key
from
book
where
exists (select book_key from sales);
The EXISTS clause is much faster than IN when the subquery results is very large. Conversely, the IN clause is faster than EXISTS when the subquery results is very small.
Also, the IN clause can't compare anything with NULL values, but the EXISTS clause can compare everything with NULLs.
Source : http://www.dba-oracle.com/t_exists_clause_vs_in_clause.htm
No comments:
Post a Comment