SQL - table alias scope

Question

I've just learned ( yesterday ) to use "exists" instead of "in".

 BAD
 select * from table where nameid in ( 
          select nameid from othertable where otherdesc =  'SomeDesc' )      
 GOOD
 select * from table t where exists ( 
          select nameid from othertable o where t.nameid = o.nameid and otherdesc =  'SomeDesc' )      

And I have some questions about this:

1) The explanation as I understood was: "The reason why this is better is because only the matching values will be returned instead of building a massive list of possible results". Does that mean that while the first subquery might return 900 results the second will return only 1 ( yes or no )?

2) In the past I have had the RDBMS complainin: "only the first 1000 rows might be retrieved", this second approach would solve that problem?

3) What is the scope of the alias in the second subquery?... does the alias only lives in the parenthesis?

for example

 select * from table t where exists ( 
          select nameid from othertable o where t.nameid = o.nameid and otherdesc =  'SomeDesc' )      
 AND 
          select nameid from othertable o where t.nameid = o.nameid and otherdesc =  'SomeOtherDesc' )      

That is, if I use the same alias ( o for table othertable ) In the second "exist" will it present any problem with the first exists? or are they totally independent?

Is this something Oracle only related or it is valid for most RDBMS?

Thanks a lot

Answer 1

It's specific to each DBMS and depends on the query optimizer. Some optimizers detect IN clause and translate it.

In all DBMSes I tested, alias is only valid inside the ( )

BTW, you can rewrite the query as:

select t.* 
from table t 
join othertable o on t.nameid = o.nameid 
    and o.otherdesc in ('SomeDesc','SomeOtherDesc');

And, to answer your questions:

1. Yes
2. Yes
3. Yes