Outer reference in SELECT clause of subquery 
Author Message
 Outer reference in SELECT clause of subquery

Oracle permits the SELECT clause of a subquery to reference a column which
is an outer reference (ie correlated).  This is illegal according to the
1989 SQL standard 5.24: Let R denote the result of the <table expression>.
Each <column specification> in the <value expression> shall unambiguously
reference a column of R).

For (a stupid) example:

        SELECT * FROM EMPS T WHERE AGE IN (SELECT T.AGE FROM EMPS);

Has anyone used this non-standard feature in real life?

More to the point, why did the standard want to ban it?  It is stupid to
select an outer reference on its own because it is a constant during the
evaluation of the subquery, but it is conceivably useful in a value
expression.

- Roger

--
******************************************************************
* Roger MacNicol, Snr. Software Engineer
* VMark Software, Inc., 30 Speen Street, Framingham, MA 01701-1800



Sun, 16 Apr 1995 03:56:35 GMT
 Outer reference in SELECT clause of subquery

Quote:

> Oracle permits the SELECT clause of a subquery to reference a column which
> is an outer reference (ie correlated).  This is illegal according to the
> 1989 SQL standard 5.24: Let R denote the result of the <table expression>.
> Each <column specification> in the <value expression> shall unambiguously
> reference a column of R).

> For (a stupid) example:

>    SELECT * FROM EMPS T WHERE AGE IN (SELECT T.AGE FROM EMPS);

> Has anyone used this non-standard feature in real life?

 Not this exact feature but the same general concept in Rdb.

     SELECT * FROM EMPS T WHERE AGE IN (SELECT MAX(AGE) FROM EMPS);

      (I believe I got it right..)

--
Mike Mattix
Agricultural Group of Monsanto
P.O. Box 174
Luling, LA 70070



Tue, 18 Apr 1995 18:47:24 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. ANSI syntax for a correlated subquery in the SELECT clause

2. Subquery on SELECT clause

3. Using Union subquery in select clause

4. Subqueries in select clause

5. Combination of LEFT OUTER JOIN, sub-select and OR in WHERE clause fails

6. results from SELECT clause used in an IN clause

7. Is it possible to use a SELECT clause in a FROM clause

8. Query throws access violation (right outer join with subquery)

9. problem with WHERE on subquery's field in outer query

10. help recursive left outer join (subquery?)

11. SubQuery & Outer Query

12. Subqueries and Outer Joins (This is very bizarre...)


 
Powered by phpBB® Forum Software