Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm not sure if this question is on-topic - if not, feel free to close. Also, if it'd be a better question for another StackExchange site, I can ask it elsewhere.

I was wondering why SQL places the SELECT clause first.

SELECT [attribute]
FROM [table]
WHERE [condition]

My understanding is that the database engine starts with the FROM and goes from there - SELECT is the last thing that is processed.

Is there a specific issue that the designers placed under consideration when making this decision?

Personally, I tend to glaze over the SELECT and look at the FROM clause first. It seems more intuitive to me to list the origin first. LINQ, for example, does this.

var table = from [table]
            where [condition]
            select [attribute];

Isn't this the case even in relational algebra? (I'm using the notation of RA)

\project_{[attribute]}( \select_{[condition]}( [table] ) )

The expression is evaluated inside-out (granted, the notation isn't declarative, it's functional). So why is SQL different?

share

migration rejected from stackoverflow.com Feb 6 at 16:11

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as primarily opinion-based by billinkc, Paul White, Mark Storey-Smith, Max Vernon, RolandoMySQLDBA Feb 6 at 16:11

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise.If this question can be reworded to fit the rules in the help center, please edit the question.

4  
Because the SQL standards committee decided it this way ..... SQL is a much older standard than LINQ ... –  marc_s Jun 22 '12 at 21:10
3  
This has been asked before, and migrated to Why the Select is before the From in a SQL Query?. Could do with a more authorative answer though IMO. Not sure how much is just speculation. –  Martin Smith Jun 22 '12 at 21:12
2  
Yes - maybe. I don't know of any source that would explain why exactly that decision was made. But it was made - it's the valid syntax now. Get used to it. Use it. Stop asking why? - it's always because! :-) –  marc_s Jun 22 '12 at 21:18
3  
@marc_s: Stop asking why?! Why would I do that? :) –  voithos Jun 22 '12 at 21:20
6  
show 9 more comments

5 Answers 5

SELECT is not the last thing that is processed; ORDER BY is. This is why the only place where you can refer to aliases you created within SELECT is in the ORDER BY. Here is Joe Celko's explanation of how a query is processed according to the standard (I stole this from my own aspfaq.com article, which stole the quote probably from a newsgroup post by Celko):

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

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 AS option allows you give a name to this working table which you then have to use for the rest of the containing query.

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.

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.

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.

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.

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.

Now, Celko was one of the main contributors to the earlier versions of the standards. I don't know if you're ever going to get a definitive answer to the WHY? question, except for speculation. My guess is that listing the actual operation first makes it very easy for the parser to know exactly what the type of operation is going to be. Imagine a 20-table join that could end up being a SELECT or UPDATE or DELETE, and remember that the code for these engines was originally written back in the days when string parsing was quite costly.

Note that if the SQL standard dictated FROM to come first, vendors may have independently decided to parse the grammar in a different order, so it still may not make sense to expect the order of clauses as written to completely obey the order of processing 100% of the time.

The same is true for things like CASE. We've seen scenarios right here on this site, for example, where the previously believed myth that CASE always processes in order and short circuits, are false. And this extends to other common beliefs as well, such as SQL Server evaluating joins in the order they were written, short circuiting WHERE clauses from left to right, or processing CTEs once or in a certain order even if they are referenced multiple times. Products are free to optimize how they see fit even if it doesn't reflect exactly how you've stated the query should work declaratively.

