Tuesday, May 29, 2018

SQL "With" statement

I've recently learned about the WITH statement in SQL that had apparently been there since the 1999 standard. It pretty much allows you to define the "temporary variables" (or "temporary views") in the SQL query for common sub-expressions, like this:

WITH
   name1 AS (SELECT ...),
   name2 AS (SELECT ...)
SELECT... FROM name1, name2, whatever ...

This solves a major problem with the SQL statements, especially for self-joins on such pre-computed data, and lets you do fork-and-join in a single statement. I'm surprised that it wasn't used all over the place for the CEP systems.