7 ms·

SQL Tricks

182 points by mariuz 11y ago

philliphaydon 11y ago
> 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 11y 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 11y 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.
    - chris_wot 11y ago
      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 :-)
- philliphaydon 11y ago
  all server -> sql server Auto corrected on my phone and can't edit :(
Dowwie 11y ago
What a useful blog post! Thanks for sharing.
groundCode 11y 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".
ilitirit 11y 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 )
- deleted 11y ago
  [deleted]
- mistermann 11y ago
  What's this doing???
  - ilitirit 11y 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 http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
    - chris_wot 11y ago
      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!)
delibes 11y 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 11y 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 11y 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 https://news.ycombinator.com/item?id=2300836
RyanHamilton 11y 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-why/ 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.
- lukaseder 11y 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-didnt-think-were-possible/35 http://de.slideshare.net/LukasEder1/10-sql-tricks-that-you-d... Sorry :)
  - RyanHamilton 11y 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 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 11y ago
      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.
snake_plissken 11y 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
- saosebastiao 11y ago
  Why? I can't think of any reason that should be faster. If it is, abandon your database for something reasonable.
ZenoArrow 11y 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 11y ago
  CTEs aren't for creating a simple value. They are for creating a table variable.
  - ZenoArrow 11y 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 11y 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.
krylon 11y 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).
- lukaseder 11y 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.
  - krylon 11y 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.

182 points by mariuz 11y ago

> 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 11y 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 11y 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.
  - chris_wot 11y ago
    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 :-)
philliphaydon 11y ago
all server -> sql server Auto corrected on my phone and can't edit :(

Dowwie 11y ago

What a useful blog post! Thanks for sharing.

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

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

deleted 11y ago
[deleted]
mistermann 11y ago
What's this doing???
- ilitirit 11y 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 http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
  - chris_wot 11y ago
    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!)

delibes 11y 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 11y 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 11y 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 https://news.ycombinator.com/item?id=2300836

RyanHamilton 11y 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-why/ 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.

lukaseder 11y 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-didnt-think-were-possible/35 http://de.slideshare.net/LukasEder1/10-sql-tricks-that-you-d... Sorry :)
- RyanHamilton 11y 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 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 11y ago
    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.

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

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

ZenoArrow 11y 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 11y ago
CTEs aren't for creating a simple value. They are for creating a table variable.
- ZenoArrow 11y 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 11y 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.

krylon 11y 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).

lukaseder 11y 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.
- krylon 11y 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.