SQL Process Order

[dw85745]

Si-the-Geek

I stumbled across this and if may be appropriate for FAQ.

Another area that needs to be added in FAQ is how to Debug an SQL in an Access DB.

===================================================

Source
http://databases.aspfaq.com/database...by-clause.html

Here is how a SELECT works in SQL ... at least in theory. Real products will optimize things when they can.


1. Start in the FROM clause and build a working table from all of the joins, unions, intersections, and whatever other table constructors are there. The <table expression> AS <correlation name> option allows you give a name to this working table which you then have to use for the rest of the containing query.

2. Go to the WHERE clause and remove rows that do not pass criteria; that is, that do not test to TRUE (reject UNKNOWN and FALSE). The WHERE clause is applied to the working in the FROM clause.

3. Go to the optional GROUP BY clause, make groups and reduce each group to a single row, replacing the original working table with the new grouped table. The rows of a grouped table must be group characteristics: (1) a grouping column (2) a statistic about the group (i.e. aggregate functions) (3) a function or (4) an expression made up of the those three items.

4. Go to the optional HAVING clause and apply it against the grouped working table; if there was no GROUP BY clause, treat the entire table as one group.

5. Go to the SELECT clause and construct the expressions in the list. This means that the scalar subqueries, function calls and expressions in the SELECT are done after all the other clauses are done. The AS operator can give a name to expressions in the SELECT list, too. These new names come into existence all at once, but after the WHERE clause has been executed; you cannot use them in the SELECT list or the WHERE cluase for that reason.

6. Nested query expressions follow the usual scoping rules you would expect from a block structured language like C, Pascal, Algol, etc. Namely, the innermost queries can reference columns and tables in the queries in which they are contained.


This means that a SELECT cannot have more columns than a GROUP BY; but it certainly can have fewer columns.

[RhinoBull]

How does that quote reflects on your thread's title? It tells nothing but "how to assign alias for some of your subqueries".

[MaximilianMayrhofer]

Actually, I think it is explaining the mechanics of how an sql query is processed by the database.

[techgnome]

Actually... it's anwering the question of "How do I use a SELECT list alias in the WHERE or GROUP BY clause?" ... doesn't say anything about how the db engine goes about doing it. Because I can tell you, queries are processed, top to bottom, left to right, inside to out. And not always in that order.

-tg

[RhinoBull]

Actually, I think it is explaining the mechanics of how an sql query is processed by the database.

Read it again then.

Actually... it's anwering the question of "How do I use a SELECT list alias in the WHERE or GROUP BY clause?" ... doesn't say anything about how the db engine goes about doing it.
Because I can tell you, queries are processed, top to bottom, left to right, inside to out. And not always in that order.

-tg

Agreed indeed!

[dw85745]

My interpretation was the same as MaximilianMayrhofer.

That's why I posted it.

Even though the Article title deals with Alias, the lead-in statement -- to the posted comment -- from the source document from which it was obtained implies this is the general order of processing for any SQL.

Whether the original author is correct or not is unknown.

[si_the_geek]

In basic theory terms, the clauses are processed in that order.. but the reality varies.

If you think of things working as they were described, you should end up writing valid SQL statements.. but that (and the efficiency) depends on the database system you are using.


To be honest I don't think that article contains anything more useful than we already have in the FAQs - it seems to me that the tutorials in the SQL section (particularly sqlcourse.com ) give a better explanation of what each of the clauses do.

Another area that needs to be added in FAQ is how to Debug an SQL in an Access DB.

I'm not entirely sure what you mean.. can you give a bit more detail of what kind of thing you are thinking of?

[RSINGH]

Because I can tell you, queries are processed, top to bottom, left to right, inside to out. And not always in that order.

-tg

Not true. The FROM clause of a query is parsed before the SELECT list. How else can aliases in the SELECT list be evaluated if they have not been defined in the FROM clause?

[si_the_geek]

That comes under the description "inside to out", and in basic theoretical terms 'FROM before SELECT' is correct.

However, the reality isn't quite the same thing, and varies between database systems... there are many steps to processing an SQL statement, which are not necessarily done in the same way that we think of them, and in many cases the clauses (or even the entire statement) actually tend to be processed more than once.

[FunkyDexter]

To be honest, a quick glance at a few execution plans will tell you that this is a gross over-simplification. On a complex query the execution plan jumps back and forward through these steps that it qualifies as doing the hokey cokey. I imagine the original author was giving just enough info to demonstrate his point and, as such, his comments should be taken with a pinch of salt. They do provide quite a good logical breakdown of what the DBMS will do, so if you apply these steps you will arrive at the same outcome but the reality of what's happening is far more complex.

edit> Hmm, I see the thread moved on in the 'almost an hour' that I had the reply window open. I've really added nothing of value have I? I'll slink off quietly now...

[RSINGH]

Thanks for that Si, I really should learn to read.

For clarification, below is the order that the SQL Server optimiser uses. I don't know about other RDBMSs. The brackets are the order.

(8) SELECT (9) DISTINCT (11) TOP
(1) FROM
(3) JOIN
(2) ON
(4) WHERE
(5) GROUP BY
(6) WITH
(7) HAVING
(10) ORDER BY

From Inside Microsoft SQL Server 2005: T-SQL Querying by Itzik Ben-Gan. A must read for any SQL developer.