Re: correlated subquery

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 30 Jan 2001 21:23:36 +0100
Message-ID: <t7e8igg3mderee@beta-news.demon.nl>

"m.mueller" <m.mueller_at_snafu.de> wrote in message news:3A77208B.9DEF2BC9_at_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

The queries are retrieving three different things

1 employees with a salary higher than the average *in their department* This query will show what I am trying to demonstrate select d.deptno, ename, sal, d.avgsal
from
(
SELECT deptno, ename, sal
FROM emp emp_alias1
WHERE sal >

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

) e,
(select deptno, avg(sal) avgsal
 from emp
 group by deptno) d
where d.deptno = e.deptno
 ORDER BY deptno
/
It will show you your query combined with the average salary per department. As you see the result of your query is correct with respect to what it does.

2 the average salary for all employees
You should replace this one by
select deptno, avg(sal)
from emp
group by deptno
3 employees with a salary higher than the average salary in the company.

Hth,

Sybrand Bakker, Oracle DBA Received on Tue Jan 30 2001 - 14:23:36 CST