Why is the SELECT clause listed first?

Question

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?

Answer 1

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...

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.