Re: correlated subquery

From: Holger Kreft <Holger.Kreft_at_sutter.de>
Date: Wed, 31 Jan 2001 13:33:47 +0100
Message-ID: <3a7806ab$1@news.ic-marl.de>

"m.mueller" <m.mueller_at_snafu.de> schrieb im Newsbeitrag news:3A77208B.9DEF2BC9_at_snafu.de...
> Hello,
> I'm trying to understand correlated subquery, got result that puzzles
> me.

Hi Manuela,

I am not sure to understand your question as you mean it. Did you notice, that the first query lists all employes with a salary higher than the average salary of the department (of the employes) while the last query lists all employes with a salary higher than the avergabe of all employes?

> 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?

The average salary of department 30 seems to be lower than 1600.

> SELECT avg(sal)
> FROM emp;

>

> output
> AVG(SAL)
> ----------
> 2073.21429

This is the average salary of all employes in all departments.

> SELECT deptno, ename, sal
> FROM emp
> WHERE sal >
> (SELECT avg(sal)
> FROM emp

In the first query there is a where-clause at this place!

> )
> ORDER BY deptno;

Hope to have helped you...

Holger Kreft Received on Wed Jan 31 2001 - 06:33:47 CST