Modern SQL

Still using Windows 3.1? So why stick to SQL-92? A lot has changed in 30 years. We embrace the standard.

[Markus Winand @MarkusWinand]

MariaDB 12.3.2 was released last week.

It's the first "stable" version of the long-term supported (LTS = 3 years) 12.3 branch.

An SQL dialect enhancement is the IS JSON predicate. Also: a bug I reported is fixed (MDEV-38835).

All charts on https://t.co/DbUylcUDcg are updated.

[Modern SQL @ModernSQL]

On HN: https://t.co/iDZaamwTiV

[Modern SQL @ModernSQL]

ORDER BY Has Come a Long Way

https://t.co/rtU5aLUN6X
The ORDER BY clause is one of the best-known SQL clauses. Yet there are a number of common misunderstandings that are worth clearing up. The article walks you through the evolution of the ORDER BY clause in the SQL standard.

[Modern SQL @ModernSQL]

@damassima For the avoidance of misunderstandings: PostgreSQL *can* use expressions in ORDER BY, but they must not contain the names of columns from the SELECT clause.

[SQL Daily @sqldaily]

Should these functions return the same value?

SELECT
ROW_NUMBER() OVER (ORDER BY x),
ROW_NUMBER() OVER (ORDER BY x)
FROM ...

What about when many rows have the same value for X?

According to the #SQL standard: yes

@MarkusWinand investigates https://t.co/x5AFtRWGhp

[Modern SQL @ModernSQL]

@rwmarti Neither would I expect the order-equivalence rule to cover different SELECTs. The wording is very open. I'm sure it covers different OVER clauses of the same SELECT (and DBMS) though.

[Modern SQL @ModernSQL]

Order-equivalent OVER clauses

https://t.co/CeM9z60F1e

About an important but rarely appreciated aspect when using multiple OVER clauses in a single query.

[Modern SQL @ModernSQL]

SQL Comments, Please!

https://t.co/Vgk1CunCGk

Standard SQL offers two flairs of comments: one starts by two dashes (--) and goes to the end of the line. The other starts with slash-asterisk (/*) and ends with asterisk-slash (*/).

#SQL #DuckDB #PostgreSQL #MSSQL

[Modern SQL @ModernSQL]

Infinite Recursion Guards

https://t.co/V3bLm5gWKh

With recursive is the only SQL construct that is not guaranteed to terminate, eventually. As the DBMS is typically a very crucial system, infinite loops must be prevented at all times. See how various SQL systems help you.

[Modern SQL @ModernSQL]

The just released version 23.26.1 of the Oracle DB supports the FILTER clause.

https://t.co/XSXvqewwwc

[Modern SQL @ModernSQL]

WITHOUT OVERLAPS Constraints

https://t.co/ftx371cz0j

New in PostreSQL 18. Since 9.2, EXCLUDE constraints can be used for the same purpose.

[Modern SQL @ModernSQL]

Adieu Apache Derby, Welcome DuckDB

https://t.co/mqUHfyLNrq

[Markus Winand @MarkusWinand]

https://t.co/j7tt9vKtzL now covers DuckDB.

[Modern SQL @ModernSQL]

GROUP BY ALL

Very popular, not yet standard but set to become standard. Also expected for PostgreSQL 19.

https://t.co/8oqau6wQJp

[Markus Winand @MarkusWinand]

Oracle 26ai¹ was released yesterday.

Noteworthy SQL addition: QUALIFY.

https://t.co/RoZo63LY47

I bet this starts an avalanche (there are already PostgreSQL patches).

¹ 26ai is the marketing version, 23.26.0 is the technical version.

[Markus Winand @MarkusWinand]

After being « gone fishin' » during summertime I just updated my website for the releases that happened during that time:

* MariaDB 11.8 and 12.0
* Oracle 23.9
* Db2 12.1.2

All charts on https://t.co/j7tt9vJVKd and https://t.co/LANd9rXpC7 are updated.

[Modern SQL @ModernSQL]

Just updated https://t.co/xcQW5xMJX0 and https://t.co/Bk1fXje2hj for MariaDB 11.7.

One enhancement is the FROM-clause column renaming, as described in this article: https://t.co/RnujkUKMAf

[Markus Winand @MarkusWinand]

A Christmas present for SQL standard lovers?

Part 1 is available for free again.

https://t.co/bzVCF4la5w

The last edition that was available for free was 2011, AFAIK. And that disappeared when 2016 was released.

Merry Christmas :)

[Markus Winand @MarkusWinand]

The scraping of content for AI training makes some changes required. I'll publish less on my website and RSS feed. The mailing lists will probably stay the same for the time being. If you want to still get everything, subscribe here:

https://t.co/sWsaCfYwDw

[Markus Winand @MarkusWinand]

Would you miss Apache Derby when I would remove it from the charts on https://t.co/DbUylcUDcg?