Hacker News Reader

Comment by rm999 | Hacker News Reader

SQL is by no means perfect. For one, t̶h̶e̶r̶e̶'̶s̶ ̶n̶o̶ ̶o̶f̶f̶i̶c̶i̶a̶l̶ ̶s̶p̶e̶c̶i̶f̶i̶c̶a̶t̶i̶o̶n̶: some dialects are meaningfully different than others, and even similar ones are often full of implementation details about the underlying database. It has a ton of quirks, and isn't as powerful as I'd always want it to be. And sometimes it can be really hard to read.

But I still haven't found a better universal "language" to talk about extracting and manipulating data. The core set of functionality of SQL (selects, joins, aggregations) is usually adequate to express powerful ideas. Most importantly, a lot of people who work with data know it, and I've found semi-technical people (including non-programmers) can learn it quickly. Anyone who has been paying attention to trends in the data world in the last 5 years knows SQL is here to stay for the long-term.

View on HN

> For one, there's no official specification

I'd take ISO/IEC 9075 as an official specification: https://webstore.iec.ch/publication/59685

> and isn't as powerful as I'd always want it to be

Do you have examples? There is a lot in SQL that is not commonly known—if you let me know what you are thinking of, I might be able to show you an adequate SQL feature.

>I'd take ISO/IEC 9075 as an official specification

Fair enough, but I'd argue it's not a de facto standard because the vast majority of implementers don't follow it. Case in point: SQL is rarely portable across databases, even if you're e.g. moving from postgres to a postgres-like system like bigquery or redshift.

>Do you have examples?

For me (as a machine learning + data engineer) it often comes up around aggregating with window functions. Things that would take a couple lines in R or Python or Scala can take dozen of extra lines with superfluous CTEs.

Ah, "dozen of extra lines" is not exactly what I was expecting for "isn't as powerful" ;)

Dozen of extra lines most probably means it's also suboptimally executed.

I don't know...optimizers are pretty good at rewriting and planning nowadays.

Declarative languages are almost always more verbose than imperative languages. Not sure why you'd use LOC to compare across them when they have readily available execution engine planners with stats and everything.

By that logic, is there anything at all in the realm of APIs and tools that is standardized then? HTML mark up? Regular expressions?

There are things I can do in SQL in one line that would take dozens in python. I don't see your point.

> > > For one, there's no official specification.

> > I'd take ISO/IEC 9075 as an official specification

> Fair enough, but I'd argue it's not a de facto standard...

It may be useful to understand the distinction between official (de jure) and common usage (de facto).

200 CHF for a programming language specification? Is it 1995?

As for myself, I wish SQL's SELECT were more expressive. It's currently organized almost exactly like a mirror of the underlying relational algebra operators that a query engine has to execute internally.

But relational operators isn't what developers like to work with. SQL is about "projecting" relations, putting them through some basic transforms, none of which compose that well because of SQL's statement-oriented syntax. For example, you can't group a group-by. A group-by takes a list of terms, but you can't funnel the output through another one without wrapping the whole query in a "SELECT FROM (...) AS ...".

I'd like a query language that more expressive, expression-based syntax where the results of each expression can be "piped" into the next step to perform transformations, joins, reductions and so on.

kdbs q-sql does improve a lot on that. Especially expressiveness.

https://code.kx.com/q/ref/qsql/ http://www.timestored.com/b/kdb-qsql-query-vs-sql/

Yeah, this is my main beef with SQL. The syntax is just clunky when you want to compose operations. Linq does this much better.

With-clauses (aka Common Table Expressions) improves this a lot, but it would be better still if you could just write:

FROM <table> WHERE <filter> SELECT <projection> GROUP BY <groupings> WHERE <filter> SELECT <projection> and so on.

SQL uses nesting as you have shown instead of a "pipe" (think of it like functional programming).

The difference is just the syntax, not the expressiveness.

Well, I am explicitly talking about syntax.

I often use temporary tables to hold intermediate results. Does that not work for you?

> I'd like a query language that more expressive, expression-based syntax where the results of each expression can be "piped" into the next step to perform transformations, joins, reductions and so on.

I do not think this can be easily fixed in SQL because the problems are not in the syntax but rather lie deeper at the level of the underlying paradigm and principles.

Reply on news.ycombinator.com