Modern SQL

Structured Primary Keys Sometimes I hit a wall when optimizing a client’s query.

Order by Has Come a Long Way aLimited support in union, except and intersect legs bExpressions must not contain selected columns The order by clause is surely one of the best-known SQL clauses.

Order-Equivalent Over Clauses a⚡Order-equivalent over clause can produce different row orders bApparently, the framing must be identical too to make over clauses order-equivalent At the heart of this article, there is a simple question: Will the following…

SQL Comments, Please!

Without Overlaps Constraints aFunctionality available with exclusion constraints bWithout keyword for: PERIOD FOR BUSINESS_TIME (…) cUsing the btree_gist extension and a range: BUSINESS_TIME tsrange GENERATED ALWAYS AS (tsrange(start_ts, end_ts)) STORED S…

Adieu Apache Derby, Welcome DuckDB Apache Derby was once a popular system to mock an SQL-Engine during unit tests.

Group by All: Generate group by from select aCannot mix aggregates with non-aggregated columns: a + count(*) bRemoves the group by all clause if it leads to an error that does not happen without group by clause Some Systems allow the special group by all…

… Is Json [Array|Object|Scalar]: Test for Valid JSON, Distinguish between Arrays, Objects and Scalars a⚡Does not recognize JSON scalars as valid JSON bNo type constraints: … is json [array|object|scalar] cAccepts unquoted object keys: {a: 1} Is json is a…

The ANY_VALUE(…) Aggregate Function: New in SQL:2023 — and a better feature from 1999 a⚡Might return null even if there are non-null values b⚡Not an aggregate function cWithout filter clause dNot as window function (over clause) The aggregate function any…

The Curious Case of the Aggregation Query An interesting query made the rounds recently.