SQL is 43 years old – Here’s why we still use it today

Re:SQL is 43 years old – Here’s why we still use it today by wolfgang42 on Tuesday May 02, 2017 @11:04AM (#14246924)

Not OP, but I find the syntax to be arcane and bizzarely restrictive. Every query has to be SELECT FROM JOIN WHERE GROUP BY HAVING ORDER BY; for some reason the table comes after its fields and the ORDER BY can't be expressed using field aliases defined at the start of the query.

Instead, I've been toying with the idea of a language where queries are expressed as a pipeline of operations on result sets, like this:

    FROM employee
      /* result set: all employees */
    LEFT JOIN department ON employee.DepartmentID = department.DepartmentID
      /* result set: all employees + their department */
    ORDER BY employee.last
      /* result set: all employees + their department, sorted */
    WHERE department.name IN ('Sales', 'Engineering')
      /* result set: Sales and Engineering employees + their department, sorted */
    FIELDS employee.first, employee.last, department.name
      /* result set: Sales and Engineering employees' first, last, and department names, sorted */

This is a SELECT, but you can for example turn it into an update just by adding a clause to the end:

    UPDATE employee.salary = employee.salary * 1.05

This is a much more regular language; there's nothing enforcing you to write these operations in a certain order, and you can add or remove clauses as necessary. For complex queries, I find that I write in this style anyway, using a chain of WITH clauses as a pipeline with a final SELECT on the end to get the results.

[ Parent | on HN ]

Re:SQL is 43 years old – Here’s why we still use it today by combatentropy on Wednesday May 03, 2017 @02:56AM (#14253591)

  > for some reason the table comes after its fields

I agree it would have been better to start with the FROM clause and have the fields listed near the end.

  > I write in this style anyway,
  > using a chain of WITH clauses

That sounds like it could make it better. I will try that more often.

  > the ORDER BY can't be expressed using field aliases
  > defined at the start of the query

In Postgres you can!

[ Parent | on HN ]

Re:SQL is 43 years old – Here’s why we still use it today by libria on Tuesday May 02, 2017 @12:36PM (#14247892)

This reads very much like LINQ query syntax:

    from e in employee
    join d in department on e.DepartmentID equals d.DepartmentID
    where new string[]{ 'Sales', 'Engineering' }.Contains(d.name)
    orderby e.last
    select e.first, e.last, d.name

Forgive any errors, it's been a while.

[ Parent | on HN ]

Re:SQL is 43 years old – Here’s why we still use it today by jrochkind1 on Tuesday May 02, 2017 @11:25AM (#14247126)

So it's just like SQL but with the order of statements being more flexible? Does not seem entirely unreasonable.

[ Parent | on HN ]

Re:SQL is 43 years old – Here’s why we still use it today by wolfgang42 on Tuesday May 02, 2017 @11:55AM (#14247448)

Basically yes, though I'd also make it so if you alias a field it's referred to by the alias from then on (e.g. FIELDS foo AS bar ORDER BY bar instead of SELECT foo AS bar ORDER BY foo). I'd probably be inclined to rename some of the keywords as well (SORT, FILTER, MAP, REDUCE perhaps).

There are some query builder libraries that let you specify clauses in a different order, like "query.from(table).where(col, value).select([...fields])", but I have yet to find one that lets you specify the query as a series of transformations on tables. (I think the word I'm looking for is "isomorphic" but that doesn't seem right somehow.)

[ Parent | on HN ]

Re:SQL is 43 years old – Here’s why we still use it today by stephenhuey on Tuesday May 02, 2017 @01:11PM (#14248277)

Have you ever seen Arel?

https://github.com/rails/arel

[ Parent | on HN ]

Re:SQL is 43 years old – Here’s why we still use it today by remotehack on Tuesday May 02, 2017 @10:25PM (#14252545)

> I've been toying with the idea of a language where queries are expressed as a pipeline of operations on result sets

That is what the relational database engine does in the first place. In fact, many really smart people have figured out how to make the engine really good at it and SQL is the best for working with it.

[ Parent | on HN ]