SELECT vs. ORDER BY - order of execution question

[cinnie]

hello

In SQL, is SELECT executed before ORDER BY, or after. Here's why I'm
confused about this;

Consider table tblEmp with fields EmpID, Name_F, Name_L, Wage

SELECT tblEmpID, tblEmp.Name_F & ", " & tblEmp.Name_L AS Name_Full
FROM tblEmp
ORDER BY tblEmp.Wage

This works fine, even though the Wage field in the ORDER BY statement is not
included in the SELECT statement. This makes me think that ORDER BY is
executed BEFORE the SELECT statement has been able to eliminate the Wage
column.

Also, SELECT tblEmpID, tblEmp.Name_F & ", " & tblEmp.Name_L AS Name_Full
FROM tblEmp
ORDER BY Name_Full

This doesn't work because the ORDER BY statement doesn't recognize
Name_Full. This again makes me think that the ORDER BY is executed BEFORE
the SELECT.

But.....

when I research SQL order of operations, most sites seem to claim that the
ORDER BY clause is executed AFTER the SELECT clause. What gives? Any
clarification?

thank you in advance

 

[KARL DEWEY]

It seems to process from end to start but not all cases.
This works --
SELECT [Something]+1 AS New, YourTable.Score, YourTable.Grade,
[Score]+[Grade] AS Something
FROM YourTable;

And this --
SELECT YourTable.Score, YourTable.Grade, [Score]+[Grade] AS Something,
[Something]+1 AS New
FROM YourTable;

And this --
SELECT [Score]+[Grade] AS Something, YourTable.Score, YourTable.Grade,
[Something]+1 AS New
FROM YourTable;

 

[cinnie]

hello Karl

I'm having trouble understanding this answer because it makes no use of the
ORDER BY clause. I'm trying to determine whether ORDER BY or SELECT is
processed first in SQL. Any thoughts?
--
cinnie

It seems to process from end to start but not all cases.
This works --
SELECT [Something]+1 AS New, YourTable.Score, YourTable.Grade,
[Score]+[Grade] AS Something
FROM YourTable;

And this --
SELECT YourTable.Score, YourTable.Grade, [Score]+[Grade] AS Something,
[Something]+1 AS New
FROM YourTable;

And this --
SELECT [Score]+[Grade] AS Something, YourTable.Score, YourTable.Grade,
[Something]+1 AS New
FROM YourTable;

--
Build a little, test a little.

hello

In SQL, is SELECT executed before ORDER BY, or after. Here's why I'm
confused about this;

Consider table tblEmp with fields EmpID, Name_F, Name_L, Wage

SELECT tblEmpID, tblEmp.Name_F & ", " & tblEmp.Name_L AS Name_Full
FROM tblEmp
ORDER BY tblEmp.Wage

This works fine, even though the Wage field in the ORDER BY statement is not
included in the SELECT statement. This makes me think that ORDER BY is
executed BEFORE the SELECT statement has been able to eliminate the Wage
column.

Also, SELECT tblEmpID, tblEmp.Name_F & ", " & tblEmp.Name_L AS Name_Full
FROM tblEmp
ORDER BY Name_Full

This doesn't work because the ORDER BY statement doesn't recognize
Name_Full. This again makes me think that the ORDER BY is executed BEFORE
the SELECT.

But.....

when I research SQL order of operations, most sites seem to claim that the
ORDER BY clause is executed AFTER the SELECT clause. What gives? Any
clarification?

thank you in advance

 

[Sylvain Lafontaine]

The problem here is not the order of execution of the Select and of the
Order By but it is the name's aliasing; ie., the creation/definition of the
names that will be given to each column returned into the resultset and
which is performed last. You cannot use an alias in an Order By because
they have not yet been produced at this stage; even if the individual
column/expression have already been computed.

If you want to use an Expression an in Order by, you must either repeat the
Expression or use its column number:

... Order by tblEmp.Name_F & ", " & tblEmp.Name_L

... Order by 2

However, I would suggest *NOT* to use the column number because if you ever
want to add or remove a column, it's quite likely that you will either
forget to change the Order By or miscaculate its new position. Also, when
someone else will read your quote, it will make it much more harder to
understand what's really happening here.

In my opinion, in your case, the best solution would be to make Order By on
both field separately:

... Order by tblEmp.Name_F, tblEmp.Name_L

It's not only a question of syntaxe but because on big database, the
database engine can use an index on the fields Name_F and Name_L is such an
index exists; whereas whenever you use an expression, you are cutting your
query from the possibility of using any index.

Finally, your names tblEmp.Name_F are ugly and will give you a lot of
trouble later. By experience, it's much better to use fully qualified names
because they are autodocumenting themselves:

tblEmployees.FirstName, tblEmployees.LastName

and even better, dropping the prefixe tbl - which is totally useless - and
using the singular form for Employee because later, when you will have to
deal with objects and collection of objects if you go to .NET, it will make
the integration easier:

Employee.FirstName + Employee.LastName

And finally, in english, it's not NameFull but FullName. Everytime you go
against your brain, you diminish productivity and rise the possibility of
making errors not only for yourself but for all these other peoples who will
have to read your code later.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[cinnie]

hi Sylvain - thank you for your insights. I still don't feel that my
original question has found an answer, although I do think I get now what you
are saying about ALIAS. I'm simply trying to get a better understanding of
how SQL is processed.

Further to the examples I mentioned in my original post, I think that there
is further evidence that ORDER BY must be processed before SELECT: How could
the clause SELECT TOP 3... be processed if the records weren't already sorted
in the ORDER BY clause?

Unrelated to my original question, I found your comments about using
prefixes like 'tbl' curious. You say that names like this are 'ugly', but I
was just striving for clarity, not beauty. I'm fairly new at this and don't
wish to question an MVP, but doesn't this run counter to standard conventions
found in most VBA and SQL texts?

thanks for your thoughts

 

[John W. Vinson]

I'm fairly new at this and don't
wish to question an MVP, but doesn't this run counter to standard conventions
found in most VBA and SQL texts?

MVP's disagree with MVP's about this ALL the time... <g>

Sometimes escalates to sectarian warfare... "Heretic!" "Blasphemer!"

 

[Sylvain Lafontaine]

HI Cinnie - For your original question, the answer are both yes and no:
depending on what you are asking in your query and how it wil be build by
the sql compiler - whatever its real name - expression in a select statement
can be computed before or after any sorting order and it's an error to build
a particular query on the assumption that there is a definite order between
them.

For example, Itzik Ben-Gan has recently given an example where the addition
of a covering index change the order of which an expression is calculated
either after or before the Order By:

http://www.sqlmag.com:80/Article/ArticleID/103533/103533.html

This is for SQL-Server 2005/8 but it shows you how there is not such a thing
as an evaluation order between the Select part and an Order By instruction.

For the rest of my comment, you are right when you say that it goes against
standard convention found in most VBA and SQL texts. However, you are not
obligated to follow them and personally, this is what my experience told me
what it's best to do. While its usually a good thing to follow standards,
not because they are good but because they save a lot of work for peoples
working in the same field, this is not always the case and many of them are
now thought of as beeing counterproductive and are no longer followed by
many - if not most - peoples working on real working cases.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[cinnie]

hi Sylvain - thanks for your response. It gives me some good insights.