Full list of differences between different versions of SQL (MySQL, SQL Server, etc?)

Question

I was wondering about the differences between dialects of SQL. I looked here and here but either I couldn't understand the answers or they weren't enough. I'm relatively new to SQL, and so I can't really find a comprehensible answer.

Answer 1

On a coder level, you generally want to be aware of ANSI sql and track how the databases differ from that.

Regular select/insert/delete/update commands are fairly portable. Generally speaking, things that will be different are non-core-syntax aspects like:

• functions. any getXXX(), rtrim(xxx) are, more often that not, vendor specific. Anyway, functions tend to mess up index use as well, so best used sparingly.
• update/from, delete/from. Those are, I believe, vendor-specific.
• dates. are surprisingly non-portable, with everyone and his dog having a different way to specify date masks for example.
• procedural/trigger extensions. Any procedural language is likely to have a lot of scope for vendor-specificity.

• case sensitivity. some databases are all UPPERCASE, some all lowercase, some are insensitive.

• any "extra stuff" in a query that isn't a column. Things like LIMIT, rownum, rowid are generally vendor-specific.

• old outer join syntax, like 'e.department_id = d.department_id(+)'

• "complicated queries". Let's say you want to flush out order lines for orders that have been entirely fullfilled.

  delete from order_lines where not exists (select 1 from order_lines s where s.order_no = order_lines.order_no and s.status <> 'fullfilled')

  DB2, in the past at least, would have likely complained about a "correlated subquery", which basically boils down to referencing a table which you are in the process of modifying, order_lines in this case.

Basically, you should kinda be OK if you stick to older ANSI SQL. I worked on PeopleSoft for years and it was deeply drilled into us to write multi-db sql (DB2/Oracle/MSSQL, etc...), without resorting to writing vendor-specific code (i.e. 1 query for Oracle, 1 for MSSQL...). It can be done, just requires a suspicious mindset about anything that looks like vendor shortcuts & sql enhancements, regardless of how attractive they are.

For specific queries, head off to sqlfiddle.com and you can test a number of cases. And, unfortunately, I don't know of any books that aim to teach actual, as opposed to proposed, ANSI SQL. And, even more unfortunately, the worthies @ ANSI SQL standards have not seen fit to make their standard freely available.

At an admin level, such as all the DDL for create table/index/..., there are way more differences to be aware of, starting with datatypes.