Standard SQL sucks and this is Why
Recently there was a post on SQL tips by the JOOQ guys. I love their work but I think standard SQL is not the solution to many of these problems. What we need is something new or in this case old, that is built for such queries. What do I mean, well let’s look through their examples reimplemented in qsql and I’ll show you how much shorter and simpler this could be.
Everything is a table
In kdb we take this a step further and make tables standard variables, no special notation/treatment, it’s a variable like any other variable in your programming language. Instead of messing about with value()() we define a concise notation to define our variables like so:
Data Generation with Recursive SQL
This is the example syntax they have used to define two tables and then join them:
What to hell! If I want variables, let’s have proper variables NOT “Common Table Expressions”.
I created two tables a and b then I joined them sideways. See how simple that was.
Running Total Calculations
Oh dear SQL how badly you have chosen your examples. Running calculations are to APL/qSQL as singing is to Tom Jones, we do it everyday all day and we like it. In fact the example doesn’t even give the full code. See this SO post for how these things get implemented. e.g. Standard SQL Running Sum
qSQL table Definition and Running Sum:
Finding the Length of a Series
This is their code:
This is KDB:
In 1974 Ken Iverson gave a talk on APL. He described how he reduced it down to a core set of operations that everything could be made from. Using these simple building blocks you could make some really cool things. It’s sad to think we may not have came that far.
qSQL/kdb is a database based on the concept of ordered lists, carrying over many ideas from APL that make array operations shorter and simpler. If you like what you see we provide tutorials on kdb to get started, this intro is a good place to get started.
We also have free online kdb training for students.
Other examples comparing qSQL vs SQL including:
– top n group by
– price difference between rows
– time based joins
Can be seen here: http://www.timestored.com/b/kdb-qsql-query-vs-sql/
Conciseness is a cardinal virtue in your APL-based theology, but I don’t share your religious conviction. It’s terrible form for you to lambaste SQL for its poor examples when you created the examples yourself to make your own point. Just as you state, qsql is certainly based on the concept of ordered lists. But neither SQL nor the relational model apply any intrinsic order to anything other than columns in a table. So that and qsql’s APL-ish use of punctuation instead of SQL’s verbal syntax tokens are really just differences. It would have been more frank of you to title this post “My Prime Examples of Why KDB qsql is Superior to Standard SQL”
Hi Andrew,
I never choose the examples, I used the same queries as from the blog article: https://blog.jooq.org/2016/04/25/10-sql-tricks-that-you-didnt-think-were-possible/
I’d be interested in having a wager that the most common SQL queries could be made “nicer” in qsql. We could for example take the most common problems on stackoverflow and do them in both languages?