Actually, in the early days of SQL, the Extended Equality outer join (*=, +=, =, etc) was the rule, not the exception. The difference in it was Oracle did it one way, Sybase did it the opposite way and Gupta (later Centura) let you pick either the Sybase or Oracle method at configuration time. Then Informix had a completely different syntax done in the from clause, to show the preserved and unpreserved tables.
Likewise, ISNULL(), NVL() and some other early functions all behaved a little differently from COALESCE(). The coalesce function looks down the list of options, and finds the highest data type. This becomes the data type of the expression. ISNULL uses the first data type of the first parameter. There was also something weird about NVL that I cannot remember (I do not work that much with Oracle).
The original Sybase T SQL believed in existential import. This was a debate in the early days of mathematical logic, which asserted that "all men are mortal" Implies that at least one man exists. Lewis Carroll held this position, but modern logic does not.
In one edition of SQL for Smarties, I published a lookup table of differences in the math in various SQL products. The IEEE rules for floating-point math were not in place. Nor did things like division by zero versus division by null exist in the standard.
As I said, this is how I made a living. I knew why and where the lack of standards would prevent two different SQL databases from returning the same result. It is very embarrassing when the front end in SQL Server does not agree with your DB2 backend. The myth I see is the belief that* a company of any size will deal with one and only one SQL product*. Multiple databases (often for different purposes like your in memory example) might be in-house, or at a supplier or an external data source.
As Jerry Weinberg showed in his research (Psychology of Programming) decades ago, If you start with the goal (portability, performance, whatever), your code will hit that goal. He also showed that it will hit the goal very often at the expense of all other considerations (AARGH!).