SQL table with aliases

Question

I just found out (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 a few questions about this:

1) The explanation I understood was as follows: "The reason it is better is that instead of creating a massive list of possible results, only matching values ​​will be returned." Does this mean that although the first subquery can return 900 results, the second will only return 1 (yes or no)?

2) I used to have a RDBMS complaint: “only the first 1000 rows can be extracted”, would this second approach solve this problem?

3) What is the size of the alias in the second subquery? ... does the alias only work in brackets?

eg

 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' )      

, (o othertable) "" , ? ?

-, Oracle, ?

Answer 1

. IN .

, ()

, :

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

, :

Answer 2

, " ". , "".

IN , , OtherTable NameID (, , OtherDesc ), , OtherTable). , . , - . SQL , , , , , () , , ( ) SQL, - .

Q1 " ", (NameID ; OtherDesc OtherTable), , .

Q2 , , . EXISTS, .

Q3 EXISTS: "t , o ". - , ( , ), "t table, , " o ", ". , , OtherDesc NameID OtherTable; OtherTable NameID OtherDesc NameID.

Answer 3

• Oracle: IN, , , . EXISTS where, , , , . . " IN EXISTS " .
• .
• , , . , , ANDed SQL. UNION AND?

Answer 4

, .

SELECT t.*
FROM yourTable t
    INNER JOIN otherTable ot
        ON (t.nameid = ot.nameid AND ot.otherdesc = 'SomeDesc')

Answer 5

, EXISTS , IN. , , SQL IN EXISTS... , IN EXISTS , ...

IN, , NULL ( NULL , IN NULLS )... EXISTS NULL , .

, NULLS ( ), EXIST , , , . , EXISTS, , , IN.. ...

But IN and EXISTS are not interchangeable ...