Re: SQL question ¿why using clause 'ALL'?

schumacker wrote:

> Hi everyone.
> I am learning Oracle and SQL. I have a little question about
> subqueries and
> grouping.
> Look at this two SQL sentences:
>
> SQL> SELECT ename, job, sal
> 2 FROM emp
> 3 WHERE sal > ALL
> 4 (SELECT sal
> 5 FROM emp
> 6 WHERE job = 'CLERK')
> 7 ORDER BY sal DESC;
>
>
>
> SQL> SELECT ename, job, sal
> 2 FROM emp
> 3 WHERE sal >
> 4 (SELECT MAX(sal)
> 5 FROM emp
> 6 WHERE job = 'CLERK')
> 7 ORDER BY sal DESC;
>
> As you will see, this two sentences look for those employees in the
> "emp" table whose salary ("sal") is higher than the salary of all of
> the clerks.
> These sentences are different, but they do the same work (that´s what
> i think).
> What i would like to know if this last phrase is correct, and, if yes,
> which
> one of the two sentences is better.
>
> Thank you very much

Don't have time right this second to deal with the statements themselves ... but questions such as "Which is better" almost always lead to someone taking the answer as gospel and applying it to cases where it isn't applicable.

So rather than giving you the answer I would suggest that you learn to use EXPLAIN PLAN and how to evaluate DML statements yourself.

You will find lots of information on EXPAIN PLAN at http://tahiti.oracle.com and additional information an a fascinating demo at: http://www.psoug.org/reference/explain_plan.html.

HTH

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)