Modern SQL: Inaugural Post

I’ve just launched my new website modern-sql.com. It’s very small right now—just six content pages—but it will grow over next month and years and might eventually become a book like Use-The-Index-Luke.com did. So I though I’d better get you on board right now so you can grow as the site grows.

So what is modern SQL about? Yet another SQL reference? Absolutely not. There are definitively enough references out there. And they all suffer from the same issues:

• They hardly cover more than entry-level SQL-92. Some tutorials don’t even mention that they describe a proprietary dialect.

• The examples just show the syntax but not how to solve real-world problems.

• They don’t document the availability of the features amongst different database products.

No question, the latter ones are a consequence of the first one. However, modern SQL is different:

• modern SQL covers SQL:2016—the current release of the ISO SQL standard. But it adheres to the fact that many databases are developed against older releases or only support a subset of the standard.

• modern SQL shows how to use the most recent SQL features to solve real world problems. It also provides “conforming alternatives” as a way to solve the same problem using other (often older) standard methods. If necessary and possible it will also show “proprietary alternatives.”

  Note that there is a hierarchy: the recommended (most idiomatic) approach is shown first, standard conforming alternatives next and proprietary alternatives last but only if inevitable.

• modern SQL documents the availability of the features amongst six SQL databases (the example is about values):

1. Only without keyword row
2. Requires keyword row: values row('r1c1','r1c2'), row('r2c1', 'r2c2')
3. Needs from clause column renaming • Only without keyword row
4. Requires column names in with clause • Only without keyword row

Click on a feature (e.g., “valid where select is valid”) to see how the availability of the features has evolved.

There you can see that SQLite just recently started to accept values where select is valid. The timeline view also documents the last checked version for those databases that don’t support a feature.

Inevitably, modern SQL will become both: an homage to the SQL standard and a rant about its poor adaptation. The availability documentation and the above mentioned alternative approaches (conforming or proprietary) are there to keep the “rant” a productive one. Look at the “select without from” use-case to get the idea.

This is just the first step of a long journey. I invite you to follow it via Twitter, Email or RSS.