share
1  
These aren't bad arguments you make, but they're not historically accurate. It's a fine answer for why should SELECT before, perhaps, but not why it was originally made that way –  Ray Jun 23 '12 at 11:29
2  
@Ray I never claimed that anything in my answer dictated any of the WHY. In fact I explicitly stated that it didn't. –  Aaron Bertrand Jun 23 '12 at 12:06
2  
@Jasmine see and I disagree that SQL has established a good convention. Since the statement isn't actually interpreted in anything even remotely resembling the order it's written, this is an endless source of confusion for anyone learning anything beyond the basic constructs. Maybe that is by design but that doesn't make it a good design. –  Aaron Bertrand Feb 4 at 19:57
2  
I disagree, too. The LINQ choice seems more natural to a programmer. The SQL committee made some very unorthodox choices in that matter. –  ypercube Feb 4 at 19:58
2  
@Jasmine people want to know things as simple as "Why can I not reference an alias created in the SELECT clause in the WHERE clause?" That is a pretty common question that has nothing to do with deeply understanding the internals. If the syntax were ordered in the way it was actually processed (as opposed to making it seem more English for some people), this would be less confusing for beginners. –  Aaron Bertrand Feb 5 at 22:04
show 8 more comments

Keep in mind that SQL originated as SEQUEL, or Structured English Query Language. It was initially designed to be written by English-speaking "non-programmers." The intent was for anyone to be able to sit down and write a simple query to find what they were looking for.

What does this have to do with SELECT (as well as DELETE and UPDATE) being first? That's how you most commonly say it in English: SELECT, FROM, WHERE:

  • Please choose the professor of a course from the curriculum that fulfills a gen-ed.
  • Go select a book off the shelf that looks interesting.
  • Remove the apples from the basket that have gone bad.

It's possible but less intuitive to use FROM, SELECT, WHERE:

  • From the curriculum, choose the professor of a course fulfilling a gen-ed.
  • Off the bookshelf, pick a book that looks interesting.
  • From the basket, remove any apples that have gone bad.

Trying to follow the way SQL processes the query is even harder: FROM, WHERE, SELECT:

  • From the curriculum, of courses that fulfill a gen-ed, choose a professor.
  • Off the bookshelf, if it looks interesting, pick a book.
  • From the basket, if they've gone bad, remove the apples.

It just doesn't make a lot of sense to switch up how people commonly form English commands if you're designing a language intended to used by accountants and librarians and everyone else trying to get information out of a database.

share
1  
Regardless of who it was intended for, the keywords are in English and English still works this way. It's still easiest to read. (+1) –  Izkata Jun 23 '12 at 3:53
2  
This actually seems like a argument to me. Of course, English is rich enough to be able to express any order, really. For example, if you add an implicit "look through" instead of the "from", you can say Look through the curriculum, of courses that fulfill a gen-ed, and choose a professor. –  voithos Jun 23 '12 at 4:35
    
If the question is "why did they make it this way", this answer is (historically) correct. The other answer seems to be arguing about a different question--something like "is this order correct" –  Ray Jun 23 '12 at 11:28
    
@Ray - Any citations that this is the historically correct answer? Would be nice to see a quote from the 1970s literature that specifically discusses this. Hugh Darwen seems to have a different theory here (see SELECT-FROM-WHERE section) –  Martin Smith Jun 23 '12 at 12:28
2  
Sure, almaden.ibm.com/cs/people/chamberlin/sequel-1974.pdf presents sequel as an attempt to reach the "casual user" or "infrequent data base user": "There are some users whose interaction with computers is so infrequent or unstructured that the user is unwilling to learn a query language. For these users natural language... seems to be the most viable alternative" –  Ray Jun 23 '12 at 12:51
show 4 more comments

Where I designing the statement formats these are things I would consider:

  • Assuming it is suitable the WHERE clause can be used for any CRUD statement.
  • DELETE requires a subset of the SELECT functionality (SELECT rows, then DELETE), so the statements should be as similar as possible.
  • SELECT may be provide data for an INSERT so it should work well in that case. Having the column list at the beginning of the statement makes it easier to compare the SELECT list to the INSERT list.
  • When building a SELECT list, primary consideration is the columns required, followed by where to source them. (For migrated columns, there may be several tables from which they can be retrieved).

Given those considerations, I find the statement design about as good as you can get. It also works well with my approach to building a query.

When I build a query, I work as follows

  • Determine which columns are required.
  • List the best tables to retrieve the columns from.
  • Add the JOIN conditions.
  • Add any static filter conditions.
  • Add any parameterized filter conditions.

