
SQL Tricks - mariuz
https://blog.jooq.org/2016/04/25/10-sql-tricks-that-you-didnt-think-were-possible/
======
RyanHamilton
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...](http://www.timestored.com/b/standard-sql-sucks-and-this-is-why/)

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.

~~~
lukaseder
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...](http://de.slideshare.net/LukasEder1/10-sql-tricks-that-you-didnt-
think-were-possible/35)

Sorry :)

~~~
RyanHamilton
"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](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."

~~~
lukaseder
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.

~~~
epalmer
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.

~~~
lukaseder
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.

~~~
epalmer
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.

------
batat
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](https://news.ycombinator.com/item?id=2300836)

------
delibes
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.

~~~
matrix
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.

------
groundCode
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".

------
krylon
After a coworker left, I was - among other things - put in charge of
maintaining a bunch of database views he added to our ERP system to do some
reports (on Microsoft SQL Server, in case it matters).

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).

~~~
lukaseder
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.

~~~
krylon
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.

------
ZenoArrow
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?

~~~
pohart
CTEs aren't for creating a simple value. They are for creating a table
variable.

~~~
ZenoArrow
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));

------
philliphaydon
> 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.

~~~
Xophmeister
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.

~~~
onion2k
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.

~~~
chris_wot
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/](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 :-)

------
ilitirit
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
        )

~~~
mistermann
What's this doing???

~~~
ilitirit
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:

    
    
        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>.
    

[http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt](http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt)

~~~
chris_wot
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!)

------
snake_plissken
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

~~~
saosebastiao
Why? I can't think of any reason that should be faster. If it is, abandon your
database for something reasonable.

------
Dowwie
What a useful blog post! Thanks for sharing.

------
ktamura
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.

