Oh, I've worked with all the major engines. To which we can add: trying to remember which seemingly basic features are supported by which engines, and which aren't (e.g. percentiles are great in Postgres, but you're SOL in MySQL). That there's virtually nothing "standard" about SQL syntax or functionality at all, except for the simplest of queries and the general basic concepts.

We're in the process of migrating our on-premise business application from SQLAnywhere to MSSQL.

Imagine the fun we're having...

I'm really curious, in the practice, what causes a product to decide "we are moving from abcSQL TO xyzSQL" ?

For us there's been two major motivations, and another few lesser.

First and most prominent is that Sybase was bought by SAP and SAP has pivoted SQLAnywhere from a general RMDBS to focus on mobile applications (ie more embedded). Apart from product development changing focus we've struggled to buy licenses for our customers, so the writing on the wall was clear.

The seconds is that we've got some big customers who run their own MSSQL servers, and they've been pushing harder and harder for us to use their servers so they can handle administration and access control directly.

A third motivation is that while we've been primarily on-prem, a lot of customers these days don't want that, so we've started offering cloud hosted options. Since we're a Windows shop Azure made sense, and as such the hosted MSSQL also makes sense and is quite convenient.

Fourth is that, while MSSQL is quite anemic in terms of SQL language functionality, it does support transactional schema updates, which makes schema upgrades much less error prone.

Given that MSSQL syntax support is quite limited and we now have developed tooling and library support for multiple databases, we'll probably also add support for PostgreSQL later.

In that direction, it's usually going to be about robustness -- more tooling, less edge cases, a wider developer base, built-in cloud support, and so forth. The more popular a database is, the easier just everything is.

Occasionally it's going to be some very specific aspect of performance limits, or a feature like spatial indexes that work in a special way. And occasionally it's going to be about cost or better integration with a particular cloud or other specific piece of tooling.

Well Java and JavaScript differ a lot too.

Some similarities, but a lot of differences too.

I agree that CTEs are a solution to some of the pain listed above, but CTEs aren't exotic. Lots of the major RDBMSs support them.

CTEs are an absolute must to get the right "do-this-then-that" approach.