If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SELECT vs. ORDER BY - order of execution question
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 -- cinnie |
#2
|
|||
|
|||
SELECT vs. ORDER BY - order of execution question
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. "cinnie" wrote: 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 -- cinnie |
#3
|
|||
|
|||
SELECT vs. ORDER BY - order of execution question
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 "KARL DEWEY" wrote: 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. "cinnie" wrote: 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 -- cinnie |
#4
|
|||
|
|||
SELECT vs. ORDER BY - order of execution question
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" wrote in message ... 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 -- cinnie |
#5
|
|||
|
|||
SELECT vs. ORDER BY - order of execution question
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 -- cinnie "Sylvain Lafontaine" wrote: 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" wrote in message ... 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 -- cinnie . |
#6
|
|||
|
|||
SELECT vs. ORDER BY - order of execution question
On Thu, 4 Feb 2010 15:26:08 -0800, cinnie
wrote: 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!" -- John W. Vinson [MVP] |
#7
|
|||
|
|||
SELECT vs. ORDER BY - order of execution question
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/Art...33/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" wrote in message ... 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 -- cinnie "Sylvain Lafontaine" wrote: 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" wrote in message ... 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 -- cinnie . |
#8
|
|||
|
|||
SELECT vs. ORDER BY - order of execution question
hi Sylvain - thanks for your response. It gives me some good insights.
-- cinnie "Sylvain Lafontaine" wrote: 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/Art...33/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" wrote in message ... 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 -- cinnie "Sylvain Lafontaine" wrote: 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" wrote in message ... 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 -- cinnie . . |
Thread Tools | |
Display Modes | |
|
|