This series of articles

This is the second of the Code In Postgres. The previous article is available here.

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.

All JavaScript examples will assume a reasonably high level of competence.

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 IN (...) clause, which we would have done if it were code.

For the next few articles our SQL will be simple enough that it will not cause a big problem. 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

SELECT
    "driverStandings".points,
    "driverStandings"."driverId",
    2017 as year
FROM "driverStandings"
WHERE "raceId" IN (SELECT "raceId" FROM races WHERE year = 2017)
ORDER BY "driverStandings".points DESC
LIMIT 1

The aim

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 often do not think about their internal workings any more, you think about the function as a distinct thing in and of itself, which frees your mind to think about the larger picture.

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.

The SQL

WITH "racesIn2017" as (
    SELECT "raceId" FROM races WHERE year = 2017
)
SELECT
    "driverStandings".points,
    "driverStandings"."driverId",
    2017 as year
FROM "driverStandings"
WHERE "raceId" IN ( SELECT "raceId" FROM "racesIn2017" )
ORDER BY "driverStandings".points DESC
LIMIT 1

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 greater.

Pro’s

  • 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.

Con’s

  • Common table expressions, from what I have seen, are optimized individually, not in the context of the main query.

Results

The results are identical to the previous version.

points driverId year
363 1 2017

(1 row)