Hacker News Reader

Comment by jrochkind1 | Hacker News Reader

Because SQL is so good at representing operations in the relational algebra that rdbms are based on, and such a mature technology, that it's awfully hard to replace it with something better? SQL and rdbms kind of go together, and always have -- it's the query language that parsimoniously represents what you can do to a relational source, whose development has happened in concert with rdbms themselves.

What don't you like about SQL?

View on HN

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.

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

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

Have you ever seen Arel?

https://github.com/rails/arel

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.

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

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

Reply on news.ycombinator.com