When I started using SQL as a young data analyst, I learnt all the typical keywords fairly quickly. SELECT, JOIN, WHERE, GROUP BY, HAVING – these all helped solve problems and manipulate my data before exporting it somewhere else.
However, I felt hamstrung when queries got a bit complex or unwieldy and was needing to export to Python or Excel to solve problems I thought should be solvable in SQL. A smart analyst1 showed me how I could tackle these.
I think of the WITH keyword as similar to a helper column in Excel, or a function you would define in Python or any other programming language.
It allows you to store a temporary view of data that you can access further along the query. I’ve seen three primary benefits to using this:
It makes your SQL much, much easier to read; It can avoid repeated code written in subqueries; and It expands your potential solution space, allowing you to more easily solve problems in SQL. Using WITH is never strictly necessary as far as I know. All SQL problems could technically be solved without it.
But this is the same as having an Excel formula going over 3 lines, or not using classes or functions when programming.
It is hard to audit, error-prone and resistant to improvement.
SQL code should be easy to read – not just for others but also for yourself. This is simply good hygiene that will help you better think through problems, write better code, and communicate to your team and business.
-
Thanks Tan for being that smart analyst showing me better SQL! ↩
