correlated subquery

From: m.mueller <m.mueller_at_snafu.de>
Date: Tue, 30 Jan 2001 21:14:03 +0100
Message-ID: <3A77208B.9DEF2BC9@snafu.de>

Hello,
I'm trying to understand correlated subquery, got result that puzzles me.
Can't figure out the different results from the following queries, typed the following original example on correlated subqueries from sql-Reference
SELECT deptno, ename, sal
  FROM emp emp_alias1
 WHERE sal >

           (SELECT avg(sal)
              FROM emp emp_alias2
             WHERE emp_alias1.deptno = emp_alias2.deptno
           )

ORDER BY deptno;

output:

    DEPTNO ENAME SAL
---------- ---------- ----------

        10 KING             5000
        20 JONES            2975
        20 SCOTT            3000
        20 FORD             3000
        30 ALLEN            1600 <- sal < avg(sal), why is this row
selected?
        30 BLAKE            2850

 6 rows selected.

tried the same thing as simple subquery, made it stepwise a) only subquery
SELECT avg(sal)
  FROM emp;

output
  AVG(SAL)

---

2073.21429

b) whole query
SELECT deptno, ename, sal
  FROM emp
 WHERE sal >

       (SELECT avg(sal)
          FROM emp
       )

ORDER BY deptno;

output:

    DEPTNO ENAME SAL
---------- ---------- ----------

        10 CLARK            2450 <- missing in correlated subquery
        10 KING             5000
        20 JONES            2975
        20 FORD             3000
        20 SCOTT            3000
        30 BLAKE            2850 

6 rows selected.

Any suggestions are welcome,
thanks a lot and have a nice day
... Manuela Received on Tue Jan 30 2001 - 14:14:03 CST