SQL Tricks

https://blog.jooq.org/2016/04/25/10-sql-tricks-that-you-didnt-think-were-possible/

by mariuz • 10 years ago

182 40 10 years ago

blog.jooq.org

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• 10 years ago

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• 10 years ago

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.

philliphaydon2• 10 years ago

all server -> sql server

Auto corrected on my phone and can't edit :(

Dowwie• 10 years ago

What a useful blog post! Thanks for sharing.

groundCode• 10 years ago

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

ilitirit2• 10 years ago

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• 10 years ago

What's this doing???

ilitirit2• 10 years ago

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

delibes• 10 years ago

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• 10 years ago

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.

batat• 10 years ago

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

RyanHamilton3• 10 years ago

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.

lukaseder6• 10 years ago

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

RyanHamilton3• 10 years ago

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

saosebastiao2• 10 years ago

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.

treebeard901• 10 years ago

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.

akavi• 10 years ago

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.

snake_plissken• 10 years ago

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

saosebastiao2• 10 years ago

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

ZenoArrow2• 10 years ago

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• 10 years ago

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

ZenoArrow2• 10 years ago

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

ktamura• 10 years ago

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.

krylon2• 10 years ago

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

lukaseder6• 10 years ago

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.

krylon2• 10 years ago

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.

SQL Tricks

Related Stories