scope of alias in subquery

[Jane Somerfield]

Can I do this?

select e.NAME, SALARY from EMP where SALARY = (select max(SALARY) from EMP e);

Does the scope of the alias "e" in the subquery matter?

[Maximilian Xavier Stocker]

Originally posted by Jane Somerfield:
Can I do this?

select e.NAME, SALARY from EMP where SALARY = (select max(SALARY) from EMP e);

Does the scope of the alias "e" in the subquery matter?

I am confused. Why the need for the alias at all in that query?

[Jane Somerfield]

The example above may not be very good.

Please see a better one below:

Select n.full_name, a.street, a.city, a.zip, a.state From address a
Where a.id = (Select n.id from name n Where n.full_name Like 'John%');

This query is wrong because the alias "n" is out of acope. How should
I use one query to list the full name and address?

[Jeanne Boyarsky]

Jane,
The reason that SQL won't work is that there isn't necessarily only one row that matches. What if two rows had the same id and a different name containing John?

Instead, you can use a regular join:

select n.full_name, a.street, a.city, a.zip, a.state 
from address a, name n
where a.id = n.id
  and n.full_name Like 'John%'