Modern SQL: A lot has changed since SQL-92
Structured Primary Keys Sometimes I hit a wall when optimizing a client’s query. Over the years, I realized that most cases fall into two categories: (1) unreasonable expectations based on the cloud bill, not on the infrastructure’s power; (2) the primary key design. This article explains how primary keys can put tables into walled gardens so that the database schema falls apart into disconnected…
Order by Has Come a Long Way a Limited support in union , except and intersect legs b Expressions must not contain selected columns The order by clause is surely one of the best-known SQL clauses. Yet there are a number of common misunderstandings that are worth clearing up. For that, I’ll walk you through the evolution of the order by clause in ISO/IEC 9075-2:2023—that is, the SQL standard. The…
Order-Equivalent Over Clauses a ⚡Order-equivalent over clause can produce different row orders b Apparently, 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 query always give the same numbering in both columns? SELECT ROW_NUMBER() OVER (ORDER BY x) , ROW_NUMBER() OVER (ORDER BY x) FROM ... I did…
SQL Comments, Please! In a program, a comment is a part of the source code that is ignored by the system. Comments are generally used for two purposes: (1) provide background information for future readers of that code, including our selves; (2) deactivate some code without deleting it yet—aka. commenting out . SQL supports comments too, of course. While SQL comments are generally ignored by the…
Without Overlaps Constraints a Functionality available with exclusion constraints b Without keyword for : PERIOD FOR BUSINESS_TIME (…) c Using the btree_gist extension and a range: BUSINESS_TIME tsrange GENERATED ALWAYS AS (tsrange(start_ts, end_ts)) STORED SQL supports temporal constraints that prevent rows with overlapping time ranges. These constraints are typically used to prevent double…
Adieu Apache Derby, Welcome DuckDB Apache Derby was once a popular system to mock an SQL-Engine during unit tests. A use-case that vanished by the ease of which “the real SQL systems” are available during development nowadays. Thus, the following note might be sad, but nor surprising: “ On 2025-10-10, the Derby developers voted to retire the project into a read-only state. Derby development and…
Group by All : Generate group by from select a Cannot mix aggregates with non-aggregated columns: a + count(*) b Removes 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 syntax as a shorthand for grouping on all select items that do not have an aggregate function. SELECT customers.id, customers.name,…
… Is Json [Array|Object|Scalar] : Test for Valid JSON, Distinguish between Arrays, Objects and Scalars a ⚡Does not recognize JSON scalars as valid JSON b No type constraints : … is json [array|object|scalar] c Accepts unquoted object keys: {a: 1} Is json is a predicate, similar to is null , to test something for valid JSON content. The test can also distinguish between Arrays ( [1,42] ), Objects (…
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 c Without filter clause d Not as window function ( over clause) The aggregate function any_value takes a non- null value from a group of rows. SELECT product_id , ANY_VALUE(order_id) AS example_order_id FROM order_lines GROUP BY…
The Curious Case of the Aggregation Query An interesting query made the rounds recently . The related blog post by Justin Jaffray compares four queries, one of them catching everybody’s attention. I invite you to have a look for yourself. First, the table definitions: CREATE TABLE aa (a INT); INSERT INTO aa VALUES (1), (2), (3); CREATE TABLE xx (x INT); INSERT INTO xx VALUES (10), (20), (30);…