I expect the parser to parse the whole statement before building the query. (Some parsers will then work backwards through the stack to build the execution plan.) The optimizer, if available, will then examine some variations to pick the best plan. Understanding how the optimizer works and is configured may influence the ordering of the WHERE clauses, and the order of tables. All other things being equal, a quick look at start and end of the query will tell me what it should be doing.

I build DELETES in the same manner, but start as a SELECT. When I am certain the SELECT conditions are correct, I replace SELECT (and its column list) with DELETE.

share
1  
One of the signs of a "database guy"... you think like the optimiser :) –  Mark Storey-Smith Jun 22 '12 at 23:09
1  
Surely though you think in terms of "what fields do I need" and then "what tables do I need" because of having written a zillion SQL statements. I know I do. –  Jon of All Trades Jun 22 '12 at 23:38
    
@JonofAllTrades I think what columns, then consider what other tables can provide those columns. When two child tables can be used, I drop the parent table if all columns are available from the child tables. When necessary, I add tables required to supply filtering criteria. For columns which are only in one table, the choice is clear as there is only one table which can provide the data. For primary key columns it is not always clear, and the data may be available from a table providing other columns. –  BillThor Jun 22 '12 at 23:51
    
Honestly @JonofAllTrades, no. I know what data I need, I know what SQL constructs are available to shape that data. But, as I construct a query my thoughts are entirely about how the database engine will interpret the request and what I can do to help it find the optimal plan. –  Mark Storey-Smith Jun 22 '12 at 23:55
2  
I never think about what fields I need, only columns. :-) Celko taught me that too. –  Aaron Bertrand Jun 23 '12 at 1:03
add comment

If you are performing a SELECT you will always need to use that, but you do not always need to use a FROM in a SELECT query.

DECLARE @dt datetime
SET @dt = '20120622' 

SELECT @dt

Plus you will always SELECT FROM something, not FROM SELECT something.

In the SELECT you are specifying the operation that you want to run first, and then telling it which table to run in the FROM clause.

I could probably take every answer from the the question posted on Programmers:

Why the Select is before the From in a SQL Query?

Here are some highlights any of these answers might offer up some additional insight.

Gnat wrote

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:

"From Employee table e Select column e.Name" "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.

Or Nikita wrote

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.

There is plenty of speculation but I think it comes down that was what was decided by the SQL standards committee.

share
1  
No, but you can FROM X SELECT Y or FROM X DELETE. You yourself say that the FROM and SELECT clauses aren't necessarily used together, so why would SELECT need to come first? –  voithos Jun 22 '12 at 21:25
3  
In standard SQL the FROM is mandatory. And even if it was optional the grammar could just be [FROM table] SELECT ... –  Martin Smith Jun 22 '12 at 21:26
    
@voithos what I am saying is you can run a query without the FROM but you have to have a SELECT (if not an UPDATE, DELETE or CREATE, etc) –  bluefeet Jun 22 '12 at 21:28
    
@bluefeet: Or DROP or DECLARE or... What I was saying is that the FROM can be used in other cases than a SELECT –  voithos Jun 22 '12 at 21:29
add comment

Let's say FROM is important for SELECT, but the very first thing the database needs to know is whether you'd like to SELECT, UPDATE, DELETE or CREATE...

share
    
This seems like a valid reason. But I suppose it's a speculation. –  ypercube Jun 22 '12 at 21:33
    
They could have chosen to have it as SELECT FROM (tables) WHERE (conditions) LIST (columns_to_be_shown) ... –  ypercube Jun 22 '12 at 21:35
    
@ypercube ooh where are these obnoxious "That answer is speculation! Let's give a bounty to someone with an opposing opinion!" folks now? :-) –  Aaron Bertrand Jun 22 '12 at 21:43
    
The database process an entire SQL statement as a whole - the grammar wouldn't be ambiguous if the SELECT, UPDATE, etc, were listed last. The engine could still choose to process it however it liked... –  voithos Jun 22 '12 at 22:41
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.