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.
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:
Writing SQL with SQL example INPUT.
+'''AS VARCHAR(50)), COUNT(1) FROM BADGES WHERE BADGES.NAME = '''
I write hundreds of lines of SQL every day, and that’s just for my day job. This repetition has allowed me to realize some exceptional tricks. Simple SQL tricks which help me write cleaner code, code that is easier to troubleshoot, guarantees more accurate data sets and is easier to manage as the query grows. So, here are three SQL tricks that I consistently use in every query I write:
SQL Tricks WHERE 1 = 1
This is one of my favorite SQL tricks that always causes people confusion when looking at my queries. I’ll reiterate this again later, but this is NOT a production ready trick, this is merely a tool to help make troubleshooting easier.
Let’s chat about the distinct vs group by clause: I often see the misuse of GROUP BY with no aggregates when a DISTINCT would suffice. However, I more often see the misuse of a DISTINCT when a GROUP BY is more practical. I wanted to talk about some, maybe not-so-obvious, reasons to use one over the other. I won’t go into what the functional differences are, but more so the practical differences.
But first, is there really a technological difference between using DISTINCT vs a GROUP BY (sans aggregates, of course)? The quick answer is that it depends on the system, there could be differences between the optimizer on one of the many DBMS out there. However, in my tests, the likely more accurate answer is simply, No.
Distinct vs Group By (sans aggregates)
These two queries not only produce the exact same result set, they produce the exact same query plans too. To see for yourself, take a look here. (Tested on SQL Server, Oracle, and Teradata)
This blog will be my virtual valet of knowledge that oozes out from time to time. I wanted a place that I could share my thoughts as my career advances and confidently build a platform to represent who I am, what I do and my thoughts on the subject of data & analytics.
Throughout my storied career in IT, I’ve made lots of mistakes. It’s those mistakes that have made me the arrogant jackass subject matter expert I am today. Working with seasoned rock stars, high-priced consultants, and everyone in between (You see what I did there?). So, it’s not a problem that you’re doing it wrong; the problem is that hopefully, I’m not.
The posts you’ll read on this site will be about all the dialogue that comes from us trying our best, failing early, and failing often.
Hope you enjoy it. Now, it’s time to fill this mug with some content!