2

It strikes me as weird that the column expressions (everything between SELECT and FROM) in an SQL SELECT statement come before the table references, when the column expressions depend directly on the aliases, joins and subqueries of the table references. The converse is not often the case, so often I end up writing the table references first anyways, especially with complex queries. This makes query writing less linear in seemingly all cases, so I'm curious then if there some historical and/or practical reason behind the order?

9

SQL was designed to be English-like, which means the expressions should more or less read like English sentences. In the initial paper you can see the use-case example sentences they translate into SQL:

Q1. Find the names of employees in the toy department.

Q2: Find the names and salaries of employees who are in the toy department and whose manager is Anderson.

Q3: Find the items sold by departments on the second floor.

Q4: Find the average salary of employees in the shoe department.

etc.

You will notice these sentences naturally follow this template:

Find <property> of <entity> <satisfying some condition>

Which leads to the SQL syntax:

SELECT <columns> FROM <table> WHERE <predicate>

So the order of clauses follows naturally from the desire to have the language be English-like.

The controversial question is if it was a good idea striving for an English-like language. In my experience it makes it far more accessible for non-programmers, but makes it rather clunky for programmers. (Note that Linq have reversed the two first clauses to better support autocompletion. Obviously autocompletion did not exist at the time SQL was designed. Linq also supports composition much more elegantly. Then again Linq is not designed for use by non-programmers.)

  • SQL was initially developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s.[14] This version, initially called SEQUEL (Structured English Query Language), was designed to manipulate and retrieve data stored in IBM's original quasi-relational database management system from Wikipedia. So yes, looks like it has a ideomatic background. – Laiv Sep 3 '17 at 19:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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