I also read a good explaination on the following site. Noting this is an Oracle site I believe logically teradata adhears to this method however functionally it will "create joins" like dieter said:
You Asked Tom:
can you give me some example at which situation
IN is better than exist, and vice versa.
and we said...
Well, the two are processed very very differently.
Select * from T1 where x in ( select y from T2 )
is typically processed as:
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;
The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to
the original table -- typically.
As opposed to
select * from t1 where exists ( select null from t2 where y = x )
That is processed more like:
for x in ( select * from t1 )
if ( exists ( select null from t2 where y = x.x )
OUTPUT THE RECORD
It always results in a full scan of T1 whereas the first query can make use of an index
So, when is where exists appropriate and in appropriate?
Lets say the result of the subquery
( select y from T2 )
is "huge" and takes a long time. But the table T1 is relatively small and executing (
select null from t2 where y = x.x ) is very very fast (nice index on t2(y)). Then the
exists will be faster as the time to full scan T1 and do the index probe into T2 could be
less then the time to simply full scan T2 to build the subquery we need to distinct on.
Lets say the result of the subquery is small -- then IN is typicaly more appropriate.
If both the subquery and the outer table are huge -- either might work as well as the
other -- depends on the indexes and other factors.
Can you please elaborate on
"And NOT EXISTS is always more efficient than an Outer Join solution filtering for NULLs.
Yes, typically In ,Exists and Join would give similar results. However as dnoeth mentioned in case join is creating dups .Eilimination in join has to be done exclusively ;In which de dup logic will be applicable for more columns and possibly more rows making it more expensive.
(Unless you write a sub query and use in join )