Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!pd7cy2so!shaw.ca!sjc70.webusenet.com!news.usenetserver.com!sn-xit-02!sn-xit-01!sn-post-02!sn-post-01!supernews.com!corp.supernews.com!not-for-mail From: Daniel Morgan Newsgroups: comp.databases.oracle.misc Subject: Re: SQL question =?ISO-8859-1?Q?=BFwhy_using_clause_=27ALL?= =?ISO-8859-1?Q?=27=3F?= Date: Mon, 02 Feb 2004 15:28:58 -0800 Organization: ATS Message-ID: <1075764482.779925@yasure> User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit Cache-Post-Path: yasure!unknown@oracle.advtechserv.com X-Cache: nntpcache 2.4.0b5 (see http://www.nntpcache.org/) X-Complaints-To: abuse@supernews.com Lines: 58 Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:135263 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@x.washington.edu (replace 'x' with a 'u' to reply)