Generate SQL with SQL?

I’m aware that many of my readers of this site likely already know how to write sql to generate sql, or sql generators, but it’s not really much of a data blog if I don’t mention it. It’s also important to mention the time value this trick represents, with a few minutes of time, you can generate thousands of lines of code that would otherwise take hours to write. Let’s chat about one of the first things I learned about SQL which, no doubt, blew my mind:

Generate SQL with SQL.

Generate SQL with SQL via database commands.
Generate SQL with SQL via database commands.

Or to put another way, generating SQL statements via a single SQL command. Really cool stuff if you haven’t seen this before, keep reading!

Let’s say you wanted to find the number of badges for each type of badge in a table of badges. This is quite easy with a simple query, but let’s see how we can do it by writing SQL with SQL:

This example can be seen in the wild here for which that output can be observed as follows:

You’ll see that the output of the first query provides the input of the second query, which ultimately gives the result we’re looking for.

While this is a simple task to solve by itself with an aggregate, this is the best example I can come up using the query editor at stack exchange. I assure you this is a trick that I use to save countless minutes, hours, days of time… A really great example is using the system tables of your DBMS. One that I use quite often is the ‘columns’ system table which allows you to generate some really creative SQL that is a huge timesaver. Here’s a real-world Teradata example:

Play around with the examples given and leave a comment below with any questions you have, I’m always fascinated by SQL generators, how do you use them to help you?