SQL Tricks(blog.jooq.org)
blog.jooq.org
SQL Tricks
https://blog.jooq.org/2016/04/25/10-sql-tricks-that-you-didnt-think-were-possible/
11 comments
So. Rather than having everything in one place (single view) you now integrated some "easier to understand" VB scripts in Microsoft Excel and tied that to the SQL Server?
This might have been easier for you at that very moment, but it doesn't sound like an easier to maintain solution...
Why not instead take your SQL skills to the next level? It will be very rewarding, and you won't look back to MS Excel based solutions.
This might have been easier for you at that very moment, but it doesn't sound like an easier to maintain solution...
Why not instead take your SQL skills to the next level? It will be very rewarding, and you won't look back to MS Excel based solutions.
When I have a program issuing SQL queries, I have to write both anyway, and it seems unlikely that either is going to be reused without the other. So I had to draw a line in dividing the work between my program (a Perl script) and the SQL queries it uses. Using simpler SQL queries and letting Perl sort out the rest resulted (IMHO) in lower total complexity.
The same goes for Pivot tables where using simpler queries and then organizing and processing the data in Excel again results in a simpler and more flexible solution in the sense that our controlling / accounting department can play around with the data themselves, not requiring any programming skills.
If I have to write a View for some external application, I have little choice but resort to more advanced techniques and readily do so. I never said it isn't fun. ;-)
But with SQL, as with all programming languages, I think avoiding unneccessary complexity is desirable. Sometimes using these advanced techniques makes things clearer, sometimes not.
The same goes for Pivot tables where using simpler queries and then organizing and processing the data in Excel again results in a simpler and more flexible solution in the sense that our controlling / accounting department can play around with the data themselves, not requiring any programming skills.
If I have to write a View for some external application, I have little choice but resort to more advanced techniques and readily do so. I never said it isn't fun. ;-)
But with SQL, as with all programming languages, I think avoiding unneccessary complexity is desirable. Sometimes using these advanced techniques makes things clearer, sometimes not.
Some of the claims made by the author are just false (or glib theatricity). For example, you can definitely calculate capped running total without vendor-specific grammar.
> Common Table Expressions (also: CTE, also referred to as subquery factoring, e.g. in Oracle) are the only way to declare variables in SQL (apart from the obscure WINDOW clause that only PostgreSQL and Sybase SQL Anywhere know).
Unless you're using all server which supports variables?
Not sure if I misunderstood what he's trying to say or not.
Unless you're using all server which supports variables?
Not sure if I misunderstood what he's trying to say or not.
That variable support isn't part of SQL, it's part of the higher query execution language -- for want of a better term -- made available by the RDBMS. Besides embedding them into a CTE or getting them from a predefined table(s), there's no way of getting arbitrary data (i.e., variables) into a SQL statement.
Local variable support is sort of part of SQL as it's part of the SQL/PSM Persistent Storage Module extension to SQL-92. Very few databases implement it according to the standard though.
The key thing that is important is that a recursive CTE allows for the retrieval of data from graph-like table schemas.
I'd sort of forgotten about this feature of SQL! But common table expressions and window functions actually keep the expressiveness of SQL and fill in gaps that just straight subselects, derived tables and joins alone can't always achieve.
Take for instance the rolling update he highlights. Before SQL Server got windowing functions (and boy, were Microsoft ever slow in implementing those in SQL Server!) the way to do a reasonable fast query (i.e. Not use cursors) was one a guy called Jeff Moden came up with in 2012:
http://www.sqlservercentral.com/articles/T-SQL/68467/
It relied on undocumented behaviour he observed in cluster indexes - so long as he could keep the order of rows returned by the database, he figured out a very fast method of doing a running total.
Obviously it was a hack and couldn't really be used, but it shows what folks can do when their favourite vendor doesn't supply critical features needed to process data in their favourite tool quickly :-)
I'd sort of forgotten about this feature of SQL! But common table expressions and window functions actually keep the expressiveness of SQL and fill in gaps that just straight subselects, derived tables and joins alone can't always achieve.
Take for instance the rolling update he highlights. Before SQL Server got windowing functions (and boy, were Microsoft ever slow in implementing those in SQL Server!) the way to do a reasonable fast query (i.e. Not use cursors) was one a guy called Jeff Moden came up with in 2012:
http://www.sqlservercentral.com/articles/T-SQL/68467/
It relied on undocumented behaviour he observed in cluster indexes - so long as he could keep the order of rows returned by the database, he figured out a very fast method of doing a running total.
Obviously it was a hack and couldn't really be used, but it shows what folks can do when their favourite vendor doesn't supply critical features needed to process data in their favourite tool quickly :-)
all server -> sql server
Auto corrected on my phone and can't edit :(
Auto corrected on my phone and can't edit :(
What a useful blog post! Thanks for sharing.
Interesting post - I love this idea that everything is a table and with INSERT, "you’re not really inserting individual rows. You’re really inserting entire tables".
Not really a trick in the spirit of the blog post, but I often catch people out with this, after telling them that it's legal to divide by zero in SQL:
SELECT *
FROM ThisTable
WHERE EXISTS
(
SELECT 1/0
FROM OtherTable
)[deleted]
What's this doing???
It's a query with an existence constraint, meaning that the query will only return data if at least 1 value is returned by the EXISTS predicate. In this case, you should get back all the data from ThisTable as long as there's a row in OtherTable.
But that's not the "tricky" bit. I'm using a feature of SQL that ignores the values in the SELECT clause when used as part of the EXISTS predicate. In the past, people used to type in the EXISTS predicate SELECT 1... because it was (wrongfully[1]) assumed to be faster than SELECT <column(s)>. This query proves that the query engine does not attempt to compute the SELECTed values. If it didn't, it would fail with a division by zero error.
[1] See Chapter 7.9:
But that's not the "tricky" bit. I'm using a feature of SQL that ignores the values in the SELECT clause when used as part of the EXISTS predicate. In the past, people used to type in the EXISTS predicate SELECT 1... because it was (wrongfully[1]) assumed to be faster than SELECT <column(s)>. This query proves that the query engine does not attempt to compute the SELECTed values. If it didn't, it would fail with a division by zero error.
[1] See Chapter 7.9:
If the <select list> "*" is simply contained in a <subquery> that
is immediately contained in an <exists predicate>, then the <select list> is
equivalent to a <value expression> that is an arbitrary <literal>.
As an aside, quite frankly I could never get EXISTS and NOT EXISTS to work in Qlikview. I honest wish Qlikview adopted a more SQL-like exists clause.
(P.S. I used to do SELECT 1 because it was shorthand... Funny how people thought it was a magic speed up!)
(P.S. I used to do SELECT 1 because it was shorthand... Funny how people thought it was a magic speed up!)
I've read a few good Jooq blog posts, and am becoming a convert to Jooq.
The tool seems to work well as a SQL DSL and helps me write testable code. Instead of an ORM that insists everything is an object, I can be honest and admit that I have a database which is a separate tool with its own features. And I have access to them all. Lovely.
The tool seems to work well as a SQL DSL and helps me write testable code. Instead of an ORM that insists everything is an object, I can be honest and admit that I have a database which is a separate tool with its own features. And I have access to them all. Lovely.
Agreed. The code I write with JOOQ is so much leaner, and easier to reason about than any ORM I've used. To me, JOOQ is a great example of what modern Java should look like.
My only complaint about JOOQ is that there isn't a .NET version.
My only complaint about JOOQ is that there isn't a .NET version.
CSS3 is also Turing-complete [1], so even while you can write a whole bunch of data processing stuff with SQL not means you should. There are indeed better tools for doing this, keep in mind what is "Q" in SQL.
[1] https://news.ycombinator.com/item?id=2300836
[1] https://news.ycombinator.com/item?id=2300836
Standard SQL sucks and this is Why...
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: http://www.timestored.com/b/standard-sql-sucks-and-this-is-w...
On another note I actually love jooq the java library and have reimplemented something similar for qSQL. If anyone from jooq is reading, I've made a free java snippet runner called jpad.io that I would be interested seeing used with jooq. I think your customers may like it.
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: http://www.timestored.com/b/standard-sql-sucks-and-this-is-w...
On another note I actually love jooq the java library and have reimplemented something similar for qSQL. If anyone from jooq is reading, I've made a free java snippet runner called jpad.io that I would be interested seeing used with jooq. I think your customers may like it.
You're probably right, and at the time SQL was "invented" (and standardised), a lot of people were right too. They wanted QUEL, they got SQL. But hey, the presentation (linked from the article) got a slide for you:
http://de.slideshare.net/LukasEder1/10-sql-tricks-that-you-d...
Sorry :)
http://de.slideshare.net/LukasEder1/10-sql-tricks-that-you-d...
Sorry :)
"SQL is the worst form of database querying, except for all the other forms."
LOL. As long as many people believe things like that, innovators can go out, learn new tools that will give them 10x more productivity and be able to make better things faster, delivering more business value.
In the words of Paul Graham http://www.paulgraham.com/avg.html
"But I don't expect to convince anyone (over 25) to go out and learn Lisp. The purpose of this article is not to change anyone's mind, but to reassure people already interested in using Lisp-- people who know that Lisp is a powerful language, but worry because it isn't widely used. In a competitive situation, that's an advantage. Lisp's power is multiplied by the fact that your competitors don't get it."
LOL. As long as many people believe things like that, innovators can go out, learn new tools that will give them 10x more productivity and be able to make better things faster, delivering more business value.
In the words of Paul Graham http://www.paulgraham.com/avg.html
"But I don't expect to convince anyone (over 25) to go out and learn Lisp. The purpose of this article is not to change anyone's mind, but to reassure people already interested in using Lisp-- people who know that Lisp is a powerful language, but worry because it isn't widely used. In a competitive situation, that's an advantage. Lisp's power is multiplied by the fact that your competitors don't get it."
>LOL. As long as many people believe things like that, innovators can go out, learn new tools that will give them 10x more productivity and be able to make better things faster, delivering more business value.
LOL. These "Innovators" are just reinventing the square wheel, only in "webscale" mode.
We had all these tools and novelties (including "nosql") back in the 70s and 80s (and part of the 90s), they sucked, we dropped them.
Seems people haven't even learned enough from the early Mongo craze which ended up with tons of teams finding out Postgres is still faster, safer AND more feature full.
>"But I don't expect to convince anyone (over 25) to go out and learn Lisp. The purpose of this article is not to change anyone's mind, but to reassure people already interested in using Lisp-- people who know that Lisp is a powerful language, but worry because it isn't widely used. In a competitive situation, that's an advantage. Lisp's power is multiplied by the fact that your competitors don't get it."
Only the LISP in this situation is SQL -- not those "10x innovations".
LOL. These "Innovators" are just reinventing the square wheel, only in "webscale" mode.
We had all these tools and novelties (including "nosql") back in the 70s and 80s (and part of the 90s), they sucked, we dropped them.
Seems people haven't even learned enough from the early Mongo craze which ended up with tons of teams finding out Postgres is still faster, safer AND more feature full.
>"But I don't expect to convince anyone (over 25) to go out and learn Lisp. The purpose of this article is not to change anyone's mind, but to reassure people already interested in using Lisp-- people who know that Lisp is a powerful language, but worry because it isn't widely used. In a competitive situation, that's an advantage. Lisp's power is multiplied by the fact that your competitors don't get it."
Only the LISP in this situation is SQL -- not those "10x innovations".
I know that article, it's excellent. And I like Paul Graham's point of view, usually. But that article addresses (and will ever only address) the "elite". Like QUEL, which didn't make it. SQL did.
This doesn't have too much to do with innovation. It has everything to do with product-market-fit. SQL is the technology for the masses. QUEL (or something else more recent, very lean and sophisticated) is the technology for the elite. But why doesn't the "elite thing" go mainstream? Wrong product-market-fit.
This doesn't have too much to do with innovation. It has everything to do with product-market-fit. SQL is the technology for the masses. QUEL (or something else more recent, very lean and sophisticated) is the technology for the elite. But why doesn't the "elite thing" go mainstream? Wrong product-market-fit.
QUEL's problem was not that it was for the elite but that Relational Technology (later named Ingres) did not have any sense on how to market and sell Ingres RDBMS and promote QUEL. Oracle's strength has always been marketing and sales and they started to take over the market quickly from Ingres in the first few years of commercial RDBMS.
Yes there were other players in the market at the time but in the very early days it really was Ingres versus Oracle.
For those of us that are older we know that Ingres was a result of Dr. Michael Stonebraker's work at Berkeley. There was at the time a university project known as Ingres and a commercial product known as Ingres. Yes postgres is what happened at Berkeley after Ingres.
I always loved QUEL and was downright pissed when it was ignored over the inferior SQL.
Yes there were other players in the market at the time but in the very early days it really was Ingres versus Oracle.
For those of us that are older we know that Ingres was a result of Dr. Michael Stonebraker's work at Berkeley. There was at the time a university project known as Ingres and a commercial product known as Ingres. Yes postgres is what happened at Berkeley after Ingres.
I always loved QUEL and was downright pissed when it was ignored over the inferior SQL.
We kind of agree, but from a different angle. I see a product in its entirety: Technology + Community. The latter should extend beyond the technology-affine end user. As you mentioned, Oracle was always better at that.
I wasn't alive back then, I only know from listening to the stories. But I'd say that most Oracle customers are rather happy being Oracle customers, while only a select "elite" regrets what happened back then.
I wasn't alive back then, I only know from listening to the stories. But I'd say that most Oracle customers are rather happy being Oracle customers, while only a select "elite" regrets what happened back then.
My memory is failing me at 62 but back then the Ingres community was much more positive about the product than the Oracle community. At one point I was president of the North American Ingres User Group (Association, what did we cann it?). I had moved from using Oracle version 1.x to Ingres 1.x in the beginning because Oracle was so bad. I think that was about 1982 or 1983.
But the company (Relational Technology) just had no idea what they had and how to sell it.
I'm an Oracle customer today because my shop has a site license. I would use mysql for what I need an RDBMS for today (very light, low volume usage). Oracle is overkill for my work. But it is always available and there are advanced features should I need them.
But the company (Relational Technology) just had no idea what they had and how to sell it.
I'm an Oracle customer today because my shop has a site license. I would use mysql for what I need an RDBMS for today (very light, low volume usage). Oracle is overkill for my work. But it is always available and there are advanced features should I need them.
VHS x Betamax competition was also cruel...
Could JOOQ be considered a kind of language for the "elite" in that respect?
Those that want the extra power to write queries quickly with type safety etc instead of manually crafting the java/sql interface. That extra speed was one of the reasons I looked at JOOQ.
I do like your idea, I guess I've made similar judgement calls in the past. I know some mainstream languages for the popularity power it gives me but for use cases where I think I can find a technical advantage I seek out new ideas/languages for that edge.
Those that want the extra power to write queries quickly with type safety etc instead of manually crafting the java/sql interface. That extra speed was one of the reasons I looked at JOOQ.
I do like your idea, I guess I've made similar judgement calls in the past. I know some mainstream languages for the popularity power it gives me but for use cases where I think I can find a technical advantage I seek out new ideas/languages for that edge.
Hmm, I haven't thought about it this way.
But I don't think so. While jOOQ does enable a lot of "advanced" language features (like row level type safety across languages, and streaming of such tuples in Java 8 Streams), I still think that it mainly addresses the "masses" - at least those "masses" that profit from a slightly more sophisticated SQL integration than just boring CRUD.
The examples exposed in the linked article certainly aren't every day examples for the masses.
But I personally absolutely agree with you. There are technologies that do enable orders of magnitude in development efficiency. For instance, I'd love to work with Clojure and Datomic. I have a huge respect for Rich Hickey. But I don't think that everyone should, or ever will, use these technologies. In the end, when you have to do recruiting, from your local market, and you need 100 developers, choose Clojure and you'll be screwed.
But I don't think so. While jOOQ does enable a lot of "advanced" language features (like row level type safety across languages, and streaming of such tuples in Java 8 Streams), I still think that it mainly addresses the "masses" - at least those "masses" that profit from a slightly more sophisticated SQL integration than just boring CRUD.
The examples exposed in the linked article certainly aren't every day examples for the masses.
But I personally absolutely agree with you. There are technologies that do enable orders of magnitude in development efficiency. For instance, I'd love to work with Clojure and Datomic. I have a huge respect for Rich Hickey. But I don't think that everyone should, or ever will, use these technologies. In the end, when you have to do recruiting, from your local market, and you need 100 developers, choose Clojure and you'll be screwed.
matt4077(1)
"SQL is the worst form of database querying, except for all the other forms."
IMO, if you think of Prolog(s) as a database(s), that model is far more enjoyable to work with than SQL.
IMO, if you think of Prolog(s) as a database(s), that model is far more enjoyable to work with than SQL.
Yes, or Datomic in particular is very nice, too. But there's a difference between "enjoyable" and "good" in a general sense.
People actually wanted QUEL? There is nothing of value there. Maybe this is a hindsight bias, but it was less powerful than SQL in pretty much every way possible. The only thing SQL got irrevocably wrong from the start was null handling which forced us into non-intuitive ternary logic that couldn't be deprecated because of backwards compatibility.
SQL gets a bad reputation from programmers that have a strictly procedural understanding of problem solving.
When you are dealing with large amounts of data a SET based approach to data retrieval will likely be the better solution. The problem often comes from trying to bridge the procedural world with the set based world.
SQL also gets a bad reputation from programmers that have a SET based approach to data too, because it's horribly uncomposable.
When I try and solve a problem with SQL, it's generally trivial to produce a solution in relational algebra pseudocode , and convert that in turn into a deeeeeeeply nested, horribly nonperformant almost SQL query. Then I have to go through to try and unnest it, fix all the little "gotchas" involving "GROUP BY" and "HAVING" and implicit aliasing, and somehow make it performant (Admittedly, that last one is usually the DB optimizer's fault.)
Relational algebra is a beautiful way to answer many questions. The issue is SQL isn't a very good way of writing it.
When I try and solve a problem with SQL, it's generally trivial to produce a solution in relational algebra pseudocode , and convert that in turn into a deeeeeeeply nested, horribly nonperformant almost SQL query. Then I have to go through to try and unnest it, fix all the little "gotchas" involving "GROUP BY" and "HAVING" and implicit aliasing, and somehow make it performant (Admittedly, that last one is usually the DB optimizer's fault.)
Relational algebra is a beautiful way to answer many questions. The issue is SQL isn't a very good way of writing it.
and convert that in turn into a deeeeeeeply nested
I find that CTEs are the solution to deep nesting. There are very few times that I haven't been able to reduce the amount of nesting down to 2 levels (just the a single query with a subquery as part of an exists or something).
That's obviously a problem if you don't have access to CTEs (mysql) or CTEs are an optimization boundary (postgres), but if you're working with a product that does not have those issues, then CTEs can really clean up a lot of sql.
I find that CTEs are the solution to deep nesting. There are very few times that I haven't been able to reduce the amount of nesting down to 2 levels (just the a single query with a subquery as part of an exists or something).
That's obviously a problem if you don't have access to CTEs (mysql) or CTEs are an optimization boundary (postgres), but if you're working with a product that does not have those issues, then CTEs can really clean up a lot of sql.
In the spirit of the post...instead of:
SELECT COUNT(DISTINCT(Some_Column)) FROM Some_Table
try:
SELECT COUNT(X.Some_Column) FROM ( SELECT Some_Column , MAX(Some_Other_Column_Which_Can_Be_Maxed) [MaxValue] FROM Some_Table GROUP BY Some_Column ) AS X
SELECT COUNT(DISTINCT(Some_Column)) FROM Some_Table
try:
SELECT COUNT(X.Some_Column) FROM ( SELECT Some_Column , MAX(Some_Other_Column_Which_Can_Be_Maxed) [MaxValue] FROM Some_Table GROUP BY Some_Column ) AS X
Why? I can't think of any reason that should be faster. If it is, abandon your database for something reasonable.
Knew most/all of these tricks already, but it's a good summary of some useful tricks.
Just one thing...
"Common Table Expressions (also: CTE, also referred to as subquery factoring, e.g. in Oracle) are the only way to declare variables in SQL (apart from the obscure WINDOW clause that only PostgreSQL and Sybase SQL Anywhere know)."
Can't say anything about other vendors, but in T-SQL (a.k.a. MS SQL) creating variables is easily done via the DECLARE keyword, e.g. DECLARE @x INT = 4; . You can create a number of structures this way, including temporary tables (there are few different types of temporary tables, but the ones specified with DECLARE are specific to the scope in which they were defined in).
Does the DECLARE keyword (or something similar) not exist within any of the SQL standards?
Just one thing...
"Common Table Expressions (also: CTE, also referred to as subquery factoring, e.g. in Oracle) are the only way to declare variables in SQL (apart from the obscure WINDOW clause that only PostgreSQL and Sybase SQL Anywhere know)."
Can't say anything about other vendors, but in T-SQL (a.k.a. MS SQL) creating variables is easily done via the DECLARE keyword, e.g. DECLARE @x INT = 4; . You can create a number of structures this way, including temporary tables (there are few different types of temporary tables, but the ones specified with DECLARE are specific to the scope in which they were defined in).
Does the DECLARE keyword (or something similar) not exist within any of the SQL standards?
CTEs aren't for creating a simple value. They are for creating a table variable.
I know what CTEs are for (in practical terms they're useful for subqueries you need to reference more than once), and like I said before, you can define temporary tables with the DECLARE keyword. To give you an example, this is valid T-SQL code...
DECLARE @temptable TABLE (id INT, fulldescription NVARCHAR(MAX));
DECLARE @temptable TABLE (id INT, fulldescription NVARCHAR(MAX));
It wasn't even that it was so exceptionally clever, but the SQL code made my eyes bleed at times.
So while I know basic SQL fairly well, I am by no means a wizard, and I tend more and more to keep the queries I write as simple as possible and to do the heavy lifting on the client side. Obviously, there are limits - to get the sum of a column, for example, it makes much more sense to do that on the server instead of transferring lots of data to the client and have it compute the sum.
As soon as I want to do more sophisticated stuff, my queries tend to get fairly ugly, and some day some other poor soul might have to look at and understand the code. I'd rather write a simple query and do the more complicated transformations using a PivotTable in Excel.
So while it's nice to know that there are these tricks, I try not to use them (not more than I have, anyway).