4 ms·
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, a
by rm999 8y ago
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.
- deleted 8y ago[deleted]
- MarkusWinand 8y ago> For one, there's no official specification I'd take ISO/IEC 9075 as an official specification: https://webstore.iec.ch/publication/59685 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.
- rm999 8y ago>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.
- MarkusWinand 8y agoAh, "dozen of extra lines" is not exactly what I was expecting for "isn't as powerful" ;)
- TomMarius 8y agoDozen of extra lines most probably means it's also suboptimally executed.
- MechanicalTwerk 8y agoI don't know...optimizers are pretty good at rewriting and planning nowadays.
- kthejoker2 8y agoDeclarative 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.
- nodelessness 8y agoBy that logic, is there anything at all in the realm of APIs and tools that is standardized then? HTML mark up? Regular expressions?
- cglace 8y agoThere are things I can do in SQL in one line that would take dozens in python. I don't see your point.
- Jedd 8y ago> > > 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).
- TomMarius 8y ago200 CHF for a programming language specification? Is it 1995?
- atombender 8y agoAs 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.
- ah- 8y agokdbs q-sql does improve a lot on that. Especially expressiveness. https://code.kx.com/q/ref/qsql/ https://code.kx.com/q/ref/qsql/ http://www.timestored.com/b/kdb-qsql-query-vs-sql/ http://www.timestored.com/b/kdb-qsql-query-vs-sql/
- olavk 8y agoYeah, 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.
- MarkusWinand 8y agoSQL 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.
- atombender 8y agoWell, I am explicitly talking about syntax.
- ccleve 8y ago