Is it Select/From or From/Select?

Over in the LINQ MSDN forums, the question came up: which is better, Select/From (as VB does it) or From/Select (as C# does it)? To quote from the thread, here’s the C# side of it, straight from Anders:

There are a multitude of reasons why select comes at the end and not the beginning of a query in C# 3.0. The more important ones are:

(1) Statement completion (Cyrus’ blog has a good explanation).

(2) Order of execution. The C# query syntax lists operations in the order they are executed.

(3) Scope of “from” variables. SQL is strange in that scope flows both upward and downward. In C#, scope extends from the point of introduction to the end of the query, which seems much more intuitive.

(4) Large select expressions. In SQL, because results are rectangular rowsets, select clauses tend to be fairly simple. However, when querying objects and XML it is quite common to have large select expressions that construct entire object graphs, possibly with multiple nested queries. Trying to understand a large select expression written in terms of variables that haven’t been introduced yet (and may not even be visible on the screen) is quite confusing.

(5) Even if we picked SQL’s ordering, the similarity would be skin deep. There are lots of other differences. For example, C#’s built-in operators and quite different from those of SQL. I actually think the different ordering is a benefit because it makes it quite clear that this is not SQL.

Note that XQuery’s FLWR (“flower”) expressions have the same ordering as C#’s query expressions–I suspect for some of the same reasons.

Anders

 and here’s the VB side of it, straight from me:

Speaking from the VB team perspective…

As is already obvious from this thread, Select/From ordering is going to be another one of those religious issues like case insensitivity that people are going to be arguing about for the next twenty years and beyond. As with case sensitivity, both sides can marshal perfectly reasonable arguments as to why their choice is the One True Way(tm) and why the other side is Consorting With the Devil(tm). I say this knowing that nothing I say now can influence that outcome and that this discussion (in the larger sense) must always end in tears, recriminations and Godwin’s law. But it’s worth saying nonetheless.

With that out of the way, I think this thread has already covered many of the relevant points relating to why VB chose to say Select/From instead of From/Select:

* For many, many programmers and for many, many VB programmers, specifically, SQL is a very familiar language. Leveraging a huge existing base of knowledge (and programmed muscle memory) makes the LINQ support more understandable and usable straight out of the gate.

* The SQL ordering of clauses is a time-tested convention that has been in continuous use for decades.

* VB emphasizes English readability. As noted in the beginning of the thread, the SQL ordering is more English readable than the obverse.

Anders raises some objections to the SQL ordering, but on the balance, we believe that the benefits outweigh the limitations. Specifically, I’d say:

* Statement completion is a significant question. We have a bunch of ideas as to how we could finesse this in the IDE, but we haven’t reached a point of being really able to try them out. This may be a real sticking point, time is just going to tell.

* I don’t agree with Anders’s points about the difficulty of understanding SQL’s clause ordering. Although some variants of SQL have invented phenomenally complex and bizarre sets of rules about how binding works, the basic rules about binding seem to have been graspable by millions of SQL programmers over the years without too much pain and suffering. As long as the rules remain relatively simple and straightforward, there don’t appear (to me, at least) to be major hurdles in implementing a very understandable Select statement where From is in the middle of the statement. Reasonable people can disagree on this point, however.

* Ultimately, we don’t believe that people will be confused as to whether this syntax is SQL because it will become almost immediately obvious that it isn’t once a programmer works with it for more than a few minutes. It’s a problem that will sort itself out on its own relatively quickly.

I would close by saying that the VB team, overall, values practicality over dogmatism. From a pure syntatic perspective, clause ordering is not fixed in stone – one could support both a Select/From order and a From/Select order without much difficulty. Indeed, we’d like to look at how clauses such as Where and Order By can be used on their own without requiring a Select or, perhaps, even a From. So while we will continue to support Select/From, other possibilities may be investigated if it becomes obvious that they make the language more usable in some strong way.

That’s about it. We now return you to your regularly scheduled religious debate…

Paul
VB Team

And that’s where it stands at the moment… I’m sure I’ll get a few comments on that, though.

31 thoughts on “Is it Select/From or From/Select?

  1. Kevin Ryall

    I have to agree with you on this – the majority of VB programmers are familiar (to some degree) with SQL, and it would be gratuitously confusing to reverse the order of the familiar SQL clauses.

    Personally, while Anders’ arguments are not unreasonable, I would definitely prefer the Select…From… order – it reads more naturally. This is why I generally prefer VB to C# – I strongly believe that a language should be designed to be easy to read, not shorter/faster to write and definitely not to be easy for a compiler to parse (within reason of course) – even though I tend to favour the C# approach on most non-readability design decisions…

    The only deal-breaker would be statement completion – if this proves impossible to achieve, then I’d reluctantly abandon the more readable order; but if you can pull it off, it would be a triumphant vindication of the VB philosophy of tackling complexity in the IDE/compiler to make the developer’s life easy, rather the C-language family tradition of making the compiler’s life easy by forcing a terser, less `natural’ syntax.

    Reply
  2. Fan [VBMVP]

    The VB syntax is more readable I think. But how about the intellisense? When I type Select a. should it show the properties to me? I think it’s a little bit hard to implement, but I still support this VB syntax. VB can also support a clear lambda expressions syntax to support the execution-ordered query expression (I think it’s more important than query comprehension. With lambda expressions, we can create other none sql-liked query syntaxes by ourselves. That will be very flexeble!).

    Reply
  3. Tim

    Im kinda torn with that, i like the readability of Select From and we are intelligent enough to know that it may look like SQL but its not.

    As has been raised in the previous two posts, my concern is over intellisesne, we would potentially have to write the from clause first if we want intellisense (as i understand it) although that could be worked around with nice IDE support that VB is known and loved for, some thing like, one could type in ‘Select From c in Custs’ press enter and then it codul put me back in between select and from so i can write that clause, or use tab to navigate between clause sections. If you can nail that part then thats half the battle.

    The other half is mucking around with C# samples vs VB samples, its already annoying enough to convert between the two as it is, this wodul be one more thing we’d have to change, now if the IDE could rewrite the LINQ when its pasted in or written out, or even if it worked with both methods.

    One thing id love to see is an integrated visual LINQ designer, kinda like enterprise manager’s sql designer but instead of tables it wodul be obejcts in scope and it could enumerate the available properties etc and a smart tag on the LINQ that lets you open it up in the designer, probably not gonna happen but it wodul be really nice, might have to wait for the brainiacs at DevExpress to write a coderush plugin or something.

    Reply
  4. Pingback: Richard Clark

  5. None

    Screw fifty years of academic research and real use of SQL around the world just for the benefit of one compiler and intellisense. Makes sense, NOT.

    They should be burnt at the stake!

    Reply
  6. Rob Conley

    —————

    I would close by saying that the VB team, overall, values practicality over dogmatism.

    ———————

    Well then what about a compilier that handles VB6 syntax then.

    The LINQ Stuff is needed and nice. But it goes to show if you can extend the language to do that then there is no reason to offer a variant to allow our old VB6 code to run under .NET.

    That would be truly an example of practicality over dogmatism. As millions of lines of VB6 code could maintained in VB.NET.

    Reply
  7. Dick Cheney

    SQL isn’t procedural – it’s declarative. Any control that the programmer has over the order of operations in SQL is implementation-dependent and is very limited. The SQL query compiler usually looks at the distribution of data and the query structure and attempts to choose an optimum decision path for the query. That’s the way queries should be handled, at least.

    So I don’t know why you’re having this discussion, except to underscore how LINQ throws the SQL and relational theory baby out with the bathwater.

    Reply
  8. Adrian Moore

    Having implemented a SQL query engine myself, the SELECT … FROM always seemed backwards to me. Choosing what data to output before you have declared your data source seems backwards. Its pretty simple when you have a single table. However, when you are joining several tables its not so obvious.

    Now be honest, when you’re writing a complex query, how many of you first type SELECT * FROM then figure out the tables to join, then go back and replace * with the columns and names you really want to you. If you answer yes, then your thinking FROM/SELECT. If you answer no, then you’re thinking SELECT/FROM.

    Even in Microsoft’s query designers, typically you pick the tables and connect them first, then you select the columns to output. If you’re doing this, then you’re thinking FROM/SELECT.

    I’m with Anders.

    Reply
  9. SB

    I do not think SQL was the result of academic research, and some relational database experts find the current SQL dialects quite appalling (see http://www.dbdebunk.com for some of the flames). SQL is quite far from traditional relational algebra notation, although admittedly the project operation is indeed written as Project<sub>attribute, …</sub>(Relation).

    I find the from … select … notation more readable, and you do write "table.field" instead of "field of table" or "field(table)" or "{ field } table" etc.

    It is easier on the brains, since you scan the text from left to right and do not have to come back to the fields or remember them to understand their origin.

    Reply
  10. Roland Kaufmann

    Don’t forget that SQL in its time was designed by IBM, which does not bode well for consistency!

    The logical way (in my book, at least) is:

    FROM xxx WHERE yyy SELECT zzz;

    First you decide from which tables you are going to fetch data, second what rows to throw away and then third which columns to keep. At least this is the order the database will have to process the query (since you may filter the rows on other fields than the ones with which you end up).

    Reply
  11. Michel Walsh

    Well, first, the FROM clause is not mandatory:

    SELECT 2 + 4

    is a valid statement. Does that means it cannot be with a FROM/SELECT syntax?

    Second, when I have a problem I know what I want, not necessary where is the solution. In fact, pure SQL is all about that: you describe WHAT you want, and leave the "engine" decided HOW to get it. So, starting by what we want sounds more logical, and a step forward a potential ideal paradigm about how we should use the computer.

    But indeed, as a developper, I know the structure, so from where what I want is likely to be found. Do you say: FROM internet GIVE ME the definition of some_word ? That sound like to impose a technology even before knowing the nature of the problem.

    Well, since we have benefits in both camps, why not allowing BOTH syntax, the SELECT / FROM and the FROM / SELECT… Are we afraid to leave freedom of personnal choice, or shall we always be "didactorial bosses"?

    Now, in SQL, the "variables" are the tables, not the columns, and their scope is like C++ scope (not C#) as in:

    int i:

    int j;

    { int i; // not allowed in C#

    int k;

    }

    with k only visible inside the inner scope, j percolates through, and the external i is "blocked" by the new declaration. In SQL, the scope are not delimited by { } but by (SELECT … FROM … )

    as in

    SELECT a.f1

    FROM a some JOIN (SELECT ….

    FROM b

    WHERE a.f2=b.g3)

    Speaking of b in the outer select is an error. I fail to understand the comment made about the variable scope… It seems there is confusion about where are the scopes, in SQL.

    Now, a suggestion… why not allowing the use of the keyword AS instead of the membership notation?

    SELECT expression AS total

    instead of the actual propostion:

    SELECT .total = expression

    The major advantage of that proposition is to remove the possible mis-interpretation of =, which is now absent, under the proposition. Also, consider:

    SELECT 4==myField As IsOk …

    versus

    SELECT .IsOk = 4 == myField …

    Well, I hope I am not too obscure…

    Reply
  12. HackAnon

    I agree with Michel,

    Also, instead of "From x in Object" how about "From object as x" like SQL does?

    Just because LISPers do "3 3 +" to add doesn’t mean 2000 years of math history will change to fit their style even if it is better for the compiler.

    Reply
  13. Pingback: VS Data Team's WebLog

  14. AnonymousOldTimer

    Such a shame that people even consider this kind of distinction interesting. Wouldn’t it be nice to be able to work in a language where not only is syntax not required for every trivial addition to the language, but in those cases where syntax does turn out to be helpful instead of a hindrance, it can actually be added and modified by the user? Such languages <a href="http://www.swiss.ai.mit.edu/~jaffer/r5rs_toc.html">do exist</a>, believe it or not…

    Reply
  15. Rommel Abesames

    why not allow both? that LINQ (and SQL) view themselves as a more declarative approach in programming seems contrary to their penchant for preferring a sequence to the SELECT FROM WHERE clauses when the meaning is all the same whatever order you put them

    Reply
  16. Bill Burrows [MVP]

    Hi Paul,

    My biggest problem in the past, where I had to embed the SQL via strings into the code, was remembering the darn database details when my head was into the logic I was trying to codify. Was the table named Employee or Employees, was the last name called LName or LastName. Was the zip code a string or numeric? Getting these details correct (and not knowing if I made a mistake until run time with some obscure SQL error) was always frustrating. That is why I have always liked the visual designers first introduced in Access and later brought into Visual Studio.

    So my biggest design request is do whatever is needed empower Intellisence to help me as much as possible. I want help in all three main clauses. When I type my statement, I want help to be able to see the data sources my project is working with, what tables/objects/collections they contain, and what the individual members exist with those tables, etc. It seems to me that the order of those questions is kind of important and it would hard to reorder the questions and provide the same level of help. It also seems to me that I write my code linearly and not jump around within the clauses of a statement.

    My big “want” is an IDE that helps me get it right the first time. In fact, I’d like to be able to right-click on a query and select “Show me the results” and see the results right there.

    Thanks for listening . bill

    Reply
  17. Pingback: Rod Paddock

  18. Roger Jennings

    Paul Vick addresses the query comprehension operator sequence issue from the VB Team’s standpoint-following the SQL convention simplifies usage because VB programmers are likely to be conversant with SQL.

    Reply
  19. Curt Sampson

    Actually, in standard SQL, "SELECT 1 + 2" without a FROM is not a valid SQL statement. It’s an extension if it works.

    That aside, I am completely with the FROM / SELECT guys: not as much because it follows the order in which the computer does the query, but it follows more closely the order in which I work out the relational algebra that eventually gets translated into a query (often after being distorted heavily to deal with SQL’s limitations).

    As for dogmacism versus practicality: you’ve got it backwards. It’s dogmatic to stick with SQL, a language millions have struggled with needlessly due to its painful syntax. It’s practical to upgrade to a language easier to learn, easier to write, and easier to undertand.

    BTW, I consider this a pretty small issue in the end compared to some of SQL’s more serious problems. The join support is hard to use (though getting better)–who thought up putting join information in the WHERE clause, anyway? And GROUP BY! Don’t get me started. Add three more columns to your SELECT and you have to add three more columns to your GROUP BY statement, all because you have to specify the columns on which you are *not* applying an aggregate function, which are usually much more numerous than those on which you are. (And heck, you already specified in the WHERE clause which columns do and don’t have an aggrigate function applied, anyway.) That also reminds me of the way you have two separate restrictions, one in WHERE and one in HAVING, to apply before and after the grouping. Which you need, but what happens when you have two groupings? At least SQL has recently added decent subselects, but if you need something more like a subroutine, create a view. And don’t forget to delete it afterwards, because views, unlike tables, can’t be temporary (or that would almost solve the "query subroutine" problem).

    Ok, enough of a rant, and this certainly was one. But I’ve taken so many eight-line relational algebra queries and had to make 40-line SQL monsters out of them that I’m just sick of it.

    Reply
  20. Pingback: Panopticon Central

  21. Pingback: Panopticon Central

  22. Builder

    "I don’t agree with Anders’s points about the difficulty of understanding SQL’s clause ordering. Although some variants of SQL have invented phenomenally complex and bizarre sets of rules about how binding works, the basic rules about binding seem to have been graspable by millions of SQL programmers over the years without too much pain and suffering. As long as the rules remain relatively simple and straightforward, there don’t appear (to me, at least) to be major hurdles in implementing a very understandable Select statement where From is in the middle of the statement. Reasonable people can disagree on this point, however"

    SQL is so simple in general, but clause ordering needs some brains to be done correctly.

    Reply
  23. Pingback: Panopticon Central

  24. Pingback: Panopticon Central

  25. Pingback: Anonymous

  26. bedroom furniture

    when you’re writing a complex query, how many of you first type SELECT * FROM then figure out the tables to join, then go back and replace * with the columns and names you really want to you. If you answer yes, then your thinking FROM/SELECT. If you answer no, then you’re thinking SELECT/FROM.

    Reply

Leave a Reply