31

Most SQL databases follow the ANSI SQL standards to a degree, but

  1. The standard is ambiguous, leaving some areas open to interpretation (eg: how different operations with NULLs should be handled is ambiguous)

  2. Some vendors contradict the standard outright or just lack functionality defined by the standard (eg: MySQL has a list of differences between the standard and their implementation)

  3. Some databases will behave differently depending on how they are configured, but configuration can be changed to have them behave the same way (eg: Oracle performs case-sensitive string comparisons by default, while SQL Server does them case-insensitve)

  4. There is some functionality that is not part of the standard but is implemented by different RDBMSs anyway, albeit with different names (eg: Oracle's LISTAGG = MySQL's GROUP_CONCAT)

Is there a resource with a comprehensive list of quirks and gotchas to pay attention to when you are trying to write something that is supposed to be compatible with multiple databases?

Deduplicator
  • 44,692
  • 7
  • 66
  • 118
NullUserException
  • 83,810
  • 28
  • 209
  • 234
  • +1 valuable question. One thing to be mindful of is that not only will you encounter problems with compatibility, you will also should consider testing your solutions on the major supported RDBMSs to make sure that there's no obscure performance hit and that there's nothing yet undocumented. At the end of the day you should cover yourself and say which platforms you specifically support while stating that other platforms might be supported later. – Seph Sep 21 '11 at 07:11
  • Indeed a valuable question which, unfortunately, remains unanswered. – FDavidov Nov 29 '16 at 12:33
  • 1
    I like the OReilly SQL quick reference because it shows me a variety of syntax and differences between several dialects. Unfortunately this is a moving target with new functionality and changes whenever new versions come out. There is also http://sqlfiddle.com/ which will let you try out several different versions of servers (oracle, mysql,...) and even versions of a given server (mysql 5.5 and 5.6). – argoc Nov 30 '16 at 02:07
  • 1
    This is a bit old but maybe of some use http://troels.arvin.dk/db/rdbms/ – Steve Ford Dec 05 '16 at 12:54

2 Answers2

5

I'm not sure how comprehensive this list is, but maybe this will help - http://troels.arvin.dk/db/rdbms/

Ruslan Nigmatulin
  • 483
  • 6
  • 14
1

Except of already mentioned some comparison you can find in Wikipedia

Also similar question was already posted on Stackoverflow where you can fin a couple of useful links.

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
Kacper
  • 4,798
  • 2
  • 19
  • 34