| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: correlated subquery
Translated into words, you correlation example is:
List an employee if their salary
is above average ___for their department__.
Query (a) is: the the average salary in the company Query (b) is: every earning more than the company average
-- Jonathan Lewis Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk Practical Oracle 8i: Building Efficient Databases Publishers: Addison-Wesley More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html m.mueller wrote in message <3A77208B.9DEF2BC9_at_snafu.de>...Received on Tue Jan 30 2001 - 14:20:49 CST
>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
 |
 |