72

This is something that bothered me a lot at school.

Five years ago, when I learned SQL, I always wondered why we first specify the fields we want and then where we want them from.

According to my idea, we should write:

From Employee e
Select e.Name

So why does the norm say the following?

Select e.Name -- Eeeeek, what does e mean?
From Employee e -- Ok, now I know what e is

It took me weeks to understand SQL, and I know that a lot of that time was consumed by the wrong order of elements.

It is like writing in C#:

string name = employee.Name;
var employee = this.GetEmployee();

So, I assume that it has a historical reason. Why?

Peter Mortensen
  • 1,050
  • 2
  • 12
  • 14
Cyril Gandon
  • 1,296
  • 1
  • 11
  • 17
  • 65
    It's a DBA conspiracy to keep OO monkeys in their place. – gbn Dec 30 '11 at 13:43
  • 3
    Unfortunately, I can't find any relevant info in the [paper that introduced SEQUEL](http://www.almaden.ibm.com/cs/people/chamberlin/sequel-1974.pdf), and I don't think there are specific citations that answer your question. gnat's answer is possibly the best explanation though - but I wouldn't dismiss the conspiracy theory. – yannis Dec 30 '11 at 14:07
  • 2
    Personally, I've always wished `Linq` could not have used the standardized `SQL` syntax. – jp2code Dec 30 '11 at 15:53
  • 5
    The clauses in a SELECT statement are **not** the order of the operation. – S.Lott Dec 30 '11 at 17:06
  • 8
    Nice question. You're next one should be why INSERT and UPDATE queries had to use different syntax models: (field1, field2) VALUES (f1, f2) vs (field1=f1, field2=f2). – LarsTech Dec 30 '11 at 18:10
  • @S.Lott: Example, http://msdn.microsoft.com/en-us/library/ms189499.aspx – gbn Dec 31 '11 at 10:39
  • @gbn: "The order of the clauses in the SELECT statement is significant"? Does **not** mean that they express (or even imply) an **order** to the operations being performed. The order of operations (the actual underlying query algorithm) is a consequence of the clauses, but not the order of the clauses. Is there some other quote on that page that's relevant? – S.Lott Jan 01 '12 at 14:41
  • I suppose that by extension, a least-to-most specific ordering would give you queries like `FROM foo WHERE bar = 5 SELECT baz`. – Blrfl Mar 21 '12 at 11:36
  • 1
    Based on [a discussion about a similar question](http://meta.programmers.stackexchange.com/questions/3325/is-the-question-about-statements-being-terminated-by-semicolons-appropriate-for)], this question does not fit the Q&A format well. – Thomas Owens Mar 21 '12 at 14:08

6 Answers6

88

Originally SQL language was called SEQUEL standing for

  • Structured English Query Language
    with the emphasize on English, assuming it to be close in spelling to natural language.

Now, spell these two statements as you'd spell English sentences:

  1. "From Employee table e Select column e.Name"
  2. "Select column e.Name From Employee table e"

Second sounds closer to natural English language that's why it is set as norm.

BTW same reasoning goes to Where etc - SQL statements were intentionally designed to sound close to natural language.

gnat
  • 21,442
  • 29
  • 112
  • 288
  • 7
    Of course Microsoft ignored that with LINQ as the FROM comes first! –  Dec 30 '11 at 13:46
  • 30
    There's a lot of lookahead logic in English :/ – Michael K Dec 30 '11 at 13:47
  • 16
    @Digger - that was by design: they couldn't support intellisense in the select part if the select came first. – Scott Whitlock Dec 30 '11 at 13:49
  • 6
    @Digger: LINQ follows the OO/modern Object.Method or Object.Property. Don't forget SQL has been around for 40 years – gbn Dec 30 '11 at 13:49
  • @ScottWhitlock and @ gbn I like LINQ, use it a lot. My comment was just an observation but I am sure there are EXCELLENT reasons why (as you state, plus others) LINQ is the way it is! –  Dec 30 '11 at 13:51
  • @Michael Quite true, English tends to put adjectives and adverbs before verbs and nouns which is *really* strange. Many students of English *needlessly* struggle with this aspect of English, as opposed to the Romantic languages that more appropriately put the verb first as it is the most important part of the sentence. – maple_shaft Dec 30 '11 at 13:54
  • 9
    I should have post this question on http://english.stackexchange.com/ instead :) – Cyril Gandon Dec 30 '11 at 13:57
  • 1
    @Digger: also note that in Linq, the `Select()` is optional, while the `From()` is not. `From()` bootstraps your query, `Select()` adds a filter / type transformation to it. It simply doesn't make sense the other way around. – tdammers Dec 30 '11 at 16:13
  • 1
    I wonder if parsing had anything to do with it - after all, the grammars for different kinds of statements are wildly different, and being able to tell unambiguously from the first word alone what kind of query we're talking about probably makes parsing a whole lot easier. – tdammers Dec 30 '11 at 16:16
  • I didn't know SQL was actually called "SEQUEL" originally. Now I have a real reason to pronounce it that way. Also, no wonder I hate SQL so much. – user606723 Dec 30 '11 at 17:07
  • 2
    @maple_shaft, ?! Subject goes before verb in most sentences in the three modern Romance languages I'm familiar with. (And in Latin word order was a lot more arbitrary, but I seem to recall a bias towards putting the verb near the end). – Peter Taylor Dec 30 '11 at 19:27
  • I wish they had done the same with sql management studio – Jay Jay Jay Sep 03 '13 at 13:44
  • 2
    @ScottSellers late comment, I know, but try to imagine LINQ without Intellisense (especially for newbies). Can you imagine? OK, now try to imagine you're implementing **Intellisense** and you have select before from...impossible right? – Adriano Repetti Apr 27 '16 at 18:46
  • What about Python's “comprehensions”? They follow expression-iteration-condition order (e.g., `[e.name for e in employees if e.is_full_time()]`), like SQL and unlike LINQ. – dan04 Sep 14 '16 at 23:03
36

Because SELECT is required in a select statement and FROM is not.

Select 'This String'

Of course your sql statement can be parsed to look for the SELECT, DELETE, UPDATE after the FROM, but is is really that big of a deal?

Remember, this was all done before intellisense. It's not that complicated.

Edit: There's probably no reason sql interpreters couldn't be built to do both.

JeffO
  • 36,816
  • 2
  • 57
  • 124
  • 2
    Though, you could also write `FROM myTable;` instead of `FROM myTable SELECT *`; This only seems like a requirement because it's what your used to. – user606723 Dec 30 '11 at 17:10
  • 16
    Just because it's required doesn't mean it has to come first. – LarsTech Dec 30 '11 at 18:07
  • 9
    In ANSI SQL `FROM` is required. This is why many RDBMSs have a table called `DUAL` [or other single row dummy table](http://dba.stackexchange.com/q/9108/3690) – Martin Smith Dec 30 '11 at 18:20
  • @LarsTech - it doesn't have to come first, but why make it complicated. It's called a select statement, just start with the word select. – JeffO Dec 30 '11 at 18:49
  • 3
    @JeffO: Okay. `SELECT FROM Customers COLUMNS FirstName, LastName, PhoneNumber`. – Allon Guralnek Jan 05 '12 at 19:22
  • @AllonGuralnek - What if I don't want to select columns from a table? – JeffO Nov 18 '16 at 17:41
  • @LarsTech - That's true, but if I were doing something like dealing with required and optional parameters, I'd expect the required ones first. – JeffO Nov 18 '16 at 17:46
10

Do not know an answer that I could back up by references, but if I had to speculate: SQL is a declarative language, a statement of such language describes what you would like to do as opposed to how you would like to do it.

As such, "SELECT X FROM Y" sounds as a more appropriate way of answering "What would I like to select from the database", as opposed to writing "FROM Y SELECT X".

In addition, in SQL, the SELECT/UPDATE/INSERT specifies the type of operation you are about to do and FROM is just a clause that helps you select from the right table in the database. Again, what are you doing with data takes precedence over how exactly you are going to achieve that.

0x4B1D
  • 241
  • 1
  • 3
  • 1
    +1: It's not imperative or procedural. The order of the clauses is merely a fit with English. Nothing more. – S.Lott Dec 30 '11 at 17:06
  • ON and WHERE may be better examples of the importance of order of clauses in a select statement. – JeffO Dec 30 '11 at 22:50
5

SQL is a structured query language targeted to English speakers. SELECT, INSERT, UPDATE, and DELETE are imperative commands. In English imperative commands begin the sentence or statement. Compare:

West young man go!

to

Go west young man!

SQL follows the second (imperative) format. Also the four imperative commands have three significantly different formats. Consider:

FROM    employees a,
        accounts b
UPDATE  ...

or 

INTO    customers a
SELECT  ...

If you know the action you are undertaking, it is easier to select the correct format.

In the case of select, you determine which attributes you want, then add the tables that have them. As you build the selection criteria, you may add additional tables. When you are dynamically adding criteria, it can usually be done at the end of a static portion of the query.

BillThor
  • 6,232
  • 17
  • 17
3

SQL statements begin with verbs. That was the choice of the language designers, and many programming languages work that way. Semantically, it is not uncommon to see programming languages that work like this:

verb(noun, noun, noun);

Also, in the case of SELECT statement that you give as an example, your proposed syntax would put the object first in the statement. Instead of a VSO (verb, subject, object) sentence order, you would have OVS, which would be a very strange when compared to natural languages. SVO (e.g. English), VSO (e.g. Arabic), and SOV (e.g. Latin) are more reasonable approximations of human speech.

David Cloutman
  • 109
  • 3
2

I think it would significantly complicate parsing, especially with subqueries, e.g.

  FROM Foo f
  JOIN (FROM Bar b
        WHERE b.ID = f.ID
        UPDATE b
           SET b.Wibble = 1) x
    ON x.ID = f.ID
SELECT f.XYZ

Parsing this would be more complicated. You could not tell that the UPDATE was a syntax error until you had parsed the FROM clause, and the parser would have to remember enough context to know that it was parsing a subquery. I don't think updates are permissible in subqueries anyway, but if they were (maybe with a RETURNING clause) then you might not be able to tell this was invalid until you had parsed the SELECT statement.

This would at least increase k (lookahead) for the grammar and at worst make it context sensitive, although this is stretching the bounds of my rather dimly remembered compiler design papers from university.

ConcernedOfTunbridgeWells
  • 161
  • 5
  • Looking at the Wiki article for [`QUEL`](http://en.wikipedia.org/wiki/QUEL_query_languages) that was developed at around the same time the order of the syntax clauses is what the OP suggests. – Martin Smith Dec 30 '11 at 19:09