Re: Does a sub-query require an alias ?

> Hello,
>
> As the subject says, do I have to have an alias for the table in
> all the sub-queries that are being used ?
> Say I have a query
>
> select * from table1 a
> where (a.col1, a.col2) in
> (select col4, col5
> from table2)
>
>
> Field names col4 and col5 in the select statement of the subquery
> do not exist in table2 but exist in table1, the query works
> without any error message whatsoever. Is this documented somewhere ?

Yup, you have to use an alias. However, if memory serves correctly, the syntactic binds occur in the proper nesting order during the parse; thus, if you have:

SELECT empno, ename
FROM e1.emp
WHERE (empno, ename) IN
(SELECT empno, ename FROM e2.emp);

the empno and ename in the subquery refer to the e2 table, not the e1 table.

This is documented. In V.6, it is on page 6-19 of the SQL language manual, under Correlated Query Usage Notes. I would assume that it continues to be documented in V.7

Welcome to the world of declarative languages ...

Mark

--

==========================================+===================================
Mark Wick                                 | VOICE:           617-494-0990
Senior Software Engineer                  |                  617-494-5449 x171
Oberon Software, Inc.                     | FAX:             617-494-0414
One Cambridge Center, Cambridge, MA 02142 | INTERNET:        mwick_at_oberon.com
------------------------------------------+-----------------------------------
  -- I understand about indecision, I don't care if I get behind
     People living in competition - all I want is to have my peace of mind.
==========================================+===================================