just to add my two cents, without the intention to open that discussion
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
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;-)
On Behalf Of Jonathan Gennick
Sent: Monday, September 20, 2004 16:37
To: Jared Still
Cc: mrichard_at_transurban.com.au; srinivast_at_kpitcummins.com;
Subject: Re: 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
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
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.
Jonathan Gennick --- Brighten the corner where you arehttp://Gennick.com
* 906.387.1698 * mailto:email@example.com
Join the Oracle-article list and receive one
article on Oracle technologies per month by
email. To join, visithttp://five.pairlist.net/mailman/listinfo/oracle-article,
or send email to Oracle-article-request_at_gennick.com and
include the word "subscribe" in either the subject or body.
Monday, September 20, 2004, 10:43:52 AM, Jared Still (jkstill_at_gmail.com)
JS> On Mon, 20 Sep 2004 15:41:34 +1000, Mark Richard
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,
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?