Markus Winand

SQL Renaissance Ambassador. Author, Trainer, Coach. PhD in Common Sense. Creator of and

[Markus Winand @MarkusWinand]

Teaching is about making students reach the same level of understanding as the teacher—but with less mental effort.

This allows students to put their mental efforts into growing beyond their teachers.

[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.

[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]

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.

[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

[Markus Winand @MarkusWinand]

Other profiles

https://t.co/ZNYAcyyLVx

https://t.co/NpoUJ2HpXr

https://t.co/ID8JYxgZeE

[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.

[Markus Winand @MarkusWinand]

@Sa07Sanel @slideshare Link: https://t.co/pAA3Rk5Svs

;)

[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.

[Markus Winand @MarkusWinand]

I’m a simple man.

While Oracle people are ecstatic about ASSERTION in the just released version 23.26.1 of the Oracle Database, I’m happy to see something as simple as FILTER :)

🧵

[Markus Winand @MarkusWinand]

Oh, BTW, I’m also ecstatic about ASSERTION, but I’ll need some time to cover it on https://t.co/DbUylcUDcg.

[Markus Winand @MarkusWinand]

I’m a simple man.

While Oracle people are ecstatic about ASSERTION in the just released version 23.26.1 of the Oracle Database, I’m happy to see something as simple as FILTER :)

🧵

[Modern SQL @ModernSQL]

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

https://t.co/XSXvqewwwc

[Markus Winand @MarkusWinand]

FILTER was the first feature I covered on https://t.co/DbUylcUDcg, only supported by PostgreSQL at the time. Today, half the system I cover support FILTER. I cannot understand why there are still systems not support such a simple, easy to understand and useful feature.

[Markus Winand @MarkusWinand]

Database management is more than persistence

A DBMS also provides access rights (GRANT), schema enforcement (types, constraints), schema evolution (ALTER), concurrency control (TX isolation, …), a query language…

Persistence = simple
DB managemant = hard

[Modern SQL @ModernSQL]

Adieu Apache Derby, Welcome DuckDB

https://t.co/mqUHfyLNrq

[Markus Winand @MarkusWinand]

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

[SQL Performance Tips @SQLPerfTips]

Another look into PostgreSQL CTE materialization and non-idempotent subqueries

https://t.co/BprzbiV7bU

[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]

At least it's not dying without notice:

Apache Derby is retired.

https://t.co/YwXgPQdbJj

Below: Poll if people would miss Derby on https://t.co/DbUylcUDcg (not even a year ago).

[Markus Winand @MarkusWinand]

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

[Markus Winand @MarkusWinand]

@ryanguill There is a logical processing sequence (engines might mix that up as long as the result is as though it would have observed this sequence):

WHERE -> GROUP BY -> HAVING (uses the result of GROUP BY) -> WINDOW -> QUALIFY (uses the result of WINDOW !!!).

[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.