4 ms·

I came expecting a treatment on Structured Query Language, was disappointed when it turned out to be on Relational Database Management Systems. It doesn't take

• jrochkind1 10y ago
  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?
  • wolfgang42 10y ago
    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.
    • jrochkind1 10y ago
      So it's just like SQL but with the order of statements being more flexible? Does not seem entirely unreasonable.
      • wolfgang42 10y ago
        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.)
        • stephenhuey 10y ago
          Have you ever seen Arel? https://github.com/rails/arel https://github.com/rails/arel
    • libria 10y ago
      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.
    • remotehack 10y ago
      > 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.
    • combatentropy 10y ago
• spacemanmatt 10y ago
  Thing is, SQL maps fairly directly to the fundamentals operations defined on Relations in their rigorous sense. Every abstraction of those operations is going to have a similar form to SQL unless it seriously over-complicates things.