Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re[2]: HI

RE: Re[2]: HI

From: Lex de Haan <>
Date: Mon, 20 Sep 2004 18:03:30 +0200
Message-ID: <>

just to add my two cents, without the intention to open that discussion again ;-)
if you have a SQL statement containing WHERE, GROUP BY, and HAVING clauses, it would theoretically still be possible to do things in any order, I guess, but I am 99% sure that any optimizer would first evaluate the WHERE clause before the GROUP BY. and obviously, the HAVING must be processed after the GROUP BY. By the way, I think we should get rid of the GROUP BY alltogether -- SQL has much more powerful and elegant constructs to achieve the same results. think about correlated subqueries in the SELECT clause. But that might open up the discussion again about whether subquery merging is acceptable under all circumstances ... and I didn't want to go there ;-)

additions/corrections welcome,

Kind regards,


skype me <callto://lexdehaan>

-----Original Message-----
[]On Behalf Of Jonathan Gennick Sent: Monday, September 20, 2004 16:37
To: Jared Still
Subject: Re[2]: HI

> Jonathan Gennick where are you? - care to comment?

LOL! Well, I certainly think I've delved more deeply into subquery execution than I ever expected to :-)

Looking back at the original post, I find this question:

> I am having a very fundamental doubt, what is the order of
> retrival of the clauses when all are inclueded in the
> select stament

The SQL standard seems to define an ordering, but your database's optimizer might surprise you. Srinivas, if you haven't already, you might want to read the following articles: 8_DISPLAYID,F4950_P8_CRITERIA:11504677087008

I don't want to become embroiled again in any argument about what is right or wrong. At this point, the important thing is probably to understand what the standard has to say, to understand that there is (or seems to be) disagreement over the whole issue, and to understand that a given vender may deviate from the standard for purposes of optimization. You'll probably never have problems unless your tables are poorly designed. The type-mismatch problem that began our whole discussion was the result of what was probably a poor design decision. I'll hedge just a bit, because I wasn't around when that decision was taken.

I will also say this. I still find it helpful to work through writing a query in a stepwise fashion, beginning with the FROM clause. I don't know how others do it, but I like to begin, conceptually at least, with my tables and then work through the remaining clauses to refine the results to those rows that I ultimately want.

Hope this helps.

Best regards,

Jonathan Gennick --- Brighten the corner where you are * 906.387.1698 *

Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit, or send email to and include the word "subscribe" in either the subject or body.

Monday, September 20, 2004, 10:43:52 AM, Jared Still ( wrote:
JS> On Mon, 20 Sep 2004 15:41:34 +1000, Mark Richard JS> <> wrote:
>> The where clause must be processed first - since it removes atomic
>> of data. Next the group by has to be applied to determine the values of
>> max(sal). This allows the having clause to be applied (which I tend to
>> think of as "where clauses applied after group by"). Finally the order
>> can be applied to ensure the result set appears in the desired order.

JS> After recent discussions on this list regarding nested sub-queries, don't
JS> assume that Oracle will always process the WHERE clause first.

JS> (Check the archives )

JS> The simple query in the previous post won't be a problem, but it is JS> nonetheless a dangerous assumption.

JS> Jonathan Gennick where are you? - care to comment?

JS> Jared
JS> --


Received on Mon Sep 20 2004 - 10:59:36 CDT

Original text of this message