This series of articles
When developing systems we often have a choice of writing code (NodeJS, C#, Python or PHP etc) or SQL. I believe that sometimes the decision to write code is taken without fully evaluating how much of the task could be offloaded to SQL.
In this series of articles I wish to show the huge benefits of using and learning SQL by examining progressively more difficult scenarios with increasing amounts of SQL knowledge. In doing this I hope to illustrate that sometimes large amounts of extra code is written for what SQL can achieve quicker, with less complexity and more readability.
To be more specific, we should try to follow the rule of least power more often.
About the Ergast data set
For this series of articles we will be using the Ergast data set, which is a provided under the Attribution-NonCommercial-ShareAlike 3.0 Unported Licence.
Where we are
We saw in the previous article how we could select rows from one table using a condition on another table. In doing this we noticed that the SQL we created did not give a name to the section of code within the
For the next few articles our SQL will be simple enough that we will not feel a great need to name sections of code. At some point however, as the complexity ramps up, we’re going to want to start naming sections of our SQL to enhance readability.
The original SQL
We would like to give a name to the section of code within the
IN (...) clause.
Common Table Expressions
Functions in code are magical, yes they have a name, take parameters and give you back a result, but the amazing thing is that they have a name. Because they have names you are not forced to think about their internal workings. You can think about a named function as a distinct thing in and of itself, which frees your mind to think about the larger picture and higher level concepts.
Giving names to things is not an alien concept to SQL. As you can see in “The original SQL” we actually gave the third column whose value is
2017 the name “year”. You can also name, or alias other things within your SQL but this falls far short of how we think (or not) about functions.
Common Table Expressions are the closest I have found to being able to name and refer to sections of SQL.
IN the above code we have taken the contents of the
IN (...) statement and moved it into a common table expression called
racesIn2017 at the top. We then have to select from that common table expression within the
IN (...) clause again so in characters typed we have something longer, but we have achieved something else…
There is no
WHERE within the
IN (...) clause any more, that complexity has been moved to the common table expression. WHERE is not difficult to understand but obviously the complexity we could have abstracted away and named in the common table expressions could have been far far greater.
- The ability to name and refer to a section of code is incredibly important because the developer can think about it as one thing, as opposed to the details that make it up.
- Common table expressions, from what I have seen, are optimized individually, not in the context of the main query. This looks to have changed in PostgreSQL 12.
The results are identical to the previous version.